Open form carrying forward data

[adams.bria]

Here is what I would like to do. I have a "client center" where you bring up the demo data on a client. On frm_cli_center, I have buttons to open 4 different forms. When a button is clicked, I would like it to open the form, carrying forward the empl_id of the current record on the client center. If that empl_id already exists, I want it to open with the data that was already entered. Any help?

 

[Pat Hartman]

Take a look at the arguments for the DoCmd.OpenForm method. You'll find at least two arguments that will do what you want (filter and where). I normally use the Where argument which is a WHERE clause without the word WHERE. The same arguments are available for the OpenForm Action (macro) also.

 

[John Big Booty]

You can use the OpenArgs portion of the OpenForm command to pass information from the calling form, and then check that information in the On Load event of the form being called.

 

[Pat Hartman]

Yes, the OpenArgs could be used for this purpose but why force yourself to write code in the report also when you only need code in the form if you use the filter or where argument?

 

[adams.bria]

I am getting compile error, variable required, can't assign expression on the stremplid of If Len(strEmplID) > 0 Then.

here is what I am opening the form with

Private Sub Open_Client_Info_Click()
DoCmd.OpenForm "frm_client_info", acNormal, , , , acFormAdd, [Empl_ID]
End Sub

and here is the load code

Private Sub Form_Load()
Dim strEmplID() As String

strEmplID = Forms!frmclient_center.OpenArgs
If Len(strEmplID) > 0 Then
    DoCmd.GoToControl "empl_id"
    DoCmd.FindRecord strEmplID
  strOpenArgs = Split(Me.OpenArgs, ";")
End If
End Sub

Sorry this is my first time trying to do this, thanks.

 

[John Big Booty]

Try;

Private Sub Open_Client_Info_Click()
DoCmd.OpenForm "frm_client_info", acNormal, , , , acFormAdd, Me.Empl_ID
End Sub

and

Private Sub Form_Load()
Dim strEmplID() As String

strEmplID = OpenArgs
If Len(strEmplID) > 0 Then
    DoCmd.GoToControl "empl_id"
    DoCmd.FindRecord strEmplID
  strOpenArgs = Split(Me.OpenArgs, ";") [COLOR="SeaGreen"]' I'm not sure what this line is doing ??[/COLOR]
End If
End Sub

 

[adams.bria]

My mistake, that line was from a previous experiment trying to get this to work.

I am still getting the compile error on strEmplid of "If Len(strEmplID) > 0 Then"


I've uploaded with appropriate forms/tables. Thanks

 

[John Big Booty]

Are you able to save that back to Access '03 as I don't currently have access to an '07 version.

 

[Pat Hartman]

If you want the popup form to populate its foreign key based on what is in the OpenArgs, then use the BeforeInsert event. You don't want to dirty the record before the user does. You'll have nothing but trouble.

Me.ForeignKey = Me.OpenArgs
or you can reference the calling form if the popup is only called from one other form.
Me.ForeignKey = Forms!NameOfCallingForm!SomeControlName

You would still use the where argument of the OpenForm method to select an existing record since that doesn't require any code in the popup form.

 

[adams.bria]

Hopefully this works. Kept getting an error when I tried to convert using the access converter.

Pat, thanks for your help. That makes sense that you wouldn't want it to populate the FK before the user enters something. However, I am not really sure I understand what you are describing. Using the beforeinsert event you would create a command that populates the FK from the openargs data? So then you would also add a filter to the form open that would go to the open args empl if it exists? Is that correct?

 

[John Big Booty]

No seems to be corrupted. I should be able to look at the '07 version (it is '07 and not '10?) latter this arvo.

 

[Pat Hartman]

You don't need the code you currently have in the Load event if you use the where argument of the OpenForm method. The where argument controls filtering the popup form without requiring any code. You would use the where argument of the OpenForm method to specify which record the form should open to. If the record exists, the form will open already populated. If the record doesn't exist, the form will open to an empty record. Usually "child" forms are created as subforms and the master/child links control populating the foreign key that links the child to the parent but occassionally, we need a popup form. That is what I assume you are creating. Since there is no master/child link to sync the main form with the popup, you need to use other methods. The "where" argument controls which record the popup opens to, and a single line of code in the popup form's BeforeInsert event takes care of populating the foreign key so that the new record is "linked" to the parent record on the main form. You can use the OpenArgs to pass the PK of the parent record to the popup form or you can have the popup form refer to the PK control on the parent record's main form.

 

[adams.bria]

Ok, if you wouldn't mind helping, is this how it should be designed?

The data that needs to be filled out should be designed as a sub-form. The original form would be based on whatever table contains the original PK. Thus when the user clicks from the client_center, it would load with the Empl_ID on the master, as that is already entered. Then (using beforeinsert) when the user types into the form, it will pull Empl from the master, and populate into the sub_form table? Is that accurate?

 

[Pat Hartman]

I created a sample with two tables, two forms, and the only two lines of code you need to make this work plus a couple extra that save the parent record before opening the popup.

 

[adams.bria]

Ok that works awesome! Thank you so much for the help!