Public Function CreateQCIVSChartsforReports() As Boolean
'Define variables for creating loop to export all teams and items
Dim intCounter As Integer
Dim cboCode As ComboBox
Dim intCounter2 As Integer
Dim cboCode2 As ComboBox
' Set the variable to point to combobox that holds Instrument ID.
Set cboCode = Forms!frmChartExport_IVS!cboInstr
' Loop through all items (Instrument IDs) to create exported excel file for each team.
For intCounter = 0 To cboCode.ListCount - 1
' Set the variable to point to combobox that holds Item ID.
Set cboCode2 = Forms!frmChartExport_IVS!cboItem
' Loop through all items (Item IDs) to create exported excel file for each team.
For intCounter2 = 0 To cboCode2.ListCount - 1
'sql for ivs test charts export
strSQLIVS = "SELECT tblGeo_QCIVSResponse.vchIVSFileName, tblGeo_QCSurvey_ops.vchDateCollected, tblGeo_QCSurvey_ops.vchSurveyInstr, tblGeo_QCIVSResponse.Test_Item, " & _
"tblGeo_QCIVSResponse.Spike_Response_Ch1, tblGeo_QCIVSResponse.Spike_Response_Ch2, tblGeo_QCIVSResponse.Spike_Response_Ch3, tblGeo_QCIVSResponse.Spike_Response_Ch4, " & _
"tblGeo_QCSeedItems.Response_Value_CH1, tblGeo_QCSeedItems.Response_Value_CH2, tblGeo_QCSeedItems.Response_Value_CH3, tblGeo_QCSeedItems.Response_Value_CH4, " & _
"tblGeo_QCIVSResponse.X_Offset, tblGeo_QCIVSResponse.Y_Offset, tblGeo_QCIVSResponse.ftRecordedEasting, tblGeo_QCIVSResponse.ftRecordedNorthing, Format(tblGeo_QCSurvey_ops.vchDateCollected, 'mdyyyy') AS Filename " & _
"FROM (tblGeo_QCIVSResponse INNER JOIN tblGeo_QCSurvey_ops ON tblGeo_QCIVSResponse.[vchIVSFileName] = tblGeo_QCSurvey_ops.[vchIVSFileName]) INNER JOIN tblGeo_QCSeedItems ON (tblGeo_QCIVSResponse.[Test_Item] = tblGeo_QCSeedItems.[Test_Item]) AND (tblGeo_QCSurvey_ops.[vchSurveyInstr] = tblGeo_QCSeedItems.[vchSurveyInstr]) " & _
"WHERE (((tblGeo_QCSurvey_ops.vchDateCollected) Between Int([Forms]![frmChartExport_IVS]![StartDate]) And (Int([Forms]![frmChartExport_IVS]![EndDate])+0.99))) AND (((tblGeo_QCIVSResponse.Test_Item)='" & cboCode2.ItemData(intCounter2) & "')) " & _
"ORDER BY tblGeo_QCSurvey_ops.vchDateCollected DESC, tblGeo_QCSurvey_ops.vchSurveyInstr, tblGeo_QCIVSResponse.Test_Item;"
'Create the query using SQL defined above
Set db = CurrentDb
Set qdf = db.CreateQueryDef(cboCode2.ItemData(intCounter2), strSQLIVS)
'export ivs response files
'Get the workbook name
BookName = "C:\Projects\Fort Ord\Data\Database\Grapher\IVS\" & cboCode.ItemData(intCounter) & "_IVS.xls"
'Export querydef into specified .xls file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, cboCode2.ItemData(intCounter2), BookName, True
'Fill the IVS Chart Table with a record for each chart file that is exported for linking chart image to report
'Creates the date part of the filename
Forms!frmChartExport_IVS!dateforfilename = Replace(Forms!frmChartExport_IVS!EndDate, "/", "")
'Define variable for Insert statement (IVS Response Chart)
Dim Insert_IVSChartTable As String
Insert_IVSChartTable = "INSERT INTO tblGeo_ChartIVS ([Test_Item], [vchSurveyInstr], [IVS_Chart_Date], [IVS_Chart_Type], [IVS_Chart_Object], [TimeStamp], [IVS_Chart_ID]) " & _
"VALUES ('" & cboCode2.ItemData(intCounter2) & "', '" & cboCode.ItemData(intCounter) & "', Format([Forms]![frmChartExport_IVS]![EndDate], 'm/d/yyyy'), 'IVSResponsePosition', 'Grapher\IVS\' & Forms!frmChartExport_IVS![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_IVS_' & '" & cboCode2.ItemData(intCounter2) & "' & '.png', Now(), Forms!frmChartExport_IVS![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_IVS_' & '" & cboCode2.ItemData(intCounter2) & "' & '.png');"
If DCount("*", cboCode2.ItemData(intCounter2)) > 0 Then
'First turns warnings off
DoCmd.SetWarnings False
'Executes the action query SQL statements defined above to add records with chart information for linking images to reports
DoCmd.RunSQL Insert_IVSChartTable
'Turns warnings back on
DoCmd.SetWarnings True
End If
'Clean up the query that was created above
qdf.Close
Set qdf = Nothing
DoCmd.DeleteObject acQuery, cboCode2.ItemData(intCounter2)
Set db = Nothing
Next
Next
End Function