Ms-access – Assign form field value to all records in subform

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.

Access can be a bit funny about filtering, especially on subforms.

Try adding explicit code to your Combobox code to clear the filters every time. So it would look something like:

Me.subformname.Form.Filter = Null
Me.subformname.Form.FilterOn = False
Me.subformname.Form.Requery

Alternatively, you could try dynamically resetting the RecordSource of the subform on every change in the Combobox. (Note that if you do this, you don't have to explicitly requery, as it is done automatically.) So in place of the code you have in the Combobox now, you'd have somthing along the lines of:

Dim mySQLString as String

'Store subform query
mySQLString = "SELECT qry_listato... <<The rest of your query here>>>"

'Set Subform RecordSource
Me.subformname.Form.RecordSource = mySQLSTring

Hopefully one of those helps you out.