Loop through values in ComboBox, set Query Criteria, Export to Excel (1 Viewer)

Sinfathisar

Registered User.
Local time
Today, 16:19
Joined
Jan 29, 2009
Messages
60
I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:

Code:
Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean

Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox

[COLOR=seagreen]' Return Control object variable pointing to list box.
[/COLOR]Set cboCode = Forms!Chart_Export_Static!cboTeam

[COLOR=seagreen]' Enumerate through all items.
[/COLOR]For intCounter = 0 To cboCode.ListCount - 1

[COLOR=seagreen]'sql for static test charts export[/COLOR]
strSQLStatic = "SELECT Static_Repeatability_Test_Table.Static_Repeatability_ID, Static_Repeatability_Test_Table.Collection_Date, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item, Static_Repeatability_Test_Table.Static_Response_CH1, Static_Repeatability_Test_Table.Static_Response_CH2, Static_Repeatability_Test_Table.Static_Response_CH3, Static_Repeatability_Test_Table.Static_Response_CH4, Seed_Test_Item_Table.Response_Value_CH1, Seed_Test_Item_Table.Response_Value_CH2, Seed_Test_Item_Table.Response_Value_CH3, Seed_Test_Item_Table.Response_Value_CH4, Seed_Test_Item_Table.Static_Test_Item_Height " & _
"FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
"WHERE (((Static_Repeatability_Test_Table.Collection_Date) Between Int([Forms]![Chart_Export_Static]![StartDate]) And (Int([Forms]![Chart_Export_Static]![EndDate])+0.99))) AND (((Static_Repeatability_Test_Table.Team_ID)=[Forms]![Chart_Export_Static]![cboTeam])) " & _
"ORDER BY Static_Repeatability_Test_Table.Collection_Date DESC, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item;"
 
[COLOR=seagreen]'Create the query using SQL defined above
[/COLOR]Set qdf = CurrentDb.CreateQueryDef("Static", strSQLStatic)

[COLOR=seagreen]'Create the query using SQL defined above[/COLOR]
If Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(0) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\Static\" & [Forms]![Chart_Export_Static]![cboTeam] & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(1) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\StaticCurve\" & [Forms]![Chart_Export_Static]![cboTeam] & "_StaticCurve.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(2) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\Static\" & [Forms]![Chart_Export_Static]![cboTeam] & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
'Get the workbook name
BookName2 = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\StaticCurve\" & [Forms]![Chart_Export_Static]![cboTeam] & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName2, True
End If

qdf.Close
Set qdf = Nothing
DoCmd.DeleteObject acQuery, "Static"
Next

End Function
I'm not sure if I am just missing something, or if I am going the wrong direction with this loop. Any tips, or advice on what else to search for to learn about doing loops would be appreciated.

Thanks!
 

Sinfathisar

Registered User.
Local time
Today, 16:19
Joined
Jan 29, 2009
Messages
60
Ok! I guess I was headed in a good direction. Here is the code that works as needed.


Code:
 Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean

Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox

[COLOR=seagreen]' Return Control object variable pointing to list box.
[/COLOR]Set cboCode = Forms!Chart_Export_Static!cboTeam

[COLOR=seagreen]' Enumerate through all items.
[/COLOR]For intCounter = 0 To cboCode.ListCount - 1

[COLOR=seagreen]'sql for static test charts export[/COLOR]
strSQLStatic = "SELECT Static_Repeatability_Test_Table.Static_Repeatability_ID, Static_Repeatability_Test_Table.Collection_Date, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item, Static_Repeatability_Test_Table.Static_Response_CH1, Static_Repeatability_Test_Table.Static_Response_CH2, Static_Repeatability_Test_Table.Static_Response_CH3, Static_Repeatability_Test_Table.Static_Response_CH4, Seed_Test_Item_Table.Response_Value_CH1, Seed_Test_Item_Table.Response_Value_CH2, Seed_Test_Item_Table.Response_Value_CH3, Seed_Test_Item_Table.Response_Value_CH4, Seed_Test_Item_Table.Static_Test_Item_Height " & _
"FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
"WHERE (((Static_Repeatability_Test_Table.Collection_Date) Between Int([Forms]![Chart_Export_Static]![StartDate]) And (Int([Forms]![Chart_Export_Static]![EndDate])+0.99))) AND (((Static_Repeatability_Test_Table.Team_ID)='" & cboCode.ItemData(intCounter) & "')) " & _
"ORDER BY Static_Repeatability_Test_Table.Collection_Date DESC, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item;"
 
[COLOR=seagreen]'Create the query using SQL defined above
[/COLOR]Set qdf = CurrentDb.CreateQueryDef("Static", strSQLStatic)

[COLOR=seagreen]'Create the query using SQL defined above[/COLOR]
If Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(0) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\Static\" & cboCode.ItemData(intCounter) & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(1) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\StaticCurve\" & cboCode.ItemData(intCounter) & "_StaticCurve.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(2) Then
'Get the workbook name
BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\Static\" & cboCode.ItemData(intCounter) & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
'Get the workbook name
BookName2 = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\StaticCurve\" & cboCode.ItemData(intCounter) & "_Static.xls"
'Export querydef into specified .xlsx file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName2, True
End If

qdf.Close
Set qdf = Nothing
DoCmd.DeleteObject acQuery, "Static"
Next

End Function
 

Users who are viewing this thread

Top Bottom