Link Child/Master fields using VBA ?

[ad2]

Right now I have a details data entry subform on the Main data entry form.

Because the subform takes up so much screen space (I want to add a second subform) I want to have the subforms open as pop-up forms from a command button.

Is there a way to create the Link Child and Link Master fields in code on the pop-up forms? I not sure how to link the forms as the Link field are only available in design view on a true subform.

 

[TomHW]

You can really think of the link child and link master fields as an additional where clause in the recordsource of the subform. Just use this idea when creating your popup forms.

Assumptions:
- When the popup form is open, the main form's record will not change (It can, but the popup form will not change with it)
- Your Link Master Field is called 'MainID'
- Your Link Child Field is called 'SubID'
- Your popup form is named 'frmSubForm'

In the OnClick event of the command button use code similar to:

Dim strForm As String
Dim strFilter As String

strForm = "frmSubForm'
strFilter = "[SubID]=" & Me![MainID]

DoCmd.OpenForm FormName:=strFilter, WhereCondition:=strFilter

This will open the form with its recordset filtered to only data that is linked with the main form.

Now if you want the popup form to change when the record on the main form changes you might consider looking into the WithEvents keyword and possibly hooking the popup form to the main forms OnCurrent event, but that will be much more complicated.

Hope this helps,
Tom