Staying on Current Record

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


Brian

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
        Me!ctlSubFrm.Form.Bookmark
 = 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).

Brian

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.

brewdog

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

  Me.lstAssigned.SetFocus
  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

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