Code:
Private Sub cboRev_AfterUpdate()
Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "' AND Revision ='" & Me.cboRev & "'"), "")
Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
'Load AlternateA into the drop down
On Error Resume Next
txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
"FROM DRAWINGS " & _
"WHERE DRAWINGS.Primary = '" & Me.txtComponent & "'" & _
"ORDER BY DRAWINGS.AlternateA; "
'Load AlternateB into the drop down
On Error Resume Next
txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
"FROM DRAWINGS " & _
"WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' AND DRAWINGS.AlternateB Is Not Null " & _
"ORDER BY DRAWINGS.AlternateB;"
'Load AlternateC into the drop down
On Error Resume Next
txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
"FROM DRAWINGS " & _
"WHERE DRAWINGS.Primary = '" & Me.txtComponent & "'" & _
"ORDER BY DRAWINGS.AlternateC;"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String 'use this especially if the recordset is to be base on a sql statement
'***
'do one or the other of these, not both
'Set rs = db.OpenRecordset("name of your query")
Set rs = db.OpenRecordset(strSql)
'***
'If you only care if there is at least one record, then this will do
If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
'If you want to know how many records were returned, then this before the counting line above
'rs.MoveLast
'rs.MoveFirst 'if you want to go back to the beginning and perform a loop on the records (which you don't)
MsgBox ("A: " & rs.RecordCount)
'close recordset and reclaim memory before exiting procedure
rs.Close
Set rs = Nothing
Just as a disclaimer, the "txtAlternateA" is actually a combobox (not a text box) and it is too much hassle to rename it and adjust all my code.