MS Access – Hyperlinking a Form to a Report Record Textbox

Topic

New Reply

I have an Access Report that lists selected information on particular Records from a single Table via a Query. This Report is normally only used in Preview mode.

What I would like to be able to do is to use the Hyperlink Properties of the Textbox in my Report that refers to the unique ID of a Record to open an existing Form to display the details of the selected Record.

Thus my question is “What code do I use in the “Hyperlink Target” property of the ID Textbox in my Report to open the Form at the selected Record?”

Thanks in anticipation of a solution.

Cheers

BygAuldByrd

Reply | Quote

Replies

The short answer is that it is not possible in Print Preview Mode. You will need to use a form to display the data to allow for the drill drown.

If you are using Access 2007 or later you can use the Report View Mode.Report View does work different than Print Preview. It works more like a form. It does allow you to do what you want. To see it in action take a look at the Tasks template for Access 2007/2010.

Reply | Quote

Hi HiTechCoach,

It would seem I was not quite clear in my description of the issue. I meant that I usually only view the Report on screen in Report mode, not in Print Preview mode.

I looked at the Tasks template and after much head scratching and searching found I had to do the following, which I elucidate for the benefit of others, to my Report (in Design mode):

[*]Select the Textbox on the Report that references the unique ID of the record to be opened in Form view
[*]Set the Textbox Format Property “Display as Hyperlink” to “Screen Only”
[*]Select the Textbox Event Property “On Click” and click on “…”
[*]Open the Macro Builder and build a macro in the following form:
29768-20120110-Macro-for-jumping-from-a-Report-to-a-Form
[*]Save the macro and close Macro Builder
[*]Save the changes to the Report
[*]Open the Report in Report (on screen display) mode
[*]The record identifier will be highlighted as a Hyperlink and when clicked should open the appropriate Form at the selected Record.

At least that worked for me.

Thanks for the to pointer in the right direction.

Cheers

BygAuldByrd

Reply | Quote

BygAuldByrd,

Glad to hear you got it figured out.

Thanks for posting your solution so others may benefit.

Reply | Quote

I tried to do this for a report linking to a report but it did not work for me.

I missed the last bit after the [column_name] & “‘”

Reply | Quote

BAB,

You can do this with a regular report by using the Click event on the ID field with code like this:

Code:

Private Sub ContractNo_Click()
           
           DoCmd.OpenForm "Contracts", acNormal, , "ContractNo = " & _
                      Chr(34) & Me.ContractNo & Chr(34)

End Sub

Note: the Chr(34)’s are only necessary if the ID value is a string type.:cheers:
31534-FormFromReport

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I am trying to click a record number for a patient that will then open the form with the specified patient’s data in it. I tried using the macro builder as stated with this thread but was unable to make it work without many different error messages. Can someone please assist me with making this work? I know it’s probably something extremely easy… I am unable, however, to figure out what I’m doing wrong.

The report is Quick View List
The form is Trauma Log

The field on the Quick View List Report is the RECORDNUMBER field. I want to be able to click on the record number field on the Quick View List and have it open this patient’s information which also includes the record number in the trauma log Form. Record Number in form matches record number in report.

I hope this makes sense.

Reply | Quote

Hi lmartinrn,

I have created from scratch using MS Access 2010 the attached zipped database (20121129 TEST Report Form Linking.accdb) which I believe, from the limited information you’ve provided, replicates your situation.

The steps I followed were:
[/FONT][/FONT][/COLOR]
[*]Create a Table called “Patient Data” with two fields: “RECORDNUMBER” and “Patient_Data”[/FONT][/SIZE]
[*]Create a Form called “FmTrauma Log” with all fields from the “Patient Data” Table
[*]Create a Table called “Rpt Quick View List” with all fields from the “Patient Data” Table

[*]Using the instructions contained in my post dated 2012-01-10, 11:51 the Report Recordnumber field was hyperlinked to the Form

[*]Open the “Rpt Quick View List” in Report View mode (NOT Print Preview mode)
[*]Click on a Recordnumber
[*]Form FmTrauma Log opens at the selected patient’s record — It worked without error messages.
[/LIST][/FONT][/COLOR]
Without more information on how you are implementing your Report to Form linking I can offer no other suggestions.

Hope this helps

Cheers

BygAuldByrd


[/FONT]

Reply | Quote

I have 2007 and my macro builder looks nothing like the your example. I do not, in fact, have the same options even through Macro Builder. In addition, my RecordNumber field is not an autonumber. It’s a unique number/alpha field that is set as the primary key of the Trauma Patient Data Table

Reply | Quote

Hi Imartinrn,

Unfortunately I do not have Access 2007, so I’m unable to assist with how to use the Access 2007 Macro Builder. Maybe some who has can respond with a solution.

I would not expect that having the RECORDNUMBER as text field should cause any problems, provided the values in that field are indexed and that there are no duplicates, ie they are unique.

I have revised my sample database such that RECORDNUMBER is now a unique Text field and it works – in Access 2010 – a copy is attached. I had to slightly modify the macro code to prevent an error because the RECORDNUMBER is now text, and in my example with a space in some of the record identifiers. Here is the revised macro code:

32489-20121128-Hyperlink-Macro-Code

Note that line:
[INDENT]Where Condition = = “[RECORDNUMBER] =” & “‘” & [RECORDNUMBER] & “‘”[/INDENT]

In this line use double quote (“), single quote(‘) double quote(“) around the second instance of [RECORDNUMBER] as shown above.

Hope this helps.

Cheers

BygAuldByrd

Reply | Quote

The macro builder was substantially redesigned for Access 2010, while 2007 had the same basic design used in 2000, 2002 and 2003. In addition, Access 2010 introduced the concept of embedding macros in a form or report, and added conditional execution of macros.

In your situation, I would look at using a continuous form to display the data that you are currently displaying on a report, and use some simple VBA to deal with the conditional aspects of what you are trying to do.

Reply | Quote

my report is based on uinion query with 4 forms
how will i make the same instruction with that
regards

Reply | Quote