ADO .find returning Run-time error '3265'

[bcogan6502]

I have been fighting with this all day and looked at so much documentation and other posts I think I am going blind. I can't find an answer that works.

I am trying to read through a set of records until the reference number changes and then return to the first record with that reference number to do some calculations. I want to continue to loop through the record set until I have touched each record twice and written out each record once.

I am using the ADO .find to return to the first record with the reference number and then bump through again to complete some calculations and write the records to the actual database I need for reporting. I keep receiving error "3265 - Item cannot be found in the collection corresponding to the requested name or ordinal." I've checked the value of my variable when it gets to the statement, and it's right, and I am using the correct field name from my record set select. And I am getting a valid string with the value of the variable. I am at a total loss. Can you help?

Here's my code:

Dim MySQL As String
               
    'Build MySQL string to extract Rep Sales info required.
     MySQL = "SELECT InvoiceLine.SalesRepRefFullName, InvoiceLine.CustomerRefFullName, InvoiceLine.RefNumber, InvoiceLine.BillAddressCity, "
     MySQL = MySQL & "InvoiceLine.BillAddressState, InvoiceLine.PONumber, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineAmount, "
     MySQL = MySQL & "[InvoiceLineAmount]*[CustomFieldInvoiceLineCommissionRate] AS Commission, InvoiceLine.CustomFieldInvoiceLineCommissionRate, "
     MySQL = MySQL & " InvoiceLine.TxnDate, Vendor.VendorAddressAddr1, Vendor.VendorAddressAddr2, Vendor.VendorAddressAddr3, Vendor.VendorAddressCity, "
     MySQL = MySQL & "Vendor.VendorAddressState, Vendor.VendorAddressPostalCode, ""Sales"" AS RecType"
     MySQL = MySQL & " FROM (InvoiceLine INNER JOIN SalesRep ON InvoiceLine.SalesRepRefFullName = SalesRep.Initial) "
     MySQL = MySQL & " INNER JOIN Vendor ON SalesRep.SalesRepEntityRefFullName = Vendor.Name "
     MySQL = MySQL & " WHERE InvoiceLine.SalesRepRefFullName <> ""Hou"" And (InvoiceLine.InvoiceLineItemRefFullName <> ""Freight"" And"
     MySQL = MySQL & " InvoiceLine.InvoiceLineItemRefFullName <> ""Shipping"") And InvoiceLine.InvoiceLineAmount <> 0 And "
     MySQL = MySQL & "(InvoiceLine.TxnDate > DateSerial(Year(Date()), Month(Date()) - 1, 1) Or InvoiceLine.TxnDate < DateSerial(Year(Date()), Month(Date()), 0)) "
     MySQL = MySQL & " ORDER BY InvoiceLine.SalesRepRefFullName, InvoiceLine.CustomerRefFullName, InvoiceLine.RefNumber"

    
    'Create Record Set of Rep Sales to process
    'Set up connection, name it cnn1
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    
    Dim MyRecordSet As New ADODB.Recordset
    MyRecordSet.ActiveConnection = cnn1
    
    MyRecordSet.Open MySQL, cnn1, adOpenKeyset, adLockOptimistic
    MyRecordSet.MoveFirst
    
    'Delete Old and Create New CommissionsTable
    
    DoCmd.DeleteObject acTable, "CommissionsTable"
    
    MySQL = "CREATE TABLE CommissionsTable ([SalesRepFullName] text (3), [CustomerRefFullName] text (25), [RefNumber] long, [BillAddressCity] text (25),"
    MySQL = MySQL & " [BillAddressState] text (2), [PO Number] text (10), [Amount] currency, [Commission] currency, [TxnDate] date,"
    MySQL = MySQL & " [VendorAddress1] text (25) ,[VendorAddress2] text (25), [VendorAddress3] text (25), [VendorAddressCity] text (25),"
    MySQL = MySQL & " [VendorAddressState] text (2), [VendorAddressPostalCode] text (9), [RecType] text (10))"
    DoCmd.RunSQL MySQL
    
    'Determine Discount
    
    Dim DiscountPercent As Long
    Dim AdjustedTotal As Currency
    Dim AdjustedCommission As Currency
    Dim CurrentReference As String
    Dim ItemRef As String
    Dim TrimmedItemRef As String
    Dim FindRecord As String
    Do While Not MyRecordSet.EOF
        'Determine Discount Percentage
        CurrentReference = MyRecordSet.Fields(2).Value
        AdjustedTotal = 0
        AdjustedCommission = 0
        DiscountPercent = 1
        Do While MyRecordSet.Fields(2).Value = CurrentReference And Not MyRecordSet.EOF
            ItemRef = MyRecordSet.Fields(6).Value
            TrimmedItemRef = Trim(ItemRef)
            Select Case TrimmedItemRef
                Case "Discount 5%"
                    DiscountPercent = 0.95
                Case "Discount 10%"
                    DiscountPercent = 0.9
                Case "Discount 15%"
                    DiscountPercent = 0.85
                Case "Discount 20%"
                    DiscountPercent = 0.8
                Case "Discount 50%"
                    DiscountPercent = 0.5
                Case "Discount"
                    DiscountPercent = 0
                Case "Samples"
                    DiscountPercent = 0
            End Select
            MyRecordSet.MoveNext
        Loop
        
        'Write Commision Record
        
        MyRecordSet.Find "InvoiceLine.Refnum = '" & CurrentReference & "'", , adSearchForward, 1

[BitBlt]

I think your last line should be more like:

MyRecordSet.Find "InvoiceLine.Refnumber = '" & CurrentReference & "'", , adSearchForward, 1

[bcogan6502]

I think your last line should be more like:

MyRecordSet.Find "InvoiceLine.Refnumber = '" & CurrentReference & "'", , adSearchForward, 1

You're right. But that wasn't the problem. I fixed that error and it didn't solve the problem. I have decided to try another route using nested Do While loops. We'll see how that goes. Right now I am running into an EOF file condition that I can't seem to get past.

[BitBlt]

It seems to me that in order to correctly use the .Find on the last line as you tried above, you would need to do a MyRecordSet.MoveFirst just prior to that, then issue the .Find command. Otherwise, you'd start from the current position and go to the end of the recordset.

Just a thought.