DCount on two fields

[Design by Sue]

I need to check the combination of a text box and a combo box that are bound to the same table for duplicates and then give a message box telling of the existing records. The 2 fields are set up as a compound index so access displays a generic message but I am trying to customize the message without doing it through error trapping (because I want it earlier in the processes on the form) I am trying to us Dcount as follows but this is not working. It seems to be checking for either rather than a combination of the 2 fields because I get my message if either the Description OR the Group is duplicated (both of the text box and the combo box can have duplicates and it is only the combination that is singular) Any thoughts what I have done wrong here?



If DCount("[Description]", "[ItemTBL]", "[Description] = '" & Description & "'") And DCount("[Group]", "[ItemTBL]", "[Group] = '" & Group & "'") > 0 Then
MsgBox "This Item is already in the database.", vbExclamation, "Already in Database"

Thanks

 

[vbaInet]

You didn't put a condition for the first DCount. But you can use only one DCount() for both anyway. Put the AND in the first one.

 

[GinaWhipp]

Try...

If DCount("[Description]", "[ItemTBL]", "[Description] = '" & Description & "'") > 0 AND DCount("[Group]", "[ItemTBL]", "[Group] = '" & Group & "'") > 0 Then
MsgBox "This Item is already in the database.", vbExclamation, "Already in Database"

 

[Design by Sue]

Tried that before the code I posted and get the same results

 

[vbaInet]

Group and Description are reserved keywords in Access:

If DCount("*", "[ItemTBL]", "[Description] = '" & Me![Description] & "' AND [Group] = '" & Me![Group] & "'") > 0

 

[Design by Sue]

Than you again everyone - used vbaInet's code and seems to be working now!

 

[vbaInet]

I would have sworn Gina has shown you this before regarding problem keywords, here it is (again):

http://allenbrowne.com/AppIssueBadWord.html

 

[GinaWhipp]

@vbaInet

I think so too!

BTW, nice catch I missed that!