Looping code based on values in query (1 Viewer)

[jimbot]

Could anyone help with a looping problem I'm stuck with:

I have a report which runs based on a value selected from a combo box (department). The combo box is built dynamically from a query - due to the nature of the data i'm analysing i haven't got the various departments stored in a table of their own (although if it would help i could create an extra table containing this info).

I have written some code to export the report data to an excel file and a .snp file, which are both named using the department selected in the combo box as a variable.

What I would ideally like to do is automate this in vba using a loop, so if " * " is selected as the input parameter, the report will loop through all the department values in the combo box's underlying query and produce a separate output for each one. I have made the wildcard available in the underlying query using a union.

Many thanks in advance for any advice offered, I hope my question makes sense

 

[pdx_man]

When the asterisk is selected, then you need to populate a recordset containing the values in the combox box (minus the *). Then use a Do loop. Psuedo code:

If ComboBoxValue= "*" Then
SET rst = openRecordset("Your ComboBox Query")
Do While NOT rst.EOF
Call DoTheExport(rst!Dept)
rst.movenext
Loop
Else
Call DoTheExport(ComboBoxValue)
End If

 

[jimbot]

thanks for your help pdx man - this has worked perfectly.

for the benefit of any others who may be looking for code to do similar, i got this working by tweaking the code as follows:

Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb
Dim Dept As String

Dept = [Forms]![frmChoosePeriod]![CmbChooseDept]


If Dept = "*" Then
Set rst = dbs.OpenRecordset("select distinct EMP_DEPT from qry_dept_only")
Do While Not rst.EOF
[Forms]![frmChoosePeriod]![cmbDept].Value = rst!EMP_DEPT
Call RunExportForAll
[Forms]![frmChoosePeriod]![cmbDept].Value = ""
rst.MoveNext
Loop
MsgBox "Files have been created in W:\File Transfer\Transaction Reporting\Output\"
Else
'MsgBox "ok"
Call RunExportForDept
End If

 

[rolaaus]

Jazz up the report export a bit

Jimbot,

You could change your msgbox code to open up "My Computer" to the export location for the end-user, or even add a Yes/No option for the msgbox to see if the user wants the directory opened for them...

Shell PathName:="C:\WINDOWS\explorer.exe """ & "W:\File Transfer\Transaction Reporting\Output\", WindowStyle:=vbNormalFocus