Tab Forms Syntax Problem

[DFINN]

Hi,

I have a main form which consists of 5 tab pages. Each of these pages has a sub form. I have a search page where a user may input search criteria to filter out the data accordingly on the customer detail page which is on another tab.I am having problems with the syntax that should be used. The syntax I use prior to putting these subforms on the tab pages was as follows:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim DocName As String
Dim LinkCriteria As String

DoCmd.Echo False

DocName = "frmCustDetails"
LinkCriteria = "[Name2] = Forms![frmMainMenu]![SurnameSearch]"
DoCmd.OpenForm DocName, , , LinkCriteria

DocName = "frmMainMenu"
DoCmd.OpenForm "frmMainMenu"
DoCmd.Close

DoCmd.Echo True

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Error$
Resume Exit_Command20_Click

End Sub

Thanks.

 

[Fizzio]

How does it now not work or where does your code stop at?

 

[DFINN]

Hi Fizzio

The problem begins with the link criteria. I am receiving a prompt to enter a parameter value for both forms to link. I think I have a problem with the form reference. Do I need to be referencing the tab page somewhere in the link criteria? If I manually type in the parameter values the sub form opens, however I would like to proceed to this sub form which is on one of the other tab pages.

 

[Fizzio]

This is where MainForm / Subform Linking gets a little messy. If you want to synchronise your subforms (on Tabs or not - it Doesn't matter) to the value in another subform, you have to do it like this.

1. Create a field on the mainform with the controlname on the main form the same as the fieldname on the subform.

2. Set the controlsource of this field to refer to the control in the initial subform.

3. In the link criteria, force that field name in both the Master and Child ID (Do not let access do this, it will not 'see' your field on the main form.

4. Do this for all the subforms you want to link to the initial subform. (Make sure they all have the Link field available in each subform.)

Told you it would get messy. Get back if you are stuck as it can be difficult to set up.

 

[DFINN]

1. I've created a field on the mainfrom with the control name linking to the search field name on the initial sub form. I've also done this on the other tab page which I am trying to link to.

2. I've set the controlsource of this field to refer to the control in the subform.

3. I've set the Master and Child link ID to refer to the new control name on the main form. Is this correct?

4 According to your step 4, Do I need to set up a control field within the sub form which links to the main form control?

 

[DFINN]

Can anyone help with the above problem.I've been working on it for days.

Thanks

DFINN

 

[Fizzio]

You have set it up correctly. You do not need to place a new control on the other subforms, just on the main form. The setup allows you to do this

Eg Main Form has all customer personal details
Subform1 contains each order for that customer
Subform2 contains the items in each order.

Subform1 is linked to MainForm via CustomerID
Subform2 is linked to Subform via OrderID (Method I explained above)
Is this your situation. It can be complex so I'm happy to have a look if you compact and zip it to me.