Nov 12 2008, 05:28 PM
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?
Nov 12 2008, 07:07 PM
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:
AddressTypeID, AN PK
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
Nov 14 2008, 08:47 AM
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.
Nov 14 2008, 07:01 PM
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...
Nov 14 2008, 07:11 PM
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.
Nov 15 2008, 10:08 AM
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.
Nov 15 2008, 11:32 AM
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here