Hyperlink a report to another report

[wisni1rr]

I have two report reports. One report displays a field called [BWAID]. The second report is a report for a single record in mor detail. I am trying to convert this textbox on my first report into a hyperlink. The hyperlink will link to the 2nd report report. I need this hyperlink to also capture what the [BWAID] is when clicked and use the [BWAID] in a paramater query to pull up another report (more detailed report).

I am looking for advice on how to approach this situation. I looks to me that I should program an OnClick VBA however I don't understand how to pull the [BWAID] value into my second report parameter query.

Thanks

[Stewart Ross]

Reports are not interactive. There is no on-click event you can use on a report, nor can you click on a hyperlink to link from one to another. Perhaps if you explained what it is you are trying to achieve we could suggest some alternative approaches for you.

-Stewart

[wisni1rr]

Basically I have a Report. The report will contain multiple records. One of the text display fields has an Id number [BWAID]. My user wants to click on the [BWAID] text box and have it open a second report which is a detailed listing of a single record.

I can set an [OnClick] Event on the report with multiple records on the [BWAID] textbox control and program the event to open another report (Detailed report). I need to pull the value out of the [BWAID] Textbox and use that as a parameter to open up my detailed report.

Hope that Helps. I can upload pics if your still not sure what I'm trying to do.

Thanks

[NeoPa]

Stewart, I believe Access versions 2007 and beyond actually do have interactive reports now.

I suspect it was to pander to those who couldn't work out how to use the thing properly in the first place, but I hear (No direct experience, you understand) that this is one of the changes introduced then.

[Mihail]

Hi.
Indeed, in 2007 reports are interactive.

wisni1rr, try that:
Create a module (will be a global module) or use an existing one
In this module dim a PUBLIC variable (say lngBWAID As Long) and design also a PUBLIC function (say RetBWAID)

Public Function RetBWAID as Long
    RetBWAID = lngBWAID
End Function

Under click event of the main report control (the text box in your case) set the value for the public variable

lngBWAID=YourTextBox.Text
'Place here the code to open the second report

Assuming that your second report is based to a query, switch to this query design view and, in criteria row (for BWAID field, of course) write RetBWAID()
DO NOT omit parenthesis !!!

That should be all.
Save and test.

I don't try this so sorry if I misspelled something. Compile the code before running

Good luck !

[wisni1rr]

Close Mihail but I haven't got it quite yet.

I'm sure it is something I am doing wrong. Here is where I am at.

I have created a module called basBWAret:

Option Compare Database
Dim lngBWAID As Long
Public Function RetBWAID() As Long
    RetBWAID = lngBWAID
End Function

On the BreakdownReport the Click Subroutine is for my textbox named BWA_ID:

 lngBWAID = BWA_ID.Text
    DoCmd.OpenReport "ListingReport", acViewPreview, , , acNormal

On the query that is used as the RecordSource on my ListingReport I have entered this as the criteria under the BWA_ID field:

RetBWAID()

As it is the first report opens and you can click on the BWA_ID textbox control. It opens the appropriate report (ListingReport. However, the report textbox conrtols are blank.

Any advice?

[Mihail]

Let's try to debug that.
Insert to more lines in your code (under click event)

Private sub BWA_ID_Click
    lngBWAID = BWA_ID.Text
        Stop
Exit sub
    DoCmd.OpenReport "ListingReport", acViewPreview, , , acNormal
End sub

Run the code as usually.
When the code stop (to Stop statement) verify the value for lngBWAID . Is it what you expected ?
(To verify the value place the mouse cursor over the lngBWAID. Or press CTRL+G. This will open the Immediate window. Type ?lngBWAID and press enter.)

If YES (it is the expected value) then press F5 to continue.

At this point run the query itself.
Do it the job as you expect ?

Let me know the answers to this questions.

[NeoPa]

I would advise checking through When Posting (VBA or SQL) Code before proceeding with your code development. It may well save you wasting a lot of your time.

[wisni1rr]

The lngBWAId variable is collecting the appropriate value.

When the VBA hits STOP I try to run the query itself

The query is attached to the Report "ListingRep ort"

When trying to run it displays an error of "unknown Access database engine error"

[Mihail]

Sorry wisni1rr.
Now I see your mistake.

I say (see post #5) that lngBWAID must be a PUBLIC variable. Maybe you misunderstood because my English.

So, make only this change:

Public lngBWAID As Long

Now should be work

[wisni1rr]

Works perfectly now!!! Thanks a lot!

[Mihail]

More one thing:
Set Option Explicit for your VBA editor.
For that, in VBA editor, go to Tools - Options and check "Require Variable Declaration".

This option will affect only new modules.
For the oldest modules write, at module top, Option Explicit.

This will save you a lot of time when debug an application.