How to refer from one product to another

[ManuelLavesa]

Hello all, the problem I am having is as follows: I have a form with a subform for order entry. Then if i have a product which has been replaced by another product. How would you do it so that the program knows and it automatically replaces the product. And adds a coment if a customer is ordering the old product name. Any ideas would be appreciated. Thank you for your help.

[June7]

Do you want this name change to be reflected in already existing old records?

[ManuelLavesa]

No because the old records were sold with the old name. So I just need it for the new records, even though the customer might still order it with the old product name. Thaks for your help.

[June7]

Products should be ordered by product code, not a name.

You have a Products table?

You entered a new record in Products for the new product name but with same product code?

You have a lot of products with this situation?

A product could have many names over time?

What you want probably won't be easy to automate.

[ManuelLavesa]

Yes I have a product table. and yes I entered a new product record with the same product code but new product name, but I need to cross reference them somehow. I do have several products like this as the manufacturer changes his mind... But the customers are the last to know... I hope it won't change more than two to three times per product. Any ideas of how to automate this? But you are right it won't be easy.

[June7]

Then you should be saving autonumber ID from Products table as foreign key in orders.

Have a field in Products table that flags record as Active/Inactive (a y/n field would work).

Restrict combobox to only active Product records.

If the customer order includes the product code as well as the product name then select product by the product code.

Otherwise, VBA procedure that does a lookup on product name to retrieve the product number and then uses that value to pull the ID of the active record for that number and set combobox with the ID value.

[ManuelLavesa]

Thank you for your reply and ideas. I have been trying your suggestion with the DLookup function. The statement I wrote is as follows

Dim ProductID As Integer
ProductID = DLookup("[ProductID]", "Product", "ProductName=" & XRefCode)

The problem I think I have is that ProductID is an autonumber on the table Product and gives me this error. Data type mismatch in criteria expression. What is the vba data type for an autonumber? I have tried Long Integer but it does not work. Do you have any suggestions?

[June7]

The issue is the ProductName criteria. Parameter for text field needs apostrophe delimiters.

"ProductName='" & XRefCode & "'"

What is XRefCode? A control on form?

Also, if DLookup doesn't find match it returns Null. Only Variant variables can hold Null.

ProductID = Nz(DLookup("[ProductID]", "Product", "ProductName='" & XRefCode &"'"), 0)

[ManuelLavesa]

Yes you were right as soon as I changed that it works like a charm. Thanks again for helping me and for all your ideas.