none
Spoke too soon... why is my validation criteria not working? RRS feed

  • Question

  • Hey guys, I made this code to validate a field being edited and it worked if the field edited is changed to something different that doesn't exist in the record, but now I'm finding a problem where if the criteria is not changed at all but other fields are it will bring the error of a duplicate record... 

    What I'm trying to understand is WHY is DLookup finding a result when my stLinkCriteria explicitly says AND records different from the current record. There are no other records in the table with a different pkVADPatientID and same Last Name, First Name (DOB).

    Dim stLinkCriteria As String
    Dim NewPt As String
    Dim CurrentRecord As String
    
    On Error Resume Next
    NewPt = Me.txtVADPatientLastName.Value & ", " & Me.txtVADPatientFirstName.Value & " (" & Me.txtVADPatientDOB.Value & ")"
    CurrentRecord = Me.txtpkVADPatientID.Value
    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> CurrentRecord"
    On Error GoTo 0
    
    If IsNull([txtVADPatientLastName]) Then
        MsgBox "Please enter a patient's LAST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientLastName].SetFocus
        Exit Sub
    ElseIf IsNull([txtVADPatientFirstName]) Then
        MsgBox "Please enter a patient's FIRST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientFirstName].SetFocus
        Exit Sub
    ElseIf IsNull([txtVADPatientDOB]) Then
        MsgBox "Please enter a patient's Date of Birth.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientDOB].SetFocus
        Exit Sub
    ElseIf Me.txtVADPatientLastName & ", " & Me.txtVADPatientFirstName & " (" & Me.txtVADPatientDOB & ")" = DLookup("[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')'", "tblVADPatients", stLinkCriteria) Then
         MsgBox "Patient " & NewPt & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtVADPatientLastName.SetFocus
    end if


    • Edited by InnVis Thursday, August 29, 2019 11:32 AM Typo
    Thursday, August 29, 2019 5:35 AM

Answers

  • Hi,

    I see several problems in your code:

    1) I guess the primary reason why the proof fails is that you include your variable name "CurrentRecord" into the string instead of its value, i.e. the result of

    & "' AND [pkVADPatientID] <> CurrentRecord"

    is sth like

    AND [42] <> CurrentRecord

    instead of

    AND [42] <> 43

    You have to build the string like this:

    & "' AND [pkVADPatientID] <> " & CurrentRecord

    2) Either

    If Me.txtVADPatientLastName…

    has to be an ElseIf or your first If structure is missing an End If.

    3) You should not name a variable "CurrentRecord" as this is the name of a built in Access property which gives back the position of the current record in a form. A better name would be e.g. strCurPatId or lngCurPatId (see 4)

    4) A field called xyzID usually is a number type and in this case the variable's type should be e.g. Long too.

    5) You should have a better error handling than On Error Resume Next, especially if you are trying to find an error. ;-)


    Karl
    http://www.AccessDevCon.com
    http://www.donkarl.com



    • Edited by Karl DonaubauerMVP Thursday, August 29, 2019 6:34 AM
    • Marked as answer by InnVis Friday, August 30, 2019 12:13 PM
    Thursday, August 29, 2019 6:23 AM
  • Hi,

    Try to change this part of the code "CurRec". You are evaluating an unknown value or variable.

    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> " & CurRec

    However, you have declared it as "String", it should be...

    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> " & """" & CurRec & """"
    I'm not sure if the ID is numeric, in this case you might want to declare it as Long.

    PS. Do a Debug.Print stLinkCriteria to display your SQL. Use it to check for syntax errors.


    • Edited by AccessVandal Friday, August 30, 2019 3:51 AM
    • Marked as answer by InnVis Friday, August 30, 2019 12:14 PM
    Friday, August 30, 2019 3:25 AM

