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.
-
-
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:
You will need to replace the field, form, and control names to match what you have.Code:WHERE ColorID = Forms![form name]![control name]
The Combo Box
Put the combo box on the form. Make the Row Source property be:
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:Code:SELECT ColorID, Color FROM tblColor
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.Code:DoCmd.OpenQuery "your query name"
-
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.-Comment
-
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.Comment
Comment