Checkbox (default to no) - must be checked before saving/exiting

[nicolam1980]

Hi

I have a feeling this might be a basic question but am new to access so
please be patient!!

I am creating a database for a team at work to use instead of paper
checklists. One such section I am creating concerns priority checks - I have
Yes/No boxes on the form that are defaulted to No.

I need to ensure people cannot save a record or exit the DB if any of these
boxes are left unchecked - can anyone help?

Thanks

Nicola

 

[Tom van Stiphout]

On Mon, 26 Oct 2009 06:21:02 -0700, nicolam1980

The appropriate place to make this check is in Form_BeforeUpdate,
because you can set Cancel=True which prevents user from leaving the
form.
if me.myCheckbox1.value = false
or me.myCheckbox2.value = false
or me.myCheckbox3.value = false
then Cancel = True

If you literally mean "all checkboxes" you could alternatively write:
dim ctl as control
for each ctl in me.controls
if typeof(ctl) is checkbox then
if ctl.Value = false then
Cancel = True
Exit For
end if
end if
next

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

Hi

I have a feeling this might be a basic question but am new to access so
please be patient!!

I am creating a database for a team at work to use instead of paper
checklists. One such section I am creating concerns priority checks - I
have
Yes/No boxes on the form that are defaulted to No.

I need to ensure people cannot save a record or exit the DB if any of
these
boxes are left unchecked - can anyone help?

How about something like (untested):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 to Me.Controls.Count - 1
If TypeOf Me.Controls(i) Is acCheckbox Then
If Me.Controls(i).Value = False Then
MsgBox "Please fill in all Checkboxes", vbOKOnly
Cancel = True
Me.Controls(i).SetFocus
Exit Sub
End If
End If
Next I
End Sub

 

[Tom van Stiphout]

On Mon, 26 Oct 2009 10:00:35 -0500, "Arvin Meyer [MVP]"

Ough, Exit Sub out of a For loop. Efficient and allowed, but doesn't
get the elegance price.

-Tom.
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

After lookinig at the code, I see a mistake which needs correcting, try
(also untested):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 to Me.Controls.Count - 1
If Me.Controls(i).ControlType is acCheckbox Then
If Me.Controls(i).Value = False Then
MsgBox "Please fill in all Checkboxes", vbOKOnly
Cancel = True
Me.Controls(i).SetFocus
Exit Sub
End If
End If
Next I
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

Still messed it up (same line too, but this time it's tested):

If Me.Controls(i).ControlType is acCheckbox Then

should be:

If Me.Controls(i).ControlType = acCheckbox Then

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

On Mon, 26 Oct 2009 10:00:35 -0500, "Arvin Meyer [MVP]"

Ough, Exit Sub out of a For loop. Efficient and allowed, but doesn't
get the elegance price.

-Tom.
Microsoft Access MVP

Perhaps not, I could have also used:

Exit For

to get out of the loop. Surely you were not suggesting that the loop
continue through every control, even after it found 1?

 

[David W. Fenton]

After lookinig at the code, I see a mistake which needs
correcting, try (also untested):

Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 to Me.Controls.Count - 1
If Me.Controls(i).ControlType is acCheckbox Then
If Me.Controls(i).Value = False Then
MsgBox "Please fill in all Checkboxes", vbOKOnly
Cancel = True
Me.Controls(i).SetFocus
Exit Sub
End If
End If
Next I
End Sub

I'd still change it according to Tom's comment about Exit Sub in the
middle of a Loop.

Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acCheckbox Then
If Not (ctl) Then
MsgBox "Please fill in all Checkboxes", vbOKOnly
Cancel = True
ctl.SetFocus
End If
If Cancel = True Then
Exit For
End If
End If
Set ctl = Nothing
Next I

Also, this is exactly the kind of place where it would be vastly
speeded up if you populated a custom collection in the form's OnLoad
event, and walked it instead of the whole Controls collection. You
then wouldn't have to check anything but the value.