Problem with referring to Query in VBA

[AinsleyW]

I am attempting to do a search before an entry is made in a text box of a Form. The source of the search is a query named Fan Equipment Log.

If an entry is made in the text box that is also in the Query then a message box should generate.


The code on the BeforeUpdate property of the text box in the sub form is as follows:


Private Sub EquipmentNumber_BeforeUpdate (Cancel As Interger)

If Me.EquipmentNumber = Query![Fan Equip Log]![EquipmentNumber] Then

MsgBox "Fan is already out. Choose another Fan."

End If

End Sub


The code above produce the compiler error:

Qualifier must be collection.


Could anyone help in sorting out this for me. Thank you.
Happy New year everyone.

 

[WayneRyan]

Ainsley,

You could use a DLookUp to accomplish this:

Private Sub EquipmentNumber_BeforeUpdate (Cancel As Interger)

If Not IsNull(DLookUp("[EquipmentNumber]", "[Fan Equipment Log]", "[EquipmentNumber] = " & Me.EquipmentNumber))
  MsgBox "Fan is already out. Choose another Fan."
End If

End Sub

Wayne

 

[AinsleyW]

The error now becomes:

User-Defined type not defined.

 

[dcx693]

Just speaking syntax-wise, the If statement in Wayne's code needs a " Then" placed at the end of it (without the quotes.