If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
List in the parameter box.
Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
#2
|
|||
|
|||
Not a way in a parameter in a query.
Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
#3
|
|||
|
|||
"Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. Thank you from Denmark to Jeff Boyce, works superb. I use it with a print report within a macho function to decide what to print. ronoee. |
#4
|
|||
|
|||
I've created several systems that use a "Report Order" form. The form lists
available reports, off a table I create that holds the Access report name and a "user" report name, plus other fields. Some of those fields allow me to enable/disable selection controls on the form, so if a report needs a criterion, the control that allows selection of that criterion gets enabled when the report is selected. Good luck! Jeff Boyce Access MVP "ronoee" wrote in message ... "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. Thank you from Denmark to Jeff Boyce, works superb. I use it with a print report within a macho function to decide what to print. ronoee. |
#5
|
|||
|
|||
Jeff -
These systems sound very interesting. Are you able to select multiple criterion? Also, can you multi-select off a list box, instead of a combo box? Thanks. "Jeff Boyce" wrote: I've created several systems that use a "Report Order" form. The form lists available reports, off a table I create that holds the Access report name and a "user" report name, plus other fields. Some of those fields allow me to enable/disable selection controls on the form, so if a report needs a criterion, the control that allows selection of that criterion gets enabled when the report is selected. Good luck! Jeff Boyce Access MVP "ronoee" wrote in message ... "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. Thank you from Denmark to Jeff Boyce, works superb. I use it with a print report within a macho function to decide what to print. ronoee. |
#6
|
|||
|
|||
I use this approach to (optionally) select one or more criteria. I don't
use a multi-select listbox, as I've not had the need/requirement yet to select more than one of some category. I believe it would be a matter of writing the code to loop through the SelectedItem collection for such a listbox, appending the selected items to the WHERE clause. -- Good luck Jeff Boyce Access MVP "ml05" wrote in message ... Jeff - These systems sound very interesting. Are you able to select multiple criterion? Also, can you multi-select off a list box, instead of a combo box? Thanks. "Jeff Boyce" wrote: I've created several systems that use a "Report Order" form. The form lists available reports, off a table I create that holds the Access report name and a "user" report name, plus other fields. Some of those fields allow me to enable/disable selection controls on the form, so if a report needs a criterion, the control that allows selection of that criterion gets enabled when the report is selected. Good luck! Jeff Boyce Access MVP "ronoee" wrote in message ... "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. Thank you from Denmark to Jeff Boyce, works superb. I use it with a report within a macho function to decide what to print. ronoee. |
#7
|
|||
|
|||
I tried what you show below, but can't get it to work.
I have a query that currently has [Enter Program Name] in the Criteria field for a field called Program Name Instead of having a free form text field, I want to use a list box so that users can pick from the list. The name of the form is PROGRAM. on that form, I have a combo box that shows the valid values and a command button to run the query... I tried entering what you said all of these ways: Forms![PROGRAM] [Forms]![PROGRAM] Forms![PROGRAM]![Item Number] and nothing will work. I get syntax errors, etc. and can't get the PROGRAM form to display... Help please!!! "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
#8
|
|||
|
|||
Keri
Any chance the field in question has been defined as a "lookup" data type in the table? If so, you've run afoul of an issue often discussed in tablesdbdesign. That is, lookup data types store one value but display something else. If you are NOT working with a lookup field, then the next most likely culprit is a spelling error. You didn't provide any information about how it doesn't work, so it's a bit tough trying to guess what might be wrong/not working... -- More info, please ... Jeff Boyce Access MVP "keri" wrote in message ... I tried what you show below, but can't get it to work. I have a query that currently has [Enter Program Name] in the Criteria field for a field called Program Name Instead of having a free form text field, I want to use a list box so that users can pick from the list. The name of the form is PROGRAM. on that form, I have a combo box that shows the valid values and a command button to run the query... I tried entering what you said all of these ways: Forms![PROGRAM] [Forms]![PROGRAM] Forms![PROGRAM]![Item Number] and nothing will work. I get syntax errors, etc. and can't get the PROGRAM form to display... Help please!!! "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
#9
|
|||
|
|||
You are good!
Yes, the field in question is defined as a lookup data type. There are two tables that use the Program Name field. One table (tblPrograms) stores the list of the valid Program Names. The other table (tblItemsForAssignment) stores the projects related to the programs, and has a Program Name field to relate a project to a specific Program. To ensure consistency in the Program names (for reporting, etc.), I have the Program Name field in tblItemsForAssignment as a lookup field, pointing to the Program Name field in tblPrograms. I thought of having the query point to the Program Name field tblPrograms instead of tblItemsForAssignment. But when I try this, and enter Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it to [Forms]![PROGRAM]![Item Number] and then displays a dialog box with "Forms!PROGRAM!Item Number" and a text input box... Hope that's enough info... "Jeff Boyce" wrote: Keri Any chance the field in question has been defined as a "lookup" data type in the table? If so, you've run afoul of an issue often discussed in tablesdbdesign. That is, lookup data types store one value but display something else. If you are NOT working with a lookup field, then the next most likely culprit is a spelling error. You didn't provide any information about how it doesn't work, so it's a bit tough trying to guess what might be wrong/not working... -- More info, please ... Jeff Boyce Access MVP "keri" wrote in message ... I tried what you show below, but can't get it to work. I have a query that currently has [Enter Program Name] in the Criteria field for a field called Program Name Instead of having a free form text field, I want to use a list box so that users can pick from the list. The name of the form is PROGRAM. on that form, I have a combo box that shows the valid values and a command button to run the query... I tried entering what you said all of these ways: Forms![PROGRAM] [Forms]![PROGRAM] Forms![PROGRAM]![Item Number] and nothing will work. I get syntax errors, etc. and can't get the PROGRAM form to display... Help please!!! "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
#10
|
|||
|
|||
Keri
Change the data type of the Program field in tblItemsForAssignment to a ?LongInt (this assumes your primary key in tblPrograms is an Autonumber - if not, change to a corresponding/compatible type). Now your tblItemsFor... table displays what it holds. Create a query that joins tblItemsFor ... and tblPrograms on this field. The value you are looking for is the key value. In your form, build a combo box based on the tblProgram ID field, plus the ProgramName (or whatever you've called it). Make the first column (the ID) of zero-width in the properties -- this displays the ProgramName first in the combo box. When you've picked a Program, the combo box's value is the first column (i.e., the ID). Now have your query look to the form, to the combo box, for the ID value it needs. -- Good luck Jeff Boyce Access MVP "Keri" wrote in message ... You are good! Yes, the field in question is defined as a lookup data type. There are two tables that use the Program Name field. One table (tblPrograms) stores the list of the valid Program Names. The other table (tblItemsForAssignment) stores the projects related to the programs, and has a Program Name field to relate a project to a specific Program. To ensure consistency in the Program names (for reporting, etc.), I have the Program Name field in tblItemsForAssignment as a lookup field, pointing to the Program Name field in tblPrograms. I thought of having the query point to the Program Name field tblPrograms instead of tblItemsForAssignment. But when I try this, and enter Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it to [Forms]![PROGRAM]![Item Number] and then displays a dialog box with "Forms!PROGRAM!Item Number" and a text input box... Hope that's enough info... "Jeff Boyce" wrote: Keri Any chance the field in question has been defined as a "lookup" data type in the table? If so, you've run afoul of an issue often discussed in tablesdbdesign. That is, lookup data types store one value but display something else. If you are NOT working with a lookup field, then the next most likely culprit is a spelling error. You didn't provide any information about how it doesn't work, so it's a bit tough trying to guess what might be wrong/not working... -- More info, please ... Jeff Boyce Access MVP "keri" wrote in message ... I tried what you show below, but can't get it to work. I have a query that currently has [Enter Program Name] in the Criteria field for a field called Program Name Instead of having a free form text field, I want to use a list box so that users can pick from the list. The name of the form is PROGRAM. on that form, I have a combo box that shows the valid values and a command button to run the query... I tried entering what you said all of these ways: Forms![PROGRAM] [Forms]![PROGRAM] Forms![PROGRAM]![Item Number] and nothing will work. I get syntax errors, etc. and can't get the PROGRAM form to display... Help please!!! "Jeff Boyce" wrote: Not a way in a parameter in a query. Is a way, though. Create a form that you'll use to feed the query the parameter. Add a combo box to the form, based on the source of your list. Modify your query to use that form, that combo box as its parameter. Instead of something like: [Please Enter Your Parameter] in the query design, it will look something like: Forms!YourNewForm!YourComboBox Now add a command button to the form to run the query. Here's the sequence of how it would work: open the form (note: the form MUST be opened and the combo box selection made for this to work) select a value from the combo box click the command button Access opens the query and notices that it needs a parameter Access sees that the parameter is pointing at the form's combo box and looks there for a value Access runs the query -- Good luck Jeff Boyce Access MVP "ronoee" wrote in message ... Is ther in the query in Access possible to create a parameter that present you a list in the parameter box popping up, so you don’t have to write the criteria but only to choose it. Anyone who knows how to do this if it is possible, will you then please help me. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |
Clear the "Recent Files" list in the hyperlink window | Mike | Powerpoint | 15 | July 22nd, 2004 02:51 AM |
Parameter query - select from list possible? | George Avery | New Users | 2 | May 12th, 2004 03:55 PM |
Multiple List function | George | Worksheet Functions | 8 | February 15th, 2004 10:13 AM |