Drop Down Menu Problem

[jamespeyton]

I have a Table called NameList that contains first and last name for all my employees. I have another table called Info that will have info about all the software each employeee has installed. I want drop down menus for last name that shows all possible last names (got it working fine) and a drop down menu for first name. The problem is that I only want to show the first names that correspond to certain last names in my name list table.


For Example:
Table:NameList
LastName FirstName
Doe John
Doe Jane
Lavil Mary

What I want is that when someone chooses the last name Doe from the drop down menu in the Info table, the drop down menu for first name should only display John and Jane, not Mary.

Thanks
-Jim

 

[FancyPrairie]

1st, you could combine them into one combobox. For example:

RowSource... Select strLastName & ", " & strFirstName From NameList

However, if you want 2 combo boxes then the Rowsource of the lastname combobox (cboLastName) would look something like this:

SELECT strLastName FROM NameList ORDER BY strLastName;

The RowSource of the firstname combobox (cboFirstName) would look like this:

SELECT strFirstName FROM NameList WHERE (strLastName=[cboLastName].[value]) ORDER BY strFirstName;

In the AfterUpdate event of the lastname combobox, add this code:

cboFirstName.Requery

 

[MSW]

This post has really helped me understand the combo box but I have another question.

Let's say I have (2) tables one is a list of technicians with the fields (TechIDNum,Firstname,Lastname)and the other table contains customer information with one of the fields being TechIDnum.

In my customer form I would like to have a drop down in the TechIDNum field that shows the available TechIDNum's from the Technicians table but also displays their first and last name so the person filling in the customer form doesn't have to refer to a document of what TechIDnum belongs to who..

I hope this makes sense..Thanks for your help..

 

[FancyPrairie]

MSW, set the column widths to something like this: 0;.5;.5 Note the first column's width is 0. Consequently, TechIDNum is hidden. All the user's see is the First and Last Name.

 

[MSW]

Thanks for the reply but I'm not sure where to put the column widths in the RowSource ?

Here's my current code which only displays in the drop down is the TechIDNum and that's it..

SELECT [Tech Table].TechIDNum, [Tech Table].LastName FROM [Tech Table];

Thanks..

 

[FancyPrairie]

One of the properties of the combo box is "Column Widths

 

[MSW]

Okay I found the Column Widths and put in the settings as you suggested but I now don't see even the TechIDNum just a blank box on the drop down.. If I put in 5;.5;.5 for the first 0 then I see the TechIDNum but nothing else. Is there anything wrong with what I have in the RowSource feild below?

SELECT [Tech Table].TechIDNum, [Tech Table].LastName FROM [Tech Table];

Thanks again for your help....Scott

 

[PHV]

What is the ColumnCount property of your combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

Yep, PHV is right. I didn't think to mention that.

 

[MSW]

That did it...

You guys are the best...

Thanks for not giving up on me..Scott