MS Access Pass Form Through Function

Question

I am trying to create a function that will allow me to use various command buttons without having to recreate the code every time.

To do this I have to pass the form name through a function

Function:

public Function NewRecord(controlForm, focusForm)
focusForm.SetFocus
DoCmd.GoToRecord , , acNewRecord
controlForm.SetFocus

controlForm - This is the main form akin to the Me function

focusForm - This is for not only the main form but when I create subforms I have to have the focus on the subform to have the command work.

To call the function I did the following:

public sub Command19_Click()
Dim controlForm
Dim focusForm

Set controlForm = Forms![frm_Sales_CustomerProfile]
Set focusForm = Forms![frm_Sales_CustomerProfile]

Call NewRecord(controlForm, focusForm)

End Sub

I get this error that States: Compile Error: Invalid Use Of Property.

Answer 1

You got trapped using an already in this context (the form) used identifier and not using a strong name (NameOfLibrary.NameOfFunction).NewRecordis a forms property, soInvalid Use Of Propertymakes sense. If you useMyModulName.NewRecord(frm1,frm2)everything is fine. If you useNewRecordin Module òr Class it works too as there is no property with same name (I assume;-)).

To be honest, I don't use strong names either (except on database or recordset objects, as I got trapped there too, assuming DAO, using ADODB), but the Pros suggest that and now we know why!

Your function should have just one argument as it is sufficent to pass only the subforms reference if you need that form NewRecord(frm as Access.Form) (note the strong name!). You can easy refer to the mainform with Set mfrm = frm.Parent

Your code;

Public Function FrmNewRecord(frm As Access.Form)
    frm.Recordset.AddNew
End Function

Public Sub Command19_Click()
    FrmNewRecord(Forms![frm_Sales_CustomerProfile]) ' mainform
    FrmNewRecord(Forms![frm_Sales_CustomerProfile]!sfrmControl.Form) ' subform
End Sub

You are passing the same form two times in your code, any reason? If Forms[frm_Sales_CustomerProfile] contains Command19 use Me. I dropped the .SetFocuspart as not necessary or any reason to for setting focus? Why is NewRecord a function? Doesn't return anything. btw: I am working on aSubForms(frm)function , that returns a collection of all subforms.

Code:

'SubForms(frm As Access.Form) returns a collection of all subform references in frm

Public Function SubForms(frm As Access.Form) As VBA.Collection
Dim ctr As Access.Control
Dim sfrm As Access.Form
Dim col As New VBA.Collection

For Each ctr In frm.Controls
    If ctr.ControlType = acSubform Then
        On Error Resume Next
        Set sfrm = ctr.Form
        If Err.Number = 0 Then
            col.Add sfrm, sfrm.Name
        End If
        On Error GoTo 0
    End If
Next ctr
Set SubForms = col
End Function

Answer 2

As a general rule to build say custom menu bars, or ribbons, you can write code that is “form” neutral like this:

Public Function MyDelete(strPrompt As String, strTable As String)

  Dim strSql        As String
  Dim f             As Form
  Set f = Screen.ActiveForm
  If MsgBox("Delete this " & strPrompt & " record?", _
                vbQuestion + vbYesNoCancel, "Delete?") = vbYes Then

So note how we don’t need to pass the form name at all – the above code simply picks up the active screen as variable “f”.

At that point you can do anything as if the code was inside the form.

So

Me.Refresh    (code inside the form)

Becomes

f.Refresh

So the key concept here is that you don’t need to pass the current active form since screenActive form will enable you to get the current form object anyway.

However, for sub forms and “common” type of code the above falls apart because screen.ActiveForm will return the main form, and not the sub form instance.

So as a second recommended approach, simply always pass the current context form object “me” like this:

Call MySub(me)

And you define your sub like:

Sub MySub(f as form)

Now in this code we can reference "anything" by using "f" in place of "me"

f.Refresh

So just pass “me” if you ever use sub forms. Given the above information, then your code becomes:

public sub Command19_Click()

   Call NewRecord(me)


End Sub

And NewReocrd becomes:

Sub NewRecord(f as form)

Now in your newreocrd code, you can use “anything” form the object such as:

f.Name    ' get name of the form.

or

City = f.City    ' get value of city control

So pass the “whole” form context.

And you could say make a routine to display the City value for any form like:

Call ShowCity(me, "City")

And then

Sub ShowCity(f as form, strControlToShow as string)

   Msgbox "City value = " & f(strControlToShow)

So OFTEN one will write code that works for any form by simply picking up the current active form as:

  Dim f             As Form
  Set f = Screen.ActiveForm

And note how the above code picks up right away the active form – this is a good idea since then if focus changes, the “f” reference will remain intact for the code that follows in that “general” routine that is called + used from many forms.

However due to the sub form issue, then often it simply better to always pass the “whole” forms instance/object with:

Call MyNewRecord(me)

And then define the sub as:

Sub MyNewReocord(f as form)

   DoCmd.GoToRecord acDataForm, f.Name, acNewRec

End Sub

And you could optional add “focus” to above with

f.SetFocus

So for a lot of menu or ribbon code, you don't pass the form object, but simply use screen.ActiveForm, and you can also use Screen.ActiveControl (again great for menu bar or ribbon code to grab what control has focus). However due to sub form limitations, then often passing "me" to the routine will achieve similar results if not better in some cases.