Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 10 votes, 5.00 average. Display Modes
Old 07-24-2014, 12:36 PM   #1
Sinfathisar
Newly Registered User
 
Join Date: Jan 2009
Location: Washington, USA
Posts: 60
Thanks: 3
Thanked 0 Times in 0 Posts
Sinfathisar is on a distinguished road
Loop through values in ComboBox, set Query Criteria, Export to Excel

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

' Return Control object variable pointing to list box.
Set cboCode = Forms!Chart_Export_Static!cboTeam

' Enumerate through all items.
For intCounter = 0 To cboCode.ListCount - 1

'sql for static test charts export
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;"
 
'Create the query using SQL defined above
Set qdf = CurrentDb.CreateQueryDef("Static", strSQLStatic)

'Create the query using SQL defined above
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 is offline   Reply With Quote
Old 07-24-2014, 01:06 PM   #2
Sinfathisar
Newly Registered User
 
Join Date: Jan 2009
Location: Washington, USA
Posts: 60
Thanks: 3
Thanked 0 Times in 0 Posts
Sinfathisar is on a distinguished road
Re: Loop through values in ComboBox, set Query Criteria, Export to Excel

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

' Return Control object variable pointing to list box.
Set cboCode = Forms!Chart_Export_Static!cboTeam

' Enumerate through all items.
For intCounter = 0 To cboCode.ListCount - 1

'sql for static test charts export
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;"
 
'Create the query using SQL defined above
Set qdf = CurrentDb.CreateQueryDef("Static", strSQLStatic)

'Create the query using SQL defined above
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
Sinfathisar is offline   Reply With Quote
Reply

Tags
combobox , export access to excel , loop , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop Through Table or Query and Export to Excel jhabey Modules & VBA 6 01-26-2014 06:19 AM
Negative Time values when export Access Query to Excel yolo Queries 17 07-11-2013 04:30 AM
Query with Criteria export to excel HELP... smally006 Modules & VBA 11 03-02-2010 02:18 AM
Export Query To Excel With Multiple Criteria Sevn Modules & VBA 39 10-07-2008 11:06 AM
need help with nested loop to export to excel stacylinda Modules & VBA 0 09-06-2006 10:40 AM




All times are GMT -8. The time now is 02:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World