Validation Rule?

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I have 1 text field that has 10 possible selections. Entry into the 2nd
field is only required when 1 of 2 options in the 1st field is selected; how
can I require entry into the 2nd field in those instances? Should I validate
in the table, or the form? I'm using Access 2003.

Thanks.
 
One approach might be to use a combobox instead of a textbox (that way, you
could limit the available choices to the 10 that are allowed).

Then you could use the AfterUpdate event of that combobox to either enable
or disable the second control, depending on the value chosen in the
combobox.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff, I did a poor job of describing my problem.

I am using a combobox, on the first field, with 10 possible text selections.
I've tried to come up with all kinds of validations & expressions, but I
can't get any of them to work. And, I haven't got a handle on the macro -
the before and after update thing. Do you know of one I could copy and adapt?

Thanks much. Gary
 
In the After Update event of your combo box (on the form), enter code like
this:
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
Me![Field2].visible = true ' note: it should be set to visible = no
in the properties box.
Else
Me![field2].visible = false
End If

Then, to make sure they entered data, in the Before Update property of your
Form, enter code like this:
Dim stMsgText as String
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
If IsNull(Me![Field2]) Then
stMsgText = "You must enter data for field2!"
MsgBox stMsgText, vbOkOnly
DoCmd.CancelEvent
End If
End If

In case you are unsure where to write the code:
Open the property sheet of the combobox/form
Go to the Event Tab
Click in the applicable event
click the "..." button to the right and select Code Builder
Enter the code!

Jill
 
Jill, I'm still unable to make it work.

Let's say that ON THE FORM I have 1 combobox named Activity (the list pulls
from a table containing only options for that field, and the autonumber), and
a text box named Address. I don't want the Address textbox to show on the
form unless HOUSE is selected in the Activity ComboBox, and then I would like
to require data entry into the Address field.

I plugged in the associated names, but it had no effect (the Address textbox
would not show unless the Visible in properties was checked Yes). When the
Address field/textbox was visible, no entry was required even when HOUSE was
selected in combobox Activity.

I know I'm doing something wrong, but I don't know what. I figure my error
is the same for both the beforeupdate and the afterupdate code. So I cut the
before update code down to one triggering option for the sake of simplicity
(mine).

Below is one of the codes I tried

Private Sub Address_BeforeUpdate(Cancel As Integer)
Dim stMsgText As String
If Me![Activity] = HOUSE Then
If IsNull(Me![Address]) Then
stMsgText = "You must enter data for Address!"
MsgBox stMsgText, vbOKOnly
DoCmd.CancelEvent
End If
End If

End Sub

Thanks for your help. I sure want to figure out my mistakes.

Gary

Mrs. Ugh said:
In the After Update event of your combo box (on the form), enter code like
this:
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
Me![Field2].visible = true ' note: it should be set to visible = no
in the properties box.
Else
Me![field2].visible = false
End If

Then, to make sure they entered data, in the Before Update property of your
Form, enter code like this:
Dim stMsgText as String
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
If IsNull(Me![Field2]) Then
stMsgText = "You must enter data for field2!"
MsgBox stMsgText, vbOkOnly
DoCmd.CancelEvent
End If
End If

In case you are unsure where to write the code:
Open the property sheet of the combobox/form
Go to the Event Tab
Click in the applicable event
click the "..." button to the right and select Code Builder
Enter the code!

Jill

Gary said:
Jeff, I did a poor job of describing my problem.

I am using a combobox, on the first field, with 10 possible text selections.
I've tried to come up with all kinds of validations & expressions, but I
can't get any of them to work. And, I haven't got a handle on the macro -
the before and after update thing. Do you know of one I could copy and adapt?

Thanks much. Gary
Click to expand...
Click to expand...
 
Forgot to say that I'm puting the code in the Address textbox event, as
opposed to the Activity Combobox - thought, I've reall tried it on both and
about every way, except the right way, I could.

Thanks.

Gary said:
Jill, I'm still unable to make it work.

Let's say that ON THE FORM I have 1 combobox named Activity (the list pulls
from a table containing only options for that field, and the autonumber), and
a text box named Address. I don't want the Address textbox to show on the
form unless HOUSE is selected in the Activity ComboBox, and then I would like
to require data entry into the Address field.

