Help getting nulls and duplicate checking to work

[MFriend]

Hi Folks:

I have a field already that I was able to figure out how to use VBA to check if the value entered into it already is in the database and it works good (the field is the primary key and can't be left blank and the user wanted to have it checked before the submit button.

I have another field that I also want to check to see if the number is already in the database (and if it is I want to return a message box). It is similar to the first except that this field allows the user to leave it empty (* see at the bottom for an explanation if needed).

I have tried for several hours but nothing seems to work: (I get an error on the COW_NUMBER field if it is null)) and I couldn't figure out how to check for nulls using vba.

Here is the code that is working for the other field:

Private Sub CALF_NUMBER_Exit(Cancel As Integer)
Dim NEWCALF_NUMBER As String
Dim stLinkCriteria As String

NEWCALF_NUMBER = Me.CALF_NUMBER.Value
stLinkCriteria = "[CALF_NUMBER] = " & "'" & NEWCALF_NUMBER & "'"
If Me.CALF_NUMBER = DLookup("[CALF_NUMBER]", "CALF_ENTRY", stLinkCriteria) Then
MsgBox "This Calf Number, " & NEWCALF_NUMBER & ", has already been entered into the database." _
& vbCr & vbCr & "Please check the Calf Number again.", vbInformation, "Duplicate Calf Number"
End If
End Sub


The field I am working on now is called "COW_NUMBER". If anything is entered it has to be 6 characters long. I need help with checking to see if it is null and if it is post a message box t and goes on to the next field. If the number already exists in the table (which is allowed) then a message box needs to be opened alerting them to the number already being in the database, but allowing them to keep the number an go on to the next field.

I would greatly appreciate any help.

Thanks, Matthew



* For those wanting more indepth information:
The access database going to be used to track the births of Calves. So the COW_NUMBER is the mothers number. The COW_NUMBER may be left null (blank) if the cowboys can't figure out which Cow is the mother. It is also possible that The COW_NUMBER could already have been entered into the Calf_Entry table IF she has had twins. The reason for the pop-up is just to let them know that they should check to see if this is a twin or if the COW_NUMBER (mother cows number) was entered incorrectly before.Calving_Screens_07302014.zip

[pbaldy]

Your error would come from here:

Dim NEWCALF_NUMBER As String

because only Variant can accept Null. When you're setting the variable try

Nz(NEWCALF_NUMBER, "")

[MFriend]

Your error would come from here:

Dim NEWCALF_NUMBER As String

because only Variant can accept Null. When you're setting the variable try

Nz(NEWCALF_NUMBER, "")

Thank you for giving me ideas. I've tried looking at a vba book I bought but I'm pretty new to this.

Here is the code I'm currently using:

Private Sub COW_NUMBER_Exit(Cancel As Integer)
Dim NEWCOW_NUMBER As String
Dim stLinkCriteria As String

NEWCOW_NUMBER = Me.COW_NUMBER.Value
stLinkCriteria = "[COW_NUMBER] = " & "'" & NEWCOW_NUMBER & "'"
If Me.COW_NUMBER = DLookup("[COW_NUMBER]", "CALF_ENTRY", stLinkCriteria) Then
MsgBox "This Calf Number, " & NEWCOW_NUMBER & ", has already been entered into the database." _
& vbCr & vbCr & "Please check the Calf Number again.", vbInformation, "Duplicate Calf Number"
End If
End Sub


I tried putting your code in at this line:
Nz(NEWCALF_NUMBER, "")= Me.COW_NUMBER.Value

but that is probably the wrong place. I tried a couple of others with the same results.

I have attached the database so anyone can check it out that wants to. Its on the Cow Number field on an "on exit".

Thanks again for any help.

Matthew

[MFriend]

Your error would come from here:

Dim NEWCALF_NUMBER As String

because only Variant can accept Null. When you're setting the variable try

Nz(NEWCALF_NUMBER, "")

Thankyou very much Paul. With your help and others I was able to get it working properly.

Thanks!

Matthew

[pbaldy]

Happy to help!