Consulting

Results 1 to 7 of 7

Thread: Link items on report to actual record(s)

  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    110
    Location

    Link items on report to actual record(s)

    Hi

    I have only started using Access this year and set up a couple of databases for work. I have a report within one of them that shows open items and a colleague has asked if it is possible to create links on the report to open the relevant record. Can any one advise if this is possible and if so where I need to be looking to set it up. I have created several test reports to plays around and looked at setting but cannot see if this is possible.

    I can get the records form to open when i click the report but it opens at the first record irrespective of whether it is open or not. I tried changing the line to, DoCmd.OpenForm "Incidents", , , "ID = " & Me!ID thinking the ID would be picked up and the relevant record would open but i just get error message.

    The report form is called incidents, the report has an ID field called ID so if the user clicks on an incident in the report with ID 14 then it is record 14 i would like to open for them?

    Thank you

    Mykal
    Last edited by mykal66; 10-21-2019 at 02:12 AM.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,083
    Location
    Mykal, using VBA in Reports is harder to work with than VBA in a Form. So the output that currently goes to a Report could just as easily go to a form to display it. It is then fairly straightforward to view a single complet record on the main display form.
    I used tabbed forms for this kind of thing.
    Where was the VBA code in the report?
    The other thing to try is
    DoCmd.OpenForm "Incidents", , , "ID = '" & Me!ID & "'"

    VBA Syntax is somewhat "variable" and sometimes one version works when another version doesn't

    ps you didn't say what the error message says.

  3. #3
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    110
    Location
    Hi OBP and thank you AGAIN! I think it is usually that comes to my rescue and I really appreciate your help every time.

    I have output the report to a form - screenshot attached and tried the line of code you suggested but still getting an error message " Run Time Error 2467 The expression you entered refers to an object that is either closed or doesn't exist"Untitled.jpg

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,083
    Location
    OK, first things first, are you using the report for printing?
    If yes then I would suggest that you open and display the data in the form first and have a "Print" command button that prints the Report as required.
    In that way they can interrogate the data from within the form.
    I create a query to supply both the Form and the Report so that the data output is the same.

    Regarding the error message it suggests that the Form name is mispelled, have you checked it?
    Also where is the code?

  5. #5
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    110
    Location
    Hi

    I have added the code below as an on click event on the ID field shown in screenshot earlier. If i remove , , , "ID = '" & Me!ID & "'" then the report closes and the incident form opens fine, it's just when i try to add the rest i get the error message.

    I have added another screenshot to show the label names. I have tried changing the code all combinations of ID & ID_Label but still get the error message
    Untitled.jpg

    HTML Code:
    Private Sub ID_Click()DoCmd.CloseDoCmd.OpenForm "Incidents", , , "ID = '" & Me!ID & "'"End Sub
    Attached Images Attached Images

  6. #6
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    110
    Location
    Hey. Figured it out, it was being being daft!

    My first line closed the report so the second line had nothing to refer to, hence the error message i was getting.

    Thank you again for you help

    Mykal

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,083
    Location
    That happens quite often when you explain the problem to someone, else you realise what is wrong.
    Well done in figuring it out.

Posting Permissions

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