Results 1 to 10 of 10
  1. #1
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38

    Validation rule based on 2 fields

    Hello. I am new to writing complicated validation rules. I need to write a rule to only allow one address to be flagged as primary address per patient.

    The address table contains the following key fields: ADDR ID (auto number primary key), PATIENT ID (numeric patient identifier), IS PRIMARY (yes/no), and other address fields.

    Each patient may have several addresses, but only one can be flagged as the primary address. I need restrict data entry so that the combination PAT ID + (IS PRIMARY= yes) can only occur once. However, PAT ID + (IS Primary =no) can occur many times.

    How do I accomplish this? Do I put it on the form or in the table and how do I write this code?



    Thanks for your help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    Put the validation rule in the form code, not on the table design.
    dont allow user to save record until the validation is good.

    if isNull(txtAcct) then
    MsgBox "you must enter Acct#"
    Exit sub
    end if

  3. #3
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    I'm not understanding your response. I can put the code in the form event "on update" or "on click" for the IS PRIMARY field on the form or wherever you recommend, but what is the code I would write to compare to see if this patient already has a primary address? In the address table they can only have one "yes" for IS PRIMARY, but they can have many "no" for IS PRIMARY. I'm not sure how to write the code so that PAT ID+ IS PRIMARY= yes only occurs once.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,834
    you could use the dlookup function - something like

    Code:
    dim primaryaddr as long
    if me.isprimary=true then
         primaryaddr=nz(dlookup("addrID","tblAddresses","IsPrimary=true and PatientID=" & me.patientID),0)
         if primaryaddr=me.addrID or primaryaddr=0 then
             msgbox "this is the primary address"
        else
             msgbox "primary address already set"
             me.isprimary=false
        end if
    end if
    where you put this code depends on how your form works - the latest event you should use is the address form before update event, but I suspect the best place to put is the isprimary control after update event.

    your field names appear to have spaces - bad idea, at some point it will waste you hours of time trying to find an error because of the spaces. If you are doing it so the form/report label 'looks right', use the field caption property, that is what it is for

  5. #5
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Thanks. The names don't have spaces in them. I was trying to simplify things when asking for help. The code below contains the actual table and field names.

    Below is a copy of the code. It is not working. I think there are at least 2 issues. 1- If IS PRIMARY =yes then the patient has a current address and cannot have another one (check box can only =yes once per patient, but can =no many times), but the code below seems do the opposite. 2- If I check the box I get the following error message- runtime '3464' "data type mismatch in criteria expression".


    Private Sub Check_Current_AfterUpdate()
    Dim primaddr As Long
    If Me.Pat_Addr_IS_PRIMARY = True Then
    primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", "Pat_Addr_Is_Primary=true and Pat_Addr_Pat_ID_DMS=" & Me.Pat_Addr_PAT_ID_DMS), 0)
    MsgBox "Primary address selected."
    Else
    MsgBox "This patient already has a primary address."
    Me.Pat_Addr_IS_PRIMARY = False
    End If

    End Sub

    THANKS for your help!

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Is the value for Patz_Addr_Is_Primary (True,False) or (Yes,No)? If it is a check box or True False it might save it as 0 and 1? So instead of True you might need a 1. Look in your table to verify.

    Maybe one of these changes or might not be the issue at all.

    primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", "Pat_Addr_Is_Primary = '" & true & "'" and Pat_Addr_Pat_ID_DMS = " & Me.Pat_Addr_PAT_ID_DMS), 0)
    primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", "Pat_Addr_Is_Primary = 1 and Pat_Addr_Pat_ID_DMS = " & Me.Pat_Addr_PAT_ID_DMS), 0)

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,834
    1- If IS PRIMARY =yes then the patient has a current address and cannot have another one
    that is what the code is checking for - if the user checks the box to true then the code checks if a primary address has already been selected. If a primary address has not been selected or the existing record is already selected as the primary address then the choice is allowed.

    2- If I check the box I get the following error message- runtime '3464' "data type mismatch in criteria expression".
    the code I provided assumes that Pat_Addr_Is_Primary is a yes/no field and Pat_Addr_Pat_ID_DMS is a number. the error is indicating this assumption is not correct, either Pat_Addr_Is_Primary is not a yesno field and or Pat_Addr_Pat_ID_DMS is not a number.

  8. #8
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Sorry, I did not have a chance to get back to this until now. Thanks for your help.

    IS_PRIMARY is stored as 0 and -1, but I think the data type mismatch error is coming from the variable Pat_Addr_Pat_ID_DMS. It is a number stored as text (not numeric as I originally thought). I understand because it is a string data type I need to put quotes around it, but I am confused about where to place the quotes as it appears twice in the code.

    Currently, if I uncheck the box on the form I receive a message "this patient already has a current address" and if I check the box I get a data type mismatch error.

    Thanks again for your help!

  9. #9
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    I tried writing the code differently and I no longer receive error messages, but it is not working as needed. Unchecking the primary address box works fine on the form, but if I check the box it jumps to the final CASE ELSE message. I am not totally understanding the how the Nz function is working.



    Private Sub Check_Current_AfterUpdate()
    Dim primaddr As Long


    Select Case True
    '==patient already has primary address
    Case Me.Pat_Addr_IS_PRIMARY = True And primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", _
    "Pat_Addr_Is_Primary=true and Pat_Addr_Pat_ID_DMS='" & Me.Pat_Addr_PAT_ID_DMS & "'"), 0)
    MsgBox "This patient already has a primary address. Please select only one primary address."
    Me.Pat_Addr_IS_PRIMARY = False

    '==patient does not have primary address
    Case Me.Pat_Addr_IS_PRIMARY = True And primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", _
    "Pat_Addr_Is_Primary=false and Pat_Addr_Pat_ID_DMS='" & Me.Pat_Addr_PAT_ID_DMS & "'"), 0)
    MsgBox "Primary address selected."

    '==primary address removed
    Case Me.Pat_Addr_IS_PRIMARY = False
    MsgBox "This is no longer the primary address."

    Case Else
    MsgBox "Address selection Error"
    End Select
    End Sub

  10. #10
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    I solved the problem. Thanks everyone for pointing me in the right direction.

    Private Sub Check_Current_AfterUpdate()
    Dim primaddr As Long


    If Me.Pat_Addr_IS_PRIMARY = False Then
    MsgBox "This is no longer the primary address."
    ElseIf Me.Pat_Addr_IS_PRIMARY = True Then
    primaddr = Nz(DLookup("Pat_Addr_ID", "dbo_DMS_Patient_Address", "Pat_Addr_Is_Primary=true and Pat_Addr_Pat_ID_DMS='" & Me.Pat_Addr_PAT_ID_DMS & "'"), 0)
    If primaddr <> 0 Then
    MsgBox "This patient already has a primary address. Please select only one primary address."
    Me.Pat_Addr_IS_PRIMARY = False
    ElseIf primaddr = 0 Then
    MsgBox "Primary address selected."
    End If
    Else
    MsgBox "Error selecting patient primary address."


    End If


    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 07-07-2016, 06:21 PM
  2. Replies: 2
    Last Post: 03-21-2016, 11:05 AM
  3. Replies: 2
    Last Post: 06-16-2015, 03:52 AM
  4. Replies: 2
    Last Post: 12-27-2013, 07:32 AM
  5. Replies: 1
    Last Post: 05-29-2013, 04:01 PM

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
  •  
Other Forums: Microsoft Office Forums