Staying on Current Record

I'm looking for a way to stay on the current record in a subsubform (embedded form) when the main form is refreshed (or requeried).  The main form has two list boxes with buttons the can be used to move numbers between these lists.  Currently, whenever the users presses a button to move numbers from one list to another, the subsubform goes back to record 1 (from whatever record it was displaying).  I would like to have it stay on whatever record they were on.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Set a Global String variable in a standard module.

When the subform is on the record that you want, set this string to the sub's Bookmark

  gstrBkMk = Me.Bookmark

When you are done, set the subform's bookmark to this string.  
For example, from the main form, if the sub's control is named ctlSubFrm:

  Me!ctlSubFrm.Form.BookMark = gstrBkMk

I ran out of time when answering this, but wanted to get the ball rolling.

In the database window, (the window that opens when you first open a database), click the modules tab, and either select the New button, or, if there are already modules, open one of those for design.

Near the top, after the options, (usually Compare Database, and Explicit), enter the following, above any subs or functions:


Global gstrBkMk As String


Save the module, and close it.

Then, on either of the button clicks which are giving you grief, (which sound like they are on the main form; I'll presume that), put the folowing before any of the other actions.


    gstrBkMk = ""
    gstrBkMk = Me!ctlSubFrm.Form.Bookmark


This presumes that the control with the sub form in it is called ctlSubFrm.  When refering to the controls on, and properties of a sub form, you refer to the Form property of the control holding the subform, regardless of the name of the form that is loaded into the control--they might be the same they might not.

Now you have in gstrBkMk, (Global String BookMark...), a unique identifier of the current record of the subform.  As long as the contents of the subform don't change, (due to requery, changing the record source, etc.), the bookmark will be valid.

Then after all of the events that need to happen in the button click, put:


    If gstrBkMk <> "" Then
 = gstrBkMk
    End If
    gstrBkMk = ""


The reason for setting the string to "", and testing to see if it is no longer = "" is to make sure that a valid bookMark was gotten in the first place, and to make sure the string never holds an invalid reference.

Setting the form's bookmark immediately positions the current record, (subject to all of the normal before update, after update, etc. events).

As you might notice from Brian's comments, there actually isn't a way that you can "stay on" the record in the subform. If the main form is requeried, Access also requeries the subform(s). That's why it runs you back to the first record in the recordsource. If you used recordsets and unbound forms, of course, you could get around this, but that's probably more work than you want to put in.

Brian's suggestion is the way I would go: set a bookmark, requery, and then return to the record. The user will most likely see the requerying go on (unless you want to play with turning Echo on and off), but they'll end up where they started.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

schmir1Author Commented:
I tried using bookmarks and I get an error (Not a valid bookmark) when I run the code.  The bookmark looks like "?|".  My code the requery two list boxes.  Here is the code:

Private Sub cmdAddOne_Click()
  strBookMark = ""
  strBookMark = Me![Issue Components on Asm Subform].Form.bookmark

  Call SelectItem(True) 'moves the selected serial numbers then requeries both list boxes
  Call SetFilter
  [Form_Issued Traveler Build].Refresh   'update the list boxes on the build form
  If strBookMark <> "" Then
    Me![Issue Components on Asm Subform].Form.bookmark = strBookMark
  End If
  strBookMark = ""
End Sub

Bookmarks are 2-character strings, and usually are indecipherable.  That part is working right.

The problem is that requerying is resetting the bookmarks.

What you need to do is find a field or pair of fields that uniquely identify the records in the subform, and then using those:

1)  Read the unique values into  variables or a variable.
2) Perform your other functions
3) Get a clone of the recordset from the sub form
4) Find the same record in the recordsetclone that you had before in the subform.
5) Set the Subform's bookmark to the same value as the record in the recordsetclone.

    MyStrVar = Me![Issue Components on Asm Subform].Form!UniqueField
2) ...
  Set rs = Me![Issue Components on Asm Subform].Form.RecordsetClone
  rs.FindFirst "UniqueField = '" & MyStrVar & "'"

  If rs.NoMatch Then
      ' Couldn't find record...
      ' Leave Subform alone
      Me![Issue Components on Asm Subform].Form.BookMark = rs.BookMark
  End If


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schmir1Author Commented:
Works perfectly.  You bailed me out again.  There may be more questions to come.  I'm converting my Access 97 DB to Access 2000 as part of this release.  Having some problems but so far nothing I can't handle (I say this with my fingers crossed).

>>> Bob <<<
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.