Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010

    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()

    Call Get_NextRec(RS1)

    End Sub

    Public Sub Get_NextRec(ByRef RS1 As ADODB.Recordset)

    With RS1
    If .BOF Or .EOF Then
    MsgBox "This is the last RECORD that meets the selection criteria"
    Call Move_Record_To_Form(RS1)
    End If
    End With

    End Sub

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    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.
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    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
        End If
        '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 = ""
        End If
        '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
        End If
        If (Me.CurrentRecord = intRecCount) Then 'If at last record
            Me.btnNextRMA.Enabled = False       'disable the next button
            Me.btnNextRMA.Enabled = True        'else enable it
        End If
            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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts