By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Hyperlink a report to another report

P: 78
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
Nov 30 '11 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Public lngBWAID As Long
Now should be work

Share this Question
Share on Google+
11 Replies


Expert Mod 2.5K+
P: 2,545
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
Nov 30 '11 #2

P: 78
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
Nov 30 '11 #3

NeoPa
Expert Mod 15k+
P: 31,299
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.
Nov 30 '11 #4

100+
P: 759
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)
Expand|Select|Wrap|Line Numbers
  1. Public Function RetBWAID as Long
  2.     RetBWAID = lngBWAID
  3. End Function
Under click event of the main report control (the text box in your case) set the value for the public variable
Expand|Select|Wrap|Line Numbers
  1. lngBWAID=YourTextBox.Text
  2. '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 !
Dec 1 '11 #5

P: 78
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim lngBWAID As Long
  3. Public Function RetBWAID() As Long
  4.     RetBWAID = lngBWAID
  5. End Function
On the BreakdownReport the Click Subroutine is for my textbox named BWA_ID:

Expand|Select|Wrap|Line Numbers
  1.  lngBWAID = BWA_ID.Text
  2.     DoCmd.OpenReport "ListingReport", acViewPreview, , , acNormal
  3.  
On the query that is used as the RecordSource on my ListingReport I have entered this as the criteria under the BWA_ID field:

Expand|Select|Wrap|Line Numbers
  1. 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?
Dec 1 '11 #6

100+
P: 759
Let's try to debug that.
Insert to more lines in your code (under click event)
Expand|Select|Wrap|Line Numbers
  1. Private sub BWA_ID_Click
  2.     lngBWAID = BWA_ID.Text
  3.         Stop
  4. Exit sub
  5.     DoCmd.OpenReport "ListingReport", acViewPreview, , , acNormal
  6. 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.
Dec 1 '11 #7

NeoPa
Expert Mod 15k+
P: 31,299
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.
Dec 1 '11 #8

P: 78
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 "ListingReport"

When trying to run it displays an error of "unknown Access database engine error"
Dec 1 '11 #9

100+
P: 759
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:

Expand|Select|Wrap|Line Numbers
  1. Public lngBWAID As Long
Now should be work
Dec 1 '11 #10

P: 78
Works perfectly now!!! Thanks a lot!
Dec 1 '11 #11

100+
P: 759
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.
Dec 1 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.