Auto populating a field on form from another open form

[sickenhoofer]

I have a form, 'frmProviderMain' with a macro which opens another form, frmEnrollmentProcessing.The macro is located on a tab on 'frmProviderMain.' I would like one of the controls/fields on the secondary form, specifically [frmEnrollmentProcessing].[ProviderMainID], to fill in based on the selected provider on 'frmProviderMain'.

I attempted to set an event procedure which would set the value when the form loads as follows. However it does not work. As I am new to VBA, I am not sure if I have the language wrong, or if I am attempting it in the wrong way (and Access 2007 HELP is no help at all).

Here is the code I was using. . . . . .

Private Sub Form_Load()
Me!frmProviderEnrollmentProcessing.Form!ProviderMainID.Value = Forms!frmProviderMain.Form!ProviderMainID
End Sub

I appreciate any help, or suggestions for a better way to do this.

[sickenhoofer]

I noticed that the posting appears to have a space in the middle of 'ProviderMainID.' However, there is no space in the code from which it was copied.

[ChipR]

Two ways to do this. I don't particularly like to reference controls on other forms, but you would use:

If CurrentProject.AllForms("frmProviderMain").IsLoaded Then
  ProviderMainID = Forms!frmProviderMain!ProviderMainID
End If

You would need the .Form if you were trying to reference a control on a subform. All that is explained in this chart (start at the top with "If you are on").

The other way is to pass the value as the OpenArgs argument of the DoCmd.OpenForm, like:

'Somewhere in frmProviderMain code
  DoCmd.OpenForm "frmEnrollmentProcessing", , , , , , ProviderMainID
 
'In frmEnrollmentProcessing
Private Sub Form_Open(Cancel As Integer)
  If IsNull(OpenArgs) Then
    MsgBox "No ID Number!"
    DoCmd.Close acForm, "frmEnrollmentProcessing"
  Else
    ProviderMainID = OpenArgs
  End If
End Sub

If you need to pass more than one value, you can combine them in one string with a delimiter, then use Split to separate them in the other form.

DoCmd.OpenForm "Sample", , , , , , value1 & "+" & value2
 
'Sample Form_Open
  Dim strArgs() as String
  strArgs = Split(OpenArgs, "+")
  value1 = strArgs(0)
  value2 = strArgs(1)

[sickenhoofer]

Thanks for your response. I understand the first solution. However, I need to clarify something on the second one.

Am I correct in understanding that in the subprocedure you listed above (lines 4-12 in 2nd example), that the form (frmEnrollmentProcessing) would close if the providermainID field is null? Closing the form would not be the desired effect.

frmEnrollmentProcessing is a form into which data is entered, but I do not want it to be set to DataEntry = yes. I want the form to display all processing events (records) related to the provider on main form. A provider may not have begun enrollment processing yet . . .in which case it should open to a blank record. So, I wouldn't want it to open and then close if the control ProviderMainID on frmProviderEnrollmentProcessing is blank.

Sorry, if I am being dense. Did I say I was new to vba?:):)

In the event that I wasn't clear above, here is some clarification on set up:
Provider Main Info
*tblProviderMain (PK =ProviderMainID - Autonum)
*qryProviderMain (feeds frmProviderMain)
frmProviderMain (view only form)

Enrollment Processing
*tblEnrollmentProcessing (PK = EnrollmentProcessingID - Autonum) . . Also has ProviderMainID (joined globally)
*qryEnrollmentProcessing
*frmEnrollmentProcessing (opens via macro on a tab on frmProviderMain)

Thanks, for your help.

[ChipR]

Yes, in the example, the form would be closed. You can leave that out completely, or take other action as your application requires.

It sounds like you may want to simply filter the records in the form that you open using the WhereCondition argument, like:

DoCmd.OpenForm "frmEnrollmentProcessing", , , "ProviderMainID = " & ProviderMainID

See OpenForm Method for more information.

[sickenhoofer]

I do want to filter as well, I just didn't list that, because I think I can get that part. My main goal was to eliminate the end user from typing the ProviderID in the field that joins it to the specific provider. In fact, that control is locked.

Although, now that you have me thinking . . . . once the filters are applied, will it automatically fill in the provider ID as they begin typing in the other fields? I know it does it for the Enrollmentprocessing Id (PK for that table).

[ChipR]

I see what you mean now, and I suppose a new record would not fill in the Provider ID automatically. Nothing better than using the OpenArgs comes to mind. In fact, you might have to save that ID in an invisible text box, and set the Provider ID equal to that when moving to a new record. Setting it just the one time in Form_Open will not be sufficient if more records will be added.

[sickenhoofer]

I appreciate all your feedback. I'm going to give the OpenArgs with the invisible text box a whirl tomorrow. Thanks, again!

[NeoPa]

You've been well lead here (Filtering; OpenArgs; etc), but I suggest setting the .Default of the appropriate control as soon as the form is opened, rather than trying to set the value over and again.