You need to do two things, firstly to disable the relevant controls depending
on the value selected; secondly when the user navigates to a record, if on a
new record to disable all the text boxes until a value is selected in the
combo box (otherwise a user could enter a value in one of the text boxes
first and then select an incompatible value in the combo box), and also, if
at an existing record, to disable the relevant combo boxes depending on the
value currently in the combo box.
To cater for when a user selects a value in the combo box or clears the
combo box by pressing the Del key you first need to handle the possibility of
there being inappropriate values already in the other controls, in which case
you'd probably want to give the user the option of proceeding by removing
those values. Validation of this sort is best done in the combo box's
BeforeUpdate event procedure, which includes a Cancel argument, so the code
would go like this:
Const conMESSAGE = "Selected value " & _
"conflicts with a value or values in other " & _
"controls. Do you wish to remove those values?"
Dim ctrl As Control
Set ctrl = Me.ActiveControl
If ctrl = "POS" Or IsNull(ctrl) Then
' if user has selected 'POS' then
' check if Due date, SO# or NTID
' controls have a value
If Not IsNull(Me.[Due Date]) Or _
Not IsNull(Me.[SO#]) Or _
Not IsNull(Me.NTID) Then
' get user confirmation whether to proceed or not
If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Warning") = vbYes
Then
Me.[Due Date] = Null
Me.[SO#] = Null
Me.NTID = Null
Else
Cancel = True
Exit Sub
End If
End If
' if user has cleared control to Null
' check if Distributor/Reseller has value
If IsNull(ctrl) Then
If Not IsNull([Distributor/Reseller]) Then
' get user confirmation whether to proceed or not
If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Warning") =
vbYes Then
Me.[Distributor/Reseller] = Null
Me.[SO#] = Null
Me.NTID = Null
Else
Cancel = True
End If
End If
End If
End If
In the combo box's AfterUpdate event procedure, which will only fire if the
above validation code is satisfied, you set the enabled property of each of
the text boxes as appropriate:
Dim ctrl As Control
Set ctrl = Me.ActiveControl
' if combo box has been cleared to Null
' disable all the text boxes
If IsNull(ctrl) Then
Me.[Due Date].Enabled = False
Me.[SO#].Enabled = False
Me.NTID.Enabled = False
Me.[Distributor/Reseller].Enabled = False
Else
' if POS selected disable three controls only
If ctrl = "POS" Then
Me.[Due Date].Enabled = False
Me.[SO#].Enabled = False
Me.NTID.Enabled = False
Me.[Distributor/Reseller].Enabled = True
Else
' enable all controls
Me.[Due Date].Enabled = True
Me.[SO#].Enabled = True
Me.NTID.Enabled = True
Me.[Distributor/Reseller].Enabled = True
End If
End If
Finally you need to enable/disable the controls when the user first
navigates to a record. This code goes in the form's Current event procedure
and is very similar to that for the combo box's AfterUpdate event procedure:
Dim ctrl As Control
Set ctrl = Me.[Direct/Pos]
' if form at a new record or
' Direct/Pos combo box is Null
' disable all the text boxes
If Me.NewRecord Or IsNull(ctrl) Then
Me.[Due Date].Enabled = False
Me.[SO#].Enabled = False
Me.NTID.Enabled = False
Me.[Distributor/Reseller].Enabled = False
Else
' if POS selected disable three controls only
If ctrl = "POS" Then
Me.[Due Date].Enabled = False
Me.[SO#].Enabled = False
Me.NTID.Enabled = False
Me.[Distributor/Reseller].Enabled = True
Else
' enable all controls
Me.[Due Date].Enabled = True
Me.[SO#].Enabled = True
Me.NTID.Enabled = True
Me.[Distributor/Reseller].Enabled = True
End If
End If
A lot of the duplication in the above code could be removed by
'modularising' it, putting the parts of the code which are duplicated or
near-duplicated in separate functions in the form's module and calling them
from the event procedures. As you say you are not very experienced with
writing code, however, I've kept each event procedure self contained, which
you'll probably find easier to follow and to implement. If you find it does
the job as you wish you might like to make a copy of the form and try
modularising the code yourself as an exercise. Jumping in the deep end is
generally the best way to learn how to swim!
Ken Sheridan
Stafford, England
