Field dependent on another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field on my form "Result" with two choices:

Satisfactory and Unsatisfactory

If Satisfactory is chosen, the next field "Action Taken" is not required and
I'd like it not to be accessible or visible or whatever. If Unsatisfactory
is chosen, I would then like the "Action Taken" field to be required and one
of the three choices in the drop down box would then be available.

Any help would be greatly appreciated.
 
Hi vladi,

You didn't state what type of control you are using for the Satisfactory /
Unsatisfactory choice, I'm assuming it's a dropdown, and for the sake of
clarity I'm going to call it "cmbSat." I'll call the Action Taken combobox
control "cmbAction"

In the cmbSat control's On Update Event:

if Me.cmbSat = "Satisfactory"
me.cmbAction.visible = false
else me.cmbAction = true
End if

Of course, you'll need to modify the code to reflect the actual control
names.
 
Can be done as follows:
Add 'AfterUpdate' event to 'Result' field
Private Sub Result_AfterUpdate()
If Result.Value = "Unsatisfactory" Then
[Action Taken].Enabled = True
[Action Taken].SetFocus
Else
[Action Taken].Enabled = False
End If
End Sub

Add 'Exit' event to 'Action Taken' field
Private Sub [Action Taken]_Exit(Cancel As Integer)
If Result.Value = "Unsatisfactory" Then
If IsNull([Action Taken].Value) Then
MsgBox ("You must select an action to be taken")
Cancel = True
End If
End If
End Sub

If user can look through current records (form is not just for entering new
records), you may want to add an "On Current" event to the form to enable or
disable the field when appropriate.
 
First, set the Visible property of the ActionTaken control to No in form
design view. Next, in the AfterUpdate and Form OnCurrent event procedures
for the Sat/Unsat control, add the code:

If Me![Satisfactory] = True Then
Me![Action Taken].Visible = False
Else
Me![Action Taken].Visible = True
End If

- You may have to customize this depending on what type of control you're
using to select Satisfactory/Unsatisfactory. If it were an option group with
the *label* Satisfactory corresponding to the *value* 1, and 2 corresponding
to Unsatisfactory, the condition test would be:

If Me![YourOptionGroup] = 1 Then

- [Action Taken] refers to the *control* on your form bound to the Action
Taken field in your table. If you've renamed the control to something other
than the field name (Access' default), use that name instead. A *field*
doesn't have a Visible property, so it will generate an error.

Enforce the required condition in the form's BeforeUpdate event. You can
also overwrite any data the user wrote there if Satisfactory was selected,
since he could theoretically choose Unsatisfactory, write in the Action Taken
field, and then select Satisfactory again. It's up to you.

' B4Update code
If Me![YourSatUnsatControl] = {value corresponding to Satisfactory} Then
If Nz(Me![Action Taken]) = 0 Then
Cancel = True
MsgBox "An Unsatisfactory mark requires an ActionTaken to be
entered." & _
vbCRLF & "Please enter a value."
Me![Action Taken].SetFocus
End If
Else
Me![Action Taken] = Null
End If

Hope that helps.
Sprinks
 
Back
Top