VBA code to check if a record already exists

[fra90]

Hello everyone,

I have a Form (ITEMS) where it's also possible to insert
the supplier name of a particular item. I'm trying to use a macro to check if
the supplier name registered in the form ITEMS already exists in the table
"Suppliers". If it doesn't, then tha macro should show a message to ask to the
user if he/she wants to update the "suppliers" table with the info of the new
supplier.

I saw on the web that I can use as conditional expression in
the macro the commands IsNull(DlookUp... etc.
however I'm having difficulties to


make this command works..
the syntax that I'm using is the following :


IsNull(DLookUp("[Supplier Name]","Suppliers","[Supplier Name]='" &
[Forms]![ITEMS]![SupplierName] & "'"))

Basically in the command I
want to say: "look for the "Supplier Name" in the table "Suppliers" that is
equal to the "SupplierName" of the form ITEMS just added. And tell me if you
find it.

but apparently there is something wrong in the syntax because
when I try to updade the SupplierName in the form ITEMS it gives me as message
"type mismatch".

I hope you guys can help me to solve this problem

[amrut]

Use Dcount instead of Dlookup. If value returned is 1 supplier exists else does not. Be careful with spelling & Lowercase/Uppercase names.

[June7]

I doubt DCount will help with the type mismatch error.

Are you using a combobox to select supplier? If not in the list then supplier doesn't exist. Use combobox NotInList event to handle when item not found. I would use VBA for this but might be possible with macro. According to MS: To run a macro or event procedure when this event occurs, set the OnNotInList property to the name of the macro or to [Event Procedure].

Shouldn't save supplier name, should save an ID.

[naeemahmad]

I used the code below to check if records exist using DLookup.. but I have since changed to DCount.

Private Sub MyCombo_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("RacerID", "Medals", "RaceID = " & Val(EventCombo.Columns(0)) & " AND Medal = '" & MedalCombo.Columns(0) & "'")) Then
MsgBox "Record Exists"
End If

End Sub