I plugged in the associated names, but it had no effect (the Address textbox
would not show unless the Visible in properties was checked Yes). When the
Address field/textbox was visible, no entry was required even when HOUSE was
selected in combobox Activity.

I know I'm doing something wrong, but I don't know what. I figure my error
is the same for both the beforeupdate and the afterupdate code. So I cut the
before update code down to one triggering option for the sake of simplicity
(mine).

Below is one of the codes I tried

Private Sub Address_BeforeUpdate(Cancel As Integer)
Dim stMsgText As String
If Me![Activity] = HOUSE Then
If IsNull(Me![Address]) Then
stMsgText = "You must enter data for Address!"
MsgBox stMsgText, vbOKOnly
DoCmd.CancelEvent
End If
End If

End Sub

Thanks for your help. I sure want to figure out my mistakes.

Gary

Mrs. Ugh said:
In the After Update event of your combo box (on the form), enter code like
this:
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
Me![Field2].visible = true ' note: it should be set to visible = no
in the properties box.
Else
Me![field2].visible = false
End If

Then, to make sure they entered data, in the Before Update property of your
Form, enter code like this:
Dim stMsgText as String
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
If IsNull(Me![Field2]) Then
stMsgText = "You must enter data for field2!"
MsgBox stMsgText, vbOkOnly
DoCmd.CancelEvent
End If
End If

In case you are unsure where to write the code:
Open the property sheet of the combobox/form
Go to the Event Tab
Click in the applicable event
click the "..." button to the right and select Code Builder
Enter the code!

Jill

Gary said:
Jeff, I did a poor job of describing my problem.

I am using a combobox, on the first field, with 10 possible text selections.
I've tried to come up with all kinds of validations & expressions, but I
can't get any of them to work. And, I haven't got a handle on the macro -
the before and after update thing. Do you know of one I could copy and adapt?

Thanks much. Gary

:

One approach might be to use a combobox instead of a textbox (that way, you
could limit the available choices to the 10 that are allowed).

Then you could use the AfterUpdate event of that combobox to either enable
or disable the second control, depending on the value chosen in the
combobox.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP




I have 1 text field that has 10 possible selections. Entry into the 2nd
field is only required when 1 of 2 options in the 1st field is selected;
how
can I require entry into the 2nd field in those instances? Should I
validate
in the table, or the form? I'm using Access 2003.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Update: Biggest problem solved. I went to the ComboBox property/event
afterupdate, and set it as you said (with option1 and option2 listed in
quotation marks (I had done that previously, but something changed). Also, I
used the info you gave to cause the save button to hide the Text Field.

Now, I just need to figure out how to require entry in the Text Field when
appropriate. I have been able to display the entry required message, but it
comes up before the Text Field is visible, and you cannot proceed.

Thanks. Again.
Gary

Mrs. Ugh said:
In the After Update event of your combo box (on the form), enter code like
this:
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
Me![Field2].visible = true ' note: it should be set to visible = no
in the properties box.
Else
Me![field2].visible = false
End If

Then, to make sure they entered data, in the Before Update property of your
Form, enter code like this:
Dim stMsgText as String
If Me![ComboboxName] = option1 or Me![ComboboxName] = option2 then
If IsNull(Me![Field2]) Then
stMsgText = "You must enter data for field2!"
MsgBox stMsgText, vbOkOnly
DoCmd.CancelEvent
End If
End If

In case you are unsure where to write the code:
Open the property sheet of the combobox/form
Go to the Event Tab
Click in the applicable event
click the "..." button to the right and select Code Builder
Enter the code!

Jill

Gary said:
Jeff, I did a poor job of describing my problem.

I am using a combobox, on the first field, with 10 possible text selections.
I've tried to come up with all kinds of validations & expressions, but I
can't get any of them to work. And, I haven't got a handle on the macro -
the before and after update thing. Do you know of one I could copy and adapt?

Thanks much. Gary
Click to expand...
Click to expand...
 
Now, I just need to figure out how to require entry in the Text Field when
appropriate. I have been able to display the entry required message, but it
comes up before the Text Field is visible, and you cannot proceed.
Click to expand...

Check in the Form's (not the control's) BeforeUpdate event to see if the two
fields are correctly filled in.
 
Eureka! It works!

Thanks Jill, for giving me exactly what I needed to get it done,

and thanks John, for pointing out what I was overlooking.

Thank you both.

Gary
 
Back
Top