Data Combo and List Box

[Zen724]

Hi,
I'm a SQL dba that's pretty new to VB and would greatly appreciate any responses.

I have an Employees database. I'm using a data combo drop down list to show 50 company Names. I would like to have a list box show the names of the employees that belong to that particular company and when the employee name is clicked have their information appear in a text box. I have been trying to figure this out for days but I have only been able to get my company names list to show all the companies. I'm unsure how to connect the items in my Drop down list to my list box.

Also my Company Names are being pulled from a table with only Company the names in it. The employee information is in a separate table. However the company name also appears in the employee’s table as well.

Again any response would be appreciated,

 

[slinko]

1. When you say data combo drop down list do you mean a combo that is linked to a database or are you populating the drop down through code. A word of advice, binding controls to database fields is easy, and fine for what you are trying to do, but they are not as flexible as populating your controls through code. I've had nightmares with bound controls!!!

2. In any case, when the combo box is clicked, use SQL and open a recordset to the database table selecting all employees for that particular company. Then step through your recordset and populate your list box. For instance,

RS as adodb.recordset
set RS = new adodb.recordset

RS.Open "SQL", _
"Extended Properties=DBQ=" & yourDB & ";" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;" & _
"FIL=MS Access", adOpenDynamic, adLockOptimistic

Do until RS.eof
'add empoloyee to list box
RS.movenext
loop
RS.close

3 Now, as far as putting their information into a text box when an employee is selected in the list box, you are going to want to do the same type of thing for that except your recordset will only contain the information regaurding that 1 employee. I'm not sure of the events for a list box, I never used them much, but if it doesn't have a click or similar, you could probably either put a button on the form to display the info, use a right click menu, or why not even use another drop down combo for the employees and when the user selects one, populate the information for that empoyee. Anyway, there are a few ideas, drop down combos are a little tricky at first but once you get them down, nothing to it. Good Luck!