Conditionally required

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. I have two option buttons: Member
(option1) and Affiliate(option2) Each are yes/no fields,
with an option group container, and code:
Option1_Click
Me!Option2 = Not Me!Option1
Option2_Click
Me!Option1 = Not Me!Option2.

If someone is a Member, I want MemberDate field required
and AffiliateDate non-enterable. If someone is an
Affiliate, I want AffiliateDate field requried and
MemberDate non-enterable. MemberDate and AffiliateDate
are Date/Time fields. Seems as though I'd attach the code
to be somehow associated with the Member field so that the
AffiliateDateName field is disabled when the Member field
is activated, and that the trigger needs to happen after
the Member field is populated. Events and coding are a
struggle for me. Any detailed suggestions?

Thanks! Stephanie
 
How about a simple suggestion? No code needed :-)

Replace your 4 fields with these two:
MemberType Number 1 = full member; 2 = affiliate.
MemberDate Date/Time date the person took out membership.

Then you want to insist that if MemberType has a value, MemberDate must
also:
1. Open your table in design view.
2. Open the Properties box (View menu).
3. Beside the Validation Rule in the Properties box, enter:
([MemberType] Is Null) OR ([MemberDate] Is Not Null)
The rule is satisifed two ways: if MemberType is blank it's happy.
Otherwise MemberDate has to be non-blank to make it happy.

Make sure you use the Validation Rule for the table (the one in the
Properties box), not the rule for a field (the one in the lower pane of
table design).

Other benefits of this approach:
- really simple if you need to add another level of membership;
- you can use the option group interface, which you can't if you use 2
different yes/no fields.
 
Allen,
Thanks! I always love it when you answer posts ;-)
Hmm. I have many reports that key off of Member = yes or
Affiliate = yes.
I don't really like the 1, 2 idea. I would perhaps prefer
having a combo box where I could select Affiliate or
Member (or add a type as needed). But I guess I would
need to fix my reports as well (adding the new table to
the query). If I went this route, could I still follow
your table properties suggestion?

I tried leaving the yes/no dilema in place and putting an
OnClick event on Member field
AffilateDateName.Enabled = False
AffiliateDateName.Locked = True

but it didn't work. Is it close?
Thanks,
Stephanie
-----Original Message-----
How about a simple suggestion? No code needed :-)

Replace your 4 fields with these two:
MemberType Number 1 = full member; 2 = affiliate.
MemberDate Date/Time date the person took out membership.

Then you want to insist that if MemberType has a value, MemberDate must
also:
1. Open your table in design view.
2. Open the Properties box (View menu).
3. Beside the Validation Rule in the Properties box, enter:
([MemberType] Is Null) OR ([MemberDate] Is Not Null)
The rule is satisifed two ways: if MemberType is blank it's happy.
Otherwise MemberDate has to be non-blank to make it happy.

Make sure you use the Validation Rule for the table (the one in the
Properties box), not the rule for a field (the one in the lower pane of
table design).

Other benefits of this approach:
- really simple if you need to add another level of membership;
- you can use the option group interface, which you can't if you use 2
different yes/no fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi. I have two option buttons: Member
(option1) and Affiliate(option2) Each are yes/no fields,
with an option group container, and code:
Option1_Click
Me!Option2 = Not Me!Option1
Option2_Click
Me!Option1 = Not Me!Option2.

If someone is a Member, I want MemberDate field required
and AffiliateDate non-enterable. If someone is an
Affiliate, I want AffiliateDate field requried and
MemberDate non-enterable. MemberDate and AffiliateDate
are Date/Time fields. Seems as though I'd attach the code
to be somehow associated with the Member field so that the
AffiliateDateName field is disabled when the Member field
is activated, and that the trigger needs to happen after
the Member field is populated. Events and coding are a
struggle for me. Any detailed suggestions?

Thanks! Stephanie
Click to expand...


.
Click to expand...
 
Okay: you can use a combo with different values. Good idea. Even a
MembershipType table to make it easy to add type if you like.

It's possible to code a solution with your existing structure, though I
would not even consider that unless there is some valid reason why a person
could have both types of membership. You will need to set the Enabled
property of the controls in their AfterUpdate event, and also in the Current
event of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Stephanie said:
Allen,
Thanks! I always love it when you answer posts ;-)
Hmm. I have many reports that key off of Member = yes or
Affiliate = yes.
I don't really like the 1, 2 idea. I would perhaps prefer
having a combo box where I could select Affiliate or
Member (or add a type as needed). But I guess I would
need to fix my reports as well (adding the new table to
the query). If I went this route, could I still follow
your table properties suggestion?

