Combo Box Issues

kvar

New Member
Joined
Oct 14, 2009
Messages
16
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!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

kvar

New Member
Joined
Oct 14, 2009
Messages
16
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,301
Office Version
  1. 365
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,289
Messages
5,600,756
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top