I have a form that I am using to up 1

[weltergon]

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

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. I am new to vba and I cannot find an answer to this anywhere.
Thanks in advance.

 

[tunsarod]

Using the Form's Bookmark property could provide a way to get back to a previous record:

E.G.

Compare Database
Option Explicit
Dim myBookmark As String


Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String
Dim var As String

myBookmark = Me.Bookmark

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

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

Me.Refresh

Me.Bookmark = myBookmark

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

But the Requery destroys any bookmarks when it rebuilds the Recordset.

If your table records each have a unique ID field, an Autonumber field for example, then use the Docmd.FindFirst method. E.G.:

Option Compare Database
Option Explicit
Dim recFlag As String


Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String
Dim var As String

recFlag = [ID_field]

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

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

Me.Requery

DoCmd.FindFirst "[ID_field] = '" & recFlag & "'"

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

 

[weltergon]

I am using access 2000, and I receive and error in the line that has docmd.findfirst... The error is that the data member cannot be found.
Is this something to do with access 2000?
Thanks in advance

 

[tunsarod]

If you have a form based on a table and use the wizard to place a combo-box that finds a record based on a selection from it's list you will find the resulting AfterUpdate event code for the combo looks something like this:

Private Sub Combo1_AfterUpdate()
' Find the record that matches the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[keyField] = '" & Me![Combo1] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Rod

This different than the equivalent code in Access97 which I suggested before.

Based on the above try the following code in your form's module:

Option Compare Database
Option Explicit
Dim recFlag As String


Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String
Dim var As String
Dim rs As Object

recFlag = [ID_field]

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

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

Me.Requery

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID_field] = '" & recFlag & "'"
Me.Bookmark = rs.Bookmark
Set rs = nothing

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub

Rod