none
Requery form's table and maintain position in table

    Question

  • I have a need to requery the table a form is bound to. However, I want to maintain my position in the table.

    Does anyone have sample code?

    This needs to work in all cases; e.g. with an empty table or when I have just deleted the currect record and the requery follows immeditely after the delete.

    Saturday, July 12, 2014 7:55 AM

Answers

  • Try the follwing code in the cmdClose_Click Event:

    Dim rsd As DAO.Recordset
      
    Dim lngPos As Long
    ' Dim lngRecordCount As Long   //Not needed '
        
    lngPos = Me.Recordset.AbsolutePosition
      
    If Not IsDate(Me!CloseDate) Then
      Me!CloseDate = Date
      Me.Dirty = False   ' No need for If '
      Me.Requery
      
      ' Need to ensure Recordset is fully populated '
      ' before setting Absolute Position '
      Set rsd = Me.RecordsetClone
      If Not rsd.EOF Then
        rsd.MoveLast
        rsd.MoveFirst
        If rsd.RecordCount > lngPos Then
          Me.Recordset.AbsolutePosition = lngPos
        Else   ' Updated record was the last '
          Me.Recordset.MoveLast
        End If
      
      Else   
        ' Requeried Recordset is empty. Do nothing. '
      End If
      
    End If

     

    Van Dinh



    Monday, July 14, 2014 11:39 PM

