VBA code to loop through all records in a table and do update query

[donbolinger]

Fairly new to VBA coding. I have a table called tblManualInput that contains up to 300 customer addresses with a unique customer # for each (field name is CustNum).

I have an update query (qryUpdateTotals) I want to run for each CustNum in the ManualInput table once a new spreadsheet of invoice data is imported.

Thanks in advance!

 

[Remou]

What is the sql for qryUpdateTotals?

Are you sure you need to store a calculated field? It violates the rules for normal tables to do so.

http://lessthandot.com

 

[donbolinger]

It actually does a series of create table queries to calculate a running points total based on gross profit. Right now, it launches the macro to run the queries based on a drop down selection of that customer number on a form. I don't have access to the code from this computer.

 

[Remou]

Dim rs As DAO.Recordset
Dim db As Database
Dim iCustNum As Integer

Set db=CurrentDB
Set rs=db.Openrecordset("tblManualInput")


Do While Not rs.EOF
   iCustNum=rs!CustNum 
   'Do something here
   rs!MoveNext
Loop

Without the SQL, it is difficult to tell what 'Do something here' should be.

http://lessthandot.com

 

[donbolinger]

Thanks Remou - I've modified your code to the following (had to change some of the field names from my original post):

Private Sub Command32_Click()
Dim rs As DAO.Recordset
Dim db As Database
Dim iCustNumID As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("ManualInput")

Do While Not rs.EOF
   iCustNumID = rs!CustNumID
   AppendTablesMacro 'Call Function "Converted Macro- AppendTablesMacro"
   rs.MoveNext
Loop
End Sub

Here is where it gets confusing. The queries are referring to the customer number (CustomerMainAccount#) that was manually selected from the drop down list on the form (ReportSelectorForm). How do I tell the new Loop to give the queries each customer number?

Here is the first query that runs:

SELECT Qcntr([ID]) AS AutoNum, ManualInput.[CustomerMainAccount#], RawData.INVDATE, RawData.OLDINVOICES, RawData.[INV-AMT] AS [Invoice Amount], RawData!GP/RawData![INV-AMT] AS GP, ManualInput!PriorYearSales*ManualInput!Multiplier AS Threshold INTO DateRangeInvoices
FROM RawData INNER JOIN ManualInput ON RawData.[Main#] = ManualInput.[CustomerMainAccount#]
WHERE (((ManualInput.[CustomerMainAccount#])=[Forms]![ReportSelectorForm]![CustNum]) AND ((RawData.INVDATE) Between [Forms]![ReportSelectorForm]![BeginDate] And [Forms]![ReportSelectorForm]![EndDate]))
ORDER BY RawData.INVDATE, RawData.OLDINVOICES;

Thank you!

 

[Remou]

You need something on the lines of:

Private Sub Command32_Click()
Dim rs As DAO.Recordset
Dim db As Database
Dim iCustNumID As Integer
Dim frm As Form
Set frm = Forms!ReportSelectorForm ' Or Me, if it is the form module.

Set db = CurrentDb
Set rs = db.OpenRecordset("ManualInput")

Do While Not rs.EOF

   strSQL= "SELECT Qcntr([ID]) AS AutoNum, m.[CustomerMainAccount#], r.INVDATE, " _ 
    & "r.OLDINVOICES, r.[INV-AMT] AS [Invoice Amount], r.GP/r.[INV-AMT] AS GP, " _ 
    & "m.PriorYearSales*m.Multiplier AS Threshold INTO DateRangeInvoices " _ 
    & "FROM RawData r INNER JOIN ManualInput m ON r.[Main#] = m.[CustomerMainAccount#] " _ 
    & "WHERE m.[CustomerMainAccount#]=" & frm.[CustNum] _ 
    & " AND ((r.INVDATE) Between #" & Format(frm.[BeginDate], "yyyy/mm/dd") _
    & "# And #" & Format(frm.[EndDate], "yyyy/mm/dd")  

   db.Execute strSQL, dbFailOnError
   rs.MoveNext
Loop
End Sub

But if you have to do this for a lot of people, it would be best just to have a table of customer ids to update and do the lot at once using IN.

http://lessthandot.com

 

[donbolinger]

Starting to get over my head, now....

In my first query (in Design View), isn't there something I can put in the Criteria to reference back to the iCustNumID that the loop is on?
Something like the SQL equivilent of this (which doesn't work):

((ManualInput.CustNumID)=[rs]![iCustNumID]))

 

[donbolinger]

Found another solution. Added a CustNumID field on the ReportSelectorForm then had the code populate that field from the loop. Now I can reference that field on the form as my query criteria. Here is the updated code:

Private Sub Command32_Click()
Dim rs As DAO.Recordset
Dim db As Database
Dim iCustNumID As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Select CustNumID From ManualInput")

Do While Not rs.EOF
   Me.txtCustNumID = rs!CustNumID
   AppendTablesMacro 'Call Function "Converted Macro- AppendTablesMacro"
   rs.MoveNext
Loop

rs.Close
db.Close

End Sub

Thanks for your time!
I'm happy now...