Yes/No Fields

  • Thread starter Thread starter Task Database Nightmare
  • Start date Start date
T

Task Database Nightmare

I have a complexed table that will be used to create several queries, forms
and tables. Have can I make a field required after someone chooses Yes from
a Yes/No Field.

Example: If you choose Yes to Print, then I want the Target Print Date to
be required.
 
On Fri, 17 Oct 2008 12:04:00 -0700, Task Database Nightmare <Task Database
I have a complexed table that will be used to create several queries, forms
and tables. Have can I make a field required after someone chooses Yes from
a Yes/No Field.
Click to expand...
Example: If you choose Yes to Print, then I want the Target Print Date to
be required.
Click to expand...

You can't do this in a Table, since tables have no useable events. You can do
it in a Form, however; use the AfterUpdate event of the checkbox control.

It's not clear what the sequence of events should be - what is the purpose of
the checkbox? Do you want the Target Print Date to automatically fill in with
today's date, or just want the user to enter a date? Is the target print date
in fact an appropriate field in the table, or will the same record be printed
multiple times (suggesting a related table)?
 
The user would enter a date - which will be a future date. This date would
only be entered in one field.
 
The user would enter a date - which will be a future date. This date would
only be entered in one field.
Click to expand...

Then I'd suggest putting code in the Form's BeforeUpdate event (table entry
could be constrained with a Table Validation rule but it's going to be
user-hostile):

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!yesnofield Then
If IsNull(Me!datefield) Then
MsgBox "If YesNoField is checked you must enter a date", vbOKOnly
Me!datefield.SetFocus
Cancel = True
End If
End If
End Sub
 
In table design view, open the Properties sheet.

Beside the Validation Rule in the Properties sheet, enter:
([Print] = False) OR ([Target Print Date] Is Not Null)

Don't use the Validation Rule in the lower pane of table design: that one
applies to one field only, and you are comparing fields.

For an explanation, see:
http://allenbrowne.com/ValidationRule.html
Basicallly, the rule is satisfied if Print is not checked; otherwise it is
only satisfied if Target Print Date has a value.

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

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

"Task Database Nightmare" <Task Database
(e-mail address removed)> wrote in message
news:[email protected]...
 
Ok - that worked in the table but it did not carry over to the form I already
created. Do I need to recreate the form? And ideally I would like to add 2
other instances of the same type of rule - just with other Yes/No Fields. Can
I use the AND function and add the other 2 rules?

Allen Browne said:
In table design view, open the Properties sheet.

Beside the Validation Rule in the Properties sheet, enter:
([Print] = False) OR ([Target Print Date] Is Not Null)

Don't use the Validation Rule in the lower pane of table design: that one
applies to one field only, and you are comparing fields.

For an explanation, see:
http://allenbrowne.com/ValidationRule.html
Basicallly, the rule is satisfied if Print is not checked; otherwise it is
only satisfied if Target Print Date has a value.

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

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

"Task Database Nightmare" <Task Database
(e-mail address removed)> wrote in message
I have a complexed table that will be used to create several queries, forms
and tables. Have can I make a field required after someone chooses Yes
from
a Yes/No Field.

Example: If you choose Yes to Print, then I want the Target Print Date to
be required.
Click to expand...
Click to expand...
 
If it worked in the table, then it will block the record in the form as
well. Can you be more precise about 'did not carry over to the form'? Do you
mean you did not get the message? Or that the value could be saved even if
it did not meet the rule? AFAIK, there is no way Access can save a bad value
in the table (even through the form) if it does not meet the rule.

Yes: you can add further rules. Be careful with the bracketing: as you are
mixing ANDs and ORs, you need to be clear here.

I'm wondering about whether it is really justified to have both pairs of
field though? Would it be possible to interpret a blank Target Print Date to
mean that Print is false, and so the dependent yes/no field is actually
redundant? As far as possible, we try to avoid both redundant fields and
dependent fields in a normalized database.

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

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

"Task Database Nightmare" <[email protected]>
wrote in message
Ok - that worked in the table but it did not carry over to the form I
already
created. Do I need to recreate the form? And ideally I would like to add
2
other instances of the same type of rule - just with other Yes/No Fields.
Can
I use the AND function and add the other 2 rules?

Allen Browne said:
In table design view, open the Properties sheet.

Beside the Validation Rule in the Properties sheet, enter:
([Print] = False) OR ([Target Print Date] Is Not Null)

Don't use the Validation Rule in the lower pane of table design: that one
applies to one field only, and you are comparing fields.

For an explanation, see:
http://allenbrowne.com/ValidationRule.html
Basicallly, the rule is satisfied if Print is not checked; otherwise it
is
only satisfied if Target Print Date has a value.

"Task Database Nightmare" <Task Database
(e-mail address removed)> wrote in message
I have a complexed table that will be used to create several queries,
forms
and tables. Have can I make a field required after someone chooses Yes
from
a Yes/No Field.

Example: If you choose Yes to Print, then I want the Target Print Date
to
be required.
Click to expand...
Click to expand...
Click to expand...
 
Still not working - isn't working in the Table either as I thought it was.
Someone suggested I just need to use the IF function but not sure where or
how. Basically I am hoping to say IF Print = Yes, then Target Print Date Is
Not Null. ( I have 3 other relationships similar to this within my table so
I would need to repeat scenario using other fields). There are several
associates that will be completing a form - form is driven from a table I
created.

Allen Browne said:
If it worked in the table, then it will block the record in the form as
well. Can you be more precise about 'did not carry over to the form'? Do you
mean you did not get the message? Or that the value could be saved even if
it did not meet the rule? AFAIK, there is no way Access can save a bad value
in the table (even through the form) if it does not meet the rule.

