• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

Passing Parameter to Access Report in VB.Net?

To test this procedure I hard coded the parameters into the query (in test.mdb).  Everthing works so far.   Now, I want to make it dynamic so I can pass the parameters at runtime.  

Do I need the dao object reference in project?  I am trying to stick with the .net exclusively.

The query name is prMemoSingle and I have two parameter mID and pID.   I am not sure how to access/pass these parameters so that I can make this code dynamic.

Secondly, does this code appropriately close/release the lock on the database?

        oAccess.CloseCurrentDatabase()
        oAccess = Nothing

Thanks!

Dim oAccess As Access.Application
        oAccess = New Access.ApplicationClass()
        oAccess.OpenCurrentDatabase(filepath:="s:\database\test.mdb", Exclusive:=False)
 
'---->  Open Access Query, pass parameters that the report is based upon.   Parameter 1 = mID, Paramter 2 = pID (both integers)
 
        oAccess.DoCmd.OpenReport(ReportName:="prMemoSingle", View:=Access.AcView.acViewNormal)
        oAccess.CloseCurrentDatabase()
        oAccess = Nothing

Open in new window

0
rrowe68
Asked:
rrowe68
  • 2
1 Solution
 
Jim P.Commented:
This is a guess based off how similar that looks to how you do it in VBA.
Dim oAccess As Access.Application
        Dim strCriteria as String
        oAccess = New Access.ApplicationClass()
        oAccess.OpenCurrentDatabase(filepath:="s:\database\test.mdb", Exclusive:=False)
 
'---->  Open Access Query, pass parameters that the report is based upon.   Parameter 1 = mID, Paramter 2 = pID (both integers)
 
    strCriteria = "[mID] = " & MymIDValue & " And [pID] = "  & MypIDValue   
 
    oAccess.DoCmd.OpenReport(ReportName:="prMemoSingle", View:=Access.AcView.acViewNormal, WhereCondition = strcritera)
 
        oAccess.CloseCurrentDatabase()
        oAccess = Nothing

Open in new window

0
 
rrowe68Author Commented:
Thanks Jimpen -- You are right.

Answer -- below


        Dim oAccess As New Access.ApplicationClass()
        Dim strWhere As String
        Try
            oAccess.OpenCurrentDatabase(filepath:="s:\database\emery.mdb", Exclusive:=False)
            strWhere = "nMemoID = " & CURRMEMO & " AND " & "lPropertyID = " & CURRPROP
            oAccess.DoCmd.OpenReport(ReportName:="prMemoSingle", View:=Access.AcView.acViewNormal, WhereCondition:=strWhere)
 
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        Finally
            oAccess.Quit()
            oAccess = Nothing
 
        End Try

Open in new window

0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now