Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I have a combo box populate two other combo boxes?

Posted on 2015-02-05
7
Medium Priority
?
207 Views
Last Modified: 2015-02-06
I have a report that I can modify in order to meet customer needs. Typically I have a Workload and a Queue report in which supervisors can list everyone trained in a Workload, any task or in a Queue, any workload. Or, they can pinpoint the workload and the queue, which becomes a "Skill."

The Skill is a contatenation of the Workload and the Queue. On my tabbed form to generate reports, the third tab is Skill List. Instead of giving the supervisor an option to choose a Workload and a Queue, this tab should only have the Skill option, which combined the workload and queue.

I want to hide the workload and queue combo boxes, and then have the Skill combo box, on change, requery the workload combo box, which would then, on change, requery the queue combo box.

Why? This would allow me to apply the current array of conditonal report settings to this third report.

As to the row sources for the workload and query combo boxes, I'm sure it is some sort of inner join that has to be worked out to get the Workload combo to fill with the workload from the Skill combo, and then the query combo to fill with a match from the Skill combo, but it would take me a couple of days, at least, to figure it out, partially because I'm interfacing with a combo box, not only another table.

So, I'm needing to do two things:
1. Set the row sources for the workload and queue combo boxes (the hard part for me).
2. Set the query on change on the Skill and workload combo boxes to query the workload and queue combo boxes
Sample-Training-Database-2015-01-12--2-.
Training-Database-be-2015-01-12--2-.accd
0
Comment
Question by:David Bigelow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 85
ID: 40593266
I open your database, after renaming the file extensions and such, and relinking. However, I have no idea which form you're referring to, or which report, etc. When posting your files here, please be sure that you (a) insure they'll run when downloaded and (b) give complete instructions as to how to recreate your issue.

With that said: You don't really have an "issue", you're asking us to do your work for you. You wrote "but it would take me a couple of days, at least, to figure it out," - which would seem to indicate that you feel your time is more valuable than ours. Please understand that we're all volunteers, and while we enjoy helping others, we don't like to be treated like employees. We'll help you, but we don't work for you, and we're not here to do your work when you're too busy to do it yourself.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1332 total points
ID: 40593373
generally, when you have cascading combo boxes, you have a data table that looks something like:

Field1    Field2    Field3
US          NY          Albany
US          NY          New York
US          NY          Buffalo
US          VA           Arlington
US          VA           Manassas

In the first combo, you would have a rowsource that is a query that looks like:

SELECT DISTINCT Field1 FROM yourTable

The 2nd combo would have a rowsource whose query looks like:

SELECT DISTINCT Field2 FROM yourTable WHERE Field1 = Forms!yourForm!combo1

The 3rd combo would have a rowsource whose query looks like:

SELECT DISTINCT Field3 FROM yourTable
WHERE Field1 = Forms!yourForm!combo1 AND Field2 = Forms!yourForm!Combo2

Then, in the AfterUpdate event of combo1, I would put:
Private sub Combo1_AfterUpdate

    me.combo2.requery
    me.combo2 = NULL
    me.combo3.Requery

End Sub

Open in new window

and for combo 2
Private Sub combo2_AfterUpdate

    me.combo3.requery
    me.combo3 = NULL

End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:JimFive
ID: 40593852
For the rowsource of your Skills combo you need to have SELECT SkillName, WorkloadID, QueueID

Then in the onChange event of that box you set the VALUE of the other combo boxes like:
cboWorkload.value = cboSkills.column(1)
cboQueue.value = cboSkills.column(2)

You don't need to update the rowsource of the hidden combo boxes because no one can see it anyway.
--
JimFive
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:David Bigelow
ID: 40594289
Dale, my man, you are the Expert! Thank you for taking the humble approach, taking my description for what it was worth, and helping me out! Based on your simplification of everything, I assure you, my thoughts were on a trajectory that wasn't set to return for quite a while. Everything is working almost as desired. When I make a selection in Combo1, Combo2 has one choice. When I select that, Combo 3 has one choice. Bingo!

The tweak I need, and what I was ultimately looking for, is, "How can I have Combo2 and Combo3 not only make it the only option, but in fact to make it the selection, to populate Combo2 and Combo3 based on the selection made in Combo1. Remember Combo2 and Combo3 will be hidden.

I realize that this seems redundant, in the way that JimFive points out, but on my form, frm_TrainingByEe, behind the Run Report button are 16 extensive conditions. Instead of rewriting all those conditions to fit 3 combo boxes, instead of 4, I'd rather just hide those two boxes and use the 3rd combo box to populate them. In the first two pages of that tabbed form, I want to be able to run exclusively Queues or exclusively Workloads. On this third tab, I want it to be only a concatenation of the two fields.

So the final help I need is in paragraph 2.
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 668 total points
ID: 40594321
Check Form1 in the attached database

Private Sub cmb1_AfterUpdate()
    Me!cmb2.Requery
    Me!cmb2 = Me!cmb2.ItemData(0)
    Me!cmb3.Requery
    Me!cmb3 = Me!cmb3.ItemData(0)
End Sub

Private Sub cmb2_AfterUpdate()
     Me!cmb3.Requery
     Me!cmb3 = Me!cmb3.ItemData(0)
End Sub

Open in new window

Sample-Training-Database-2.accdb
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40594406
Thank you, hnasr!
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40594515
Welcome!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question