Display a record on subform based on form's co

Question

I have a main form, which has a combo box i've named FLRecCombo. This was originally set up so that selecting an option in the combo box would move to that record on the main form's record source.

I then also had subforms on the main form, which when initially set up would move to their record in their record source based on whatever record source the main form was currently on using a common field (i.e. you could use the combo box to move to a given record on all subforms).

Note how all this is in the past tense...

I wanted to add some code to the FLRecCombo AfterUpdate event in VBA; unfortunately this seems to have cut the relationships between combo box, main form and subforms. I think possibly there was a macro created in the FLRecCombo AfterUpdate event, which I've brashly overwitten by going through the code builder.

How might I reinstate the behaviour I initially had? I've tried a method that was suggested on this website; here's the object references:

• Subform name: Finance_FunderAllocation subform
• Subform record source: Finance_HeadRec_FunderAllocation
• Subform record source field name to match: Funding Line
• Form record source field name to match: FundingLine

Here's the code that I tried:

Private Sub FLRecCombo_AfterUpdate() 

    With Me.[Finance_FunderAllocation subform].Form.Recordset
        .FindFirst "Funding Line=" & Me.FLRecCombo
    End With 

End Sub

Access debugger doesn't like this though, saying:

Run-time error '3077':

Syntax error (missing operator) in expression.

And then it highlights the line of code that starts .FindFirst

Any help would be much appreciated!

Answer 1

That should read:

 .FindFirst "[Funding Line]=" & Me.FLRecCombo

Or possibly, if funding line is text:

 .FindFirst "[Funding Line]='" & Me.FLRecCombo & "'"

You need the square brackets because you have a space in your field name. For your own sake, consider getting rid of all spaces in table and field names.

Finally, you can do an awful lot with link child and master fields for subforms, including filtering the contents of the subform without any code.

 Link Master Fields: MyCombo
 Link Child Fields : [Funding Line]