how do you turn off the auto save on tables?

[llaxmi]

I understand that there is a way to prompt users entering data in a table or
form whether they want to save the information they have enterred or not. Is
there a setting or control for this, or is a macro necessary?

 

[Ken Snell [MVP]]

Table? No.

Form? Yes, but you must program the form to do that. You would use either a
macro or VBA code to run code on the form's BeforeUpdate event to ask the
user if he/she wants to save the data, and then to act accordingly based on
the user's answer.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If vbNo = MsgBox("Do you want to save the data?", vbQuestion+vbYesNo, _
"Save The Data?") Then Cancel = True
End Sub

 

[llaxmi]

Thanks Ken.
I am getting an error "expected end sub" when I close the record though.

 

[Ken Snell [MVP]]

Post what you have as the code. You may have a linewrap error.

 

[llaxmi]

Hi Ken:
first, thanks again for your guidance. I got the code to prompt ok and the
yes option seems to work ok, however, the no option returns a message, you
cant save this record at this time, MS Access may have encountered an error
while trying to save a record. If you close the object any data changes will
be lost. Do you want to close the database object anyway?save anyway? yes,
closes and no allows you to click save and then everything is ok.

Here is the code...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If vbNo = MsgBox("Do you want to save the data?", vbQuestion + vbYesNo, _
"Save The Data?") Then Cancel = True
End Sub

Private Sub Form_Load()

End Sub


Private Sub returntomenu_Click()
On Error GoTo Err_returntomenu_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_returntomenu_Click:
Exit Sub

Err_returntomenu_Click:
MsgBox Err.Description
Resume Exit_returntomenu_Click

End Sub
Private Sub exitaccess_Click()
On Error GoTo Err_exitaccess_Click


DoCmd.Quit

Exit_exitaccess_Click:
Exit Sub

Err_exitaccess_Click:
MsgBox Err.Description
Resume Exit_exitaccess_Click

End Sub
Private Sub open_ship_data_Click()
On Error GoTo Err_open_ship_data_Click

Dim stDocName As String

stDocName = "Q shipment data"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_open_ship_data_Click:
Exit Sub

Err_open_ship_data_Click:
MsgBox Err.Description
Resume Exit_open_ship_data_Click

End Sub
Private Sub shipped_cans_Click()
On Error GoTo Err_shipped_cans_Click

Dim stDocName As String

stDocName = "Q shipped cans"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_shipped_cans_Click:
Exit Sub

Err_shipped_cans_Click:
MsgBox Err.Description
Resume Exit_shipped_cans_Click

End Sub

 

[Ken Snell [MVP]]

That error message usually occurs because the user is trying to close the
database after entering data. I had understood that the user was clicking a
button or using the navigation buttons to move away from the record that was
being entered.

Provide more details about exactly what the user is doing on the form before
and during the "data save".
--

Ken Snell
<MS ACCESS MVP>

 

[llaxmi]

Yes, I should have been more specific. The error is occuring after going to
file|close. There is also a command button on the form to return the user to
the switchboard (closing the form) and the prompt does not occur when the
button is pressed. Here is the code for the button:

Option Compare Database

Private Sub returntoswitchboard_Click()
On Error GoTo Err_returntoswitchboard_Click


DoCmd.Close

Exit_returntoswitchboard_Click:
Exit Sub

Err_returntoswitchboard_Click:
MsgBox Err.Description
Resume Exit_returntoswitchboard_Click

End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If vbNo = MsgBox("Do you want to save the data?", vbQuestion + vbYesNo, _
"Save The Data?") Then Cancel = True
End Sub

Private Sub Form_Load()

End Sub


Private Sub returntomenu_Click()
On Error GoTo Err_returntomenu_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_returntomenu_Click:
Exit Sub

Err_returntomenu_Click:
MsgBox Err.Description
Resume Exit_returntomenu_Click

End Sub
Private Sub exitaccess_Click()
On Error GoTo Err_exitaccess_Click


DoCmd.Quit

Exit_exitaccess_Click:
Exit Sub

Err_exitaccess_Click:
MsgBox Err.Description
Resume Exit_exitaccess_Click

End Sub
Private Sub open_ship_data_Click()
On Error GoTo Err_open_ship_data_Click

Dim stDocName As String

stDocName = "Q shipment data"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_open_ship_data_Click:
Exit Sub

Err_open_ship_data_Click:
MsgBox Err.Description
Resume Exit_open_ship_data_Click

End Sub
Private Sub shipped_cans_Click()
On Error GoTo Err_shipped_cans_Click

