Existing Records

[Arob]

I have seen several posts on finding duplicate records where
If DCount([productid], "order details", "[orderid] = '" & Me![productid] & "'&quot > 0 Then
MsgBox "Product number aleady exists"
However I have two fields, product ID and Order ID. There can be multiple product id's under one orderId. However because it looks at table and sees similar product id's under different order ID's I still receive the message box. Whenever trying to enter a product ID into a separate record. Am I on the right track or can anyone help me with a different solution.

 

[AccessAce]

You can check more than one field in the criteria portion of the DCount function. Like this:

If DCount([productid], "order details", "[orderid] = '" & Me![orderid] & "' AND [productid] = '" & Me![productid] & "'&quot > 0 Then
MsgBox "Product number in this Order aleady exists"

 

[Arob]

I am getting a data type mismatch error in the criteria. I think this is because the orderid is a number field and there is "" around it. I am not the best at puncuation, so I am not totally sure.

 

[nq]

I had a similar problem recently and used the DLookup function instead.
Simplified, I had a "Buildings" table in which there were three fields - "ID", "Location" and "Province". On the form were two bound controls for location and province. I used the following code to check for existing records, issue a message and then goto the existing record to edit it rather than add a new record. I issued the Form.Undo command because I had previously used the AddNewRec function and no longer wished to add a record.
This may be an alternative.

Nigel.

-------

varLocation = Me!Location
varProvince = Me!Province
If Not IsNull(DLookup("[ID]", "Buildings", "[Location]=" & Me!Location & " AND [Province]='" & Me!Province & "'&quot) Then
MsgBox "Duplicate Entry - Edit Existing Data", vbInformation
Form.Undo
Me.RecordsetClone.FindFirst "[ID]=" & DLookup("[ID]", "Buildings", "[Location]=" & varLocation & " AND [Province]='" & varProvince & "'&quot
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

------