Results 1 to 10 of 10
  1. #1
    jschneiter is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    10

    Requiry form without loosing current record


    Hello,

    I have a form with a bunch of businesses.
    Some of these businesses are Active and some are in-active
    By default the query only shows those that are active on the form
    I have a check box that requerys to include inactive as well.
    The problem is when it requery, the form refreshes and goes back to the first record.

    I understand, when you requery, it erases the bookmark.
    I thought I could grab the (store_id) then run the requery then "docmd findrecord" and go to that saved ID, but it just stays put on the first record

    I have found MANY different ways of accomplishing this but none have worked.
    I have faith that someone in here would know an easy way to accomplish this.

    PS If this requires Public subs or Modules, please be detailed on how these work. I have YET to understand and full take advantage of these methods.

    Thanks in advance as usual!!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, when you requery you reload the form, so naturally the first record becomes the current record. Several ways you can deal with this:
    - You can store the record id in a variable, then requery and then go to that record.
    - you can just store the bookmark value of the current record as in
    Code:
    Dim varBookmark as Variant
    varBookmark = Me.Bookmark
    Me.Requery
    Me.Bookmark = varBookmark
    - You can also use a recordset clone of the form and set the bookmark to the current record, then requery, then go to the record that corresponds to the bookmark that was saved. Not sure why one would use the bookmark property of a recordset clone just for a simple place marker but I guess it is done occasionally.

    EDIT - this will probably not work if you're adding or deleting a record after setting the bookmark. I re-read your post and also now realize you're saying the bookmark changes because of the requery, not that it gets erased (as in there is no value). You'd probably be better off getting the record PK id then using DoCmd.Goto to get back to it - assuming you have a PK field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I worked out a sample for you. Assume that you're on a filtered form with 10 out of 17 records showing. The autonumber PK field is named Entry. Obviously as you navigate the records at some point the Entry value and record number will get out of sync. If you removed the filter, the form will show 17 records, which is the same effect as when you alter the criteria and reload your form. I threw in the requery anyway, just to show that it's placement is important for you. You must find the PK value before requerying or altering the filter.
    Code:
    Private Sub Command0_Click()
    Dim lngID As Long
    Dim rs As DAO.Recordset
    
    lngID = Me.Entry
    Me.FilterOn = False '<< not used in your case
    Me.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "Entry =" & lngID '<< this finds the new bookmark for the ID once the recordset clone changes because of requery.
    If rs.NoMatch = False Then Form.Bookmark = rs.Bookmark 'set new form bookmark to the clone bookmark that matches the PK value
    
    Set rs = Nothing
    
    End Sub
    Very minimal and no error handling, but hopefully will work for you and provide a starting point at least.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The simplest method of doing this is to requery the form recordset rather than the form itself.
    Code:
    Me.Recordset.Requery
    It should do exactly what you want.

    See https://social.msdn.microsoft.com/Fo...hrough-requery for further discussion about that approach as well as other solutions. Also see https://www.devhut.net/2012/10/19/ms...e-same-record/
    Last edited by isladogs; 03-30-2021 at 01:02 AM. Reason: Extra link
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by isladogs View Post
    The simplest method of doing this is to requery the form recordset rather than the form itself.
    Code:
    Me.Recordset.Requery
    It should do exactly what you want.
    Best tip of the day! I could have avoided dozens of lines of code over the years if I had known this!

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OP doesn't say what kind of form it is so I tried this on a single record form - no luck. Copied form, made it a cf and tried again; still no luck. The form opens with the recordsource limited to Attendance values of 1.
    Here's what I wrote for a button click event
    Code:
    strSql = "SELECT [Attendance Log].Entry, [Attendance Log].[Last Name], [Attendance Log].[First Name],"
    strSql = strSql & " [Attendance Log].Attendance From [Attendance Log];"
    Me.RecordSource = strSql
    Me.Recordset.Requery
    By re-writing the recordsource to include all Attendance values I believe I'm replicating what the OP is doing:
    By default the query only shows those that are active on the form
    I have a check box that requerys to include inactive as well.
    however, this could mean a filter is being modified and some clarification might help there.

    In the posted links the users were adding or deleting records. Either I've done this wrong or requerying the recordset won't work when the recordsource is being altered. I suspect it's the latter but the current record becomes #1 regardless of which form I tried this on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    jschneiter is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    10
    OMG Micron!!! I tried your first reply and it worked!!!!

    Private Sub Command0_Click()
    Dim lngID As Long
    Dim rs As DAO.Recordset

    lngID = Me.Entry
    Me.FilterOn = False '<< not used in your case
    Me.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "Entry =" & lngID '<< this finds the new bookmark for the ID once the recordset clone changes because of requery.
    If rs.NoMatch = False Then Form.Bookmark = rs.Bookmark 'set new form bookmark to the clone bookmark that matches the PK value

    Set rs = Nothing

    End Sub
    Thank you soooooooo much. You know how many years I have been looking for this??

    So it indeed goes back to the current record after a requery. EXCEPT for when your on a record that gets removed from the requery, in that case it just goes to the first record.

    I was thinking of checking to see if its on a record that will be removed (In-Active), if so then go back a record. That will take some trial and error I think. But for now, it works.

    Again, thank you so much!!

    Peace!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad to have been of some help. When you think of it, if you store a pointer to your current record but end up deleting it, you cannot possibly go back to it. The NoMatch will return true, so you'll end up at the first record upon requery. I suppose you could write 'if the noMatch is true, go to a bookmark plus or minus 1' that you also created before the requery. However, one would need to deal with the possibility that there is no record before or after the bookmark thus do nothing if that were true. Then you'd simply go to the first record after the requery.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Code:
    Dim varBookmark As Variant
    
    
    Private Sub Form_Open(Cancel As Integer)
    '*=*=*=*=*=*=*=*=*=*=*=*=( OPEN OPEN OPEN OPEN OPEN )=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    lngRecID = Me.OpenArgs
    
    Me.RecordsetClone.FindFirst "RegistryID = " & lngRecID             'lngRecID
    varBookmark = Me.RecordsetClone.Bookmark
    
    End Sub
    Then in the code that adds the new record to the pets table................
    Code:
    Private Sub lblAddPets_Click()
    If Nz(DLookup("AptNum", "QPets", "AptNum = " & intAptNum)) = 0 Then
        InitPets (intAptNum)
        Me.Requery
        Me.Bookmark = varBookmark
    End If
    
    Me.lblAddPets.Caption = "Pet(s)"
    
    Me.tbsp1.Visible = True
    Me.tbPet1.Visible = True
    Me.tbSp2.Visible = True
    Me.tbPet2.Visible = True
    
    Me.tbsp1.Requery
    Me.tbPet1.Requery
    Me.tbSp2.Requery
    Me.tbPet2.Requery
    
    End Sub
    The above changes puts me back into the error conditions of the OP

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Got it all.
    Thanks,
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-18-2018, 10:23 AM
  2. Make last record in Form's RecordSource the current record
    By GraeagleBill in forum Programming
    Replies: 6
    Last Post: 06-27-2018, 10:43 AM
  3. Replies: 6
    Last Post: 05-09-2017, 10:43 AM
  4. Replies: 3
    Last Post: 02-06-2015, 01:18 PM
  5. Form loosing link to table
    By kcm4491 in forum Forms
    Replies: 9
    Last Post: 03-23-2011, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums