Ask Your Question

How do I open an add data form with computed answers?

asked 2017-06-18 15:01:13 +0200

David_Ssc gravatar image

updated 2017-06-23 12:55:10 +0200

I have a form that I use to add new data to the database. I want to preset certain data values based on other information already entered elsewhere. If I run the following code directly, the values do not show on the screen - if I run it in debug mode line by line, the value of txtAliasFirst does show on the data entry screen:

Sub CEOpen(oEvent as object)
' Open form
    oContainer = ThisDatabaseDocument.FormDocuments.getByName("CEntry")
    Me = oContainer.component.getDrawPage.getforms.getbyindex(0)    
    Me.getByName("txtAliasLast").text = sAliasLName
End sub

What command do I need to make the data visible?

Edited 6/22/17 I am running into a Function Sequence Error when I try to save the data in the opened form. Here is the situation:

I now have the button on a prior form execute this macro to open form "CEntry" to add a new record:

Sub CEOpen(oEvent as object)
  ' Open form
    ObjTypeWhat =
    ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat,"CEntry", FALSE) 
End sub

A second macro is tied to the When Loading Event of the form:

Sub CEInit(oEvent as object)
    Me = ThisComponent.Drawpage.Forms.getByName("fCEntry")  ' fCEntry' is the name of the Main_Form
    Me.getByName("txtClearance Performed By").text = mUser
    Me.getByName("txtClearance Performed By").commit()
' . . . several other screen fields are loaded from Global variables like mUser
End Sub

There is also a button on this screen that reads in values for additional screen fields from a CSV file. The user has a chance to edit the data brought in from the Global variables, the CSV file, or add additional data entered by hand. All data on the screen appears correct.

The error arises in a Save button macro:

Sub CESave (oEvent as Object)
    Me = oEvent.Source.Model.Parent
    oDatasource = ThisDatabaseDocument.CurrentController
    If NOT (oDatasource.isConnected()) THEN oDatasource.connect()
    oConnection = oDatasource.ActiveConnection()
End Sub

The Me.UpdateRow() generates the Function Sequence Error. (Me.isModified and Me.isNew are TRUE. I have compared the Me objects in all four macros and it appears to refer to the same oDatabaseForm). In addition, if I close the window manually and say Yes to saving the changes, all the data is saved correctly. What am I missing???

Thanks, David

edit retag flag offensive close merge delete


@David_Ssc This edited section should be presented as a new question as the original question has already been answered.

Ratslinger gravatar imageRatslinger ( 2017-06-23 05:49:23 +0200 )edit

Thanks - still learning the forum etiquette. I also figured out the answer Me.insertRow() instead of Me.UpdateRow() without the auto-numbered ID field.

David_Ssc gravatar imageDavid_Ssc ( 2017-06-23 13:35:12 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-06-18 16:31:59 +0200

Ratslinger gravatar image

updated 2017-06-18 19:23:07 +0200

Your code is certainly incomplete. To open a form use:

ObjTypeWhat =
If ThisDatabaseDocument.FormDocuments.hasbyname(FORM_TO_OPEN) Then 'Check the form exists'
    ThisDataBaseDocument.CurrentController.Connect() 'If the form exists connect to the database'
    ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, FORM_TO_OPEN, FALSE) 'Open the form'
    MsgBox "Error! Wrong form name used. "+chr(10)+"Form Name = " & ObjName
End if

Once the form is open, you can access your form with:

oForm = ThisComponent.Drawpage.Forms.getByName(INTERNAL_FORM_NAME)

Then using oForm you can access your controls. Your code does not show where sAliasName is coming from.

If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more


The code listed above with "CEntry" substituted for FORM_TO_OPEN and "fCEntry" for the INTERNAL_FORM_NAME" erred on the oForm instruction with "An exception occurred Type: Message: ." The form "CEntry" did open, though.

David_Ssc gravatar imageDavid_Ssc ( 2017-06-18 19:22:16 +0200 )edit

You'll notice the presented code is not together. Your question doesn't explain the steps you are using. I can only guess you have a form where you enter some data. Then you open another form where you want to use the data already present on the other form. Is this close? If so, you need a lot more code.

Ratslinger gravatar imageRatslinger ( 2017-06-18 19:31:22 +0200 )edit

I have a main menu screen that opens the database connection and loads a Global variable sAliasLName. On that form I have a button that opens the CEntry screen for entering new data. The macro tied to the button opens the form and sets four record fields, one of which is "txtAliasLast". Interestingly, one of the fields is also a date field which I am computing with NOW() to be today's date - and it is visible on the opened record. However, the four other text are not. Single stepping shows all

David_Ssc gravatar imageDavid_Ssc ( 2017-06-18 20:03:35 +0200 )edit

The macro attached to the button - this can be used to open the new form. However, the newly opened form cannot be accessed from that same macro. There is no recognition of the other form so you can't access controls on one form from another form easily. When opening the new form, attach a macro to the On Open event of the document, In that macro, get your controls and fill from whatever Global variable(s) you have set from the other form.

Ratslinger gravatar imageRatslinger ( 2017-06-18 20:29:00 +0200 )edit

@David_Ssc If you want to look at a sample - click here. There is a sample in my answer. It opens a new form using the record information from the form opening it.

Ratslinger gravatar imageRatslinger ( 2017-06-18 21:02:14 +0200 )edit

Splitting the macro into two with all of the loading of fields in a When Loading event worked great. Thanks.

David_Ssc gravatar imageDavid_Ssc ( 2017-06-18 23:31:35 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-18 15:01:13 +0200

Seen: 35 times

Last updated: Jun 23