DLookup Problem

[Bob M]

OK here goes. I have three tables:

Products, Suppliers and ProductSupplierJoin.

Products has the following fields among others:
ProductID - Key field
VendorProductID Indexed No duplicates

Suppliers has the following field among others
SupplierID - Key field

ProductSupplierJoin has the following fields
ProductID
SupplierID

The relationships are as follows Products is linked to ProductSupplierJoin via ProductID 1 to many
Suppliers is joined to ProductSupplierJoin via SupplierID 1 to many.
The join table is so I can account for more than one supplier supplying the same VendorProductID. Actually I thinking of renaming that field since I really hold the ManufacturerID.

I have a combo box which has a rowsource that selects records from Products which have a corresponding record for the supplier in ProductSupplierJoin so I get on Products supplied from that supplier.

If this supplier has not in the past supplied this particular product I need to add it the the list of products supplier by this supplier; however, I also need to check to see if this product is supplied by any other supplier (ei. Has a record in Products). So here is my Dlookup problem.

In the notonlist event property I have the following code to check if the product alreadys exist in the product table.

If Not IsNull(DLookup("[VendorProductNo]","Products","[VendorProductNo] = '" & Me![ProductID].Column(1) & "'")) Then


etc. etc. I add the record dynamically to the ProductSupplierJoin table and continue.

Problem is that even when I enter a product which exists in the Products table but hasn't been supplied by this particular supplier it skips to the statement where it run a products entry screen to add the "new" product.

I know there is something I'm missing but not sure where.

Can anyone help.

 

[Bob M]

I tried this same code on another form only I replaced the combo box ProductID (of which VendorProductNo is in column 1) with a control VendorProductNo. So the syntax is the same except I replaced the field I was retrieving to ProductID and the VendorProductNo = me.VendorProductNo instead of Me.ProdcutID.Column(1) and it worked. Perhaps the problem is in the column reference, I don't know.

Anybody??

 

[Elana]

Is your bound column the first column in the combo box? If so, the column reference is 0.

Just a thought.

 

[Bob M]

I check that, The Vendor Product Number is the second item in the list. Anyway it doesn't matter. In the notonlist in the ProductID combo I simple send the user to the product entry screen and check there if the vendor product exists. It works beautifully since I took out the column reference and replace it with a direct reference to the vendor product no.

Thanks. I guess I just needed to walk around and think a bit.