Combining Two DCount expressions in one

[nmodhi]

Hello all,

I'm trying to create two DCount expressions in one simply to check if two fields are equal to another set of two fields. For example:



abc 123
abc 321

should be okay and return nothing. However,

abc 123
abc 123

should inform the user a duplicate entry is being entered. On my main form I have a subform in which the table of these fields are sourced too.

Here's what I have so far in the before update of the textbox, I believe it's something close to this

 
If DCount("Field0","table","Field1=" & Me!field1 & " AND Field2= " & Me!Field2 &"") > 0 Then 
MsgBox ("Duplicate Entry")

But it doesn't seem to to work. Any help is really appreciated since I've been stuck on this for days

[MAF4Fam6]

One way to accomplish this...is by creating a simple Macro.

I use Access 2007... but, this should also work in Access 2000:

1. Open a New Macro in Design View.

2. Enable the Condition Column on the Macro screen.

See the following screen shot for visual:

Attachment 715

3. In the Action column on Row 1, type: MsgBox

a) Update the MsgBox's Action Arguments at the bottom of Macro screen.

b) Update the MsgBox's Condition statement to:

DCount("[MyTablePrimaryKeyField]","MyTableName","[MyTableField1]=Forms![MyFormName]![MyFormField1] And [MyTableField2]=Forms![MyFormName]![MyFormField2]")>0

Change key [names] (above) accordingly.

4. Next, in the Action column on Row 2, type: CancelEvent

a) Update the CancelEvent's Condition statement to an ellipsis(...):

...

The following screen shot is what you should have:

Attachment 716

5. Save, name, and close the Macro.

6. Insert the Macro's Name into the BeforeUpdate Event of your Form's Text Box.

-RC