Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts

    MS Access - Hyperlinking a Form to a Report Record Textbox

    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

  2. #2
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    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.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts

    MS Access - Hyperlinking a Form to a Report Record Textbox

    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):
    1. Select the Textbox on the Report that references the unique ID of the record to be opened in Form view
    2. Set the Textbox Format Property "Display as Hyperlink" to "Screen Only"
    3. Select the Textbox Event Property "On Click" and click on "..."
    4. Open the Macro Builder and build a macro in the following form:
      20120110 Macro for jumping from a Report to a Form.JPG
    5. Save the macro and close Macro Builder
    6. Save the changes to the Report
    7. Open the Report in Report (on screen display) mode
    8. 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

  4. The Following 2 Users Say Thank You to BygAuldByrd For This Useful Post:

    Dick Fitzwell (2016-10-26),emijayne (2013-03-06)

  5. #4
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    BygAuldByrd,

    Glad to hear you got it figured out.

    Thanks for posting your solution so others may benefit.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #5
    New Lounger
    Join Date
    Jul 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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] & "'"
    Last edited by Robdemanc; 2012-07-22 at 17:17.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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.
    FormFromReport.PNG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a link from a Report to a Form

    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.

  9. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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:
    1. Create a Table called "Patient Data" with two fields: "RECORDNUMBER" and "Patient_Data"
    2. Create a Form called "FmTrauma Log" with all fields from the "Patient Data" Table
    3. Create a Table called "Rpt Quick View List" with all fields from the "Patient Data" Table
    4. Using the instructions contained in my post dated 2012-01-10, 11:51 the Report Recordnumber field was hyperlinked to the Form
    5. Open the "Rpt Quick View List" in Report View mode (NOT Print Preview mode)
    6. Click on a Recordnumber
    7. Form FmTrauma Log opens at the selected patient's record -- It worked without error messages.

    Without more information on how you are implementing your Report to Form linking I can offer no other suggestions.

    Hope this helps

    Cheers

    BygAuldByrd


    Attached Files Attached Files
    Last edited by BygAuldByrd; 2012-11-27 at 19:11.

  10. #9
    New Lounger
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by lmartinrn; 2012-11-27 at 21:31.

  11. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    153
    Thanks
    28
    Thanked 3 Times in 2 Posts
    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:


    20121128 Hyperlink Macro Code.JPG

    Note that line:
    Where Condition = = "[RECORDNUMBER] =" & "'" & [RECORDNUMBER] & "'"

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

    Hope this helps.

    Cheers

    BygAuldByrd





    Attached Files Attached Files

  12. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  13. #12
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    my report is based on uinion query with 4 forms
    how will i make the same instruction with that
    regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •