I'm new to Access--I've taught it in the classroom, but it's been a long time . . .

I need to create a combobox that diplays names based on a query

I have two tables. Table 1 holds employee names and their areas of expertise, recorded as numbers (1,2,3,etc)

Table 2 lists the numbers of the areas of expertise and then the name/descrptions of each of these.

I have a form with two combo boxes. In Combo box 1, the user will select the area of expertise from a dropdown list of the descriptons. Based on what he chooses, the names of the employees who are listed to have those areas of expertise will appear in Combo box 2, so he can select which one to assign to a task.

So,

The query to select the names appearing in the Combobox2 need to come from a query that uses the value entered in the Combobox 1. Right now, Combobox 1 has as a source both fields from Table 2, the number and the description of the area of expertise. Only the description shows.

How do I build a query that filters Table 1 by the number (not description) selected in Combobox 1 of the form and displays the name of the employees who are qualified to perform that task in Combobox 2?

I've attached a picture of the two tables.

Hi
Welcome to Utter Access Forums
Unfortunately your attached picture is not available, could you try again..?
Based on your tables I could put something together to assist you.
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

OK. I've saved it as a jpg. I hope that does better.

I should mention a few other things here. When I store both the area from combo1 and the employee from combo2 into the Questions Table, I need to store the corresponding values, not not the text, but the user wants to select from the words.

One thing I've learned since I posted this question is that it might be easier to get the intial information from a series of option buttons than from a combo box. That way the values are recorded in the table with no problem, but the user sees the labels.

Then, I'd have to base Combobox 2 on the option (value) selected and only have to worry about returning a value to the table for that Controlbox2.

From what I'm seeing, I'll have to use code for this. I have written many macros for Excel and Word, but I haven't used macros in Access, so I will need a little direction on what to do with the code! Thanks you for any help!!!

Hi

Some things to remember and take into consideration..
Area's will change (add/delete/Title change)
Can a person have more than (1) area of expertise..? (list boxes have a multiple pick option)
Would these be options to consider.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Yes, employees will change and areas of expertise may change, but maybe not many areas will be added. What areas each employee has are determined in the table. But if it would be just as easy to do this with 2 combo boxes, maybe that would be better . . . I was just concerned with returning the values to the Questions table when the combo boxes are displaying text.

Displaying text in a combo window does not mean that the combo is bound to that data, you can display any amount of information but if the bound column is (1) "default" then that record reference is stored and not the text.

Hi
Looking over your image I see table called tblSME.. do you have repeating field names of "Area of Expertise"..??
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

No. I had to look--this is someone else's database, but he went nuts and is out on medical disability . . . So I have an opportunity to learn a lot!

*tblQuestions has a field called intArea that lists the area in which a question falls.

*tblAreas has a field called intAreaID that lists each of the areas.(That's what needs to be entered into the intArea field of tblQuestions when a new question is added.)

*tblAreas also has a field called txtAreatext that lists the descrptions of each of the areas (that's what needs to show up in Combobox1)

*tblSME has five fields for each SME, each called intArea1, intArea2, etc, but all of their captions say "Area of Expertise."

Does that help? Thank you so much for looking at this!

Hi
No..

The above description indicates an un-normalized structure which is often called "Repeating Groups", each entry should be stored as a single record and not within a record(multiple entries).
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

The SMEs can have up to 5 areas of expertise. Each of the 5 fields named "Area of Expertise" can hold a number indicating a different area for that SME.

The tblAreas then holds the numbers of the areas and their descriptons.

How would I set it up correctly? I'm not sure I dare change this structure, because I don't know what all reports and forms already use that data.

Can it be made to work the way it's set up?

Thanks!

Hi
To change the structure will impact on the rest of the application either directly or some where down the line..
Offering a "work around" is not option as the hole will only get bigger.
It may be possible to restructure the table with a minimum of impact, this will also included changing/coding both field and control references within the application. (not a easy task)
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)