I tried leaving the yes/no dilema in place and putting an
OnClick event on Member field
AffilateDateName.Enabled = False
AffiliateDateName.Locked = True

but it didn't work. Is it close?
Thanks,
Stephanie
-----Original Message-----
How about a simple suggestion? No code needed :-)

Replace your 4 fields with these two:
MemberType Number 1 = full member; 2 = affiliate.
MemberDate Date/Time date the person took out membership.

Then you want to insist that if MemberType has a value, MemberDate must
also:
1. Open your table in design view.
2. Open the Properties box (View menu).
3. Beside the Validation Rule in the Properties box, enter:
([MemberType] Is Null) OR ([MemberDate] Is Not Null)
The rule is satisifed two ways: if MemberType is blank it's happy.
Otherwise MemberDate has to be non-blank to make it happy.

Make sure you use the Validation Rule for the table (the one in the
Properties box), not the rule for a field (the one in the lower pane of
table design).

Other benefits of this approach:
- really simple if you need to add another level of membership;
- you can use the option group interface, which you can't if you use 2
different yes/no fields.


Hi. I have two option buttons: Member
(option1) and Affiliate(option2) Each are yes/no fields,
with an option group container, and code:
Option1_Click
Me!Option2 = Not Me!Option1
Option2_Click
Me!Option1 = Not Me!Option2.

If someone is a Member, I want MemberDate field required
and AffiliateDate non-enterable. If someone is an
Affiliate, I want AffiliateDate field requried and
MemberDate non-enterable. MemberDate and AffiliateDate
are Date/Time fields. Seems as though I'd attach the code
to be somehow associated with the Member field so that the
AffiliateDateName field is disabled when the Member field
is activated, and that the trigger needs to happen after
the Member field is populated. Events and coding are a
struggle for me. Any detailed suggestions?

Thanks! Stephanie
Click to expand...
Click to expand...
Click to expand...
 
You win! After much heavy whining on my side (if a newbie
whines on-line, does anyone hear?!) I added a
MemberOrAffiliate table and added the MemberOrAffiliateID
to Contacts table.
The validation rule does work (thanks!), however the
Access error box "you can't go the specified error" is not
descriptive enough to let a user know that the issue is
that they selected Member/Affiliate but didn't enter the
AssociatedSince date.
How can I give a more descriptive error message? Thanks.

Can I salvage this?:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If Member = True And IsNull(Me!MemberDate) Then
iAns = MsgBox("Please enter a Member 'Since' Date to
continue, or click Cancel to erase this record",
vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!MemberDate.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
End Sub
-----Original Message-----
Okay: you can use a combo with different values. Good idea. Even a
MembershipType table to make it easy to add type if you like.

It's possible to code a solution with your existing structure, though I
would not even consider that unless there is some valid reason why a person
could have both types of membership. You will need to set the Enabled
property of the controls in their AfterUpdate event, and also in the Current
event of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks! I always love it when you answer posts ;-)
Hmm. I have many reports that key off of Member = yes or
Affiliate = yes.
I don't really like the 1, 2 idea. I would perhaps prefer
having a combo box where I could select Affiliate or
Member (or add a type as needed). But I guess I would
need to fix my reports as well (adding the new table to
the query). If I went this route, could I still follow
your table properties suggestion?

I tried leaving the yes/no dilema in place and putting an
OnClick event on Member field
AffilateDateName.Enabled = False
AffiliateDateName.Locked = True

but it didn't work. Is it close?
Thanks,
Stephanie
-----Original Message-----
How about a simple suggestion? No code needed :-)

Replace your 4 fields with these two:
MemberType Number 1 = full member; 2 = affiliate.
MemberDate Date/Time date the person took out membership.

Then you want to insist that if MemberType has a value, MemberDate must
also:
1. Open your table in design view.
2. Open the Properties box (View menu).
3. Beside the Validation Rule in the Properties box, enter:
([MemberType] Is Null) OR ([MemberDate] Is Not Null)
The rule is satisifed two ways: if MemberType is blank it's happy.
Otherwise MemberDate has to be non-blank to make it happy.

Make sure you use the Validation Rule for the table (the one in the
Properties box), not the rule for a field (the one in
Click to expand...
the
lower pane of
table design).

