Unable to call a sub from form

[diS]

Hi all!
I have several buttons in database that runs exactly same procedure (Save and Close).
In order to avoid duplicating codes I'm trying to create subroutine and call it with buttons event.. I hope it could be done..

The problem is that when subs containing codes are called they won't work (nothing happens), but code is working when used directly in button event (on_click), so I assume it has to be modified in order to work as subroutine.

Here are codes:

Close button:

Sub Close()
If MsgBox("Save changes before closing?", vbYesNo, "Saving...") = vbYes Then
    DoCmd.Save
Else
    If Me.Dirty = True Then DoCmd.RunCommand acCmdUndo
End If
    DoCmd.Close

Save button:

Sub Save()
    Dim strMsg As String
    Dim iResponse As Integer
    strMsg = "Do you wish to save the changes?" & Chr(10) & _
        "Click Yes to Save or No to Discard changes."
    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
    If iResponse = vbNo Then
        If Me.Dirty = True Then DoCmd.RunCommand acCmdUndo
    Else
        Me.Dirty = False
        DoCmd.Close
End If

And subs are called with:

Private Sub cmdSave_Click()
    Save
End Sub

and

Private Sub cmdClose()
    Close
End Sub

What I would like is to create two subs and call them from form buttons.
Is there a way to do it?
Thanks!

 

[Minty]

Both Save and Close are reserved words in access. I suspect if you called you subs something else like MySave and MyClose you would probably get a bit further.

I also suspect that trying to use acCmdUndo may not always undo everything that has been updated if you are using a bound form. If any previous data on the form was updated and saved by another method, then further updates are made you'll only undo partial information.

 

[diS]

First of all thanks for quick response.

Changing name of Save and Close did made a change but I also needed to erase Me. in Me.Dirty.
Code partially works but not in the way it should. As you said there is a problem with acCmdUndo: it doesn't undo changes.

Buttons Save and Close are in bound popup form that pops up when user selects record in another form to edit it.

The way I imagined buttons Save and Close work is this:
- When user changes record and press Save it asks whether to save or discard changes. If Yes is selected record is saved and popup form is closed. When No is chosen all changes are discarded and form is still open.
- When user press Cancel it asks to Save. If Yes then record is saved, if No changes are abandoned. Both choices closes the form.

Also it would be be great to redesign codes so they act more logically since save or discard is asked even if nothing has changed.
For Save button I assume I could go with me.buttonname.enabled = True/False to show it only when record is changed, but not sure how to do it..
Close button should always be enabled so different approach is needed.

Any help is appreciated.

 

[The_Doc_Man]

There is another issue to consider. Where is the subroutine? In the class module where it will be used, or in a general module where several forms could share it. Because Me (as a shortcut to refer to a form or report) doesn't work correctly in a general module.

If you are trying to use a general module, then you need to do something to pass in the form reference.

Public Sub FormRef( fFrm as Access.Form, etc....)

    If fFrm.Dirty Then etc. ...

End Sub

Then when you call it, you can use FormRef(Me). Note also that if you only used this in one form and the subroutine only applied to the form and was in the class module, then you don't have to do it the above way because in a Class Module, "Me" works fine.

 

[diS]

Considering that I want to use modules to avoid duplicating codes, what would be better choice, class or standard module?
I am pretty new with VBA so working with modules is step further in my access knowledge.. and seeking for solution online didn't simplify it..

Edit: I just tried with class module but it shows Compile error: Sub or Function not defined.

 

[Minty]

You might want to look into Transactional Bound Forms. These use a commit/rollback model that would probably be better suited to your needs.
They aren't that straight forward to use and I think you would need to repeat the coding for each form, due to the nature of their workings.

 

[diS]

What are Transactional Bound Forms and to use them?
Net provides a few information about them.

 

[Minty]

They aren't straight forward, and troubleshooting them is a interesting. An overview here https://bytes.com/topic/access/insights/671094-dao-transaction-processing-what

If you only have a few fields it may be better to use a unbound form, that loads the existing information, then when "saved" updates back into the original record.

 

[diS]

Uff.. seems a bit complicated for rookie like me.

Is there other way to use module for codes above?
If not I think I'll just have to duplicate them in form.

 

[Frothingslosh]

You're honestly making it way harder than it needs to be. There's no particular need to replace those two functions with custom procedures. To close a form, just use DoCmd.Close. Making a public function out of that single command is just making things more difficult for yourself.

The same thing applies to saving the record, just use Me.Dirty = False like you were.

Reuse of code is perfectly fine when standardizing the code into a function is longer than just reusing the bloody code, and it's not like these two particular lines are ever going to need to be changed. The entire point of consolidating commonly-used code into a single function is a) to reduce the amount of code entered, and b) to minimize the amount of work necessary when the code is changed. Putting Me.Dirty = False and DoCmd.Close into standard functions doesn't reduce the amount of code in the slightest, and these commands won't ever need maintenance unless MS decides to screw everyone and remove two functions that are a fundamental part of the application.

EDIT: One thing you should be aware of: DoCmd.Save saves the FORM, not the record.

 

[diS]

Sure, thanks for that.
My intention is that user is asked before saving/closing so code will remain mostly same with replaced DoCmd.Save.

Anyhow, thank you all for advice, at the end I think I'll just stick w/codes at each form and button.

 

[IBadri]

Create one macro for all record Action repeated in different forms.

Here I create a one general macro name "McRecord" which include a general command used in all my forms without repeating it each time.

Like Close, Home, Next Record, etc...

Note that: in the "Object Type" and "Object Name" "LEAVE THEM BLANK", so the macro command shall work for all the open forms.

and in one form you can designe a one button to call each sub-macro, then copy all the buttons from this form to the other forms as required without any other modification.

By this way you use one macro for all the forms to do the same command.

In sub-macro "Close" if you need to be asked to save each time, then in the "Save" select Prompt, and if you need to save automaticaly then select Yes.

That's all

Here I covert the macro to visual basic:-

'------------------------------------------------------------
' McRecord_Close
'
'------------------------------------------------------------
Function McRecord_Close()
On Error GoTo McRecord_Close_Err

DoCmd.Close , ""
Beep


McRecord_Close_Exit:
Exit Function

McRecord_Close_Err:
MsgBox Error$
Resume McRecord_Close_Exit

End Function