How do I get back to the same record in form when closing report run from the form?

[Elyso]

I have two issues and hopefully you can help me out here. Basically I have a form (called MyForm1) connected to a table with some hundred records....

In MyForm1 there is a button (CmdButton1) with an onclick event attached. When CmdButton1 is clicked a macro is run (stored in Access' own macro module which will OpenReport and Close form). The macro will open MyReport1 (all data in MyReport1 come from a query) and close MyForm1.

In MyReport1 I have an OnClose event attached to the report which will OpenForm... In other words, when the report is closed the macro will go back to MyForm1.

My problem first issue is that I am always taken back to the first record in the form but I want to be taken back to the record from which I ran the report.

Secondly, today when CmdButton1 is clicked I have the query that is connected to MyReport1 ask the user for the ID of the record in the form. Is there anyway I can skip this step, meaning that when CmdButton1 is clicked, the query will "know" that the ID is should use is the one for the form record visible at the moment?

Any help here will be highly appreciated, particularly with the first issue...

Thanks in advance!

 

[John Big Booty]

You can use the OpenArgs portion of the DoCmd.OpenForm to pass the ID to the form being opened and then use DoCmd.GoToRecord to go to that record.

 

[MStef]

Not close the form, but hide the form, and when you close the report, the form is visible again.
Look at "DemoSameRecordA2000.mdb" (attachment, zip).
Open Form1 and try.

 

[Elyso]

Thank you both of you... I liked your solution MStef but when I close the report I want the form to be maximised in sized but it is not.. for some reason...

John Big Booty, your solution appears to be the most professional one but I am not a wiz in VBA so don't know how to use your information.....Can you be more clear about what you mean it would be great....

 

[MStef]

In Sub form_Current (On Cirremt event), put the code:

DoCmd.Maximize

 

[Elyso]

Thx for your swift reply...

I tried to go into my form, clicked properties and chose the tab called Event... first row "On Current", chose the code builder and put in the following...

Private Sub Form_Current()
DoCmd.Maximize
End Sub


Saved and tried again but still it won't work....

 

[John Big Booty]

Thank you both of you... I liked your solution MStef but when I close the report I want the form to be maximised in sized but it is not.. for some reason...

John Big Booty, your solution appears to be the most professional one but I am not a wiz in VBA so don't know how to use your information.....Can you be more clear about what you mean it would be great....

I'll be away from my computer for a couple of days so will not get a chance to post a sample until early next week, but will do so as soon as I get the opportunity.

 

[MStef]

It works properly on my PC (access 2000).

 

[Elyso]

I use Access 2007... maybe that makes a difference... I don't know.

John, if you can post a sample it would be awesome...

 

[John Big Booty]

OK I've had a play with what you are doing.

Now because you are opening a report and closing a form, that you want to then re-open and go back to your original record. I couldn't use the OpenArgs as I had first suggested to pass the record ID between the two, because of the limitations of events available on the report.

Instead the ID can be passed around via a Public Variable. Down load the sample DB, have a play with it. Then check out Module1, The code behind the button on the main form that opens the report, and also the code in the Report's OnClose Event.

 

[MStef]

If you don't solve the problem yet,
look at "DemoGetBackRecA2000.mdb" (attachment, zip).
Open Form1 and try.
Remember current record, and reopen the form on this record.

 

[Elyso]

Thank you to both of you. The problem has now been solved. The public variable solved the issue.