Disallow entry of a form control based on value from another table

[drolette]

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?

 

[NetworkTrade]

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.

 

[drolette]

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

 

[Ken Sheridan]

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]

Ken, I haven't had a chance to try this yet but I wanted to thank you so much
for your thoughtful response. It is more than I ever expected and I
appreciate it greatly.

Linda

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

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

 

[Ken Sheridan]

Linda:

It was kind of you to reply so nicely. If you do have any problems
implementing what I gave you please don't hesitate to get back to me.

Ken Sheridan
Stafford, England

Ken, I haven't had a chance to try this yet but I wanted to thank you so much
for your thoughtful response. It is more than I ever expected and I
appreciate it greatly.

Linda

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

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?

 

[L. Drolette]

Hi Ken, I briefly looked this over and I think it assumes that the field
'gender' is in the same table that is the control source of the form. But
'gender' is actually in a different table.

Linda

Ken, I haven't had a chance to try this yet but I wanted to thank you so much
for your thoughtful response. It is more than I ever expected and I
appreciate it greatly.

Linda

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

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?

 

[Ken Sheridan]

Linda:

You can do one of two things:

1. For the form's RecordSource use a query that joins the current table to
the table containing the Gender column. That way you can simply examine the
value of Gender as the code I posted does. You don't need to bind a control
to it so long as it’s a column in the underlying recordset. As the gender
column is not shown in the form at present then you don't need the code for a
Gender control's AfterUpdate event procedure of course, just that for the
form's Current event procedure. This is likely to be the most efficient
method, though in real terms you'd probably not notice any difference in
performance between it and the following method.

2. Use the DLookup function to get the value of the Gender column in the
relevant row in the other table. Let's assume the other table is called
People, its column Gender and the column which relates the People table to
your current table is called PersonID and is a number data type, then the
code for the form's Current event procedure would be amended like so:

''''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

' declare variables to hold the Gender value
' and the criterion for looking it up in People
Dim strGender As String, strCriteria As String

' the criterion is that the PersonID in people
' equals the PersonID of the form's current record
strCriteria = "PersonID = " & Me.PersonID

' get the value of the gender column in the
' People table for the current PersonID.
' the Nz function is used here to return a
' zero length string if the Gender in the
' People table is Null, i.e. has not been entered
strGender = Nz(DLookup("Gender", "People", strCriteria),"")

' 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 the Gender column looked up from
' the people table. Note that if strGender is a
' zero length string, i.e. no gender has been
' entered in People for this person then neither
' of the condition below will be True, so all
' of the gender related controls will be disabled.
For Each ctrl In Me.Controls
If ctrl.Tag = "Male" Then
ctrl.Enabled = (strGender = "Male")
ElseIf ctrl.Tag = "Female" Then
ctrl.Enabled = (strGender = "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''''


Ken Sheridan
Stafford, England

Hi Ken, I briefly looked this over and I think it assumes that the field
'gender' is in the same table that is the control source of the form. But
'gender' is actually in a different table.

Linda

Ken, I haven't had a chance to try this yet but I wanted to thank you so much
for your thoughtful response. It is more than I ever expected and I
appreciate it greatly.

Linda

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

:

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?

 

[L. Drolette]

You're awesome Ken. Thank you so much.
Linda

Linda:

You can do one of two things:

1. For the form's RecordSource use a query that joins the current table to
the table containing the Gender column. That way you can simply examine the
value of Gender as the code I posted does. You don't need to bind a control
to it so long as it’s a column in the underlying recordset. As the gender
column is not shown in the form at present then you don't need the code for a
Gender control's AfterUpdate event procedure of course, just that for the
form's Current event procedure. This is likely to be the most efficient
method, though in real terms you'd probably not notice any difference in
performance between it and the following method.

2. Use the DLookup function to get the value of the Gender column in the
relevant row in the other table. Let's assume the other table is called
People, its column Gender and the column which relates the People table to
your current table is called PersonID and is a number data type, then the
code for the form's Current event procedure would be amended like so:

''''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

' declare variables to hold the Gender value
' and the criterion for looking it up in People
Dim strGender As String, strCriteria As String

' the criterion is that the PersonID in people
' equals the PersonID of the form's current record
strCriteria = "PersonID = " & Me.PersonID

' get the value of the gender column in the
' People table for the current PersonID.
' the Nz function is used here to return a
' zero length string if the Gender in the
' People table is Null, i.e. has not been entered
strGender = Nz(DLookup("Gender", "People", strCriteria),"")

' 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 the Gender column looked up from
' the people table. Note that if strGender is a
' zero length string, i.e. no gender has been
' entered in People for this person then neither
' of the condition below will be True, so all
' of the gender related controls will be disabled.
For Each ctrl In Me.Controls
If ctrl.Tag = "Male" Then
ctrl.Enabled = (strGender = "Male")
ElseIf ctrl.Tag = "Female" Then
ctrl.Enabled = (strGender = "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''''


Ken Sheridan
Stafford, England

Hi Ken, I briefly looked this over and I think it assumes that the field
'gender' is in the same table that is the control source of the form. But
'gender' is actually in a different table.

Linda

Ken, I haven't had a chance to try this yet but I wanted to thank you so much
for your thoughtful response. It is more than I ever expected and I
appreciate it greatly.

Linda

:

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

:

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