Loop through records not moving to the next row??

**epb** · 10-08-2012, 11:05 AM

I believe I need to change the index of a table to index on three fields. Those three fields are [POPart], [PONum], and [POLine]. The table is [CustOrdersTable1].

The [POLine] field is not populated for the vast majority of records. I wanted to create VBA code to loop through all records sorted by the three fields above and in the order above.

I did create the code but as I go row by row in the debugger the recordset abosoluteposition is incrementing but the [POPart], [PONum] and [POLine] are not changing their values as the absolute position changes. I am sure there is something simple I am missing.

Maybe there is a better way to do this but I would still like to know why the rst.absolutepostion increments but the column data does not.

Here is the code:

Code:

Private Sub Command271_Click()
On Error GoTo Err_Command271_Click

Dim rst As DAO.Recordset
Dim PONumCount As Long
Dim CurrentPart As String
Dim CurrentPONum As String

Set rst = CurrentDb.OpenRecordset("CustOrdersPKFix qry")

Me.AllowEdits = True
With rst
    If Not (rst.EOF Or rst.BOF) Then
    rst.MoveFirst
    CurrentPart = Me.POPart
    CurrentPONum = Me.PONUMB
    PONumCount = 1
        Do Until rst.EOF
            rst.Edit
            'Check the part number to see if it changed
            'If changed reset the PONumber and set the POline to 01
            If (CurrentPart <> Me.POPart) Then
                CurrentPart = Me.POPart
                CurrentPONum = Me.PONUMB
                PONumCount = 1
                If (Nz(Me.POLine, 0) = 0) Then
                    Me.POLine = PONumCount
                Else
                    PONumCount = Me.POLine
                End If
            Else
                If (CurrentPONum <> Me.PONUMB) Then
                    CurrentPONum = Me.PONUMB
                    PONumCount = 1
                    If (Nz(Me.POLine, 0) = 0) Then
                        Me.POLine = PONumCount
                    Else
                        PONumCount = Me.POLine
                    End If
                Else
                    If (Nz(Me.POLine, 0) = 0) Then
                        Me.POLine = PONumCount
                    Else
                         PONumCount = Me.POLine
                    End If
                    PONumCount = PONumCount + 1
                End If
            End If
            rst.Update
            'Next Record
            rst.MoveNext
        'Next Loop
        Loop
        Me.AllowEdits = False
        rst.Close
    End If
    
End With
Set rst = Nothing

Exit_Command271_Click:
Me.AllowEdits = False
    Exit Sub

Err_Command271_Click:
    MsgBox Err.Description
    Resume Exit_Command271_Click
    
End Sub

**pbaldy** · 10-08-2012, 11:26 AM

You aren't referring to the values from the recordset. One way is:

rst!FieldName

**jzwp11** · 10-08-2012, 11:33 AM

I do not see anywhere in your code where you reference the [POPart], [PONum] or [POLine] from the recordset.

I see where you assign a form control value to a variable like these statements
CurrentPart = Me.POPart
CurrentPONum = Me.PONUMB

I assume that you really meant to reference the recordset value not a form control. The proper syntax is: rst!POPart

oops I see Paul beat me again.