Hi All, trying to figure out how to use a combobox in a query whose rowsource is aware of the value in field in the "parent" query.

In other words, I configure a field in a query as a combobox. I'd like the value in the combobox to match the underlying table field, but make the choices in that combobox filtered by another value on the same row.

Is this possible?

Many thanks.

How did you put a ComboBox in a query?

Might I assume that the ComboBox is actually on a form, and you want to reference that Control as the Criteria in your Query?

Or are you simply trying to provide a RowSource for a ComboBox on a Form with some criteria of what is to be available for selection?

It's rather unclear what you have and what you desire... Please expand on your explanation and reference applicable forms, control names, tables, and fields in tables.

Have you tried a query?

Here is an example:

tblAddressTypes
----------------------------
AddressTypeID, AN PK
AddressType, Text
IsActive, Yes/No

RowSource: SELECT AddressTypeID, AddressType FROM tblAddressTypes WHERE IsActive = False

Then in your Combo Box Properties, set Columns = 2, Widths = 0;1, Bound Column = 1

This would then store the Primary Key value for the chosen Address Type

Hello John and thank you.

You may configure any field in the query designer as a combobox by selecting the field and choosing properties.

I am not trying to do any of the things you asked about.

It is a common thing to use expressions in queries which reference another field in the query.

I'm trying to do much the same thing. The difference is that I have configured a field as a combobox. I'd like to filter its rowsource on the value of another field within the query.

Actually, I already knew it *can* be done, but it doesn't mean it is a good idea... I guess my method to convey that wasn't clear.

Of course expressions are used in queries -- all the time. But not Lookups.

Data with no hidden masks belongs in tables where Programmers can always SEE the data and develop straight-forward methods to manipulate the data without jumping through hoops to do so, and allow Users to modify their list of common selected values. Putting the selections in Lookups of Tables/Queries means that if the User wants more or less selections, they have to call the Programmer to do it. Having Lookups in Tables and Queries is akin to having all your VBA code currently in Modules stored in a gazillion Macros. A Maintenance Nightmare.

Have you considered that the "feature" you are leveraging is unique to MS Access? If you ever need to "up-size" to SQL Server, kiss them Lookups Bye-Bye. You will have to code the functionality the way it should have been done to start with.

Therefore, I cannot advise you how to attempt what you are trying to do with just a Query.

Something to remember... Just because an application provides a method of doing something doesn't mean using THAT method is the best choice...

To add to what John said.

I believe that Tables and Queries are for developers and should NEVER be seen by the end user.

A user should only enter/view data via a form/report.

If you allow the user access to your queries and they change something then Your database will crash and you will be blamed.

Just my opinion.

Thank you both.

I think we've gotten crossed up, however. I fail to understand how the idea of an end user accessing a table or query entered this discussion. At no point did I mention the user accessing or changing anything.

I am simply trying to configure a combobox in a query which uses another field in the same query as part of its rowsource.

Whether or not it is a good idea to do so is not what I am asking.

Hi

What Rain & John are trying to determine is how you can have a combobox in a query?

You can have a Form which has a combobox Control which is based on a query

I believe you are talking at cross purposes due to the terminology you are using

Can you upload the DB with the Combobox in the query so we can see how you have achieved this?

My 2 cents worth

Mike