Solved How to use ME in a public function

[AlliCarr]

Hi

I have the following code in most of my forms when exiting.

Private Sub ExitComms_Click()

On Error GoTo ErrorHandler
Dim ctl As Control

'If record is changed do you want to save changes
If Me.Dirty Then
    If MsgBox("Do you want to save changes?", vbYesNo + vbQuestion, _
                "Save") = vbNo Then

'If not, undo changes and close
        Me.Undo
        DoCmd.Close

'Otherwise save changes, confirm and close
    Else
        Me.Dirty = False
        MsgBox "Changes saved"
        DoCmd.Close
    End If

'If no changes made close record
Else
DoCmd.Close
End If

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg
          
End Sub

I'm currently tidying, formatting and commenting all of the code in my database and thought it would be cleaner to add this as a public sub in a module and then call it from each form. However, it doesn't like the 'Me' keyword and I'm struggling to make sense of the solutions I have found online.

Any suggestions greatly appreciated.
Kind Regards
Allison

 

[Gasman]

Pass the form in as an object and use that.

 

[cheekybuddha]

Something like:

Private Sub ExitComms_Click()

  CloseForm Me

End Sub

' In a standalone module
Public Sub CloseForm(frm As Access.Form)
On Error GoTo ErrorHandler

' If record is changed do you want to save changes
  If frm.Dirty Then
    If MsgBox("Do you want to save changes?", vbYesNo + vbQuestion, _
                "Save") = vbNo Then
'     If not, undo changes and close
      frm.Undo
'Otherwise save changes, confirm and close
    Else
      frm.Dirty = False
      MsgBox "Changes saved"
    End If
  End If

' Close the form'
  DoCmd.Close frm.Name

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg
   
End Sub

 

[Gasman]

Changing all references of Me to frm.

 

[cheekybuddha]

Changing all references of Me to frm.

Oops!

Normally I would use a With block, so only needing to reference once, but I thought I would try and remain as close to the original as I could

Thanks for the heads up GM, now corrected!

 

[AlliCarr]

Something like:

Private Sub ExitComms_Click()

  CloseForm Me

End Sub

' In a standalone module
Public Sub CloseForm(frm As Access.Form)
On Error GoTo ErrorHandler

' If record is changed do you want to save changes
  If frm.Dirty Then
    If MsgBox("Do you want to save changes?", vbYesNo + vbQuestion, _
                "Save") = vbNo Then
'     If not, undo changes and close
      frm.Undo
'Otherwise save changes, confirm and close
    Else
      frm.Dirty = False
      MsgBox "Changes saved"
    End If
  End If

' Close the form'
  DoCmd.Close frm.Name

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg
  
End Sub

Hi David

Thanks for this, it works great!

I note the comments about using a With block. How would I do this in this instance?

There's definitely other areas in the code that I could use a With block rather than repeating lines of code.

 

[MajP]

People often overuse the With block IMO and instead of helping it makes the code more confusing and harder to debug. Often adding confusion with little or any savings.

It works nice when you have a lot of properties right next to each other, but gets confusing when the properties or methods are divided up by code.

Good time to use to avoid repeating.

me.subformname.form.somecontrol.visible = true
me.subformName.Form.somecontrol.backcolor = vbred
me.subformName.form.someControl.forecolor = vbgreen
me.subformName.form.somecontrol.setfocus

to

with me.subformname.form.somecontrol
    .visible = true
   .backcolor = vbRed
   .forecolor = vbgreen
   .setFocus
end with

Bad. Not helpful or saving anything. Confusing and adding an additional block of code

Public Sub CloseForm(frm As Access.Form)
On Error GoTo ErrorHandler

With Frm
' If record is changed do you want to save changes
  If .Dirty Then
    If MsgBox("Do you want to save changes?", vbYesNo + vbQuestion, _
                "Save") = vbNo Then
'     If not, undo changes and close
      .Undo
'Otherwise save changes, confirm and close
    Else
      .Dirty = False
      MsgBox "Changes saved"
    End If
  End If

' Close the form'
  DoCmd.Close .Name

end with
Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg

End Sub

 

[cheekybuddha]

I'm not sure a With block would save you much here (in fact, it might actually be more lines of code!).

I would do it like this:

Public Sub CloseForm(frm As Access.Form)
On Error GoTo ErrorHandler

