Using a Save button

[texasyankee]

I'm using Access Prof. 2003.

I found this tip dated 2005 from Graham R Seach, Microsoft Access MVP
Sydney, Australia. When I applied it, it stopped Access from autosaving but
now I can't get the form to save data at all.

Add a module-level variable:
Private blnOK2Save As Boolean

In the Click event for the save button, add the following code:
blnOK2Save = True
RunCommand acCmdSaveRecord
blnOk2Save = False

Then in the form's BeforeInsert and BeforeUpdate events, add the following
code:
If (blnOK2Save = False) Then Me.Undo

I have the following code ON Click for my save button.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Can someone else help me with this?

 

[Dirk Goldgar]

I'm using Access Prof. 2003.

I found this tip dated 2005 from Graham R Seach, Microsoft Access MVP
Sydney, Australia. When I applied it, it stopped Access from autosaving
but
now I can't get the form to save data at all.

Add a module-level variable:
Private blnOK2Save As Boolean

In the Click event for the save button, add the following code:
blnOK2Save = True
RunCommand acCmdSaveRecord
blnOk2Save = False

Then in the form's BeforeInsert and BeforeUpdate events, add the following
code:
If (blnOK2Save = False) Then Me.Undo

I have the following code ON Click for my save button.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Can someone else help me with this?

You don't say that you have followed Graham's suggestions completely.
Graham said:

In the Click event for the save button, add the following code:
blnOK2Save = True
RunCommand acCmdSaveRecord
blnOk2Save = False

But you said:

I have the following code ON Click for my save button.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Is that all the code in your button's Click event procedure? What happens
if you replace it with the three lines Graham suggested?

 

[texasyankee]

Thanks for the quick response. I did add his code and it did stop Access from
saving automatically. I just wanted to be sure that readers knew about the
code I had for the save button, thinking maybe there was a problem with that.

 

[Dirk Goldgar]

Thanks for the quick response. I did add his code and it did stop Access
from
saving automatically. I just wanted to be sure that readers knew about the
code I had for the save button, thinking maybe there was a problem with
that.

I'm afraid I don't understand. If the code in your save button is what you
said it was, then that *is* the problem. If, instead, the code is what
Graham posted, and you find that you can't use the button to save a record,
then there is some other problem that isn't obvious from what you have
posted so far.

If you do still have a problem, please post a copy/paste of the complete
event procedure that are relevant to the problem.

 

[texasyankee]

Sorry. I had used Graham's code but when it didn't work and I replaced it
with the DoCmd. I changed it back so now the on click code reads:

Private Sub Form_BeforeInsert(Cancel As Integer)
If (binOK2Save = False) Then Me.Undo
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (binOK2Save = False) Then Me.Undo
End Sub

Private Sub Save_Click()
On Error GoTo Err_Save_Click

blnOk2Save = True
RunCommand acCmdSaveRecord
blnOk2Save = False

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

When he said add a module-level variable, I wasn't sure what he meant so I
clicked Modules under Objects in the DB window and added created a Module 1
with his code called Save. Is this what he meant.

Option Compare Database

Private binOK2Save As Boolean

Anyway, when I clicked save, the screen cleared but nothing was saved to the
table. I appreciate your assistance.

 

[Dirk Goldgar]

When he said add a module-level variable, I wasn't sure what he meant so I
clicked Modules under Objects in the DB window and added created a Module
1
with his code called Save. Is this what he meant.

Option Compare Database

Private binOK2Save As Boolean

No, and that's where your problem is coming from. He meant that you should
add the variable at the module level in the *form's* code module, where the
event procedures are. Delete the Module1 that you created (unless you're
also using it for something else), and then In the *form's* module, go to
the (Declarations) section and put the variable declaration there.

While you're at it, add the line "Option Explicit" underneath the "Option
Compare Database" line in that module, so that the whole Declarations
section of the form's code module looks like this:

Option Compare Database
Option Explicit

Private binOK2Save As Boolean

And then open the Tools -> Options dialog in the Visual Basic editor (not
the main Access application window) and put a check mark in the box for
"Require Variable Declaration".

The cause of your problem, and the reason you got no error message, is that
you had the VB editor set with the default option to accept non-declared
variables. As far as VB was concerned, you had four completely independent
variables named "binOK2Save": one local to Module1, one (undeclared) local
to your save button's Click event procedure, and two more (undeclared) local
to your BeforeInsert and BeforeUpdate event procedures.

 

[M.]

Hi there,

Maybe the problem is that your supposedly global variable blnOK2Save is
currently defined as private

Try changing in your module1 the line

Private binOK2Save As Boolean

to

Public binOK2Save As Boolean

(Public variables are intended for sharing between modules, while private
variables are intended for use within their own module)


Best regards,

M.

 

[Dirk Goldgar]

Hi there,

Maybe the problem is that your supposedly global variable blnOK2Save is
currently defined as private

Try changing in your module1 the line

Private binOK2Save As Boolean

to

Public binOK2Save As Boolean

(Public variables are intended for sharing between modules, while private
variables are intended for use within their own module)

That would make it work, but there's no reason for the variable to be stored
in a standard module in the first place, and would be error-prone if similar
code were used on some other form. This variable should be declared in the
form's module, and left Private.

 

[texasyankee]

Here's what I have now:

Option Compare Database
Option Explicit

Private binOK2Save As Boolean

Private Sub Form_BeforeInsert(Cancel As Integer)
If (binOK2Save = False) Then Me.Undo
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (binOK2Save = False) Then Me.Undo
End Sub

Private Sub Save_Click()
On Error GoTo Err_Save_Click

blnOk2Save = True
RunCommand acCmdSaveRecord
blnOk2Save = False

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

When I click on save, I'm getting a Variable Not defined dialog that
highlighting the binOK2Save in the Private Sub Save Click.

I'm new to this and would love to know what each piece of this code this
telling Access to do. Thanks for your patience.

 

[Rob Parker]

That's because you have a different (undeclared) variable in Sub Save_Click.

Your declaration, and the Form-BeforeInsert and Form_BeforeUpdate events,
have a variable binOK2Save, but the variable in Save_Click is blnOK2Save.
Notice the difference: the first has an "i", the second has an "l". They
must all be the same. And I suggest that the "correct" spelling is
blnOK2Save - the three-letter bln prefix tells you (or anyone else later
reading the code), that the variable is a boolean datatype (ie. contains a
True/False value).

Note also how Option Explicit has caught this error for you ;-)

HTH,

Rob

 

[texasyankee]

I think my eyes need new glasses. Thanks for catching that. I am so proud of
myself- I even added code to clear the screen after saving.

 

[texasyankee]

Thanks so, so much. It worked like a charm and I figured out how to add code
that clears the screen after saving. Thanks again.