Combo Box Issues

[kvar]

Okay, Access 2003, XP

First, I have a simple 2 column table named "Products". Column 1, "Product_Name", has, you guessed it....a list of Product Names. Column 2, "Employee" has the Employees name that handles that product.

Okay, so, on my form I have 2 Combo Boxes. Box 1 "Product Combo", uses Column 1 of the "Products" table to generate the list of values. No problem.
Box 2, "AssignedToCombo", should show the name of the employee that handles that product.

In the "ProductCombo" On Change event I have the following code:

Private Sub ProductCombo_Change()
If Me![ProductCombo] = "Unassigned" Then
Me![AssignedToCombo] = "Unassigned"
Else
Me![AssignedToCombo].RowSource = "Select [Employee] From [Products] WHERE Product_Name = '" & Me![ProductCombo] & "'"
End If
End Sub


What happens, is when I select a Product from the "ProductCombo" then the only option available in the "AssignedToCombo" Drop down is the employees name that handles that product. Which means the code is doing what it should........sort of.

The Problems:
1. When I open the form, or move to a new record, both of the Combo Boxes are blank....everything is available from the drop down, but initially they are blank. They both have a default value of "Unassigned" (which is also in the list for both columns), but they just show blank boxes until you select something. I want them to actually SHOW "Unassigned" in both boxes until another selection is made.

2. Related to 1. (I think), when a product is selected in "ProductCombo", I want that employees name to auto-fill(?) into the "AssignedToCombo". Currently, it is the only available option from the drop-down, but I don't want users to have to click and select both.

3. And this one I have no idea where to start with....Once problems 1 and 2 are resolved....The employees name should automatically pop up in the "AssignedToCombo", but then I want the rest of the list of employees to be available on the drop-down. Occasionally, work gets handed off to another employee, even though it's not technically "their product", so there has to be an option to do that.

I hope that all made sense, and I apologize for the length...but I'd rather the issues be clear! Thank you for ANY help anyone can provide!

 

[kvar]

Just noticed something else that I think is relevant:

With the above code, if "Unassigned" is selected in the "ProductCombo", then "Unassigned" is AUTOMATICALLY populated into the "AssignedToCombo".......AND on clicking the drop-down, the rest of the list is available.

So basically, as long as "Unassigned" is the Product, then it works exactly how I want it to.........weird!

Of course, the ProductCombo still doesn't automatically fill....but I think this is a clue!

 

[jackd]

Just noticed something else that I think is relevant:

With the above code, if "Unassigned" is selected in the "ProductCombo", then "Unassigned" is AUTOMATICALLY populated into the "AssignedToCombo".......AND on clicking the drop-down, the rest of the list is available.

So basically, as long as "Unassigned" is the Product, then it works exactly how I want it to.........weird!

Of course, the ProductCombo still doesn't automatically fill....but I think this is a clue!

Here is a link to some combo Box tutorials (video)
http://www.datapigtechnologies.com/flashfiles/combobox2.html

You can see others on the Forms section at http://www.datapigtechnologies.com

See if this helps.

Also, for reference, try this site: http://www.fontstuff.com/access/acctut10.htm