By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Query of a field from a table requires the AutoNumber ID

P: 2
I created a simple databa se in Access using three tables. Two tables are used for drop down lists in the main database. When I establish the query using the two fields that use the drop down lists, I have to enter the autonumbe in the query box that corresponds with the list item. For example if the list were a series of colors and blue was associated with autonumber ID 2, I now have to enter 2 in the query box instead of "blue" to get the correct records from the query... I know there must be a simple solotion. Please advise.
Sep 26 '12 #1
Share this Question
Share on Google+
3 Replies

Seth Schrock
Expert 2.5K+
P: 2,941
That is how it works in a relational database. A simple way to make it so that the users don't have to know the number associated with the color is to make the queries criteria be linked to a combo box that has all of the color in the color table listed. Here's how:

The Query
The WHERE clause of the query will be:
Expand|Select|Wrap|Line Numbers
  1. WHERE ColorID = Forms![form name]![control name]
You will need to replace the field, form, and control names to match what you have.

The Combo Box
Put the combo box on the form. Make the Row Source property be:
Expand|Select|Wrap|Line Numbers
  1. SELECT ColorID, Color
  2. FROM tblColor
Make sure that the Bound To property is set to 1. Set your column count to 2. Make your column widths be 0"; 1". Set the After_Update event to:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "your query name"
You will now have a list of all the colors and when one is selected, the query will open showing all the records with the color you selected.
Sep 26 '12 #2

P: 2
Seth, I truly appreciate your response, but this is a personal database and I'm just doing the query in the "Design View" of Access. But your response makes me think there is likely a better way to query using a form as opposed to using the query function of Access.-
Sep 26 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,941
There are only two ways to query data: with a query def (like what we are working with here) and SQL code in VBA, but both work identically (virtually at least). All a form can do is sort. You can base a form on a query, but the form isn't doing any querying on its own.
Sep 26 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.