VBA code to print report based on value within field

[juliana28]

Hello,
I need a little help. I need to create vba code that will print a report a report dependednt on value within field. If field "form" has a value of 1 , print report 1 if field "form" has a value of 2, print report 2, and so forth. There are 5 reports and the value in field form are listed 1-5. The reports are based on a query where they have to enter the employee id (pk). Then it needs to print out based on the value. I thought a case statement would be good but I cant get the right syntax to get it going.

Database Employee
Tabble: Employee

Fields:

Employee ID (pk)
Name
Address
Form Can have Value of 1,2,3,4 or 5
Amount
Query1
Contains all fields from table & criteria (Enter Employee ID)

Report1 (based on Query 1)
Report2 (based on Query 1)
Report3 (based on Query 1)
Report4 (based on Query 1)
Report5 (based on Query 1)

 

[dhookom]

Having a field named "form" has me chasing my tail trying to figure out how to explain. Let's assume the field name is "fldForm" so when I say "form", I mean Access form object.

Let's assume you have a form bount to the Employee table. You might have a button or some event to run the report.

Dim strWhere as String
Dim strReport as String
[green]'assuming Employee ID is numeric[/green]
strWhere = "[Employee ID]=" & Me.txtEmployeeID
strReport = Choose(Me.fldForm,"report1","report2","report3","report4","report5")
DoCmd.OpenReport strReport, acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP