Nested Loops with comboboxes dependent on eachother (1 Viewer)

Sinfathisar · Jul 8, 2015

I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items. My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. Any ideas on how to force the combobox to requery through code in a module?

Many thanks!

CJ_London · Jul 8, 2015

try using the combo2 on enter event to populate the combo2 rowsource rather than the combo1 afterupdate event

Sinfathisar · Jul 9, 2015

Thanks CJ_London. That didn't seem to do the trick though. I also have noticed that it also won't select any value in combo1 if it is default with a blank value. This is not the case in previous versions of my code where the two combo boxes are not dependent on each other. Not sure of the cause yet.

vbaInet · Jul 9, 2015

Sinfathisar said:
I am running an export function from a module1 that contains a loop nested within a second loop.

How are you running the export?
If Combo2 is reliant on Combo1 then you don't need the values in Combo1 anymore since it's tied to Combo2... just use the values in Combo2.

Sinfathisar said:
I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. Any ideas on how to force the combobox to requery through code in a module?

Are you saying that you're trying to fire the AfterUpdate event through code? When you're trying to manipulate data in a control, you won't be changing the value of the control in code, you work directly with the query bound to the control via a recordset. Events don't fire through code but of course you can call them in code.

Sinfathisar · Jul 9, 2015

I guess I am not seeing how I don't need the values from combo1.

Here is my code, combo 1 is cboInstr and combo 2 is cboItem (I simplified terms in my original post). cboInstr determines the excel file, and cboItem determines each sheet in the file where data goes. I have queries set up in my comboboxes to pull values from each of their respective tables.

I've limited the items in cboItem using criteria from cboInstr. I'm just not sure how to pick that information up in the code module. I know the afterupdate won't fire in my code - but drawing a blank on how to get the right values in my combobox.

Code:

 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

MarkK · Jul 9, 2015

You seem to be trying to re-purpose your user interface to do stuff it wasn't really intended to do. Do you know how to open a recordset? If not, look into that. Then you can get exactly the data you need, when you need it, without all the extra baggage of trying to pull it out of a Combo or List.

Sinfathisar · Jul 10, 2015

So looking in to the recordset - I am thinking about trying to implement my export this way...

http://www.access-programmers.co.uk/forums/showthread.php?t=215859

Comment #9.

Can I have my query live in VBA instead of in the database? I am not having luck calling the function in the query criteria when the query string is in the code.

vbaInet · Jul 11, 2015

If you're going to be exporting a filtered query then it must be a querydef. You can create a query manually by entering only the SELECT part of the SQL, then in code you alter the SQL property of the querydef object, save it and export it.
Remember to put the SQL back to its default string.

Let us know how you get on.

Sinfathisar · Jul 15, 2015

Got it! I did not end up implementing the code as in my previous comment. I still ended up with a double loop - the outside loop going through my combobox to pick up the Instrument ID for each excel file, and the inner loop going through a recordset to pick up the sensor ID for each sheet in the excel file. The recorset is much slower but it will work for our purposes.

Thanks for the ideas guys

Nested Loops with comboboxes dependent on eachother (1 Viewer)

Sinfathisar

Registered User.

CJ_London

Super Moderator

Sinfathisar

Registered User.

vbaInet

AWF VIP

Sinfathisar

Registered User.

MarkK

bit cruncher

Sinfathisar

Registered User.

vbaInet

AWF VIP

Sinfathisar

Registered User.

Similar threads

Users who are viewing this thread