Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Bookmarks in a sub form (2003)

    I am updating records in a subform using an SQL statement. Problem is that this sets the focus to the first record. I need to keep the focus to the current record. I can get this to happen, using the recordset bookmark, if I open the form on its own but when it is a subform the following code doesn't work and I just can't figure out how to modify it to work in a sub form.

    Set rst = Me.Recordset.Clone
    rst.Bookmark = Me.Bookmark
    'do my SQL stuff here.... CurrentDb.Execute strSQL
    Me.Bookmark = rst.Bookmark

    Can you give me any pointers to the syntax for using the bookmark in a sub form please.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmarks in a sub form (2003)

    In what sense doesn't it work?
    Do you get an error message? If so, what does it say?
    Does it do something but not what you wanted? If so, what does it do?
    Does it do nothing at all?

    Where do you have the code that you describe? In the main form's code module or in the subform's code module? In the main form's module, Me refers to the main form. If you want to refer to the subform from the main form, you'd have to use Me.Subformname.Form instead of Me where Subformname is the name of the subform as a control on the main form.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmarks in a sub form (2003)

    The code is behind the after_update event of a text box on the subform. While me.txtItem_Cost gets the data from the field in the current row on the subform, Me doesn't work for the bookmark; I've now tried -

    Set rst = Forms!F0065.SubForm.Form.RecordsetClone
    rst.Bookmark = Forms!F0065.SubForm.Form.Bookmark
    'do SQL stuff here ... CurrentDb.Execute strSQL note - this moves the focus to the first record in the subform but it need to stay in the current row
    Forms!F0065.SubForm.Form.Bookmark = rst.Bookmark

    The last line raises the error "Object invalid or no longer set." the same error as it did in my previous example that used the syntax - Me.Bookmark = rst.Bookmark
    I even tried using Me.Parent.Subform.Form.Bookmark
    Note "Subform" is the name of the subform control on the parent form.

    Any idea?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bookmarks in a sub form (2003)

    Does the record source of your subform have a field that uniquely identifies the records (for example the primary key)?
    If there is such a field, let's say that it's named ID and that it is a number field. You should be able to use code like this:

    Dim lngID As Long
    ' Capture current ID
    lngID = Me.ID
    ' Do your SQL stuff
    CurrentDb.Execute strSQL
    ' Get back to original record
    Set rst = Me.RecordsetClone
    rst.FindFirst "ID=" & lngID
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    End If

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmarks in a sub form (2003)

    No there wasn't a unique identifier, but there is now and everything is right with the world.

    Many Thanks Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •