Validation rules tying a field to another field

[philvalko]

Hi all. As always, thanks in advance for the input.

I'm building a database of housing developments done by my organization. There are a number of rules that I would like to enforce--many of the rules for data entry in a given field, depend on the value entered in a separate field. For example, if someone enters "Residential" in the BldgType field, then the Affordability field cannot be Null (If they enter "Commercial" for BldgType, the Affordability field CAN be Null).

Similarly, if someone enters "LLC" in the PartnerType field, then the value in the PartnerRole field must be either "Member" or "Managing Member", whereas if they enter "LP" in PartnerType, then the value of PartnerRole must be either "Managing General Partner", "General Partner", or "Limited Partner."

Phil

 

[Matt Greatorex]

In the After Update event of the second field, perform a check against the values of the first and second fields. If the condition isn't met, don't allow the value to remain in the second and notify the user as to why via a message box.

e.g.

If [Forms]![[I]form name[/I]]![PartnerType] = "LLC" then
     If [Forms]![[I]form name[/I]]![PartnerRole] <> "Member" Then
             If [Forms]![[I]form name[/I]]![PartnerRole] <> "" Then
                    Msgbox "This combination is unacceptable"
                    [Forms]![[I]form name[/I]]![PartnerRole] = ""
                    Exit Sub
             End If
      End If
ElseIf [Forms]![[I]form name[/I]]![PartnerType] = "LP" Then

etc.
etc.

End If

 

[philvalko]

Thanks Matt.

Is there a way that I can set it up on a form so that when someone selects "LLC" from the partnertype combo box, the PartnerRole combo box automatically updates to show only the permissible values?

 

[Matt Greatorex]

That's the way I'd do it.

In the AfterUpdate event of PartnerRole, you would set the rowsource of PartnerType and requery it (to ensure that the change is immediately reflected).

For example, assuming that the PartnerType data is in a table of the same name and has two columns, PartnerType and PartnerRole, if the rowsource when the form opens is

SELECT PartnerType FROM Partner ORDER BY PartnerType;

You could change it using

Dim str_SQL as String

str_SQL = "SELECT PartnerType FROM Partner " & _
              "WHERE PartnerRole = '" & _
             [Forms]![[I]name of form[/i]]![PartnerRole] & _
             "' ORDER BY PartnerType;"

[Forms]![[I]name of form[/i]]![PartnerType].RowSource = str_SQL

[Forms]![[I]name of form[/i]]![PartnerType].Requery

Hope that helps.