Microsoft Access - Dlookup with multiple criteria

Question

I have 2 tables in my DB. One called Manufacture and the other Product.

The Manufacture table has 2 fields:

• Number
• Name

The Product table has 3 fields:

• Number
• Name
• Manufacture_Number

What I want is When I create a new product, it shows me if the product already exists. I'm currently using this code:

DLookup("[Name]", "Product", "[Name] = '" & me.txtName.value & "'")

It works just fine, but once happened that a product with the same name but different manufacture. I could not create a record because of that.

I only could not create a new record if the is the same product and the same manufacture as already created.

How do I create one Dlookup for multiple criteria in different tables?

Answer 1

I suspect that you don't really want to look across different tables. You want to prevent adding a product with the same name and manufacturer as an existing product, so you want to check both fields in the Product table:

DLookup("[Name]", "[Product]", "[Name] = '" & me.txtName & "' And Manufacture_Number = " & me.txtMfrNumber)

It's more likely that your input control for the manufacturer is going to be a combo box, but that works too - just replace txtMfrNumber with the name of the control that you're using to get this number.

Note that you don't need to specify the .Value on the end of me.txtName as Value is the default property of your text box.

Answer 2

You have to nest the DLookups like this:

DLookup("[Name]","Product","[Name]='" & me.txtName.value & "' AND Manufacture_Number=" & Nz(DLookup("Number","Manufacture","[Name]='" & me.txtManufactureName.value & "'" ,0)))

I am using Nz to avoid errors whenDLookup returns null.