My database uses unbound forms exclusively. A listbox on the main form is bound to a query via the RowSource property. When you doubleclick the listbox a new form opens and the selected row is displayed in the new form which allows you to edit the table via the OpenRecordset method. The requirement now is to be able to use navigation buttons to scroll backwards and forwards through the table records in this form.
Please see the code below. If I give the recordset module level scope by placing the declaration at the top of the form module, then I can scroll backwards and forwards through the recordset from a button that runs code in a different procedure i.e. cmdPrevious_Click(). Only I want to be able to scroll backwards and forwards from the record that is opened via the OpenArgs parameter and at the moment, the backwards navigation button for example, takes me to the BOF. As you can see, I have tried using bookmarks but these are not working and I get error 3159 - not a valid bookmark. I would appreciate some help as to how I can do this.
Many thanks.
Paul
Code:
Option Compare Database
Option Explicit
Dim mlngUserID As Long, mstrBookmark As String
Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim qdf2 As DAO.QueryDef, rst2 As DAO.Recordset
Dim strSQL As String, strSQLadd As String
If Not IsNull(Me.OpenArgs) Then
Set db = CurrentDb
mlngUserID = Me.OpenArgs
Set qdf = db.QueryDefs("qryUsers")
Set rst = qdf.OpenRecordset 'open recordset for later (ie. move previous, move next etc)
strSQL = qdf.SQL
strSQLadd = "WHERE [tblUsers].[User ID] = " & mlngUserID & " ORDER BY tblUsers.Surname"
strSQL = Replace(strSQL, "ORDER BY tblUsers.Surname;", strSQLadd)
Set qdf2 = db.CreateQueryDef("", strSQL)
Set rst2 = qdf2.OpenRecordset()
mstrBookmark = rst2.Bookmark
With rst2
Me.txtForename = !Forename
Me.txtSurname = !Surname
Me.txtUsername = !Username
Me.txtEmail = !Email
Me.txtTelephoneExt = ![Tel Ext]
Me.cboDivision_PSG = !Division_PSG
Me.txtSection = !Section
Me.cboWorkspace = !Workspace
Me.cboReportUser = ![Report User]
Me.chkReportOwner = ![Report Owner]
Me.chkLaunchPadAccount = ![Launch Pad Account]
Me.txtNotes = !Notes
End With
End If
Exit_Form_Open:
rst2.Close
Set rst2 = Nothing
qdf2.Close
Set qdf2 = Nothing
Set db = Nothing
Exit Sub
Err_Form_Open:
Call MsgBox(Err.Description, vbExclamation, "Error #: " & Err.Number)
Resume Exit_Form_Open
End Sub
Private Sub cmdPrevious_Click()
On Error GoTo Err_cmdPrevious_Click
rst.Bookmark = mstrBookmark
rst.MovePrevious
If rst.BOF Then
MsgBox "at beginning"
rst.MoveFirst
End If
With rst
Me.txtForename = !Forename
Me.txtSurname = !Surname
Me.txtUsername = !Username
Me.txtEmail = !Email
Me.txtTelephoneExt = ![Tel Ext]
Me.cboDivision_PSG = !Division_PSG
Me.txtSection = !Section
Me.cboWorkspace = !Workspace
Me.cboReportUser = ![Report User]
Me.chkReportOwner = ![Report Owner]
Me.chkLaunchPadAccount = ![Launch Pad Account]
Me.txtNotes = !Notes
End With
Exit_cmdPrevious_Click:
Exit Sub
Err_cmdPrevious_Click:
Select Case Err.Number
Case Else
Call MsgBox(Err.Description, vbExclamation, "Error #: " & Err.Number)
Resume Exit_cmdPrevious_Click
End Select
End Sub