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