Scrolling Through a Record Set

[DavidWE]

I have a form where a user scans barcodes. After each scan a record set is filled and then written to a table called PayJournal. The data that will be written to that table is displayed in textboxes on the form.

I would like to know if there is a way for the user to scroll backwards and forwards through the recordset and have it displayed in the textboxes before the data is actually saved to the table.

I have the following code:

Option Compare Database
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Public Sub Form_Load()
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   With rst
        'Specify the recordset to open with a client side cursor
        .CursorLocation = adUseClient
        'Open the recordset without any records
        .Open "SELECT * FROM PayJournal WHERE 1=0", cnn, adOpenStatic, adLockBatchOptimistic
        'Disconnect it
        Set .ActiveConnection = Nothing
    End With
End Sub

After each scan the following sub is run to load the values into the recordset. The data does not get saved at this point.

Public Sub txtBar_Change()
 'filling of the recordset here
          With rst
              .AddNew
              .Fields("EmployeeID") = txtEmployeeID.Value
              .Fields("FirstName") = txtFName.Value
              .Fields("LastName") = txtLName.Value
              .Fields("Operation") = txtOperation.Value
              .Fields("Process") = txtProcess.Value
              .Fields("Degree") = txtDegree.Value
              .Fields("Fabric") = txtFabric.Value
              .Fields("Location") = txtLocation.Value
              .Fields("Degree") = txtDegree.Value
              .Fields("ProcessRate") = CDbl(txtPRate.Value)
              '.Fields("ProcessDate") = Date
              .Fields("ProcessDate") = txtProcessDate
              .Fields("ProcessQty") = txtQty.Value
              .Fields("ProcessPay") = CDbl(txtPPay.Value)
              .Fields("CutNo") = CutNo
              .Fields("PRStatus") = PRStatus
              .Update
          End With
End Sub

The following updates the tables after the user clicks a command button and responds yes to a question to save. A routine is called to query the table again for the next batch of scans. If I am able to scroll back and forward through the record set, then I think it would have to be before the following sub is run.

Public Sub AddLog()
    With rst
        'Reconnect
        Set .ActiveConnection = cnn
        'Write back to the table
        .UpdateBatch
        .Close
    End With
    Set rst = Nothing
    Set cnn = Nothing
    LogCount = 0
    Call Connection
End Sub

Public Sub Connection()
Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   With rst
        'Specify the recordset to open with a client side cursor
        .CursorLocation = adUseClient
        'Open the recordset without any records
        .Open "SELECT * FROM PayJournal WHERE 1=0", cnn, adOpenStatic, adLockBatchOptimistic
        'Disconnect it
        Set .ActiveConnection = Nothing
    End With
End Sub

Can it be done with the way I am creating and saving the recordset, or do I need to approach it another way and start from scratch?

I might have to consider using a separate form to allow the user to view the records just scanned.

All of the above code does work. I hope I posted all of the relevant code.

Thanks for any help or advice.

 

[smith9891]

Not sure if this helps but could you paste the info into a duplicate table and show this table on a tab page or another form and then do an append into your master table? (Remembering to delete the temp data)

 

[DavidWE]

Thanks, smith9891. I probably will try something similar to your suggestion.