Cannot maintain reocrd position after re-query - help!

[Rachel]

Hi,

I have a hugeof a problem, I have a form called frm_Search and on
this
form I have a command button which opens a popup form (frm_Invoice),
I
have a command button on the popup form also, which when pressed re-
query's the form 'frm_Search'. This is to ensure that data entered
(frm_Invoice) is displayed in the 'frm_search' form.


However, once it has re-queried the record position (on the
frm_Search
form) goes back to the first record and not the record that has been
updated via the frm_Invoice popup form.


I understand from reading up on this issue, that when something is
re-
queried the bookmark doesn't work.


I am totally lost with this now and any help would be greatly
appreciated. I have tried scanning the net for record clone
information etc but I just seem to get even more confused


This is my code from the popup form command button (frm_Invoice):


Private Sub cmd_Save_Invoice_Click()


On Error GoTo Err_cmd_Save_Invoice_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms!frm_Search.Requery


Exit_cmd_Save_Invoice_Click:
Exit Sub


Err_cmd_Save_Invoice_Click:
MsgBox Err.Description
Resume Exit_cmd_Save_Invoice_Click


End Sub


This is my code from the command button on the frm_Search form (which
opens the popup form):


Private Sub cmd_Invoice_Click()
On Error GoTo Err_cmd_Invoice_Click


Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "frm_Invoice"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_cmd_Invoice_Click:
Exit Sub


Err_cmd_Invoice_Click:
MsgBox Err.Description
Resume Exit_cmd_AddInvoice_Click


End Sub


Many Thanks


Rachel

 

[Sylvain Lafontaine]

The solution would be to store the value of the primary key before making
the requerying and then use it to reposition the form. In order to have a
better visual effect, it's better to first make a clone of the recordset and
use it to make the search; something like:

Dim rs As ADODB.Recordset
set rs = Me.Recordset.Clone

if (rs.BOF and rs.EOF) then exit sub

rs.MoveFirst
rs.Find .......
if (rs.EOF) then exit sub

Me.Bookmark = rs.Bookmark

You could also use the RecordsetClone method instead of Recordset.Clone;
however, there have been many reports of problem with this method in the
past when used in an ADP project. (The RecordsetClone method will cache the
recordset on the first call and reuses/returns the same recordset for each
subsequent call.).