check box with required fields

[SylvieB]

Hello Group
I need help.
On a form, I created a check box named approval. “approval†can only be
checked if the following fields are not blank: ProposalAmount, AwardDate,
win%.
If one of them or all are blank, a pop up message will display asking the
user to fill out those fields.
Making those fields required in the table won’t work because the condition
is based only on those 3 fields. The form contains other fields that don’t
need to be filled out to put a check mark.
How do you accomplish that? I’m using Access 2007. Thanks in advance for
your help.
I started writing this code but it does not work

Private Sub chkDg3offer_BeforeUpdate(Cancel As Integer)

If Me.chkDg3offer = True Then
If ProposalAmount = 0 Then
MsgBox "you must enter a value.", vbInformation, "missing value"
Cancel = True
End If
End If
End Sub

 

[Dorian]

You need the listed code attached to the FORM beforeupdate event not the
event for the checkbox.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[SylvieB]

Thank you Dorian for your suggestion, it works much better and thank you Ken
for fixing my code. I still have two little issues if you can please help me.
1- How do I add two other controls as part of the condition? I need to add
awardDate and Win%. I don't know how to write the code. I tried:
If Nz(proposalamount, awarddate, win%, )) = 0 then;
that did not work.
2- if no value is entered, the msg pops up on the screen but after clicking
ok twice to clear the message, it closes the form. How can i make the message
disappears once the user clicks OK and leave the form open to enter a value?
Thank you very much for your help.

 

[SylvieB]

ten thousands thanks... It's working great... You are a genius!!!

You need to use a Boolean OR operation to see if any of the controls are Null
or zero:

If Me.chkDg3offer = True Then
If Nz(ProposalAmount, 0) = 0 _
Or IsNull(awarddate) _
Or Nz([win%], 0) = 0 Then
MsgBox "you must enter a value.", vbInformation, "missing value"
Cancel = True
End If
End If

But you might prefer to test each separately so you can show a different
message for each, e.g.

If Me.chkDg3offer = True Then
If Nz(ProposalAmount, 0) = 0 Then
MsgBox "you must enter a proposal amount value.", _
vbInformation, "missing value"
Cancel = True
ElseIf IsNull(awarddate) Then
MsgBox "you must enter an award date.", _
vbInformation, "missing value"
Cancel = True
ElseIf Nz([win%], 0) = 0 Then
MsgBox "you must enter a win percentage.", _
vbInformation, "missing value"
Cancel = True
End If
End If

Note that the awarddate is tested for Null with the IsNull function rather
than for zero. In the case of a date/time field testing for zero would
actually be testing for a vale of 30 December 1899 00:00:00. That might
sound strange but date/time values in access are implemented as a 64 bit
floating point number as an offset from that date/time, which is represented
by zero under the skin.

I've wrapped win% in square brackets because of the percentage sign in its
name. As this is a special character it is necessary for the name to be
wrapped in brackets.

Ken Sheridan
Stafford, England

Thank you Dorian for your suggestion, it works much better and thank you Ken
for fixing my code. I still have two little issues if you can please help me.
1- How do I add two other controls as part of the condition? I need to add
awardDate and Win%. I don't know how to write the code. I tried:
If Nz(proposalamount, awarddate, win%, )) = 0 then;
that did not work.
2- if no value is entered, the msg pops up on the screen but after clicking
ok twice to clear the message, it closes the form. How can i make the message
disappears once the user clicks OK and leave the form open to enter a value?
Thank you very much for your help.

That should work if the value of the ProposalAmount control is 0, but if it
might be Null, particularly if it doesn't have a DefaultValue property of 0,

[quoted text clipped - 40 lines]

End If
End Sub