Cannot get a step through a set of query records working (1 Viewer)

[atrium]

I want to step through a Query file. I keep getting an error "Item not found in collection"
The Query file DdFixPaymentFeeQry has 752 records on it. I had it going on a different version of the project and i wanted to make a few tweaks, now I don't know what I have changed

Private Sub Form_Load()
     '--------------------------------- the loop Setup Starts here  - -------------------------------
     Dim strQryFileSql, strQryFile As String
     Dim strWhereStmnt As String
     Dim dbs As Database
     Dim RecCounter, PreviousRec, FirstRec As Integer
     Dim TaskCompleted As Integer
     Dim rsQuery As DAO.Recordset
     Dim lUserID As Long
     DBEngine.SetOption dbMaxLocksPerFile, 1000000
     On Error GoTo ErrTrap
     TaskCompleted = 0
     strQryFile = "DdFixPaymentFeeQry"
     Me.QryFileFld = strQryFile

     Set dbs = CurrentDb()
     strQryFileSql = "SELECT * FROM " & strQryFile          '
     Set rsQuery = dbs.OpenRecordset(strQryFileSql, dbOpenDynaset)
     Me.CurrentSchedId = 0
     PreviousRec = 0
     FirstRec = 0
     '----------------------------------------------------------------------------------------------
     '----------------- The LOOP starts Here -------------------------------------------------------
     RecCounter = 1
     If (rsQuery.BOF And rsQuery.EOF) Then
        MsgBox "There's no records on the incoming file - DdFixPaymentFeeQry"
        Exit Sub
     End If
     Do While Not rsQuery.EOF
        Me.DdSchedIdFld = rsQuery![DdSchedId]              '--- has the Schedule Id changed ? -----------------
        If Me.DdSchedIdFld <> Me.CurrentSchedId Then
           Me.CurrentSchedId = Me.DdSchedIdFld             '----save the DdSchedId
           Me.PaymentFeeFld = DLookup("PaymentFee", "DdSchedules", "DdSchedId = " & Me.CurrentSchedId)
        End If
        If PreviousRec = 13 And rsQuery![TransCode] = 13 Then
           '---- then create Transaction fee transaction ---------------
           DoCmd.SetWarnings False
           DoCmd.OpenQuery "AddTransPaymentFeeFixQry"              ' create the Transaction fee record for this payment transaction
           DoCmd.SetWarnings True
        End If
        Me.TransCodeFld = rsQuery![TransCode]
        Me.DdSchedIdFld = rsQuery![DdSchedId]
        Me.MatterIdFld = rsQuery![MatterId]
        Me.MatterNoFld = rsQuery![DdSchedules.MatterNo]
        Me.MatterTitleFld = rsQuery![MatterTitle]
        Me.DueDateFld = rsQuery![DueDate]
        Me.DueAmountFld = rsQuery![DueAmount]
        Me.DDAmountFld = rsQuery![DDAmount]
        Me.DaysOfIntFld = rsQuery![DaysOfInt]
        Me.TransStatusFld = rsQuery![TransStatus]
        Me.DdSchedTransIdFld = rsQuery![DdSchedTransId]
        Me.ABADateFld = rsQuery![ABADate]
        Me.TransDateFld = rsQuery![TransDate]
        Me.RefNoFld = rsQuery![RefNo]

        PreviousRec = Me.TransCodeFld
        rsQuery.MoveNext
        
        Me.DaysOfIntFld = 0
        ' ----- Process this transaction ------------------------
JumpToHere:
     Loop
    ' -----------------------------------------------------------------------------------------------------------------------------------
ErrTrap:
                Select Case err.Number
                
                    Case 94, 5941
                        'Loop to the next one
                        GoTo JumpToHere
                    Case Else
                        MsgBox err.Number & " - " & err.Description, vbCritical
                        Resume
                End Select
'------------------------------------------------------------------------------------
rsQuery.Close
Set rsQuery = Nothing
End Sub

-------------------------------------------------------------------------------------------------------------------------------------------------------------

I Would really appreciate some help on this if anyone is available.

 

[arnelgp]

put a Breakpoint on the Load event and run your form.
when it pause, press F8 to step through the code, then you will
see which line got error.

 

[The_Doc_Man]

Does a particular line get highlighted when that error occurs? Or do you have error reporting suppressed in some way? Usually, that error is one that would lead to highlighting the error line, which would greatly help in the diagnosis.

 

[atrium]

Reply to Arnelgp
The error is generated when I execute line Me.DdSchedIdFld = rsQuery![DdSchedId] and if I put my cursor on any of the fields in the collection they show the error
Reply to The Doc_man
As soon as it hits the above line (first interrogation of the any of the fields in the collection) it goes to the on-error routine and displays the error.
If I force it it goes to the next record etc

 

[ebs17]

The Query file DdFixPaymentFeeQry has 752 records on it

I see no point in looping overwriting the form text fields (752 times!). Apart from a possible flickering of the screen, you can't see it.

The only real action is checking if two consecutive records have the value TransCode = 13. With that you can
a) Filter the query immediately before creating the recordset, or
b) (even more intensively) immediately determine the data records with a suitable predecessor/successor in a query and use them direct in the unknown AddTransPaymentFeeFixQry.

Of course, this creates a problem: The code is much shorter and less error-prone.

 

[atrium]

I really want to know what is wrong with the code. I can't get it to populate the first record yet.
I doesn't get past the first Me.DdSchedIdFld = rsQuery![DdSchedId] when it comes up with the error "Item not found in collection"

 

[arnelgp]

check your form in design view if you have a textbox named DdSchedIdFld
or in your query if you have DdSchedID.

 

[Gasman]

You should always save a copy, before you change anything, if you do not know what you are doing. Then you can always start afresh.
The error is plain enough, those fields do not exist in the recordset, so inspect that.
Copy the sql and use that in a qbe sql window.

 

[atrium]

Gasman, I had taken a copy of the whole project to do my testing.

Yes it was the field names in the recordset

Thank you everyone