Return to same record after Requery

  • Thread starter Thread starter Dadioles
  • Start date Start date
D

Dadioles

I need to return to the same record after a Requery and am too much of a
beginner to solve it myself.

I did find this code on the internet but did not have the skill to apply it
properly:

----------
Private Sub NoOfDays_AfterUpdate()
Dim lngKeyVal As Long
'Save the value for the current record
lngKeyVal = Me.txtKeyFieldControl
'Requery the form
Me.Requery
'Move back to the original record
With Me.RecordsetClone
..FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
------------

The field that identifies the record in my database is called "CompanyPanel"
and is a text key field, not numeric.
The Form is called "1MasterForm" and it contains two SubForms.
I have created a Command Button called "Refresh Display" and the simple code
that I put into it was:

Private Sub RefreshDisplay_Click()
DoCmd.Close
DoCmd.OpenForm "1MasterForm"
End Sub

But obviously although it does what I want by way of cleaning up the screen
I need it to return to the record that was being displayed, not the first
record.

Thank you in advance for any advice.

Best wishes to all from Les
 
The key is the Requery method, which allows you to re-collect your view of
the data without closing/opening the form.

Assuming that txtCompanyPanel is a text box containing the current
CompanyPanel entry, try this:

Private Sub RefreshDisplay_Click()
Dim varCompanyPanel As String
varCompanyPanel = txtCompanyPanel
Me.Requery
Me.RecordsetClone.FindFirst "[CompanyPanel] = " & varCompanyPanel
Me.Bookmark = Me.RecordsetClone.Bookmark
CompanyPanel.SetFocus 'or set the focus to some other control, or omit this
line
End Sub
 
Hi Les,

Try this code for your RefreshDisplay button code:

Private Sub RefreshDisplay_Click()
Dim strKeyVal As String
'Save the value for the current record
strKeyVal = Me.CompanyPanel
'Requery the form
Me.Requery
'Move back to the original record
With Me.RecordsetClone
.FindFirst "[CompanyPanel] = """ & strKeyVal & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

HTH,

Rob
 
Thank you so much for your replies, I am very grateful to you Brian and Rob.
The solution that I have now adopted is working well. It is copied from
Allen Browne although it threw me a bit as mine is a text field and his was
written for numeric.

Here is what I have done in case it helps anyone else:-
Remember, my key was a text field called CompanyPanel


1. Create a table called tblSys with the following fields:

Variable (text 20 - primary key)
Value (text 80)
Description (text 255)


2. Add this to the Forms UnLoad Event:

Private Sub Form_Unload(Cancel As Integer)
Dim rs As DAO.Recordset
If Not IsNull(Me.CompanyPanel) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'CustomerIDLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "CustomerIDLast"
![Value] = Me.CompanyPanel
![Description] = "Last customerID, for form " & Me.Name
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.CompanyPanel
.Update
End If
End With
rs.Close
End If
Set rs = Nothing
End Sub


3. Add this to the forms Load Event:

Private Sub Form_Load()
Dim varID As Variant
Dim strDelim As String
strDelim = """"
varID = DLookup("Value", "tblSys", "[Variable] = 'CustomerIDLast'")
With Me.RecordsetClone
.FindFirst "[CompanyPanel] = " & strDelim & varID & strDelim
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


4. I also created a Command Button on the form to force a manual refresh:
My form was called 1MasterForm

Private Sub RefreshDisplay_Click()
DoCmd.Close
DoCmd.OpenForm "1MasterForm"
End Sub


Now every time the form is opened it opens at the same record that was open
when it was closed.

Thanks again, as a beginner it has taken me hours to sort this one out but
very satisfying and I must repeat how grateful I am to all those selfless
people who freely share their talents via the internet.

Les
 
Back
Top