Go to record using vriteria from a report

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

Answer 1

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

Answer 2

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

Answer 3

Chris,

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

---

Miriam Bizup Access MVP

Answer 4

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

Answer 5

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

Answer 6

Also, does it have to be a filter or can it simply be a GoToRecord command?

---

Chris | UK

Answer 7

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...

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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...

Answer 13

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

Answer 14

Glad you're sorted!

---

Miriam Bizup Access MVP

Answer 15

Hi Chris. Congratulations! Miriam and I were happy to assist. Good luck with your project.

Answer 16

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