All replies

  • Hi,

    I see several problems in your code:

    1) I guess the primary reason why the proof fails is that you include your variable name "CurrentRecord" into the string instead of its value, i.e. the result of

    & "' AND [pkVADPatientID] <> CurrentRecord"

    is sth like

    AND [42] <> CurrentRecord

    instead of

    AND [42] <> 43

    You have to build the string like this:

    & "' AND [pkVADPatientID] <> " & CurrentRecord

    2) Either

    If Me.txtVADPatientLastName…

    has to be an ElseIf or your first If structure is missing an End If.

    3) You should not name a variable "CurrentRecord" as this is the name of a built in Access property which gives back the position of the current record in a form. A better name would be e.g. strCurPatId or lngCurPatId (see 4)

    4) A field called xyzID usually is a number type and in this case the variable's type should be e.g. Long too.

    5) You should have a better error handling than On Error Resume Next, especially if you are trying to find an error. ;-)


    Karl
    http://www.AccessDevCon.com
    http://www.donkarl.com



    • Edited by Karl DonaubauerMVP Thursday, August 29, 2019 6:34 AM
    • Marked as answer by InnVis Friday, August 30, 2019 12:13 PM
    Thursday, August 29, 2019 6:23 AM
  • Hi,

    "CurrentRecord" is a reserved word in Access. If you have the code on the Form's module, that might create a headache for you.

    If that's the case, rename your "CurrentRecord". A suggest to read up...

    Reddick naming convention 

    Thursday, August 29, 2019 10:06 AM
  • Hey, thanks for replying! The if was actually an ElseIf. I accidentally pasted it wrong. I will try your first suggestion when I get to work.
    Thursday, August 29, 2019 11:34 AM
  • Hey all of these suggestions are great! I'm self taught so I am not familiar with proper error handling yet. I'm just starting to understand in-depth explanations such as these. I will make sure to change the name of my variable and make it long not string.
    Thursday, August 29, 2019 11:56 AM
  • OK, so I tried this and it gave me an error "Run-time error '2471': "The expression you entered as a query parameter produced this error: 'CurRec'

    Dim stLinkCriteria As String
    Dim NewPt As String
    Dim CurRec As Long
    On Error Resume Next
    NewPt = Me.txtVADPatientLastName.Value & ", " & Me.txtVADPatientFirstName.Value & " (" & Me.txtVADPatientDOB.Value & ")"
    CurRec = Me.txtpkVADPatientID.Value
    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> CurRec"
    On Error GoTo 0
    
    If IsNull([txtVADPatientLastName]) Then
        MsgBox "Please enter a patient's LAST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientLastName].SetFocus
        Exit Sub
    ElseIf IsNull([txtVADPatientFirstName]) Then
        MsgBox "Please enter a patient's FIRST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientFirstName].SetFocus
        Exit Sub
    ElseIf IsNull([txtVADPatientDOB]) Then
        MsgBox "Please enter a patient's Date of Birth.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientDOB].SetFocus
        Exit Sub
    ElseIf Me.txtVADPatientLastName & ", " & Me.txtVADPatientFirstName & " (" & Me.txtVADPatientDOB & ")" = DLookup("[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')'", "tblVADPatients", stLinkCriteria) Then
         MsgBox "Patient " & NewPt & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtVADPatientLastName.SetFocus
    else
    end if
    I should add that it worked as a string with your suggestion above, but I would like to learn why the long is not working. The ID field IS a number.


    • Edited by InnVis Thursday, August 29, 2019 10:17 PM
    Thursday, August 29, 2019 12:48 PM
  • Hi,

    Try to change this part of the code "CurRec". You are evaluating an unknown value or variable.

    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> " & CurRec

    However, you have declared it as "String", it should be...

    stLinkCriteria = "[VADPatientLastName] & ', ' & [VADPatientFirstName] & ' (' & [VADPatientDOB] & ')' = " & "'" & NewPt & "' AND [pkVADPatientID] <> " & """" & CurRec & """"
    I'm not sure if the ID is numeric, in this case you might want to declare it as Long.

    PS. Do a Debug.Print stLinkCriteria to display your SQL. Use it to check for syntax errors.


    • Edited by AccessVandal Friday, August 30, 2019 3:51 AM
    • Marked as answer by InnVis Friday, August 30, 2019 12:14 PM
    Friday, August 30, 2019 3:25 AM