Base macro that opens a new/clean record in another form

[meakasteve]

I have a form that lists customer’s names and addresses in a grid. I can highlight a particular row, press a button on the form and another form opens up displaying a lot more details about that customer. Both the first form (with the button) and the second (with the details) are based on the same table. The macro which achieves this and is attached to the first form is based on Ratslinger’s solution to my question “what code do I need in a Base macro to access a field in highlighted row in a datasheet” from Oct. 14 2016. His solution captures the primary key associated with the customer highlighted in the first form and uses it to display in the second form the data relevant to that customer.

I am now struggling with writing a second macro which would be attached to a second button on the first form and would cause the second form to be opened (irrespective of which row is highlighted in the first form) at a new record. I could then enter details about a new customer on this second table and when this second form is closed this data would be stored in the table (and be displayed in the list of the customer names and addresses in the grid of the first form).

Basically I want a macro that opens up a new/clean record in second form in which data can be entered and which automatically stores the autovalue of the new primary key number for the table. The nearest reference to this problem I have found on the internet is Creating macros in Base (View topic) • Apache OpenOffice Community Forum. But it has so far not helped me. If anyone in the the community can help me I would be most grateful.

[Ratslinger]

This is based upon using the global variable to store the key. First (main) form. New button tied to macro:

Sub GetNew()
    iCustID = 0
	FormChange("CUSTDATA")
End Sub    

Now when new form opened you already have a macro attached to its’ opening. It needs to be modified. Based on last supplied code:

Sub	OpenFormAtRecord()
	Dim oForm As Object
	Dim sSelect As String
	oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
'if form opened without call from another form exit
'	If iCustID = 0 Then Exit Sub
	If iCustID = 0 Then
	    oForm.moveToInsertRow
	Else
'Set filter to global variable
'CUSTID here is the table KEY field
    	sSelect =  "( CUSTID = " & iCustID & " )"
		oForm.Filter = sSelect
'Reload form to get the specified record.
		oForm.Reload()
		oForm.Filter = ""
		iCustID = 0
	end if
End Sub

The change is in the If statement. Instead of exiting with a 0 key present, a new record is presented. If not 0, then the else portion executes to get the previously selected record.

Hope that’s clear. Pretty minor change.

Edit 2018-05-09:

This is mainly in response to the recently added comment.

While this particular answer uses Global variables, the process can be done without them. Although I did not see this post until after generating a sample, it is simple enough to see that with a little effort in searching you can most likely find the answer already published. To access one form from another the key is to use ThisDatabaseDocument in the macro. The referred to post → Open form via macro in Libreoffice Base.

To carry this one step further, attached is a sample. The sample is based upon the original question and presents macros using both methods. The forms to use are: CUSTOMERS & CustomersNoGlobal. Selection on each will open the related Customer Data form.

Sample - PassFormDataWithNoGlobal.odb

The answers are out there. You need to put in a bit of effort sometimes to find them!

[meakasteve]

Many thanks Ratslinger. Yes it was only a minor change but that testifies to the elegance of your original code!

[Xoristzatziki1]

This maybe is a good temporary solution but is very buggy, since such a global variable is very easy to be “pollute” any other button-form that will try to open the form without setting the variable. Even the variable itself may be used for other purpose, which means a very specified variable should be used for every macro that will need to do the same thing for other forms. For the moment I use this solution but I came to a point that I messed my variables…

[Ratslinger]

@Xoristzatziki1 (forget old user name? - xoristzatziki) This answer was specific to an on-going set of questions and is not meant to be a fix all solution.

For what you specify, global variables are NOT necessary to do the same thing. Open new form; access through current form (using parent); make appropriate modifications to newly opened form; Close original form. It’s all a matter of logic and how to use macros. Have now tested same process without Global variables & have no problems.