Highlight unbound subform record that matches other subform record

[maw230]

I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted.

I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:

Private Sub Accounting_Unit_Enter()
'find where AUs match. only works for first Subform2 record
If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then
msgbox "Match"
End If
End Sub

My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1, but I'm not sure how to accomplish this.

 

[maw230]

Here is what I have so far. This works on the very first record of subform1, but none after. "AU_View2" = main form. "Notes_subform" = subform2. Code is embedded in 'AccountingUnit' Enter event of subform1.

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = Forms("AU_View2").[Notes_subform].Form.Recordset
If Me.AccountingUnit = rst![AccountingUnit] Then
msgbox "12"
End If

This code sometimes produces the error "No current record" on this line:

If Me.AccountingUnit = rst![AccountingUnit] Then

 

[jdraw]

What are you trying to do in plain English?

How is/are Mainform linked to Subform1 and subform2?

You have no looping in your code--is that what you intended?
You don't appear to OpenRecordset???

 

[maw230]

What are you trying to do in plain English?

highlight the notes_subform record where its AccountingUnit (AU) matches the current Main_subform AU. So when the user tabs through the AUs on the Main_subform it will cause the AU(s) on the notes_subform to be highlighted.

How is/are Mainform linked to Subform1 and subform2?

The mainform isn't linked to either subform nor are the subforms linked.

You have no looping in your code--is that what you intended?
You don't appear to OpenRecordset???

I don't know what looping I would need, and I don't know what OpenRecordset does.

See my wonderfully crafted answers above.

 

[jdraw]

Yes, beautifully crafted with bolding -- but we have no idea where all these pieces fit.
Perhaps someone else can guess what you are doing and resolve the puzzle pieces. As for me, I'd like to see a copy of the database -remove anything private/confidential.
I'd like to help, but I don't have any idea how your code is supposed to help do what you want.

I also recognize you have over 400 posts, so you know something about giving the reader some context for your issue/problem/opportunity.

Good luck.

 

[maw230]

I thought I was quite clear in what I was trying to accomplish. I want to highlight records in one form that match records in another based on a common field. While I understand the desire to see the db there are times such as this one when it didn't occur to me that it would be necessary. That's probably why I need this forum in the first place.

I should have posed my question more generally I think (i.e. "How can I highlight records in one form that match records in another based on a common field?"), and I'm sure there would still be a need for context, but some general ideas could be tossed around.

Aside from the coffee fueled diatribe posted above I have decided to pursue another method that's cleaner than highlighting and apparently much easier, but I thank you for your help.

 

[jdraw]

Typically when you have 2 sets of records with a common field, and you want to know what is one and not the other (unmatched), you could run a query.
Anything not unmatched is matched.