Before update data validation question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning all,
On a form there is a combo box(Type), limited to list. There is also a
set of 10 check boxes (A - J). If a specific selection is made on the combo
box, the user must select at least two of the check boxes. Here is what I am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
 
Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control, or in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If
 
Thank you for responding Ken :)

This is on the Form's Before Update Event. I am testing the event by
selecting the type as 13, and selecting none, 1, and 2 of the check boxes
(each as a seperate test), then closing the form. The message and cancel are
not firing.

I re-wrote it to the example you posted, and the event still isn't firing.
Any suggestions for what I am doing wrong?

Ken Snell said:
Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control, or in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If

--

Ken Snell
<MS ACCESS MVP>

Renee said:
Good Morning all,
On a form there is a combo box(Type), limited to list. There is also a
set of 10 check boxes (A - J). If a specific selection is made on the
combo
box, the user must select at least two of the check boxes. Here is what I
am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
Click to expand...
Click to expand...
 
Renee,

What do you mean it doesn't work? The If structure never kicks in, even
when you expect it? If that's the case then most likely the combo is not
returning what you expect it to; to check, add a line like:

Debug.Print Me.Type

in your procedure, and see the value returned in the immediate window.
Also, your code treats the returned value as numeric; any chance the
combo's rowsource is text?

Now, on the rest of your code: you can exploit the faxt that a boolean
value returns 0 for false, -1 for true in a numeric expression, in order
to count the checked boxes in a loop like:

X = 0
For Each ctl in Me.Controls
If ctl.Name Like "Check_*" Then X = X - ctl.Value
Next

or, if they were named Check_1 through Check_10 instead:

X = 0
For i = 1 To 10
X = X - Me.Controls("Check_" & i)
Next

or, as they are:


X = 0
For i = 65 To 74
X = X - Me.Controls("Check_" & Chr(i))
Next

HTH,
Nikos
 
I confirmed the value is numeric. My current attempt looks like this:

intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J

If Me.Type = 13 And intX > -2 Then ' one or none have been selected as true
MsgBox "Select the combination ...", vbOKOnly
Cancel = True
End If

Thank you all again for your time,
Renee
 
Is the form bound to a query or table? If not, the form's BeforeUpdate event
will not occur.

How are you closing the form?

--

Ken Snell
<MS ACCESS MVP>


Renee said:
Thank you for responding Ken :)

This is on the Form's Before Update Event. I am testing the event by
selecting the type as 13, and selecting none, 1, and 2 of the check boxes
(each as a seperate test), then closing the form. The message and cancel
are
not firing.

I re-wrote it to the example you posted, and the event still isn't firing.
Any suggestions for what I am doing wrong?

Ken Snell said:
Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control, or
in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If

--

Ken Snell
<MS ACCESS MVP>

Renee said:
Good Morning all,
On a form there is a combo box(Type), limited to list. There is also
a
set of 10 check boxes (A - J). If a specific selection is made on the
combo
box, the user must select at least two of the check boxes. Here is what
I
am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
Click to expand...
Click to expand...
Click to expand...
 
The form and event are working now. I had made a mistake before the If
statement, so the sequence never got to it. Sorry for my mistake all, I do
appreciate the help!

My question now is:
When I click the close command button and trigger the form's before
update event...it will prompt the message, cancel the update, and close the
form. How can I change it so it will still prompt the message, cancel the
update, and return to the active form so the user can make the corrections.
How do I interupt/ cancel the form closing when the Form's update event is
cancelled?

Thanks again!
Renee


Ken Snell said:
Is the form bound to a query or table? If not, the form's BeforeUpdate event
will not occur.

How are you closing the form?

--

Ken Snell
<MS ACCESS MVP>


Renee said:
Thank you for responding Ken :)

This is on the Form's Before Update Event. I am testing the event by
selecting the type as 13, and selecting none, 1, and 2 of the check boxes
(each as a seperate test), then closing the form. The message and cancel
are
not firing.

I re-wrote it to the example you posted, and the event still isn't firing.
Any suggestions for what I am doing wrong?

Ken Snell said:
Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control, or
in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If

--

Ken Snell
<MS ACCESS MVP>

Good Morning all,
On a form there is a combo box(Type), limited to list. There is also
a
set of 10 check boxes (A - J). If a specific selection is made on the
combo
box, the user must select at least two of the check boxes. Here is what
I
am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
Click to expand...
Click to expand...
Click to expand...
 
I would take a different approach.

