Need help with a simple Duplicate Item check.

[Benjamin Bolduc]

Hello everyone,

I need to run a line of code that checks the "TempPurchase Order Entry" table for duplicate [Product Code]'s.

If there are duplicate [Product Code]'s, I'd need a msgbox to come up for user verification.

Thanks so much for your help!

Ben

 

[workmad3]

umm... are duplicate product codes allowed or not? if not, then just set the index on the field in the table to Yes, no duplicates, and access will handle it. If not, then its a bit more complicated.

Probably the best way is to run a Query on the product code and if its got records in it, you have duplicates so msgbox, otherwise you dont, so no problem.

 

[Benjamin Bolduc]

Duplicates in this table are OK in general. Most of the time, though, it's bound to be a user mistake.

This is just a temporary table used to accumulate products on an order. The info is appended to the real table upon confirmation of the order and is then purged for the next one.

The application is already "code heavey" so I'm trying to avoid using any more querys. Do you know if there's a simple check that can be done in a line or two of code?

Thanks again for you help!

Ben

 

[workmad3]

not really. You need to get the data in order to check it, and to get the data you need to do a query. If you want to do it, im afraid you will need another query (unless my limited knowledge of access is leading me astray again)
the check is only a line or 2 though

rs = CurrentDB.OpenRecordset("SELECT [Product Code] FROM [TempPurchase Order Table] Where [Product Code] = " & txtProductInput)
If Not (rs.BOF And rs.EOF) Then
msgbox "Duplicate items"
End If

 

[Benjamin Bolduc]

This looks great, thanks alot.

What do I set rs and txtProductInfo to though?

 

[workmad3]

oh sorry, rs is just a recordset, so

Dim rs As DAO.Recordset

Set rs = Currentdb (etc)

txtProductInput should just be where the product code to be checked comes from(was guessing that it would b a text box)