CODEWITHSUNDEEP
vbams-access
Naz
Naz

Reputation: 910

Opening Form From Report

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.

Upvotes: 1

Views: 200

Answers (1)

Thomas G
Thomas G

Reputation: 10206

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

Upvotes: 1

Related Questions