Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    14

    Unanswered: how to use vba to check for duplicates with nulls allowed.

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    stLinkCriteria = "CALF_NUMBER = " & "'" & NEWCALF_NUMBER & "'"
    If  DCount("*", "CALF_ENTRY", stLinkCriteria) > 0 Then
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    14
    Hi Sinndho:

    Thank you for trying to help me with the problem, I appreciate it very much.

    I'm still getting the runtime error if the COW_NUMBER is left blank.

    Here is the code I used with yours (which maybe I did wrong, but it does work correctly except for the runtime error on null):

    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 DCount("*", "CALF_ENTRY", stLinkCriteria) > 0 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 currently have the vba code on an "on exit" because I want it to fire when the user exits and clicks on the Calf number field.

    I've attached the database as well as a screen pic of the runtime error.

    Thanks again for helping anyone.
    Attached Thumbnails Attached Thumbnails runtime_error.JPG  
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Test the value of the control before proceeding:
    Code:
    NEWCOW_NUMBER = Nz(Me.COW_NUMBER.Value, "")
    If Len(NEWCOW_NUMBER) > 0 Then
        stLinkCriteria = ...
        ...
    End If
    Have a nice day!

  5. #5
    Join Date
    Jun 2010
    Posts
    14
    Sinndho:

    Thank you for the help .... its working now!

    Thanks!

    Matthew

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •