Pop-up Messages for Form Fields and Validation Rules

[davesaint]

I've created a database within my organization to track and manage Program, Project, and Team Issues. I created a data entry form to enter issue information into the DB. Three of the many fields I added to the form are called Program, Project, and Team. These three are combo boxes with drop-down lists to select from. If a user of the DB populates the Program and Project Fields the Team Field should be left blank. If a user of the DB populates the Team Field they should not populate the Program or Project Fields. What safeguards can I add to these fields to do what I want? Is it possible to setup the form where if a user populates the Program and Project fields and then tries to populate the Team field the field will not accept the data?Maybe a message pops up stating only one of the fields can be populated but not both for example and vice-versa

 

[papparuff]

You can use the Enabled property and the Tag property of those fields affected.

For those fields on the form that must be enabled when the user selects the Project or Program combobox, place the word "Program,Project" in the Tag property of each control. For those fields that must be enabled when the user selects the Team combobox, place the word "Team" in the Tag property of each of the fields. We also create a procedure to either enable the fields or not enable them, depending on which combobox is selected. The name of the procedure is EnableDisableControls.

Name your comboboxes as follows:
Project cboProject
Program cboProgram
Team cboTeam

We will use part of the name of the comboboxes to determine which fields on the form should be enabled and which should not.

Here is the Code:


Private Sub EnableDisableControls(strCtl As String)
Dim ctl As Control

' Enumerate through each control to see if the values
' in the Tag property equals the name of the combobox
' (minus the cbo portion of the name, Enable the field.
For Each ctl In Me.Controls
If InStr(ctl.Tag, Right(strCtl, Len(strCtl) - 3)) > 0 Then
ctl.Enabled = True
' If the tag property is not equal to the name of
' the combobox less the cbo) but there is something
' in the tag property, Disable the field.
ElseIf Len(ctl.Tag) > 0 Then
ctl.Enabled = False
End If
Next ctl

End Sub

Private Sub cboProject_AfterUpdate()
Dim ctl As Control

Set ctl = Screen.ActiveControl
' Call the procedure to enable/disable controls
EnableDisableControls (ctl.Name)

End Sub

Private Sub cboTeam_AfterUpdate()
Dim ctl As Control

Set ctl = Screen.ActiveControl
' Call the procedure to enable/disable controls
EnableDisableControls (ctl.Name)

End Sub
John Ruff - The Eternal Optimist