asked on

Trying to make a message box appear if two criteria in a table exists

I am trying to make a message box appear if any one record in a table has TWO fields that match what the user is entering entered exists.

But I'm getting a syntax error with:

If DCount("[LOT]", "HEADER", "[LOT] = " & Forms!frmCRMAIN.Form!subfrmHEADER.Form!txtLOT & "'" & " AND [SERIAL] = '" & Forms!frmCRMAIN.Form!subfrmWRHEADER.Form!txtSERIAL & "'") Then

Note: Serial is a number field. Lot is a text field.

Dale Fye

if the [LOT] field is numeric, try:

If DCount("[LOT]", "HEADER", "[LOT] = " & Forms!frmCRMAIN.Form!subfrmHEADER.Form!txtLOT & " AND [SERIAL] = '" & Forms!frmCRMAIN.Form!subfrmWRHEADER.Form!txtSERIAL & "'") Then

if [LOT] is text, then try:

If DCount("[LOT]", "HEADER", "[LOT] = '" & Forms!frmCRMAIN.Form!subfrmHEADER.Form!txtLOT & "' AND [SERIAL] = '" & Forms!frmCRMAIN.Form!subfrmWRHEADER.Form!txtSERIAL & "'") Then

SteveL13

ASKER

Serial is a number field. Lot is a text field.

Dale Fye

then try:

If DCount("[LOT]", "HEADER", "[LOT] = '" & Forms!frmCRMAIN.Form!subfrmHEADER.Form!txtLOT & "' AND [SERIAL] = " & Forms!frmCRMAIN.Form!subfrmWRHEADER.Form!txtSERIAL) Then

Dale Fye

Steve,

This is why I generally do:

strCriteria = "[LOT] = '" & Forms!frmCRMAIN.Form!subfrmHEADER.Form!txtLOT & "' AND " _
            & "[SERIAL] = " & Forms!frmCRMAIN.Form!subfrmWRHEADER.Form!txtSERIAL
If DCount("[LOT]", "HEADER", strCriteria) Then

Open in new window

It allows me to put a breakpoint on the IF line and inspect the value of strCriteria during debugging, to make sure I have the correct wrapping of text and numeric values that are being embedded in strCriteria.

SteveL13

ASKER

Am getting Expected list separator or )

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

try this

If DCount("*", "HEADER", "[LOT] = '" & Forms!frmCRMAIN!subfrmHEADER.Form!txtLOT & "' AND [SERIAL] = " & Forms!frmCRMAIN!subfrmWRHEADER.Form!txtSERIAL) Then

SteveL13

ASKER

Works perfectly. Thanks.