All replies

  • This needs to work in all cases; e.g. with an empty table or when I have just deleted the currect record and the requery follows immeditely after the delete.

    Hi AllTheGood...,

    You can grab the CurrentRecord property of the form. Then you process the record. After the Requery you go to the record with the saved CurrentRecord.

    Take care: this only works for a well sorted RecordSet.

    Imb.

    Saturday, July 12, 2014 9:41 AM
  • Here's an example to get you started:

        Dim lngID As Long
        
        lngID = Me.TransactionID
        Me.Requery
            
        With Me.RecordsetClone
            .FindFirst "TransactionID = " & lngID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With

    Ken Sheridan, Stafford, England

    Saturday, July 12, 2014 10:33 AM
  • Thanks for your help. I tried this code and it is still going back to record 1 on every requery where the current record is not found.

    The user needs to be able to page thru records one at a time from the first to the last. There is a button on the form that changes the current record such that it no longer is in the record source. I need the user to be able to continue paging from this point. So the position needs to be on the next record after the one that no longer appears. Is this possible? Hope this is clear.

    Needless to say the code needs to work in all cases e.g. where the last record in the recordset is deleted or where after the current record is deleted, the recordset is empty.



    Saturday, July 12, 2014 5:27 PM
  • >>
    ... where the last record in the recordset is deleted or where after the current record is deleted, the recordset is empty.
    <<

    1.  Why do you need to Requery the Form's Recordset if the record is deleted through the Delete action on the Form? The normal deletion on the Form will make the "next" record the new CurrentRecord or if the deleted record was the "last" record, the new "last" record the new CurrentRecord.

    How does the user delete the record on the Form?

    >> 
    The user needs to be able to page thru records one at a time from the first to the last. There is a button on the form that changes the current record such that it no longer is in the record source. I need the user to be able to continue paging from this point. So the position needs to be on the next record after the one that no longer appears.
    <<

    2.  Please post the code for the CommandButton_Click Event (add comments so that we can follow if the code is complex) so that we can understand your process.

      


    Van Dinh


    • Edited by Van DinhMVP Tuesday, July 15, 2014 3:05 AM Typos
    Saturday, July 12, 2014 6:01 PM
  • You'll need to grab the key of the row immediately following the one you exclude from the form's recordset in the form's sort order to the variable.  In the example I posted the form is based on a query named qry123Account, and is sorted in ascending transaction date order, so the code would be;

        Dim lngID As Long
        Dim varDate As Variant
        Dim strCriteria As String
        
        strCriteria = "Transactiondate >= #" & Format(Me.TransactionDate, "yyyy-mm-dd") & _
            "# And TransactionID <> " & Me.TransactionID
        varDate = DMin("TransactionDate", "qry123Account", strCriteria)
        
        If Not IsNull(varDate) Then
            strCriteria = "TransactionDate = #" & Format(varDate, "yyyy-mm-dd") & _
                "# And TransactionID <> " & Me.TransactionID
            lngID = DLookup("TransactionID", "qry123Account", strCriteria)
                
        End If

        ' your code goes here
        
        Me.Requery
                
        With Me.RecordsetClone
            .FindFirst "TransactionID = " & lngID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With

    The value of the key must be grabbed to the lngID variable before the row is excluded from the recordset of course, so the code you do this would go where I've remarked he above code in boldface with 'your code goes here'.

    Ken Sheridan, Stafford, England

    Saturday, July 12, 2014 6:18 PM
  • As long as the form's record source is a query that sorts in a unique way, I think I might do it differently:

    Dim lngPos As Long

    With Me.Recordset
    lngPos = .AbsolutePosition
       .Delete
       Me.Requery
       If .RecordCount > lngPos Then
          .AbsolutePosition = lngPos   End If
    End With

    Saturday, July 12, 2014 10:23 PM
  • Thank you for your response.

    I have one table and two forms bound to the table.

    The 'Open' form record source has WHERE CloseDate IS NULL

    The 'Closed' form record source has WHERE CloseDate IS NOT NULL

    The 'Open' form has a 'Close' button which executes:

       If Nz(Me!CloseDate, "") = "" Then
          Me!CloseDate = Date
       End If
       If Me.Dirty = True Then Me.Dirty = False
       Me.Requery

    This is the code that is going back to record 1 each time it runs so I have to page forward to where I was each time. I need to maintain my position to go forward from the point where I was before the requery.


    Monday, July 14, 2014 5:29 PM
  • Thanks, I'll give it a try and report back...
    Monday, July 14, 2014 5:30 PM
  • Try the follwing code in the cmdClose_Click Event:

    Dim rsd As DAO.Recordset
      
    Dim lngPos As Long
    ' Dim lngRecordCount As Long   //Not needed '
        
    lngPos = Me.Recordset.AbsolutePosition
      
    If Not IsDate(Me!CloseDate) Then
      Me!CloseDate = Date
      Me.Dirty = False   ' No need for If '
      Me.Requery
      
      ' Need to ensure Recordset is fully populated '
      ' before setting Absolute Position '
      Set rsd = Me.RecordsetClone
      If Not rsd.EOF Then
        rsd.MoveLast
        rsd.MoveFirst
        If rsd.RecordCount > lngPos Then
          Me.Recordset.AbsolutePosition = lngPos
        Else   ' Updated record was the last '
          Me.Recordset.MoveLast
        End If
      
      Else   
        ' Requeried Recordset is empty. Do nothing. '
      End If
      
    End If

     

    Van Dinh



    Monday, July 14, 2014 11:39 PM
  • Hi.

    As an alternative to code a set of macro actions could do the trick. Introduce an unbound control into your form and use a macro to delete the current record. A macro can handle the deletion as well as the requery and should contain the following actions based on the assumption that the current record is the one you need to delete:

    1) GoTo Action: Next Record

    2) SetValue Action: Set the value of the unbound control ([Forms]![X]![Y] to the value of the ID field (of this next record, which at this stage is the current record) [Forms]![X]![Z].

    3) GoTo Action: Previous record (the one you needed to delete)

    4) DeleteRecord Action: This action will do the deletion

    5) Requery Action

    6) GoToControl Action: Specify the name of the [ID] field

    7) FindRecord Action: =Forms![X]![Name of the unbound control]

    The above set of actions will delete the current record and take you to the next record.

    You may encounter an error message to the effect that you are at the end of a recordset if and when you delete the last record in a recordset. Since a deletion is involved, may I suggest that you first try these actions in a dummy set of records having a dummy form. The actions should all be contained in a single macro. The name of this macro should be set to the "OnClick" event of the Delete Rcd command button. Please let me know if this suggestion has worked for you. If so I could try to find a way to avoid the possible error message after the last record is deleted.

    Jugwunth P M

    Wednesday, July 16, 2014 3:00 AM
  • Hi

    You could use a bookmark to identify the next record but I am not versed in the code to do that.

    Wednesday, July 16, 2014 3:05 AM