With frm
' If record is changed do you want to save changes
  If .Dirty Then
    If MsgBox("Do you want to save changes?", vbYesNo + vbQuestion, _
                "Save") = vbNo Then
'     If not, undo changes and close
      .Undo
' Otherwise save changes, confirm and close
    Else
      .Dirty = False
      MsgBox "Changes saved"
    End If
  End If

' Close the form'
  DoCmd.Close .Name
End With

Exit Sub

ErrorHandler:
    Dim msg As String
    msg = Err.Number & ":" & Err.Description
    MsgBox msg
  
End Sub

 

[cheekybuddha]

Saying that, and taking @MajP's comment in to account, had you used a With block in your original code, then it would only have taken replacing Me once with frm in the conversion, and less chance of missing an instance like I did. So there are advantages too!

 

[AlliCarr]

Thanks for this.

I can see the point about adding a With block to the code to close forms and that it doesn't really make sense to do that.

@MajP thanks for the example of the good With block, there are loads of examples such as this that I can update and make it much more readable.

Thanks all for your help!

 

[MajP]

I will use a with block in that case, but never will use it if there are other code blocks (while, if then, etc.) in between the with. Just creates more chance of error. I tend to use lots of variables instead.

me.subformname.form.somecontrol.visible = true
me.subformName.Form.somecontrol.backcolor = vbred
me.subformName.form.someControl.forecolor = vbgreen
me.subformName.form.somecontrol.setfocus

to

dim ctrl as access.control
set ctrl = me.subformname.form.somecontrol
ctrl.visible = true
ctrl.backcolor = vbred
ctrl.forecolor = vbgreen
ctrl.setfocus

about the same amount of code savings. Easier to debug and read IMO.

 

[The_Doc_Man]

@AlliCarr - you say you were confused about uses of Me. in VBA code. Part of the issue is to understand that MS Office products (not limited to Access) have all sorts of textual "shortcuts" that relieve you from typing longer qualifier strings.

Sometimes the shortcut is one of omission. For instance, if you are talking about getting the value from a control on a form, you don't need .VALUE (even though it is the right property) because for any control that actually COULD have a value, .VALUE is the default property. So you very rarely have to use it explicitly. That is, because of the default, X=textbox.VALUE is the same as X=textbox in VBA.

There are times when you have to refer to a database element and its formal reference is a big, ugly string. You can get to it using the shortcut CurrentDB - which is far less typing than Workspaces(0).Databases(0). as a way to get to your primary database.

The use of Me. falls into the latter "shortcut" category because without it you would have to use Forms( "formname" ). or something longer - AND would have to remember the form name. So inside a form's class module, the shortcut Me. represents the longer string that refers to the open form that is the host for the class module in which your code is running.

But to the point of your confusion: Why does it not recognize this in general modules? The answer is that Me. is evaluated when you are compiling the code, not when you are executing the code. Since the form's class module and any general module get compiled at different times (one module at a time), if you used the shortcut Me. in a general context, there is no form associated with it when it is compiled. And when you call that general module, your code is NOT running in the immediate context of the class module. It is running in a different "layer" and therefore cannot easily "see" things associated with that class module. Remember, a general module contains things can could be called by ANY code in ANY class module you have, and potentially you have one for each form.

The trick that David (cheekybuddha) showed you in post #3 is simply that you can pass in the form as an argument to the general module routines that need it by declaring a variable to be of type Access.Form and then passing in Me as an argument to the call sequence, whatever it was. By passing the reference to that form, you can access its properties freely through the form variable.

 

[Pat Hartman]

Me. refers to the current form or report object. When the code is running from a standard module or a standalone class module, no form or report is current until code execution returns to it. So, Me. is invalid outside of a form or report class module.

Also, as Doc pointed out, VBA is an interpreted language so each module is "compiled" when it is initially loaded and no code modules that have not yet been referenced are loaded.

 

[CJ_London]

If you used a public function rather than a sub, per post #3 change

Public Sub CloseForm(frm As Access.Form)

To

Public Function CloseForm(frm As Access.Form)

Then you don’t even need to have a form module for the close event. Just put

=CloseForm([FORM])

against the close event on the property sheet (where you would normally see ‘Event Procedure’

I sometimes use this method in the form open event to populate other events, sometimes conditional on why the form is being opened, e.g.

public function setEvents(frm as form)

frm.onclose=“=closeform([form])”
‘add other events

end function

And against the open event

=setEvents([form])