need help with bookmarks or similar

[weltergon]

I have a form that I am using to update a table with information. Here is the code behind my update button:

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String
Dim var As String

stDocName = "qryUpdateManager_billing_fact"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "qryUpdateManager_project"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.Requery

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

I need to do a requery to refresh my form but it keeps taking me back to the first record and I need it to remember which record I am on after the requery and to take me there so I can continue to update records. Can anyone tell me how do do this? I am using access 2000 if it matters.
Thanks in advance.

 

[SBendBuckeye]

Before you refresh the queries, save the key of the current record being processed. After you have done the requery, use the recordsetclone to find the saved key and then resync the bookmarks.

The above is a general description. If you need help on how to do that, please post again and I'll try to post some working code for you.

Good Luck!

 

[weltergon]

Thank you for replying, if its possible can I see an example. Thank you for your help.

 

[SBendBuckeye]

Note new code added to your sub as defined above. If your primary key is not numeric then you will need to tweak the following code. Also, make sure you have a reference to DAO or the following code won't work. When you are in the code area, click on Tools References and look for a Microsoft DAO
library. If one is selected, your are fine. If not, find it and check the newest one (on my system it is 3.6)

Good Luck!

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String
Dim var As String
Dim intSaveKey As Integer '<== New Code
Dim recClone As DAO.Recordset '<== New Code
Dim strTemp As String '<== New Code

intSaveKey = form primary key '<== New Code

stDocName = &quot;qryUpdateManager_billing_fact&quot;
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = &quot;qryUpdateManager_project&quot;
DoCmd.OpenQuery stDocName, acNormal, acEdit

Me.Requery

'Example &quot;CustomerID = &quot; '<== New Code
strTemp = &quot;tblPrimaryKeyName = &quot; '<== New Code
Set recClone = Me.RecordsetClone '<== New Code
recClone.FindFirst strTemp & intSaveKey '<== New Code
Me.Bookmark = recClone.Bookmark '<== New Code
recClone.Close '<== New Code
Set recClone = Nothing '<== New Code

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

 

[weltergon]

I tried this and its not working. I was receiving an error on the line that sets up the DAO connection so I commented it out and it ran without error but its still going to the first record. also this form is running off of a query and I am not sure which key should be the primary key, does it matter if there are 2 keys in the query?
Thanks in advance

 

[weltergon]

I only have update code, does anyone know how within this code I can save my place in the recordset, do a requery, and go back to the same record. The form is fed from a query.
Thanks in advance

 

[SBendBuckeye]

I had a chance to play around with this a little bit this afternoon. I ran a form off of a query and did 2 update queries based on a command button click.

The thing that is resetting you to record 1 is the me.requery. Do you need to requery your main form query? If you comment out that line, it will stay on the current record for you.

If you need to requery, let me know and maybe we can get it to work for you. By the way, what version of Access are you using?

Good Luck!

 

[weltergon]

Hi,
I am using Access 2000 I believe it looks like it is version 9.0. I actually do need the requery so I can see the refreshed data. Is there anyway around this?
thanks fo your help.

 

[SBendBuckeye]

My weekend plans just got cancelled so maybe I'll have some time to play around some more.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!