You can do it rather more elegantly by setting the Enabled properties of the
relevant controls to False depending on the value in the Gender control. I
don't think you could do this with macro, but its not difficult to do it with
code. I'll try and explain just how as fully as possible below, so you
shouldn't have any trouble, and I think you'll find it a lot less scary than
you might imagine.
Before tackling the code, however, you'll need to identify which controls
you want to enable/disable depending on the gender. To do this you can use
the Tag property of the controls. Open the form in design view and select
each of the controls to be enabled only if the gender is male. As the Tag
property of each enter Male. Do the same for the relevant controls for the
controls to be enabled only if the gender is female, and as the Tag property
of each enter (you guessed it) Female. You'll find the Tag property on the
'Other' tab of the control's properties sheet. For any controls to be
enabled whatever the gender you don't need to enter anything as the Tag
property.
Now, you want the controls to be enabled, disabled in two circumstances;
firstly when an existing record is viewed depending on the gender value in
it, and secondly when a user enters a gender value in a record. You also
want the user to be unable to enter anything in the other controls before
entering a gender when they first move to a new blank record.
So, to cater for existing records, and when a user first moves to a blank
new record the code will go in the form's Current event procedure. To insert
the code do as follows:
1. Open the form in design view.
2. In the 'Events' tab of the form's properties sheet select the On Current
event property.
3. Click on the 'build' button, that's the one to the right with an
ellipsis (3 dots) on it.
4. Select 'Code Builder' in the next dialogue and then click the 'OK'
button.
5. The VBA window will open at the form's Current event procedure and
you'll see the following lines in place:
Private Sub Form_Current()
End Sub
6. In the space between these lines paste in the following code. The only
thing you might need to change is the name of the gender control; I've called
it [Gender] and assumed its of Text data type, with possible values of 'Male'
or 'Female'. The lines beginning with apostrophes are comment lines, and are
there to explain what the code is doing. I've commented the code rather more
than I normally would, but good commenting is an important part of writing
code:
''''code starts''''
On Error GoTo Err_Handler
' first declare an object variable
' of type Control. This will be used
' to return a reference to each control
' on the form in the code below
Dim ctrl As Control
' is form at a new record?
' if so disable both male and female controls
If Me.NewRecord Then
' loop through all controls in the form's
' Controls collection and if Tag property
' is 'Male' or 'Female' disable the control
For Each ctrl In Me.Controls
If ctrl.Tag = "Male" Or ctrl.Tag = "Female" Then
ctrl.Enabled = False
End If
Next ctrl
Else
' if form at an existing record then
' disable relevant controls depending on
' value of Gender control. The Nz function
' is used here to cater for Null (blank) gender
' controls and returns a zero-length string
' if the control is Null. Otherwise a type-mismatch
' error would occur.
For Each ctrl In Me.Controls
If ctrl.Tag = "Male" Then
ctrl.Enabled = Nz(Me.[Gender], "") = "Male"
ElseIf ctrl.Tag = "Female" Then
ctrl.Enabled = Nz(Me.[Gender], "") = "Female"
End If
Next ctrl
End If
Exit_Here:
Exit Sub
Err_Handler:
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
''''code ends''''
To enable/disable the controls when a user enters a value the gender control
you put code in the AfterUpdate event procedure of the Gender control in the
same way:
1. Open the form in design view.
2. In the 'Events' tab of the form's properties sheet select the On Current
event property.
3. Click on the 'build' button, that's the one to the right with an
ellipsis (3 dots) on it.
4. Select 'Code Builder' in the next dialogue and then click the 'OK'
button.
5. The VBA window will open at the form's Current event procedure and
you'll see the following lines in place:
Private Sub Gender_AfterUpdate()
End Sub
6. In the space between these lines paste in the following code. This time
its simpler as you only need to examine the value of the gender control
without worrying whether the form is at a new record or not. However, you
need to take account of the possibility of a user either changing the gender
selected, or deleting the gender from the control completely to make it
blank, in which case you'd want to remove any values already entered in the
other controls, i.e. make them Null:
''''code starts''''
On Error GoTo Err_Handler
Dim ctrl As Control
' disable relevant controls depending on
' value of Gender control.
' if gender control is emptied, i.e. made Null
' or gender is changed then also set relevant
' controls to Null
For Each ctrl In Me.Controls
If ctrl.Tag = "Male" Then
ctrl.Enabled = Nz(Me.[Gender], "") = "Male"
If Not ctrl.Enabled Then
ctrl = Null
End If
ElseIf ctrl.Tag = "Female" Then
ctrl.Enabled = Nz(Me.[Gender], "") = "Female"
If Not ctrl.Enabled Then
ctrl = Null
End If
End If
Next ctrl
Exit_Here:
Exit Sub
Err_Handler:
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
''''code ends''''
Ken Sheridan
Stafford, England
drolette said:
Hi. Thank you for your response. I think this will take more than a
brush-up since I don't know VBA code. Is it possible to do this with a
macro? I know how to do the message box, it's the expression that I'm having
trouble with. I just want to say "if gender from table x is male, display
message "This subject is male so female-related questions should not be
entered". They would then have to delete the data from the field to
continue. Would I use Dlookup? I will look into locking fields.
Thanks.
L. Drolette
:
in the forms code area of this site or in the help of the product itself look
up:
for an error message to the user: Msgbox
to skip to a specific field: tab tab control and SetFocus
to lock specific fields: lock fields
and to implement these on a conditional basis you need to brush up on
If/Then VBA code that will go into the AfterUpdate Event.
--
NTC
:
I have a table that stores a person's gender (male or female). I also have
several forms which ask for entry of certain fields based on the person's
gender, e.g. if female answer the following questions otherwise skip to next
question. I would like to disallow the data-entry folks from entering data
in the female-related questions if the subject is male, and if they do, spit
out an error message. How would I do this?