Other benefits of this approach:
- really simple if you need to add another level of membership;
- you can use the option group interface, which you
Click to expand...
can't
if you use 2
different yes/no fields.


Hi. I have two option buttons: Member
(option1) and Affiliate(option2) Each are yes/no fields,
with an option group container, and code:
Option1_Click
Me!Option2 = Not Me!Option1
Option2_Click
Me!Option1 = Not Me!Option2.

If someone is a Member, I want MemberDate field required
and AffiliateDate non-enterable. If someone is an
Affiliate, I want AffiliateDate field requried and
MemberDate non-enterable. MemberDate and AffiliateDate
are Date/Time fields. Seems as though I'd attach the code
to be somehow associated with the Member field so that the
AffiliateDateName field is disabled when the Member field
is activated, and that the trigger needs to happen after
the Member field is populated. Events and coding are a
struggle for me. Any detailed suggestions?

Thanks! Stephanie
Click to expand...
Click to expand...


.
Click to expand...
 
Replace:
If Member = True And IsNull(Me!MemberDate) Then
with
If IsNull(Me!MemberDate) And Not IsNull(Member) Then

You could also use the Validation Text property of the Properties box in
table design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Stephanie said:
You win! After much heavy whining on my side (if a newbie
whines on-line, does anyone hear?!) I added a
MemberOrAffiliate table and added the MemberOrAffiliateID
to Contacts table.
The validation rule does work (thanks!), however the
Access error box "you can't go the specified error" is not
descriptive enough to let a user know that the issue is
that they selected Member/Affiliate but didn't enter the
AssociatedSince date.
How can I give a more descriptive error message? Thanks.

Can I salvage this?:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If Member = True And IsNull(Me!MemberDate) Then
iAns = MsgBox("Please enter a Member 'Since' Date to
continue, or click Cancel to erase this record",
vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!MemberDate.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
End If
End Sub
-----Original Message-----
Okay: you can use a combo with different values. Good idea. Even a
MembershipType table to make it easy to add type if you like.

It's possible to code a solution with your existing structure, though I
would not even consider that unless there is some valid reason why a person
could have both types of membership. You will need to set the Enabled
property of the controls in their AfterUpdate event, and also in the Current
event of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks! I always love it when you answer posts ;-)
Hmm. I have many reports that key off of Member = yes or
Affiliate = yes.
I don't really like the 1, 2 idea. I would perhaps prefer
having a combo box where I could select Affiliate or
Member (or add a type as needed). But I guess I would
need to fix my reports as well (adding the new table to
the query). If I went this route, could I still follow
your table properties suggestion?

I tried leaving the yes/no dilema in place and putting an
OnClick event on Member field
AffilateDateName.Enabled = False
AffiliateDateName.Locked = True

but it didn't work. Is it close?
Thanks,
Stephanie

-----Original Message-----
How about a simple suggestion? No code needed :-)

Replace your 4 fields with these two:
MemberType Number 1 = full member; 2 =
affiliate.
MemberDate Date/Time date the person took out
membership.

Then you want to insist that if MemberType has a value,
MemberDate must
also:
1. Open your table in design view.
2. Open the Properties box (View menu).
3. Beside the Validation Rule in the Properties box,
enter:
([MemberType] Is Null) OR ([MemberDate] Is Not
Null)
The rule is satisifed two ways: if MemberType is blank
it's happy.
Otherwise MemberDate has to be non-blank to make it happy.

Make sure you use the Validation Rule for the table (the
one in the
Properties box), not the rule for a field (the one in the
lower pane of
table design).

Other benefits of this approach:
- really simple if you need to add another level of
membership;
- you can use the option group interface, which you can't
if you use 2
different yes/no fields.


in message
Hi. I have two option buttons: Member
(option1) and Affiliate(option2) Each are yes/no
fields,
with an option group container, and code:
Option1_Click
Me!Option2 = Not Me!Option1
Option2_Click
Me!Option1 = Not Me!Option2.

If someone is a Member, I want MemberDate field required
and AffiliateDate non-enterable. If someone is an
Affiliate, I want AffiliateDate field requried and
MemberDate non-enterable. MemberDate and AffiliateDate
are Date/Time fields. Seems as though I'd attach the
code
to be somehow associated with the Member field so that
the
AffiliateDateName field is disabled when the Member
field
is activated, and that the trigger needs to happen after
the Member field is populated. Events and coding are a
struggle for me. Any detailed suggestions?

Thanks! Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top