Create a report from within Excel (1 Viewer)

[BenC2]

Gday, I have a report in access that takes its data from a query. That query in turn has criteria which are passed to it from a form presented to the user. This form takes name, and a start and end date. That is all.

What i'd like to do is from excel, open the report at the name specified by the user (ie, clicking the name shown in a cell). That is, instead of having to select the name from a combo box on the form, then putting in the 2 dates, have excel automatically pass that information to access and simply present the user with the finished report.

Here is the code i'm using to open the report;

Sub Report()

        Dim LPath As String
        Dim LCategoryID As String

        'Path to Access database
        LPath = "\\aa-server\AerospaceSMS\AerospaceSMS.accdb"

        'Open Access and make visible
        Set oApp = CreateObject("Access.Application")
        oApp.Visible = True

        'Open Access database as defined by LPath variable
        oApp.OpenCurrentDatabase LPath

        'Open form called Categories filtering by CategoryID
        LCategoryID = Range("A19").Value
        oApp.DoCmd.OpenForm "studentreport"
        'oApp.SearchName.Value = "Your new value"

    End Sub

Thanks for the help.

 

[simongallop]

Second attempt!

Add this line to your Excel code:

'Run Code
oApp.Application.Run "Test_Message", strN, dteN

I previously assigned name and date to strN and dteN

Create a module in Access with the following code:

Function Test_Message(strName as string, dteStart as date)
MsgBox "Welcome " & strName & ". The date is " & format(dteStart,"dddd dd mmmm") & "."
End Function

This shows that the info can be passed from Excel to Access. You might want to declare the variables in Access as Public then it is easier to reference from OnOpen code within your forms/reports etc.

HTH