Return to same record after Requery

[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

 

[Brian]

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

 

[Rob Parker]

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

 

[Dadioles]

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