Loop through selection boxes and execute a task
I attached a very basic database that has two combo boxes that are populated by querying specific criteria in a table. The first combo box is Me.cboZone, the second combo box is Me.cboArea. Me.cboZone must be selected first to generate a list of options for Me.cboArea to select.
Currently, I select the first option in Me.cboZone. Then I individually select all the corresponding options in Me.cboArea that are associated with what’s displayed in Me.cboZone to execute a given task.
So the manual steps would look something like this in my database:
For Me.cboZone select the first record which is: ‘Atlantic’. Then for Me.cboArea select the first record which is ‘MD’ and perform task (e.g. MsgBox “Task complete”). Next for Me.cboArea select the next record and perform task. Then keep select all the applicable records in Me.cboArea until the end, then select the second record in Me.cboZone, and do the same thing all over again until complete.
There has to be a block of code that could automate this process, but I’m not strong enough with loops to put it together. I’d really appreciate a solution for this.
Database2-.accdb
Currently, I select the first option in Me.cboZone. Then I individually select all the corresponding options in Me.cboArea that are associated with what’s displayed in Me.cboZone to execute a given task.
So the manual steps would look something like this in my database:
For Me.cboZone select the first record which is: ‘Atlantic’. Then for Me.cboArea select the first record which is ‘MD’ and perform task (e.g. MsgBox “Task complete”). Next for Me.cboArea select the next record and perform task. Then keep select all the applicable records in Me.cboArea until the end, then select the second record in Me.cboZone, and do the same thing all over again until complete.
There has to be a block of code that could automate this process, but I’m not strong enough with loops to put it together. I’d really appreciate a solution for this.
Database2-.accdb
ASKER
Hi Capricorn, sorry it has taken so long to respond. I've been away for most of the day. The code you provided is super close to what I need. However, instead of looping through like:
Atlantic
MD
NJ
I would need it to loop through like this:
Altantic
MD
Then...
Altantic
NJ
You'll see I added some code that forces it to display in the combo box field each loop since the values listed in the combo box fields are tied to obects: "Query1" and "repDaily".
Thanks again for your help on this! I attached my revised database. ;-)
Database2-Rev2.accdb
Atlantic
MD
NJ
I would need it to loop through like this:
Altantic
MD
Then...
Altantic
NJ
You'll see I added some code that forces it to display in the combo box field each loop since the values listed in the combo box fields are tied to obects: "Query1" and "repDaily".
Thanks again for your help on this! I attached my revised database. ;-)
Database2-Rev2.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely outstanding! Thanks again so much!!!
Database2-Rev.accdb