Put a button on the form labeled "Close". Use that button's click event to
run your data validation code (in the Click event -- remove it from the
form's BeforeUpdate event) and then to close the form. *Do not use the Red X
button on the form to close the form!*

This way, your code can be stopped if the validation says something is
wrong. For example:

Private Sub CommandButtonClose_Click()
Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
else
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Renee said:
The form and event are working now. I had made a mistake before the If
statement, so the sequence never got to it. Sorry for my mistake all, I do
appreciate the help!

My question now is:
When I click the close command button and trigger the form's before
update event...it will prompt the message, cancel the update, and close
the
form. How can I change it so it will still prompt the message, cancel the
update, and return to the active form so the user can make the
corrections.
How do I interupt/ cancel the form closing when the Form's update event is
cancelled?

Thanks again!
Renee


Ken Snell said:
Is the form bound to a query or table? If not, the form's BeforeUpdate
event
will not occur.

How are you closing the form?

--

Ken Snell
<MS ACCESS MVP>


Renee said:
Thank you for responding Ken :)

This is on the Form's Before Update Event. I am testing the event by
selecting the type as 13, and selecting none, 1, and 2 of the check
boxes
(each as a seperate test), then closing the form. The message and
cancel
are
not firing.

I re-wrote it to the example you posted, and the event still isn't
firing.
Any suggestions for what I am doing wrong?

:

Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control,
or
in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If

--

Ken Snell
<MS ACCESS MVP>

Good Morning all,
On a form there is a combo box(Type), limited to list. There is
also
a
set of 10 check boxes (A - J). If a specific selection is made on
the
combo
box, the user must select at least two of the check boxes. Here is
what
I
am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
Click to expand...
Click to expand...
Click to expand...
 
Will do, thank you :)

Ken Snell said:
I would take a different approach.

Put a button on the form labeled "Close". Use that button's click event to
run your data validation code (in the Click event -- remove it from the
form's BeforeUpdate event) and then to close the form. *Do not use the Red X
button on the form to close the form!*

This way, your code can be stopped if the validation says something is
wrong. For example:

Private Sub CommandButtonClose_Click()
Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
else
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Renee said:
The form and event are working now. I had made a mistake before the If
statement, so the sequence never got to it. Sorry for my mistake all, I do
appreciate the help!

My question now is:
When I click the close command button and trigger the form's before
update event...it will prompt the message, cancel the update, and close
the
form. How can I change it so it will still prompt the message, cancel the
update, and return to the active form so the user can make the
corrections.
How do I interupt/ cancel the form closing when the Form's update event is
cancelled?

Thanks again!
Renee


Ken Snell said:
Is the form bound to a query or table? If not, the form's BeforeUpdate
event
will not occur.

How are you closing the form?

--

Ken Snell
<MS ACCESS MVP>


Thank you for responding Ken :)

This is on the Form's Before Update Event. I am testing the event by
selecting the type as 13, and selecting none, 1, and 2 of the check
boxes
(each as a seperate test), then closing the form. The message and
cancel
are
not firing.

I re-wrote it to the example you posted, and the event still isn't
firing.
Any suggestions for what I am doing wrong?

:

Define "doesn't work". During which event are you running this code?

You could simplify the code a bit....(Cancel usage assumes that you're
running this code in the Exit or BeforeUpdate event for the control,
or
in
the form's BeforeUpdate event):

Dim intX As Integer
intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J
If Me.[Type] = 13 And intX > -2 Then
MsgBox "message"
Cancel = True
Exit Sub
End If

--

Ken Snell
<MS ACCESS MVP>

Good Morning all,
On a form there is a combo box(Type), limited to list. There is
also
a
set of 10 check boxes (A - J). If a specific selection is made on
the
combo
box, the user must select at least two of the check boxes. Here is
what
I
am
working with, on the before update event:

Dim X As Integer
X = 0

If Me.Check_A = True Then X = X + 1
If Me.Check_B = True Then X = X + 1
If Me.Check_C = True Then X = X + 1
If Me.Check_D = True Then X = X + 1
If Me.Check_E = True Then X = X + 1
If Me.Check_F = True Then X = X + 1
If Me.Check_G = True Then X = X + 1
If Me.Check_H = True Then X = X + 1
If Me.Check_I = True Then X = X + 1
If Me.Check_J = True Then X = X + 1

If Me.Type = 13 And X <= 1 Then
MsgBox ....
Cancel = True
Resume Exit_Update
End If

Any suggestions as to why this doesn't work, or a better answer?

Thank you,
Renee
Click to expand...
Click to expand...
Click to expand...
 
I suppose it's solved now with Ken's help, right?
I confirmed the value is numeric. My current attempt looks like this:

intX = Me.Check_A + Me.Check_B + Me.Check_C + Me.Check_D + _
Me.Check_E + Me.Check_F + Me.Check_G + Me.Check_H + _
Me.Check_I + Me.Check_J

If Me.Type = 13 And intX > -2 Then ' one or none have been selected as true
MsgBox "Select the combination ...", vbOKOnly
Cancel = True
End If

Thank you all again for your time,
Renee

:
Click to expand...
 
Back
Top