Unanswered: Losing the Recordset after click event (navigation buttons)
I would like the user to be able to control the Recordset Row movement, by clicking the left/right arrow navigation buttons. When the user Click a navigation arrows, I am trying to pass the recordset to a called procedure. I have tried the following, and am getting compile errors: byref argument type mismatch
What am I doing wrong? Your help is appreciated.
Private Sub NextRecbtn_Click()
Public Sub Get_NextRec(ByRef RS1 As ADODB.Recordset)
If .BOF Or .EOF Then
MsgBox "This is the last RECORD that meets the selection criteria"
1. I would not pass a Recordset by reference: it's passing a pointer to a pointer (the address of an address).
2. What's RS1 precisely ? Is it instanciated from the ADODB library? One common error consists in mixing ADO and DAO objects in code.
I think I understand your goal. When I setup buttons/labels for my record navigation, I do all the work under the form current procedure.
The movement procedure can simply be taken from the Next Record selection in the button wizard.
Dim intRecCount As Integer 'count of total RMA records
Dim strNumber As String 'RMA number in text
'Setup and refresh a recordset
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not (rs.BOF And rs.EOF) Then
intRecCount = rs.RecordCount 'Get the total record count
intRecCount = 1
'Control the navigation label
If Not IsNull(Me.IDrma) Then
strNumber = Me.txtRMA.Value 'Get the RMA number in text
Me.lblRMARecCount.Visible = True
Me.lblRMARecCount.Caption = "RMA" & vbNewLine & Me.CurrentRecord & _
" / " & intRecCount & vbNewLine & strNumber
Me.lblRMARecCount.Visible = False
Me.lblRMARecCount.Caption = ""
'Control availability of navagation buttons
If ((Me.CurrentRecord - 1) = 0) Then 'If at first record
Me.btnPreviousRMA.Enabled = False 'disable the prev. button
Me.btnPreviousRMA.Enabled = True 'else enable it
If (Me.CurrentRecord = intRecCount) Then 'If at last record
Me.btnNextRMA.Enabled = False 'disable the next button
Me.btnNextRMA.Enabled = True 'else enable it
Set rs = Nothing
This updates a label as the user navigates through the form. It shows, in generic terms, "Record x of y RecordNumber". It also disables the next and previous navigation buttons as you go so that the user can't navigate beyond the set of records.