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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-08-2015, 02:40 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
Nested Loops with comboboxes dependent on eachother

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!

Sinfathisar is offline   Reply With Quote
Old 07-08-2015, 02:48 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Nested Loops with comboboxes dependent on eachother

try using the combo2 on enter event to populate the combo2 rowsource rather than the combo1 afterupdate event
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2015, 03:08 PM   #3
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: Nested Loops with comboboxes dependent on eachother

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.

Sinfathisar is offline   Reply With Quote
Old 07-08-2015, 03:29 PM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Nested Loops with comboboxes dependent on eachother

Quote:
Originally Posted by Sinfathisar View Post
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.

Quote:
Originally Posted by Sinfathisar View Post
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.
vbaInet is offline   Reply With Quote
Old 07-08-2015, 04:06 PM   #5
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: Nested Loops with comboboxes dependent on eachother

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
Sinfathisar is offline   Reply With Quote
Old 07-08-2015, 04:32 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 07-10-2015, 02:24 PM   #7
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: Nested Loops with comboboxes dependent on eachother

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

http://www.access-programmers.co.uk/...d.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.

Sinfathisar is offline   Reply With Quote
Old 07-10-2015, 07:07 PM   #8
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Nested Loops with comboboxes dependent on eachother

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.
vbaInet is offline   Reply With Quote
Old 07-14-2015, 03:58 PM   #9
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: Nested Loops with comboboxes dependent on eachother

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

Sinfathisar is offline   Reply With Quote
Reply

Tags
combobox , loop , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
converting nested loops into a shorter algorithm accessuser1023 Modules & VBA 13 03-06-2013 01:47 PM
Output XML with Nested Loops smbarney Modules & VBA 2 06-27-2012 07:25 AM
Cascading ComboBoxes dependent on two criteria aldeb Forms 0 01-15-2009 04:31 AM
Question about dependent comboboxes ga11ardo Forms 7 10-25-2007 01:04 AM
Nested Loops joycek Modules & VBA 4 08-15-2006 06:41 AM




All times are GMT -8. The time now is 01:39 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