How to use Macro 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

 

[CJ_London]

why not just have the user select from a combo box?

 

[Fra90]

Combo box resolves perfectly my problem , I'm just a beginner and I'm Learning by myself how to use access.. but sometimes I have some difficulties. Thank you for your help!

Another issue that I'm dealing with now is how to open a form (from a macro for example) only if the form is not empty.
Is there a way to do that?

 

[CJ_London]

I don't use macros so can't advise on that but in VBA you could do something like this pseudo code

if dcount("*","MyTable","MyCriteria")<>0 then docmd.openform "myForm"

 

[Geotch]

I think an option for the combo box event property is "not in list". So if you enter a new supplier, you can have it run a macro that opens your supplier form so you can add the supplier.

Create a macro that opens the form you want and goes to a new record.

Go back to the property of the combo box, event tab, not in list, hit the drop down and choose the macro you just created. Save everything and try it out.

If you need an example I can throw one together quickly.

 

[Mihail]

Learn how to use the NotInList event.