Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return To Same Record (Acc 2K SP3)

    I have a main form with a sub-form. On the sub-form I have a filter which works perfectly. I have a command button on the sub-form which uses "Docmd.ShowAllRecords" which works, but, it takes the main form back to record 1 in the recordset.

    How can I keep the main form on the current record when I clear the filter on the subform.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return To Same Record (Acc 2K SP3)

    You can't keep it there, you will have to return to that record by searching for it in the subform's recordsetclone (DAO) or recordset.clone (ADO) and then setting the form's bookmark to match the recordset's bookmark. That means that before you issue the ShowAllRecords statement, you need to store the key value that identifies the current record in a variable and then use that variable after the filter is removed to find the record's position in the full recordset.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return To Same Record (Acc 2K SP3)

    Thanks. I understand the principle of what you are saying. I've looked up the help screen on bookmarks but I can't make sense of it. Do you have some sample code to explain your solution?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return To Same Record (Acc 2K SP3)

    OK, in a vanilla 2000 MDB with an ADO reference set, you could do something like this, assuming your recordset contains a field called ID that is an autonumber or long integer and is a unique key on the record:

    <pre>Dim rst As Object
    Dim lngID As Long

    ' set a variable to the value of the ID field in the current record
    lngID = Me.ID

    ' show all the recordcs
    DoCmd.ShowAllRecords

    ' create a recordset clone
    Set rst = Me.Recordset.Clone

    ' look for the saved ID in the recordset clone
    rst.FindFirst "[ID] = " & lngID

    ' set the form's bookmark to point to the record found
    Me.Bookmark = rst.Bookmark</pre>



    If you were using DAO, you would do it almost the same way, but it would look like this:

    <pre>Dim rst As DAO.Recordset
    Dim lngID As Long

    ' set a variable to the value of the ID field in the current record
    lngID = Me.ID

    ' show all the recordcs
    DoCmd.ShowAllRecords

    ' create a recordset clone
    Set rst = Me.RecordsetClone

    ' look for the saved ID in the recordset clone
    rst.FindFirst "[ID] = " & lngID

    ' if a match was found, synchronize the bookmarks
    If Not rst.NoMatch Then
    ' set the form's bookmark to point to the record found
    Me.Bookmark = rst.Bookmark
    End If</pre>

    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return To Same Record (Acc 2K SP3)

    Thanks for the code. I notice the principle in the end of the code is the similar to the code created by the combo box wizard to find a record. However, alas, I've tried both samples, the ADO and DAO, each time the same error msg: 3021, no current record.

    The only change I made was instead of "lngID = Me.ID" I used "lngID = Me.Parent!ID" because I wanted to find a match on the parent form (yes I did use the real ID name). I actually tried both ways but neither worked.

    Any ideas as what I'm doing wrong?

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

    Re: Return To Same Record (Acc 2K SP3)

    If the code is run from the subform, and you want the main form to return to a certain record, you'll have to refer to the main form throughout, so everywhere Charlotte's mentions Me., change it to Me.Parent.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return To Same Record (Acc 2K SP3)

    Yes, the code is run from a sub-form and I have tried Me.Parent!ID.

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

    Re: Return To Same Record (Acc 2K SP3)

    OK, let's try a different approach. Replace DoCmd.ShowAllRecords by

    Me.Filter = ""
    Me.FilterOn = False

    This has the same effect on the subform as ShowAllRecords, but it doesn't do anything with the main form, so the code to return to the same record is superfluous. This worked for me in a quick test.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return To Same Record (Acc 2K SP3)

    I overlooked the "main form" business in your original post. To reiterate what Hans suggested, you don't just use Me.ParentID, you have to use Me.Parent.ParentID (or whatever the field is called on the parent form. Furthermore, the recordsetclone would have to be on the parent form's recordset, not the subform. Sorry for steering you wrong.
    Charlotte

Posting Permissions

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