Validation rule based on 2 fields

[Slush]

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!

[ranman256]

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

[Slush]

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.

[CJ_London]

you could use the dlookup function - something like

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

[Slush]

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!

[Bulzie]

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)

[CJ_London]

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.

[Slush]

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!

[Slush]

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

[Slush]

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