Dim stDocName As String

stDocName = "Q shipped cans"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_shipped_cans_Click:
Exit Sub

Err_shipped_cans_Click:
MsgBox Err.Description
Resume Exit_shipped_cans_Click

End Sub

 

[Ken Snell [MVP]]

Using the button to return to the switchboard is the correct way to go.
Using File | Close to end a session is never a recommend way to exit from a
form in an application. So may I suggest that you change the procedure that
you've been using and just use the button instead?

Otherwise, you'll need to trap for the effort to close the application
(using hidden forms that run code to check for global variable's value to
see if the application can be closed or not) and prevent that from
happening.

--

Ken Snell
<MS ACCESS MVP>

 

[llaxmi]

how can I get the "return to switchboard" command button I put on the form to
also prompt to save before exit like the "exit access" button I made? it
seems like the code is there (see below) but nothing happens when you click
the button other than closing the form and returning you to the switchboard.
Should the event procedure be on another action than "on click"?

 

[Ken Snell [MVP]]

Add one line to your code for the button. Use this sub:

Private Sub returntoswitchboard_Click()
On Error GoTo Err_returntoswitchboard_Click
' force the form to save its data, which will trigger the
' form's BeforeUpdate event
If Me.Dirty = True Then Me.Dirty = False
DoCmd.Close

Exit_returntoswitchboard_Click:
Exit Sub

Err_returntoswitchboard_Click:
MsgBox Err.Description
Resume Exit_returntoswitchboard_Click

End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[llaxmi]

Hmm, no luck with this. Still no prompt on click.
The only thing that changed is that there is now a green dotted line
surrounding the text on the command button. Here is the code now...

Option Compare Database

Private Sub returntoswitchboard_Click()
On Error GoTo Err_returntoswitchboard_Click
' force the form to save its data, which will trigger the
' form's BeforeUpdate event
If Me.Dirty = True Then Me.Dirty = False
DoCmd.Close

Exit_returntoswitchboard_Click:
Exit Sub

Err_returntoswitchboard_Click:
MsgBox Err.Description
Resume Exit_returntoswitchboard_Click

End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
If vbNo = MsgBox("Do you want to save the data?", vbQuestion + vbYesNo, _
"Save The Data?") Then Cancel = True
End Sub

Private Sub Form_Load()

End Sub


Private Sub returntomenu_Click()
On Error GoTo Err_returntomenu_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_returntomenu_Click:
Exit Sub

Err_returntomenu_Click:
MsgBox Err.Description
Resume Exit_returntomenu_Click

End Sub
Private Sub exitaccess_Click()
On Error GoTo Err_exitaccess_Click


DoCmd.Quit

Exit_exitaccess_Click:
Exit Sub

Err_exitaccess_Click:
MsgBox Err.Description
Resume Exit_exitaccess_Click

End Sub
Private Sub open_ship_data_Click()
On Error GoTo Err_open_ship_data_Click

Dim stDocName As String

stDocName = "Q shipment data"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_open_ship_data_Click:
Exit Sub

Err_open_ship_data_Click:
MsgBox Err.Description
Resume Exit_open_ship_data_Click

End Sub
Private Sub shipped_cans_Click()
On Error GoTo Err_shipped_cans_Click

Dim stDocName As String

stDocName = "Q shipped cans"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_shipped_cans_Click:
Exit Sub

Err_shipped_cans_Click:
MsgBox Err.Description
Resume Exit_shipped_cans_Click

End Sub

 

[Ken Snell [MVP]]

Did you edit the data on the form, or enter a new record, before clicking
the button to return to the switchboard? If you don't modify the data or
enter a new record, then you won't get the question about whether to save
the data or not.

 

[llaxmi]

yes, also tried closing the program entirely and re-entering just to make
sure.
I also made sure the code appeared in the "on click" action on the button
property when I opened the form. I changes several fields, tabbed out of the
last changed field and presed the command button and it simply returned me to
the switchboard. I did notice though that when I closed the switchboard 9by
clicking a command button on the form) that I was prompted to save the data
or not. I'm fine with that but it makes me think that perhaps I have
something wrong still...i.e. the code is firing on the switchboard exit
rather than on the return to switchboard click?

 

[Ken Snell [MVP]]

I have no idea, from what you've described, about why you don't get the
prompt when you click the "switchboard" button. Based on the code you've
posted, and what you say the form is doing, the message about saving the
data should be displayed.

I wonder if the form on which you're entering data is actually using a
recordsource that has fields bound to the controls on the form? I would
expect so, based on your earlier statement about how you did get the message
in an earlier trial.

