how to pass parameter from report to open another report

[malikge]

Hi,
I am creating a small application in access 2007.
I am tracking the working of the vehicles.
I have created a query and on that query I have created a report.

I want to know is it possible that in report there is a hyperlink on vehicle number and by clicking it, it will open another report only showing the tracking of that particular vehicle?
In other words, from main report I want to pass the vehicleId as parameter to another report

Thanks...

 

[pbaldy]

You could use this technique:

http://www.baldyweb.com/wherecondition.htm

Note the first report would have to be in Report view, not Preview.

 

[malikge]

Thanks for you reply.
I am new to access,
so could you please tell me how can I use

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

I have found out the way to make hyperlink on the report. On the OnClick event of the vehicle Number I have to put some VB code.
Can you explain me how can use that code in VB.
Thanks

 

[pbaldy]

Not clear on the question, but does this help?

http://www.baldyweb.com/FirstVBA.htm

 

[malikge]

DoCmd.OpenReport "SecondReportName", , , "FieldName = '" & Me.ControlName & "'"

Can you tell me what is Me.ControlName?

I am totally confused right now.

1. First I have to make a query which take vehicleID as parameter?
2. Create a report on that query?
3. On first report's vehicleID OnClick event place the above code?

 

[malikge]

I have found another way of doing this through [Embedded Macro].

In that macro's
Action tab ---> OpenReport
Arguments tab ---> Parametrized Report Name, Report, , , Normal

In the main report on vehicleNumber OnClick enent I have placed this macro.
When I click the vehicleNumber it shows the dialog box asking for the vehicleID and after entering the id I can view the required report.

Now when I click the vehicleNumber is it possible that it automatically passes that vehicle's id to "Parametrized Report Name" and display that report.

 

[pbaldy]

Was this not clear?

ControlName is the name of the control on the current form that contains the value to be shown on the second form

A control is a textbox, combo, etc. As to your question, you can base the report on a query but it wouldn't take the parameter. My code is passing the parameter. To use your other method, your query (or wherever the parameter is) would need to point to wherever you're clicking.

 

[malikge]

pbaldy I am totally stuck here. Nothing is getting through to me.

I am using your code like this:

DoCmd.OpenReport "Parametrized Vehicle Query", , , "VehicleID = '" & Me.tblVehicle.vehicleID & "'"

the VehicleID is required on the second report, and tblVehicle.vehicleID is on the first report.

I am adding this code on OnClick event:

Private Sub Many_to_Many_vehicleID_Click()
DoCmd.OpenReport "Parametrized Vehicle Query", , , "VehicleID = '" & Me.tblVehicle.vehicleID & "'"
End Sub

To use your other method, your query (or wherever the parameter is) would need to point to wherever you're clicking.

as you mentioned that, I am pointing my main report to second report and it open that report but did not pass the parameter automatically.

 

[pbaldy]

Does this work?

DoCmd.OpenReport "Parametrized Vehicle Query", , , "VehicleID = '" & Me.vehicleID & "'"

 

[malikge]

No, this gives an error message.

Compile error:
Method or data member not found

 

[pbaldy]

When you start typing "Me." you should get a list of valid selections. Does the syntax in your original code come up that way? Can you post the db here?

 

[malikge]

After typing "Me." it gives me the valid selections.
The valid selection is "Me.tblVehicle_vehicleID"

Now it gives the error that:

Run-time error '3075':
Syntax error in string in query expression 'VehicleID = '21'.

I think your code works with string.
the 'VehicleID = '21' is the id of the vehicle at number 21.

As mentioned at:
http://www.baldyweb.com/wherecondition.htm

I have also tried using this for numeric value.

DoCmd.OpenReport "Parametrized Vehicle Query", , , "VehicleID = " & Me.tblVehicle_vehicleID

but it gives me a dialog box asking for Vehicle Id to be entered.

My database.

 

[pbaldy]

This works for me:

DoCmd.OpenReport "Parametrized Vehicle Query", acViewPreview, , "VehicleID = " & Me.tblVehicle_vehicleID

 

[malikge]

Thanks a lot man.
Much appreciated

 

[pbaldy]

Happy to help.

 

[beti]

Hello,

I got the same problem, I tried what you explain but It still doesn't work. I have no ideal. Can you take a look on my database? If so, I'll post it

Thank you very much.

Happy to help.

 

[pbaldy]

Sure, you can attach it here.

 

[beti]

First, I would like to say thanks for quick responding.
I'm attaching db for your consider. You're welcome to fix anything on the db.
What I would like to do is open rptMaverick report and click on the link of part no, for example '3384305', then it'll open another report named rptPart-list (which I created from query). I wrote the code on the text box of 3384305 but It didn't work ...

Sure, you can attach it here.

 

[pbaldy]

How is it not working? I click on that record, and the second report comes up restricted to it.

 

[beti]

Again, thanks for quick responding. I found out why it didn't work ...
My access sw set Security Warning which disabled VBA Macro, I set it enable then it works.
I don't know if you familiar with this, everytime I open access sw, there're a bar on the top say 'Security Warning Cetain content in the database has been disabled' and it has Option button to choose it 'Enable this conent' and I have to click every time. Do you know how to set it permanent?

That's why I think the code is right but why it didn't work, I'm very happy now and very thanks for your help.

How is it not working? I click on that record, and the second report comes up restricted to it.