Passing parameter to an Access Report 2

[EdRev]

If I have an Access Report name rptAge and I have a field in that report called to rptTitle to hold the Report Title, how do I pass the string from my VB program into that field?

Any help will be greatly appreciated.

 

[vincentmairiaux]

I had the same problem. I think you do not need ADO or other chinese food. I found this and it works (provided Acces is installed on the running PC). Hope you can use it too:

first step: I made a new query in Access to select the records I need (doesn't matter how you name it)
second step: I made a report in Access based on this query - I called this report rptReport (must be the same name in the example code below)
third step: I use this code

first some variable explanation for this examples:
- rptReport = name of report in Access
- TABLE = table in database also used in query
- FIELD = field parameter you want to pass to the report, in this case an Integer
- Combobox = combobox on the form from wich the user can select to obtain the appropriate report

'example 1. in order to have the report printed immediately without preview:
Dim Acc As Access.Application
Set Acc = New Access.Application
Acc.OpenCurrentDatabase "C:\Path Database"
Acc.DoCmd.OpenReport "rptReport", acViewNormal, , "TABLE.FIELD=" & CInt(ComboBox.Text)
Acc.Visible = False 'not sure this is really needed
Set Acc = New Access.Application

'example 2. in order to have a preview of the report (you can then click the print icon to have it printed):
Dim Acc As Access.Application
Set Acc = New Access.Application
Acc.OpenCurrentDatabase "C:\Path Database"
Acc.DoCmd.OpenReport "rptReport", acPreview, , "TABLE.FIELD=" & CInt(ComboBox.Text)
Acc.Visible = true
Set Acc = New Access.Application

Important: in your query design, put no Criteria value in the TABLE.FIELD, you will just be passing the parameter value using the Acc.DoCmd.OpenReport statement, and the report will pass your parameter further upstream to the query in order to make the right selection

good luck
vincent
acady@yucom.be

 

[EdRev]

Vincent,
thank you for the very detailed help. I haven't tried it yet, but it looks like it will work for me.

 

[shmiller]

Another option you could try is to have a table in the Access DB to store the parameter(s). You could then use ADO to populate the parameter table before opening your report and have your report based on a query that gets values from the parameter table
ex.
Two tables:
tblParameters(Parameter1,Parameter2, etc.)
tblEmployees(EmployeeID, EmployeeName, etc.)

Query to base the report on:
SELECT *
FROM tblEmployees
where EmployeeID in
(select Parameter1 from tblParameters);

Then all you would have to do in you program is use ADO to insert the appropriate values in tblParameters, then open your report.
Hope this helps

 

[EdRev]

thanks shmiller,

i did what you suggested and it worked perfectly.