Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Thumbs up Unanswered: MoveNext...Please!

    All I want to do is move through records in a table and display the next row of data on a form each time the command button is pressed. When the form loads, there is data in the fields on the form from the ProbQueue table (first row of data). When I press the command button, the following code will move only once to the next record and display the next row of data. If I press the command button again, it will not display the third row of data. Nothing happens. What am I missing to get the subsequent rows of data to display when the command button is pressed? Currently, there are 81 records in the table. Thanks for your help.

    Dim db As Database, RS As Recordset

    Set db = CurrentDb()
    Set RS = db.OpenRecordset("ProbQueue", dbOpenDynaset)

    If RS.RecordCount = 0 Then
    MsgBox "There are no records to update.", vbExclamation, "Queue is Empty"
    GoTo Exit_cmdNext_Click:
    End If

    If RS.EOF = True Then
    MsgBox "You are at the end of the recordset.", vbExclamation, "End of Recordset"
    GoTo Exit_cmdNext_Click:
    End If

    RS.MoveNext

    txtProbID.Value = RS!ID
    txtDate.Value = RS!Date
    txtEmpNum.Value = RS!ENum
    txtEmployee.Value = DLookup("FullName", "tbl_Employees", "EmployeeID = txtEmpNum.Value")
    txtJobCode.Value = RS!Jcode
    txtProblem.Value = RS!Problem
    txtDescription.Value = RS!Description
    txtResolution.Value = RS!Resolution

    RS.Close

  2. #2
    Join Date
    Apr 2006
    Posts
    157
    Didn't you just close the recordset in the command button's click event?
    Only quitters quit!

  3. #3
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    close the recordset at the moment you close the form. Here each time you click you reopen the recordset and the pointer is directing to the first record.

  4. #4
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    Is there a way of displaying the data from the table onto the form without opening the recordset each time the command button is pressed? Also, when I removed RS.Close, the information displayed still starts at the first record, not the last record displayed. I modified the code, but movenext still does not work like it does when the form is bound to the table. Here is the modified code:

    Private Sub cmdNext_Click()
    On Error GoTo Err_cmdNext_Click

    Dim db As Database, RS As Recordset, Msg1 As String
    Dim Msg2 As String

    Set db = CurrentDb()
    Set RS = db.OpenRecordset("ProbQueue", dbOpenDynaset)

    If RS.RecordCount = 0 Then
    MsgBox "There are no records to update.", vbExclamation, "Queue is Empty"
    GoTo Exit_cmdNext_Click:
    End If

    If RS.EOF = True Then
    MsgBox "No more records.", vbExclamation, "End of Recordset"
    GoTo Exit_cmdNext_Click:
    End If

    Do

    RS.MoveNext

    Msg1 = MsgBox("Do you wish to process this record", vbYesNoCancel, "Problem Queue")
    If Msg1 = vbYes Or Msg1 = vbCancel Then
    GoTo Exit_cmdNext_Click:
    Else
    If RS.EOF = True Then
    MsgBox "No more records.", vbExclamation, "End of Recordset"
    GoTo Exit_cmdNext_Click:
    End If
    txtProbID.Value = RS!ID
    txtDate.Value = RS!Date
    txtEmpNum.Value = RS!ENum
    txtEmployee.Value = DLookup("FullName", "tbl_Employees", "EmployeeID = txtEmpNum.Value")
    txtJobCode.Value = RS!Jcode
    txtProblem.Value = RS!Problem
    txtDescription.Value = RS!Description
    txtResolution.Value = RS!Resolution
    txtRecordCount.Value = RS.AbsolutePosition
    End If

    Loop While Msg1 = vbNo

    Exit_cmdNext_Click:
    Exit Sub
    Err_cmdNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdNext_Click

    End Sub

    If I click YES to wanting to process the record, and then process the record, when I press the next command button it starts from the first record in the table, not the last record processed. How can I make the next record appear without having to start from the beginning of the recordset?

  5. #5
    Join Date
    Apr 2006
    Posts
    157
    you may declare your recordset and database variables globally and set the respective objects on your forms onload event
    check the eof and recordcounts in the oncurrent event and finally
    you only have RS.movenext is left in the command's click event
    Only quitters quit!

Posting Permissions

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