run macro once for each row in a table

[blutimus]

hello

i currently use a form with a dropdown list to choose a customer, then hit 'go' on the form to run a macro which opens a make table query then runs a module to export it to excel. the customer comes from a table.

i would like to figure out how to run the macro for each customer in the table using a loop, instead of one at a time as i do now.

can this be done without modifying my module, macro and form... so the current method is still an option?

please be explicit, i am a novice at this.\

thanks

blute

 

[denosaur]

blute,

I would suggest modifying your module...
Add a sub that iterates through your customer table and as it iterates through each record, have it call your other sub that makes the table and exports it to Excel.
I'll assume that your sub that makes the table and exports it has an argument, strCustomerID.

Sub MySub()

Dim rsCustomers As Recordset
Dim strSQL As String
Dim strCustomerID As String

strSQL = "SELECT * FROM TblCustomers ORDER BY CustomerID"
Set rsCustomers = CurrentDb.OpenRecordset(strSQL)

If rsCustomers.BOF And rsCustomers.EOF Then
    MsgBox "No records
Else
    Do While Not rsCustomers.EOF
        strCustomerID = rsCustomers!CustomerID
        MakeTableandExportItSub(strCustomerID)
        rsCustomers.MoveNext
    Loop
End If

Set rs = Nothing
End Sub

I hope this helps you out.

denosaur

 

[blutimus]

thanks for the reply... actually i use the strCustID as a parameter in the make table query. sorry i wasn't clear before.

blute

 

[cghoga]

Use a for each loop

'''''''''
Dim var As Variant
Dim val as String

For Each var In Me.NameofListBoxonYourForm.ItemsSelected

'set the value and hold it in a variable called val
val = Me.NameofListBoxonYourForm.ItemData(var)

'run your code
DoCmd.RunMacro.......

Next var
'''''''''

Hope this helps.