Passing a Variable to Access <Resolved>

[rabaile]

I'm attemping to run an Access report through VB. The code works just fine, but for this particular report, the query it pulls from to generate the report requires the user to enter a parameter. It pops open a message box and asks for "Employee ID", which is used as a filter. How do I modify my existing code to pass the employee ID entered in my VB program to Access without the user having deal with the message box?

Private Sub EmployeeHRWeek()
        Dim A As Object
        A = CreateObject("Access.Application")
        A.Visible = False
        A.OpenCurrentDatabase("f:\svrc\timeclock\Timekeeping.mdb", False, "password")
        A.DoCmd.OpenReport("EmployeeHoursByWeek")
        A.Quit()
    End Sub

Thanks.

[RobDog888]

Are you doing this in VB6 or VB.NET?

You can eliminate the parameter from the report source's query. Then when you open the report you can specify a Where condition of the parameter you want to filter on.

Private Sub EmployeeHRWeek()
    Dim A As Object
    A = CreateObject("Access.Application")
    A.Visible = False
    A.OpenCurrentDatabase "f:\svrc\timeclock\Timekeeping.mdb", False, "password"
    A.DoCmd.OpenReport ReportName:="EmployeeHoursByWeek", WhereCondition:="[EmployeeID]=888"
    A.Quit
End Sub

Moved from Classic VB forum.

[rabaile]

VB.Net, sorry I posted in the wrong forum.

When I tried your code, I got this message:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in microsoft.visualbasic.dll

Additional information: Data type mismatch in criteria expression.

Private Sub EmployeeHRWeek()
        Dim A As Object
        A = CreateObject("Access.Application")
        A.Visible = False
        A.OpenCurrentDatabase("f:\svrc\timeclock\Timekeeping.mdb", False, "password")
        A.DoCmd.OpenReport(ReportName:="EmployeeHoursByWeekVB", WhereCondition:="[EmployeeID]=154387")
        A.Quit()
    End Sub

[RobDog888]

What is the data type for field - EmployeeID? Number or Text?

[RobDog888]

I got this to work for me, simulating your situation.

Private Sub EmployeeHRWeek()
        Const acViewPreview As Long = 2
        Dim A As Object
        A = CreateObject("Access.Application")
        A.Visible = True
        A.OpenCurrentDatabase("D:\RobDog888.mdb", False, "")
        A.DoCmd.OpenReport(ReportName:="Report3", View:=acViewPreview, WhereCondition:="[Field2]='rob'") 'My field is a Text field
        Stop 'So I can preview the report
        A.Quit()
    End Sub

[rabaile]

Works great with the single instead of double quotes. Thanks so much.

[RobDog888]

Your welcome. The single quotes just designate a Text value. So you EmployeeID field is a Text data type.