Need DCount help! My syntax is so wrong...

[bassman197]

I am trying to get a form to check and see if the contract number already exists before allowing a user to enter it. The form has a cumbersome name, which doesn't help with coding, but I'm stuck with it.
The following code is meant to find out if the contract number the user is trying to enter already exists in the table overdues in the field [Contract Num]. I've gotten it to the point where it doesn't return an error, but it doesn't do what I'd hoped either - it simply opens the error message form no matter what number you enter, existing or not. Since contract numbers contain a dash, the field [Contract Num] in the table is a TEXT field. The DCount line is causing the problem; the field and table parts maybe OK, but the criteria part is a mess!

[SIZE=2]
Private Sub Contract_Num_BeforeUpdate(Cancel As Integer)
'If the contract number does not yet exist
If IsNull(DCount("[Contract Num]", "overdues", "[Contract Num]=forms![overdues1 new contract].[Contract Num].value")) Then
'then allow the entry
Exit Sub
Else
'open error message
DoCmd.OpenForm "Contract Number Duplicate"
'and undo users input
Cancel = True
Me.[Contract Num].Undo
End If
End Sub
[/SIZE]

I think I should have the criteria in quotes, since it's a text field. Any advice appreciated!

 

[boblarson]

If DCount("*", "overdues", "[Contract Num]= " & forms![overdues1 new contract].[Contract Num].value) = 0 Then


or if contract Num is text:

If DCount("*", "overdues", "[Contract Num]= " & Chr(34) & Forms![overdues1 new contract].[Contract Num].value & Chr(34)) = 0 Then

 

[AccessNub]

bob is right, you cant do an IsNull on a Dcount because the DCount will never be Null or blank. You have to set it to check to see if the DCount = 0.

 

[bassman197]

Thanks, Bob!

That worked great, and I see the distinction between null and zero now. I would never have gotten there using my If IsNull! I noticed you used a wild card in the first argument (expression), instead of calling out the field in the table. Is this because the field is specified over in the criteria? (Still trying get a handle on this syntax).

 

[boblarson]

With DCount I used "*" because that just says count all records which match the criteria. No field name is required.