Problem with dlookup

[rick982]

I searched the forum here and found what I thought was just the answer I was looking for in an earlier thread. However when I try and paste the code from the post I keep getting a compile error "Expected seprator or )".

Being the nooby that I am I have tried putting () around just about everything that I can think of but it still doesn't seem to like it .

All I am trying to do and I thought it would be simple, is to check and make sure the serial number that is being entered in the text box does not already exist in the table. Occasionally the user may have to input a list of say 8-10 serial numbers and I want to be able to warn them if they enter a duplicate before completing the entire form. The field is set to required, indexed - no duplicates and basically I am trying to avoid the generic 3022 message that most users do not understand and replace it with one of my own.

Private Sub txtserialnumber_BeforeUpdate(Cancel As Integer)
 
If DCount("[serialnumber]","[SerialNumber]","[serialnumber]" = "& Me.[[COLOR=black]serialnumber] = 0 )[/COLOR] [COLOR=blue]---error appears here---[/COLOR] 
Then
MsgBox "The Serial Number [" & Me.[txtserialnumber] & "] is a duplicate."
Cancel = True
 
End If
 
End Sub

Can anyone tell me what I am missing here or can someone possibly suggest a better solution?

I have attached the stripped down database in case my explanation is not clear enough.

Any help would be greatly appreciated.

Richard

 

[evanscamman]

1st, you are confusing Dcount and Dlookup.
Dlookup will return a value from a table, Dcount counts the # of records that match your criteria.

Dcount will work for what you want to do, but don't include the comparison to 0 inside the Dcount statement.
Complete the Dcount statement, then compare the Count with 0 as follows:

If DCount("[SerialNumber]","tblSerialNumber","[SerialNumber] = " & Me.SerialNumber) = 0 then

Note the closing parenthesis that completes the Dcount statement BEFORE trying to compare to 0.

BTW, it is good form to prefix your table names with "tbl", queries with "qry" and forms with "frm", etc...
The [ and ] are only required when your fieldname includes (horrors) spaces on dashes or other special characters.




Evan

 

[rick982]

Evan,

Thanks that worked perfectly once I sorted my logic out. All I had to do was to change the = to > to make it work the way I wanted it to. Thanks a bunch for the explanation of what I was doing wrong.

Filed away under "Knowledge of VBA" - a VERY small file right now but gettting larger

Richard