Combo Boxes - populating

[NKA]

Apologies if this has been answered already - I have spent over an hour looking at several options, but cannot find the one I need!

I have an unbound combo box on a form. The rowsource is data from a table.

I am displaying "choose..." when the form is first opened (variable).

I would also like to be able to have at the top of my list the option to select "ALL".

The idea is that a form will be opened based on the combo selection - if "all" selected - open form with all records, otherwise filter to selected item.

Does anyone understand what I am looking for? Any help will be appreciated! NKA

The answer's always easy - if you know it! ;-)

 

[BoxHead]

NKA,

Try this...

Rowsource for combo box:

SELECT DISTINCT [your field name] FROM [your table name]
UNION
SELECT " ALL" FROM [your table name];

The second part of the Union query will add the text in quotes (" ALL&quot to the list. The space included at the beginning of the word should keep it at the top.

On the update event that triggers the filter event:

If Combo1 = " ALL" Then
me.filter = ""
me.filteron = false
else
me.filter = "your field name = " & """" & Combo1 & """"
me.filteron = true
End If

Replace your field name with the name of the field from your table; your table name with the name of your table; and Combo1 with the name of your combo box.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[uclabruin99]

I wanted to further extend this question, I was wondering what if this combo box wasn't on a form but on a regular excel worksheet, would I still be able to connect to a particular table and a field to populate that table?

I have already tried the range and ODBC thing. I am creating a tool that is multi-user where the info for all the drop-down boxes placed in an Excel worksheet should come from Access tables (fields).

Please advise. Thanks.
UCLABruin99

 

[BoxHead]

ucla,

No idea. You might want to try an Excel forum or at the very least, start your own thread.

Good luck.

John

Use what you have,
Learn what you can,
Create what you need.

 

[sdimov]

Popultaing the ComboBox in Excel is not an issue. Another question - is Excel used as a data entry tool or a calculation engine?
Why not design the app in Access and export the data into Excel (maybe as a pivot table)?
Will users be updating Access from Excel? In that case the two-way traffic seems redundant.

Svet