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?
BrianWrenConnect With a Mentor Commented:
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

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

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.

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

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 <<<
All Courses

From novice to tech pro — start learning today.