return to selected location in continues form

[WalterInOz]

Hi, can someone help me with this please?

The first form of my database is a continues form based on a qry. The list contains about 400 records (frmFirst). When I select a record to work on I click on the selector and all details of that record show up (frmBook). When I close frmBook frmFirst appears again after first having gone through a requiry. The really irritating thing is that frmFirst always returns to the first record of the list and I'll have to click through the whole list to go back to where I was. How do pass on a value to make me return to the point where I was in frmFirst?

Thanks for your help,
Walter

 

[namliam]

in pseudo code

You need to store the PK of the record you were on
then requery
and do a search on that PK and reselect it...

I hope its clear enough...

 

[giovi2002]

Namliam has right and if you ever find code for bookmarks don't use it.

In the after update event or after insert event you can capture the primary key.

- Use docmd.findrecord to find the autonumber after your requery
- use docmd.gotorecord .... acnext to goto the next record after the one modified or inserted
(don't have the code here can post the general procedures tomorrow, best thing is to put it in a general module so you can easily refer to it via your form code)

Only prob in your case is that you modify in the subform and requery the main form, to be honest i don't know how to store the PK unless the PK of your main form is also mentioned in the subform.

 

[WalterInOz]

namliam and giovi2002,

I can see the solution as suggested but don't understand how to get there. I have the following now that obviously misses a few things. I cannot figure out how to proceed from here. Could you please be a bit more specific?

stDocName = "frmBooks2"
DoCmd.FindRecord , , , , , acCurrent, [Booknumber]
DoCmd.GoToRecord acDataForm, "frmAllBooks" ......... and now what?

Thanks guys.

 

[namliam]

You are looking to return to frmBooks2 or frmAllBooks??

Once your return to your form (or previous to returning to it, using the on exit of the closing form) you can execute that search. On your subform you should have the PK, so most times its easiest from there..

That is if you still have the main one open...

 

[WalterInOz]

Hi Mailman,

You're confused. My poor VBA skills must have thrown you.
I want to go from frmBooks2 back to frmAllBooks. There is no subform involved.
FrmAllList is a continues form based on a qry and shows all books from 0001 to (currently) 3378.

When I've been working on e.g. bok 3205 I'd like to return to that position in the list (which is not really a list, it's frmAllBooks).

The PK present on both forms is BookID so what I need to do is tell frmAllBooks that it needs to open in a position that shows the record with the same BookID I've just worked on in frmBooks2. Correct?

Something like:
open frmAllBooks where [bookID] = forms!frmBooks2!BookID
should probably be in the openingsargument of frmAllBooks.
I just haven't got a clue how to really get it right. I've tried lots of combinations, almost randomly as I really don't quite know what I'm doing, but to no avail. A little more help would be great.

 

[namliam]

assuming that at that time the form frmAllBooks is closed, you can pass on the ID thru to the OpenArgs part of Docmd.openform

Then in the on open event of that form check if any OpenArgs has been given, if so perform the search...

Does that help at all?

 

[WalterInOz]

Does that help at all?

No not really. That's what I had figured out already. I'm still stuck on how I do that, correct syntax etc

With this code I aim to pass on the value with the 'on close' event:

open "frmAllBooks" where forms!frmAllBooks.BookID = forms!frmBooks2.BookID

Doesn't work so something is wrong. Any hints?

 

[namliam]

docmd.OpenForm "Formname",acNormal,"Filter","where clause",ID

The filter and where work directly on the form level and cause only that record to be visible, not to jump to record x.

The ID is where you can pass the openargs, in this case your ID.

Then in the form "On Open" event you can retrieve the argument using Me.Openargs.

using docmd.findrecord you can then lookup the record.
Alternatively you can use recordsets and the recordsetclone if you want...

Does that than help?