Message box help-probably easy

[Debbiedo]

I searched this group but still could not find the answer to this
coding problem

On closing a form (Access 2003) I want the following to occur:

If ALL of check boxes 1,2,3 are not checked then display the following
message box:

"No check boxes were selected. Delete record and close?"

Option "Yes" deletes the present record from the form and closes form.

Option "No" returns to form.

Else close form

Many thanks in advance for any and all help.

Deb

 

[Guest]

You could use the Unload event of your form to trap for this. The code might
look something like following: (you will nee to modify the field names to fit
your form)

Private Sub Form_Unload(Cancel As Integer)

Dim Response As Integer

If Me.CheckBox1 = False And Me.CheckBox2 = False And Me.CheckBox3 =
False Then

Response = MsgBox("No check boxes were selected. Delete record
and close?", vbYesNo, "Incomplete Record")

If Response = vbYes Then

code to delete goes here

Else

Cancel = True
Me.CheckBox1.SetFocus

End If
End If

End Sub

The code use use to "delete" the record might vary depending on what type of
form you are using. For example if it is an unbound form, and the user hasn't
taken any action to save the record yet, then you could probably use Me.Undo
to just "undo" it before it is saved to the table. If your form is bound
directly to a table, and there are other fields on your form (besides the
check boxes) that have already been filled out, then you will need to put in
code to actually delete the record from the table.

HTH

 

[Guest]

Use the form's UnLoad event because it can be canceled if necessary

With Me
If Abs(.Check1 + .Check2 + .Check3) <> 3 Then
If MsgBox(""No check boxes were selected." & vbNewLine & _
Delete record and close?", vbQuestion + vbYesNo) = vbYes
Then
.Undo
Docmd.Close
Else
Cancel = True
End If
End If
End With

Couple of items. First your description of the state of the check boxes
was confusing. First, you said "If ALL of check boxes 1,2,3 are not
checked", But the message box says "No check boxes were selected. Delete
record and close?".
The first says all the boxes must be checked, but the second says if any
check box is checked, so I don't know which it is. The code above is looking
for all the boxes to be checked. If you want to test for at least one
checked, change the line to:
If .Check1 + .Check2 + .Check3 = 0 Then

Then, you did not say if this was for new records or existing records. The
code above will only handle new records.

 

[John W. Vinson]

I searched this group but still could not find the answer to this
coding problem

On closing a form (Access 2003) I want the following to occur:

If ALL of check boxes 1,2,3 are not checked then display the following
message box:

"No check boxes were selected. Delete record and close?"

Option "Yes" deletes the present record from the form and closes form.

Option "No" returns to form.

Else close form

Many thanks in advance for any and all help.

Deb

Use the form's BeforeUpdate event (which is cancellable and fires before the
record is written to disk - Close comes too late):

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not(Me!Check1 OR Me!Check2 OR Me!Check3) Then
Cancel = True ' you cancel the update in either case
iAns = MsgBox("No check boxes were selected. Delete record and close?", _
vbYesNo)
If iAns = vbYes Then
Me.Undo ' erase the user's input
'DoCmd.Close acForm, Me.Name ' if you want to actually close the form
End If
End If
End Sub

John W. Vinson [MVP]

 

[Guest]

John, are you saying the Unload event is too late?

 

[John W. Vinson]

John, are you saying the Unload event is too late?

Well, I was replying to a downloaded message, and hadn't seen your response -
but yes, I believe that Unload fires after both BeforeUpdate and AfterUpdate.
If the OP is trying to prevent the record from being written to disk (rather
than just prevent closing the form), then yes, it's too late.

John W. Vinson [MVP]

 

[Debbiedo]

Use the form's UnLoad event because it can be canceled if necessary

With Me
If Abs(.Check1 + .Check2 + .Check3) <> 3 Then
If MsgBox(""No check boxes were selected." & vbNewLine & _
Delete record and close?", vbQuestion + vbYesNo) = vbYes
Then
.Undo
Docmd.Close
Else
Cancel = True
End If
End If
End With

Couple of items. First your description of the state of the check boxes
was confusing. First, you said "If ALL of check boxes 1,2,3 are not
checked", But the message box says "No check boxes were selected. Delete
record and close?".
The first says all the boxes must be checked, but the second says if any
check box is checked, so I don't know which it is. The code above is looking
for all the boxes to be checked. If you want to test for at least one
checked, change the line to:
If .Check1 + .Check2 + .Check3 = 0 Then

Then, you did not say if this was for new records or existing records. The
code above will only handle new records.
--
Dave Hargis, Microsoft Access MVP













- Show quoted text -

Sorry for the confusion.

If they did not select any of the boxes then the message box should
appear.

 

[Debbiedo]

You could use the Unload event of your form to trap for this. The code might
look something like following: (you will nee to modify the field names to fit
your form)

Private Sub Form_Unload(Cancel As Integer)

Dim Response As Integer

If Me.CheckBox1 = False And Me.CheckBox2 = False And Me.CheckBox3 =
False Then

Response = MsgBox("No check boxes were selected. Delete record
and close?", vbYesNo, "Incomplete Record")

If Response = vbYes Then

code to delete goes here

Else

Cancel = True
Me.CheckBox1.SetFocus

End If
End If

End Sub

The code use use to "delete" the record might vary depending on what type of
form you are using. For example if it is an unbound form, and the user hasn't
taken any action to save the record yet, then you could probably use Me.Undo
to just "undo" it before it is saved to the table. If your form is bound
directly to a table, and there are other fields on your form (besides the
check boxes) that have already been filled out, then you will need to put in
code to actually delete the record from the table.

HTH













- Show quoted text -

It is a bound form, of course. The record is always new.

 

[Debbiedo]

Use the form's BeforeUpdate event (which is cancellable and fires before the
record is written to disk - Close comes too late):

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not(Me!Check1 OR Me!Check2 OR Me!Check3) Then
Cancel = True ' you cancel the update in either case
iAns = MsgBox("No check boxes were selected. Delete record and close?", _
vbYesNo)
If iAns = vbYes Then
Me.Undo ' erase the user's input
'DoCmd.Close acForm, Me.Name ' if you want to actually close the form
End If
End If
End Sub

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John, Would I want to use OR or AND in the if/and statement? If they
check even one of the 3 boxes, a message box will NOT need to appear,
i.e. all 3 boxes have to be unchecked for the message box to appear.

Thanks

 

[John W. Vinson]

John, Would I want to use OR or AND in the if/and statement? If they
check even one of the 3 boxes, a message box will NOT need to appear,
i.e. all 3 boxes have to be unchecked for the message box to appear.

Thanks

Sorry... that was a bit cryptic! Using Boolean logic here: the expression

Me!Check1 OR Me!Check2 OR Me!Check3

will be TRUE if any one of the checkboxes is checked.

Not(Me!Check1 OR Me!Check2 OR Me!Check3)

will be True only if that expression is False.

Therefore if any checkbox is checked, the IF block will not be executed; if
they're all unchecked, it will be executed.

John W. Vinson [MVP]

 

[Debbiedo]

Sorry... that was a bit cryptic! Using Boolean logic here: the expression

Me!Check1 OR Me!Check2 OR Me!Check3

will be TRUE if any one of the checkboxes is checked.

Not(Me!Check1 OR Me!Check2 OR Me!Check3)

will be True only if that expression is False.

Therefore if any checkbox is checked, the IF block will not be executed; if
they're all unchecked, it will be executed.

John W. Vinson [MVP]

Well, it turns out that the data is already updated so the before
update command won't work. I am actually adding some data from another
form (FormA) to the one above (FormB) upon opening FormB. I needed to
refresh the data at this time. I suppose I could not refresh, but I
did have reasons for wanting to do so. Sorry, I am new to this and
don't always know what variables are pertinent.

Deb

 

[John W. Vinson]

Well, it turns out that the data is already updated so the before
update command won't work. I am actually adding some data from another
form (FormA) to the one above (FormB) upon opening FormB. I needed to
refresh the data at this time. I suppose I could not refresh, but I
did have reasons for wanting to do so. Sorry, I am new to this and
don't always know what variables are pertinent.

Try using the form's Current event, then, to check at the time the record is
loaded.

You're not loading data from FormA into FormB though. Forms are *NOT*
containers for data; they are just windows onto the data stored in tables. If
you're storing these same three yes/no fields in two different tables, you may
be on the wrong track with regard to your table structure!

John W. Vinson [MVP]

 

[Debbiedo]

Try using the form's Current event, then, to check at the time the record is
loaded.

You're not loading data from FormA into FormB though. Forms are *NOT*
containers for data; they are just windows onto the data stored in tables. If
you're storing these same three yes/no fields in two different tables, you may
be on the wrong track with regard to your table structure!

John W. Vinson [MVP]

I am not loading the 3 fields from one table to the next. This info is
in a single text field (which is where the problem lies). I am
extracting this data , and other fields (such as address, which are
'loaded' into the other table via FormA and FormB), so that I can
geocode them. The software program that uses this info does not
recognize joined tables which is very annoying but we are committed to
it, so what can I do? The original table is not the best design, but
it is generated from a MS Word form document from another department.
I have no control over its design. I need to glean info from this
table and put into another table. I am trying to create a process that
will automate as much data transfer as possible so that the mundane
task of copy and paste is minimized for our data entry personnel. I am
not a programmer but I see the need for this, so here I am.

Thanks for all the help thus far. I shall try the Current event and
see how that works.
Deb

 

[Debbiedo]

I am not loading the 3 fields from one table to the next. This info is
in a single text field (which is where the problem lies). I am
extracting this data , and other fields (such as address, which are
'loaded' into the other table via FormA and FormB), so that I can
geocode them. The software program that uses this info does not
recognize joined tables which is very annoying but we are committed to
it, so what can I do? The original table is not the best design, but
it is generated from a MS Word form document from another department.
I have no control over its design. I need to glean info from this
table and put into another table. I am trying to create a process that
will automate as much data transfer as possible so that the mundane
task of copy and paste is minimized for our data entry personnel. I am
not a programmer but I see the need for this, so here I am.

Thanks for all the help thus far. I shall try the Current event and
see how that works.
Deb- Hide quoted text -

- Show quoted text -

I solved this problem by taking a slightly different tact. I added a
delete button to my form and added the following to a command button:

Private Sub UpdateAndExit_Click()
If Not (Me!Check1 Or Me!Check2 Or Me!Check3) Then
MsgBox "No boxes were selected. Please select boxes or delete
record.", vbOKOnly, "Warning"
Else
DoCmd.Close
End IF
End Sub

Thanks for all the help.