Return To Previous Current Record After Requery

[Leonthefixer]

Hi,

First post here, hopefully someone can help me with a solution as I can't find one anywhere.

I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.

I have the following code:

Private Sub cmdSaveTradeAndExit_Click()

DoCmd.RunCommand acCmdSaveRecord 'Save the current record
DoCmd.Close 'Close the current form

Dim CrId As Integer

CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord
Forms!frmTransactionMainActivePopUp.Requery
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId

End Sub

But I am getting the following error:

Run-time error '2498':

An expression you entered is the wrong data type for one of the arguments

And the following is in yellow in the debug:

DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId

Can anyone help?

Many thanks!

 

[ted.martin]

Here is a better way:

Dim strBookmark as String

Private Sub Form_Load()

strBookmark = ""   ' Reset this variable to Empty - precautionary

End Sub

Private Sub Form_Current()

If strBookmark <> "" then

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "YourControl = '" & strBookmark & "'"
Me.Bookmark = rs.Bookmark
YourControl.SetFocus
strBookmark = ""
Set rs = Nothing

End If

End Sub


Private Sub YourControl_DblClick(Cancel as Integer)

strBookmark = YourControl.Value    ' You need some kind of event to set the Bookmark value.  Double-Click is the usual one.

'Place your Action to open PopUp form here


End Sub

This code was used to Return the Focus to the same record that initiated the opening of another form, when this 2nd form is closed.

 

[Leonthefixer]

Hi,

Many thanks for taking the time to reply to my post. I am a bit new to VBA so it will take me some time to get my head around what you have posted but I will come back with any questions in the next day or so.

I know you said your way was better, but out of interest would it be possible to do it my way by correcting the line of code that is causing the error?

Many thanks again!

 

[ted.martin]

I don't think your code will work. The code I provided is straightforward and you will take a big step forward in your access programming if you can work out what it is doing. Good luck.

 

[adfeds]

Out of curiosity, I use the bookmark code in my database and it works well, but I was hoping to have multiple bookmarks. Two really, one for my main form and a second on another form, but they override each other. I tried to create multiple variables to store the bookmark, but didn't know enough about the recordset, i believe, to make it work. Any suggestions?