Please Help

[AlbertS]

I have a database and a frontend. But i wrote a peace of code for the frontend to check duplicates. It worked perfect. Its suppose to check duplicates only when the field is filled in. If left blank it must send it off to database. After a few months and about 1400 records that the one field is blank its started to give me the info box that i made for a blank field.
Please can someone help me i am new to VB and Access but im trying to become a better.

Here is the code that i putted on the button that must check for duplicates before it sends the data off.

sel = "[InvoiceNr]= " & IIf(IsNull(Me.InvoiceNr), 0, Me.InvoiceNr) & ""
Lookupcnt = DCount("[InvoiceNr]", "newshipment", sel)
If Lookupcnt > 0 Then

Set txt = Form_newshipment!InvoiceNr
Me.invoicenrstar.Visible = True
MsgBox ("Invoice Number Already Exist"), vbCritical
txt.SetFocus
Exit Sub
Else

Me.invoicenrstar.Visible = False



End If


I have changed the 0 value to 1 then it works but what if it comes back after a few records with the same ploblem.

Thank you

[John_G]

If the user forgets to fill in the invoice number, your code is setting the number that it checks for to 0, in other words sel = "[InvoiceNr]= 0" . The first time this happens, you get a record with invoice number = 0 saved to your database (probably not what you wanted to do), because the DCount returns 0. The next time it happens, you will get the error message.

What you want to do is give an error message and exit the routine before you proceed with the DCount:

if IIf(IsNull(Me.InvoiceNr), 0, Me.InvoiceNr) = 0 then
msgbox "Please enter a valid invoice numnber"
exit sub
endif

Put this before the sel = ... statement.

HTH

John

[AlbertS]

Thank you for the reply John. The thing is i have a person that will enter only a few of the data then another person that will come in later and enter the invoice number and amount to finish a quote or shipment off.... I am going to try what you said.

Thanks alote again!!

[AlbertS]

I found the problem one of the users wrote a record of with the bloody invoice number 0. Thats why the thing gave me an error. But thanx a Mil John