Opening Form From Report

Question

I have the following lines of code in 2 different VBA functions in a Module they both have the same aim of opening a form to a specific record;

stLinkCriteria = "[ID]=" & Reports![Rpt_Manufacture].[ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

and

stLinkCriteria = "[ID]=" & Forms![frmManufactureList]![frm_Products].[ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

How can I change this so I only have one function that I can call from reports or forms and it will open the form to a specific record. I have tried the me! version on reports but I get an 'Invalid use of Me keyword' which I guess is because I can not use it from a Module.

UPDATE #1 Based on the answer below by Thomas G I used this code;

Option Compare Database

Public Function CmdOpenProductDetails(ByRef theObject As Object)
On Error GoTo Err_CmdOpenProductDetails

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FrmProductDetails"

    stLinkCriteria = "[ProductID]=" & theObject![ProductID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdOpenProductDetails:
    Exit Function

Err_CmdOpenProductDetails:
    MsgBox Err.Description
    Resume Exit_CmdOpenProductDetails

End Function

And from the Form and Report for the product names I have an onClick event that reads;

=CmdOpenProductDetails()

However, if I click a product name on a form I get the error message;

The express On Click you entered as the event property setting produced the following error. The expression you entered has a function contain the wrong number of arguments. (The expression may not result in the name of a macro, UDF or Event Proc) (There may have been an error evaluating the function).

If I click from a report I get the error message;

MS Access cannot find the object 'CmdOpenProductDetails(). Make sure you have saved it and that you have typed it correctly.

Eh... You need to actually use VBA when passing parameters... You should pretty much never assign a button to a UDF, but always add VBA that calls the UDF — Erik A
@ErikvonAsmuth I created an on click event to code that calls the CmdOpenProductDetails UDF but now I get an 'Argument not optional' message — Naz
I tried "Private Sub ProductID_Click(Me)" but I got an Expected identifier error. — Naz
You might want to read a VBA tutorial. That's not how you pass an argument to a sub, and is not valid. You should call it like Open_Form Me — Erik A

Thomas G Thomas G · Accepted Answer · 2018-04-30T09:48:09

Pass the form byref in a sub

SOmething like

Public Sub Open_Form(ByRef theForm As Form)

    Dim stLinkCriteria As String

    stLinkCriteria = "[ID]=" & theForm![ID]
    DoCmd.OpenForm theForm, , , stLinkCriteria

End Sub


Private Sub TestIt()

        Open_Form Forms![frmManufactureList]![frm_Products]

End Sub

Note that you might have to tweak it a bit because I dont know the exact context. Maybe you have a subform and so you should pass the mainform instead

But the idea is there

UPDATE following Erik proposal:

You can pass either a form or a report as an object to make it work for both

Public Sub Open_Form_or_Report(ByRef theObject As Object)

    Dim stLinkCriteria As String


    stLinkCriteria = "[ID]=" & theObject![ID]


    If TypeOf theObject Is Form Then

        DoCmd.OpenForm theObject , , , stLinkCriteria

    ElseIf TypeOf theObject Is Report Then
        DoCmd.OpenReport theObject , , , stLinkCriteria

    Else
        MsgBox "Error :  the type should be a Form or a Report"

    End


End Sub


Private Sub TestIt()

        Open_Form_or_Report Forms![frmManufactureList]![frm_Products]

End Sub

Opening Form From Report

1 Answers