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