Ms-access – Access 2007 best way to link multiple subforms to main form with tabs

Say you have a form named frmMain. That form includes two fields in its record source: FigureID; and Figure_name. The form also includes a text box control named txtFigureID which is bound to the FigureID record source field.

frmMain also contains a subform control based on a form named frmSub. The record source for frmSub also includes FigureID and Figure_name fields. The subform control's link master/child field property is Figure_name. Therefore frmSub will display all the rows where Figure_name matches the corresponding value in the frmMain's current record.

Now, if you want frmSub to exclude the specific record (as identified by the unique FigureID value) which is the current record in frmMain, add a WHERE clause to frmSub's record source query:

WHERE FigureID <> Forms!frmMain!txtFigureID

I'm only guessing here, but hope that description is close enough to your actual situation to be useful. If not, show us the SQL you're using as the record source for your subform.

Edit: You get the parameter prompt only when you first open frmMain. Afterwards, you can navigate between records in frmMain, and frmSub shows you only the records you want to see ... without asking you again to supply a parameter value.

The reason that happens is because the subform loads before its parent form ... so a control on the parent form is not available when the subform loads.

I think the cure may be to save the subform without the WHERE condition in its record source. Then, when the main form loads, it can re-write the subform's record source to include the WHERE condition.

So, in frmMain's load event:

Private Sub Form_Load()
    Dim strSql As String
    strSql = "SELECT FigureID, Figure_name FROM YourTable" & vbCrLf & _
        "WHERE FigureID <> Forms!frmMain!txtFigureID"
    Debug.Print strSql
    Me.subformControlName.Form.RecordSource = strSql
End Sub

Watch out for subformControlName. It's a control, not a form. The subform control may have the same name as the form it contains. But it could be a different name.

I don't see ANY tab control on your example form. There is a difference between a tab control on form as opposed to a navigation form.

The suggesting here is to use a tab control here. This is the control here:

So, you don't want to use a navigation form for doing this, you want to use a tab control.

A navigation form causes a FULL re-load of the form. And MORE important is when you switch to a different tab on a navigation form, it CAUSES A FULL UN-LOAD of the existing form, and re-loads in place a new form (so yes, in these cases, no only does a form load/unload occur, but record position is lost).

So you want to use a tab control here, not a navigation form. A navigation form is really only a sensible choice for the top most navigation, NOT for sub forms that belong to a given parent form. In this case, you find using a tab control does the trick.

And you will have to delete existing controls and re-drop them from the ribbon tool box to be able to place such controls on a GIVEN/SINGLE tab control. Or you can CUT + PASTE existing controls on a form onto the tab control

Likely best way to do this is to highlight all controls on the form, move them down to make room for the tab control. You then drop in the tab control. You then select the controls you want to place on a particular tab (the ones you just moved down). You then choose cut, and then click on the page (tab control) you want, and do a ctrl-v. (or as noted, just re-drop the controls onto the tab control from the ribbon).

So the confusing here was due to dropping in a navigation form as opposed to using a tab control. Using a navigation form will not quite work for this task.