Help me Access my data please.
August 3, 2006 7:49 AM Subscribe
I play with a rather large and multi-layered database. Using the Enter Parameter Value function, is there a way to make that Prompt box into a drop box displaying all the options?
Say you are tracking seventy people who interface with two hundred different organizations in different ways. You want the query to prompt you for the person so you can see who they have been talking to, but you don't want to have to type out the whole name. Instead, when the box pops up to prompt you to enter a name, the box is linked to the table with the names of all the people and you just choose the one you wish to run the query on. Is this possible in Access?
It seems like it should be. Because it would be brilliant.
Say you are tracking seventy people who interface with two hundred different organizations in different ways. You want the query to prompt you for the person so you can see who they have been talking to, but you don't want to have to type out the whole name. Instead, when the box pops up to prompt you to enter a name, the box is linked to the table with the names of all the people and you just choose the one you wish to run the query on. Is this possible in Access?
It seems like it should be. Because it would be brilliant.
In a properly normalized database, most tables would not have a person-name in them; they'd have a numeric person-ID derived from the primary key of the People table. You might be able to simplify your queries somewhat if you make a combo box control that displays names but returns numeric ID's.
The trick is to make the control a two-column control, sourced from the People table, with one column being the numeric person-ID and the other being the person's name. Choose the person-ID column of the control as the data source, and make the width of the control's person-ID column zero. The control's user will then see only names, and the control's clients will get fed ID's.
posted by flabdablet at 8:36 AM on August 3, 2006
The trick is to make the control a two-column control, sourced from the People table, with one column being the numeric person-ID and the other being the person's name. Choose the person-ID column of the control as the data source, and make the width of the control's person-ID column zero. The control's user will then see only names, and the control's clients will get fed ID's.
posted by flabdablet at 8:36 AM on August 3, 2006
This thread is closed to new comments.
Within the query, set the criteria of the person's name column to be the combobox within the form (use the 'Build' feature). The criteria will look something like [Forms]![frmFormName].[cboComboBoxName].
Good luck.
posted by mezzanayne at 8:24 AM on August 3, 2006