none
Go to record using vriteria from a report RRS feed

  • Question

  • Is it possible to use the DoCmd.GoToRecord method to go the form record identified by the selection in a report.  For example, the report is run and the user decides that changes need to be made to a particular record and would like to close the report and go to the relevant record in the main data entry form.  Can the GoToRecord command use the record selection on the report as the criteria for the record to go to or will the command only accept the record number as the criteria?

    Thanks


    Chris | UK

    Monday, February 22, 2016 10:23 AM

Answers

  • You can do this with a report that is displayed in *report view*.  The code behind a command button on the report would look something like this:

    DoCmd.OpenForm "FormName", wherecondition:="SomeField= '" & Me.SomeField & "'"
    Docmd.Close acReport, Me.Name

    This will work only in Report View.  Once a report has completely displayed in print preview, it's functionality is comparable to a hardcopy of the report.


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 4:42 PM
    Monday, February 22, 2016 11:38 AM
  • Chris,

    Did you place the command button in the detail section of the report?


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 4:42 PM
    Monday, February 22, 2016 4:30 PM
  • Also, does it have to be a filter or can it simply be a GoToRecord command?

    Chris | UK

    Hi Chris. If you don't want to filter the form, you could try the following:

    DoCmd.OpenForm "FormName"
    Forms!FormName.Form.Recordset.FindFirst "ID=" & Me.ID

    (untested)

    Hope that helps...

    PS. Another option is to use the OpenArgs parameter.

    • Marked as answer by ChrisParkin Monday, February 22, 2016 5:53 PM
    Monday, February 22, 2016 5:32 PM
  • Chris,

    I just tested the report and page headers as well - confirmed that it does not work (the record selection gets lost).

    An alternative to the button is the Double Click (or click) Event of a control, or even the detail section of the report.  The double-click event of the detail section could do the trick with no visible control...

    The GotoRecord command is based on record position to my underderstanding... first, second third etc rather than the ID field, so I don't think you can get that to work the way you are expecting.


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 5:54 PM
    Monday, February 22, 2016 5:40 PM

All replies

  • You can do this with a report that is displayed in *report view*.  The code behind a command button on the report would look something like this:

    DoCmd.OpenForm "FormName", wherecondition:="SomeField= '" & Me.SomeField & "'"
    Docmd.Close acReport, Me.Name

    This will work only in Report View.  Once a report has completely displayed in print preview, it's functionality is comparable to a hardcopy of the report.


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 4:42 PM
    Monday, February 22, 2016 11:38 AM
  • Thanks Miriam

    I have got it to work - almost...  What I wanted to do was make a record selection in the Report View and then the command would take me to that particular record in the Form View.

    What the code is doing is filtering the first record of the dataset and not the selection from the report.

    Have I misunderstood something?

    Thanks.


    Chris | UK

    Monday, February 22, 2016 3:46 PM
  • Chris,

    Did you place the command button in the detail section of the report?


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 4:42 PM
    Monday, February 22, 2016 4:30 PM
  • That approach should take you to the form, filtered to the current record in the report, assuming the criteria you are using in the WhereCondition matches at least one record in the form's recordsource.  If no match is found, the form will open to the first record.

    (Am I understanding your question correctly?)

    

    Miriam Bizup Access MVP

    Monday, February 22, 2016 4:38 PM
  • You are understanding perfectly Miriam.

    Putting the command in the details section results in exactly what I wanted.  I had just one main button at the top of the report thinking that I could select the record I wanted in the report and then click the command button.

    Your solution puts an individual command button for each record on the report.  Can you just confirm that it cannot be done they way I was trying please.

    Thanks.


    Chris | UK

    Monday, February 22, 2016 4:42 PM
  • Also, does it have to be a filter or can it simply be a GoToRecord command?

    Chris | UK

    Monday, February 22, 2016 4:51 PM
  • Can you just confirm that it cannot be done they way I was trying please.

    Hi Chris. Pardon me for jumping in, I could be wrong but I think the "only" way to do what you want is if you add a "mechanism" for "selecting" the record before clicking on the button at the top of the report. For example, you might have to add a checkbox field to the table to indicate which record was selected.

    Just my 2 cents...

    Monday, February 22, 2016 5:29 PM
  • Also, does it have to be a filter or can it simply be a GoToRecord command?

    Chris | UK

    Hi Chris. If you don't want to filter the form, you could try the following:

    DoCmd.OpenForm "FormName"
    Forms!FormName.Form.Recordset.FindFirst "ID=" & Me.ID

    (untested)

    Hope that helps...

    PS. Another option is to use the OpenArgs parameter.

    • Marked as answer by ChrisParkin Monday, February 22, 2016 5:53 PM
    Monday, February 22, 2016 5:32 PM
  • Hi DBG, nice to hear from you again.

    No pardon necessary; all contributions gratefully received.  I was thinking that a record in a report has been selected when it looks like this:

    I will try your code so the form is not filtered.  As for OpenArgs, that is yet another parameter that I have yet to even hear of never mind look it up!!

    Thanks again and grateful as always.


    Chris | UK

    Monday, February 22, 2016 5:39 PM
  • Chris,

    I just tested the report and page headers as well - confirmed that it does not work (the record selection gets lost).

    An alternative to the button is the Double Click (or click) Event of a control, or even the detail section of the report.  The double-click event of the detail section could do the trick with no visible control...

    The GotoRecord command is based on record position to my underderstanding... first, second third etc rather than the ID field, so I don't think you can get that to work the way you are expecting.


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Monday, February 22, 2016 5:54 PM
    Monday, February 22, 2016 5:40 PM
  • Side note - the Report View does give you SOME form-like programmability/functionality, but it is not fully there (things don't quite work the way they would work in form events). 

    Miriam Bizup Access MVP

    Monday, February 22, 2016 5:46 PM
  • Hi DBG, nice to hear from you again.

    No pardon necessary; all contributions gratefully received.  I was thinking that a record in a report has been selected when it looks like this:

    I will try your code so the form is not filtered.  As for OpenArgs, that is yet another parameter that I have yet to even hear of never mind look it up!!

    Thanks again and grateful as always.


    Chris | UK

    Hi Chris. You are correct in saying records are "selected" when highlighted; however, the selection is lost as soon as you click elsewhere, such as the header of the report. So, as soon as you click on the button, you'll notice the "selection highlight" goes away.

    OpenArgs is a parameter or argument to the OpenForm method. It's a way to pass information to the opening form. For example, if you pass the ID field to the opening form, it can navigate the record pointer to it.

    Just my 2 cents...

    Monday, February 22, 2016 5:49 PM
  • DBG and Miriam, between the two of you I have exactly what I want...

    DBG, your (untested) code works perfectly; takes me to the record in the form view and unfiltered.  I get what you say about losing the selection.

    Miriam, great idea to put the code on the Dbl Click event.

    Thanks very much to you both.


    Chris | UK

    Monday, February 22, 2016 5:53 PM
  • Glad you're sorted!

    Miriam Bizup Access MVP

    Monday, February 22, 2016 5:55 PM
  • Hi Chris. Congratulations! Miriam and I were happy to assist. Good luck with your project.
    Monday, February 22, 2016 5:57 PM
  • Hi DBG and Miriam

    Reviving an old one here... I want to do exactly the same thing on a report in another DB so I have used the same code as is currently working in the original DB.

    The problem is that I get an Error code "Error 3464 Data type mismatch in criteria expression" when I execute the double click.

    The only difference between the fields in each DB is that the one that works is a number and the one that doesn't work is a short text field.

    Can you help?  Thanks.


    Chris | UK

    Tuesday, June 21, 2016 2:44 PM