Try changing your "DoCmd.Close" step to this:
DoCmd.Close acForm, Me.Name

--

Ken Snell
<MS ACCESS MVP>

 

[llaxmi]

Hi Ken
Well, enough of this for the weekend! I can't tell you how much I
appreciate your input. Have a great weekend. When you return, here is some
info that may help....
----------
I'm not entirely clear what you mean by the form using a record source
bound to the controls. Here is a description of the complete form that may
help to clarify...

The form (which populates a single table) has 2 command buttons, one to
return the user to the switchboard and the other to exit and close access. It
has a one-to-many relationship linked by an auto number field.

There are several combo boxes for items like customers, branches and etc.,
used for data integrity. The other fields are fill-in type fields (qty, date
etc.). The form also has a subform which populates a single table. It is
just a simple table for data entry of barcode numbers and dates.

Here's what works/doesn't:
clicking close and exit command button returns prompt
clicking the form window "x" button returns the prompt
clicking file|close returns the prompt
clicking the return to switchboard button does not
clicking the close and exit button on the switchboard form returns the prompt
clicking a field in the subform returns the prompt
tabbing into the subform does not return a prompt
changing/adding a record in the subform and then clicking backinto the to
the main form and clicking any of the above does not return a prompt
changing/adding a record in the subform and then clicking backinto the to
the main form and then tabbing into the next field does return a prompt when
clicking on the buttons as above.

thanks again.

 

[Ken Snell [MVP]]

Comments inline...
--

Ken Snell
<MS ACCESS MVP>

Hi Ken
Well, enough of this for the weekend! I can't tell you how much I
appreciate your input. Have a great weekend. When you return, here is
some
info that may help....

What I mean is that the form's Recordsource property (in Properties window)
contains the name of a table, the name of a query, or an SQL statement. And
that the controls on the form have the name of one of those fields in the
Control Source property of the control.

Here is a description of the complete form that may
help to clarify...

The form (which populates a single table) has 2 command buttons, one to
return the user to the switchboard and the other to exit and close access.
It
has a one-to-many relationship linked by an auto number field.

So it does seem that your form has a Recordsource that should be the name of
a table.

There are several combo boxes for items like customers, branches and etc.,
used for data integrity. The other fields are fill-in type fields (qty,
date
etc.).

And it does seem that the controls are bound to fields in the form's
recordsource.

The form also has a subform which populates a single table. It is
just a simple table for data entry of barcode numbers and dates.

Ahhh... more on this later.

Here's what works/doesn't:
clicking close and exit command button returns prompt
clicking the form window "x" button returns the prompt
clicking file|close returns the prompt

clicking the return to switchboard button does not

Upon reading through your posted code again, it appears that I may have
misunderstood which procedure is the one for this action. I see a
"returntoswitchboard_Click" procedure, which is what I assumed was the code
for this button. But I also see "returntomenu_Click" procedure, which also
seems to be designed to "return" to a switchboard. If the former is the code
that runs when you click the "return to switchboard" button, the message
should appear. If the latter is the code that runs when you click the
"return to switchboard" button, the message will not appear because you're
not telling the form to save its data before you open a new form over the
top of this form.

clicking the close and exit button on the switchboard form returns the
prompt
clicking a field in the subform returns the prompt

tabbing into the subform does not return a prompt

This action will not prompt you with the message unless you changed data in
one of the "bound" controls on the main form before tabbing into the
subform.

changing/adding a record in the subform and then clicking backinto the to
the main form and clicking any of the above does not return a prompt
changing/adding a record in the subform and then clicking backinto the to
the main form and then tabbing into the next field does return a prompt
when
clicking on the buttons as above.

Both of the above actions are changes to the subform's data, not to the main
form's data. The code that we discussed earlier is designed to work for the
main form's data only; that is because you put the code in the main form's
BeforeUpdate event.

 

[llaxmi]

Hi
So, "returntoswitchboard_Click" and "returntomenu_Click" are redundant and
eliminating "returntomenu_Click" should solve the problem?
p.s. the buttons were created using the wizard. and I'm ok with the subform
prompt situation.

 

[Ken Snell [MVP]]

I don't know that they're redundant. You need to look at the form in design
view and see what the name of the command button is that you click to return
to the switchboard.

The name to the left of the "_Click" in the procedure name is the name of
command button. The name of the button dictates which procedure is being run
when you click the button.

--

Ken Snell
<MS ACCESS MVP>

 

[llaxmi]

WooHoo
That did it ken. Thanks for being so patient. I hope this helps others too!