Yes: you can add further rules. Be careful with the bracketing: as you are
mixing ANDs and ORs, you need to be clear here.

I'm wondering about whether it is really justified to have both pairs of
field though? Would it be possible to interpret a blank Target Print Date to
mean that Print is false, and so the dependent yes/no field is actually
redundant? As far as possible, we try to avoid both redundant fields and
dependent fields in a normalized database.

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

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

"Task Database Nightmare" <[email protected]>
wrote in message
Ok - that worked in the table but it did not carry over to the form I
already
created. Do I need to recreate the form? And ideally I would like to add
2
other instances of the same type of rule - just with other Yes/No Fields.
Can
I use the AND function and add the other 2 rules?

Allen Browne said:
In table design view, open the Properties sheet.

Beside the Validation Rule in the Properties sheet, enter:
([Print] = False) OR ([Target Print Date] Is Not Null)

Don't use the Validation Rule in the lower pane of table design: that one
applies to one field only, and you are comparing fields.

For an explanation, see:
http://allenbrowne.com/ValidationRule.html
Basicallly, the rule is satisfied if Print is not checked; otherwise it
is
only satisfied if Target Print Date has a value.

"Task Database Nightmare" <Task Database
(e-mail address removed)> wrote in message
I have a complexed table that will be used to create several queries,
forms
and tables. Have can I make a field required after someone chooses Yes
from
a Yes/No Field.

Example: If you choose Yes to Print, then I want the Target Print Date
to
be required.
Click to expand...
Click to expand...
Click to expand...
 
Go back to the original reply.

It does work. As stated be sure to use the correct validation rule.

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

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

"Task Database Nightmare" <[email protected]>
wrote in message
news:[email protected]...
 
Obviously I am doing something wrong. It works when I just use one parameter
but when I add the others, it doesn't work anymore. Below is what I have
noted in the Table Properties. I tried it both with OR and AND and neither
works. Help please - thanks!

([Print]=False) Or ([Target Print Date] Is Not Null) Or ([Supply]=False) Or
([Target Supply Date] Is Not Null) Or (=False) Or ([Email Target Date]
Is Not Null) Or ([Mail]=False) Or ([Mail Target Date] Is Not Null) Or
([Posting]=False) Or ([Posting Target Date] Is Not Null)
 
Obviously I am doing something wrong. It works when I just use one parameter
but when I add the others, it doesn't work anymore. Below is what I have
noted in the Table Properties. I tried it both with OR and AND and neither
works. Help please - thanks!
Click to expand...

It's pretty tricky! You need a mix of OR and AND, and it needs to be the
correct mix.

The validation rule is a Boolean algebra statement using the OR operator (A OR
B = True if A is true, or B is true, or both are true) and the AND operator (A
AND B is true if and only if both A and B are true); if the entire expression
is TRUE the record is valid. You need all five pairs of conditions to be TRUE
(whether true by virtue of the checkbox *or* by virtue of the non-null field)
so you need some parenthesis nesting and some ANDs:

([Print]=False Or [Target Print Date] Is Not Null)
AND
([Supply]=False Or [Target Supply Date] Is Not Null)
AND
(=False Or [Email Target Date] Is Not Null)
AND
([Mail]=False Or [Mail Target Date] Is Not Null)
AND
([Posting]=False Or [Posting Target Date] Is Not Null)
 
Ok - it worked in the Table. If I enter a task in the table it works - but
not in the Form?

John W. Vinson said:
Obviously I am doing something wrong. It works when I just use one parameter
but when I add the others, it doesn't work anymore. Below is what I have
noted in the Table Properties. I tried it both with OR and AND and neither
works. Help please - thanks!
Click to expand...

It's pretty tricky! You need a mix of OR and AND, and it needs to be the
correct mix.

The validation rule is a Boolean algebra statement using the OR operator (A OR
B = True if A is true, or B is true, or both are true) and the AND operator (A
AND B is true if and only if both A and B are true); if the entire expression
is TRUE the record is valid. You need all five pairs of conditions to be TRUE
(whether true by virtue of the checkbox *or* by virtue of the non-null field)
so you need some parenthesis nesting and some ANDs:

([Print]=False Or [Target Print Date] Is Not Null)
AND
([Supply]=False Or [Target Supply Date] Is Not Null)
AND
(=False Or [Email Target Date] Is Not Null)
AND
([Mail]=False Or [Mail Target Date] Is Not Null)
AND
([Posting]=False Or [Posting Target Date] Is Not Null)
[/QUOTE]
Click to expand...
 
Ok - it worked in the Table. If I enter a task in the table it works - but
not in the Form?
Click to expand...

What "doesn't work"? Surely the form is bound to the table? What happens when
you enter an invalid record via the form?

You can also use VBA code in the Form's BeforeUpdate event to check the
validity of the data:

([Print]=False) Or ([Target Print Date] Is Not Null) Or ([Supply]=False) Or
([Target Supply Date] Is Not Null) Or (=False) Or ([Email Target Date]
Is Not Null) Or ([Mail]=False) Or ([Mail Target Date] Is Not Null) Or
([Posting]=False) Or ([Posting Target Date] Is Not Null)

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!Print Then
If IsNull(Me![Target Print Date]) Then
MsgBox "Please fill in Target Print Date"
Me![Target Print Date].SetFocus
Cancel = True
End If
End If

<similar code for the rest>

One question: might it make sense to have all of the target dates simply have
a Default Value of Date(), or some other suitable default, so the user can
just accept the default rather than filling them all in?
 
Back
Top