Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Making a drop-down query criteria 3
- Thread starter ehsguy77
- Start date
-
2
- #2
ehsguy,
Sure, in your query, just say:
SELECT blah, blah WHERE <fieldname> = '" & forms!<formname>.<comboboxname> & "';"
The single quotes are necessary for a text criterion, but not for a numeric one.
Write back if you have problems with how to load up the combo box.
Tranman
Sure, in your query, just say:
SELECT blah, blah WHERE <fieldname> = '" & forms!<formname>.<comboboxname> & "';"
The single quotes are necessary for a text criterion, but not for a numeric one.
Write back if you have problems with how to load up the combo box.
Tranman
- Thread starter
- #3
I'm trying to write this query from the query design view. Does the Select query have to be written in the properties of a form? I'm not sure how to make a select query in the query design window, especially with the whole "Select blah, blah", because I just dragged the blah stuff into a query column.
- Thread starter
- #4
ehsguy,
Let's back up a minute.
When you said, "make a drop-down of values available as query criteria", did you mean that you want to have a drop-down that, when you select a value, restricts the values you can see on the same form/navigates the form to a specific record? Or, did you mean that you have some query that does something else entirely, unrelated to the form, and that you just want to have a list of values to pick from for the criteria of that query?(This is what I assumed you wanted.)
In the first case, we're talking about either a filter on the form's data or record navigation, and in the second, we're just talking about a query that uses a field on a form for the "where" clause.
Just FYI: When you use the query design grid, the whole "Select blah blah" stuff is created in the background every time. You just don't see it unless you change your query from design view to SQL view. As to whether you have to type the SQL in the properties of the form, instead of using the query design grid, the answer is no.
To create or modify a query upon which the form will be based, just open the properties window of the form, go to the "Data" tab, and click on the words, "Record Source". You will see a drop-down and an elipse (...) appear over on the right side. If you click the drop-down arrow, a list of all existing tables and queries will appear. If you want your form to be based on an existing table or query, just select the one you want.
If you want to create a new query (SQL statement) to serve as the basis of the form, just click on the elipse. A show table form will open, which shows all of the existing tables and queries (same info as in the drop-down). If you then click the close button on the show table form, a blank query design grid will appear, and you can use that to create the query you want. When you click the "X" (close form button up in the upper right corner), it will ask you if you want to save the changes you made. If you say "yes", the "Select blah blah" statement will be created in the "Record Source" box of your properties window. Neither way is significantly better than the other-having a SQL statement in the "Record Source" is really no better or worse that basing your form on an existing table or query.
Here again, this is probably not what you want to do, if you are just trying to pick a row for the form to display by using a drop-down.
I'm a bit uncertain exactly what you want to accomplish, but if you write back with a few more specifics, I'll be glad to explain how to accomplish what you want.
Tranman
Let's back up a minute.
When you said, "make a drop-down of values available as query criteria", did you mean that you want to have a drop-down that, when you select a value, restricts the values you can see on the same form/navigates the form to a specific record? Or, did you mean that you have some query that does something else entirely, unrelated to the form, and that you just want to have a list of values to pick from for the criteria of that query?(This is what I assumed you wanted.)
In the first case, we're talking about either a filter on the form's data or record navigation, and in the second, we're just talking about a query that uses a field on a form for the "where" clause.
Just FYI: When you use the query design grid, the whole "Select blah blah" stuff is created in the background every time. You just don't see it unless you change your query from design view to SQL view. As to whether you have to type the SQL in the properties of the form, instead of using the query design grid, the answer is no.
To create or modify a query upon which the form will be based, just open the properties window of the form, go to the "Data" tab, and click on the words, "Record Source". You will see a drop-down and an elipse (...) appear over on the right side. If you click the drop-down arrow, a list of all existing tables and queries will appear. If you want your form to be based on an existing table or query, just select the one you want.
If you want to create a new query (SQL statement) to serve as the basis of the form, just click on the elipse. A show table form will open, which shows all of the existing tables and queries (same info as in the drop-down). If you then click the close button on the show table form, a blank query design grid will appear, and you can use that to create the query you want. When you click the "X" (close form button up in the upper right corner), it will ask you if you want to save the changes you made. If you say "yes", the "Select blah blah" statement will be created in the "Record Source" box of your properties window. Neither way is significantly better than the other-having a SQL statement in the "Record Source" is really no better or worse that basing your form on an existing table or query.
Here again, this is probably not what you want to do, if you are just trying to pick a row for the form to display by using a drop-down.
I'm a bit uncertain exactly what you want to accomplish, but if you write back with a few more specifics, I'll be glad to explain how to accomplish what you want.
Tranman
How do I do this?"
You have to create a combo box on the form. Open the form in design view and go to View|Toolbox. First, verify that the "Wizard" button on the Controls Toolbox is selected. (It looks like a magician's wand.) Then find the Combo Box control in the toolbox and click on it, then draw it on the form. Once it's there, a wizard dialog will open, and ask you about where you want the combo to get its data(if you pick "I'll type it", you will hard code a list of values for the combo box. If you say "Table or Query", it will ask you which one, then which specific field from that table or query.
Then you'll have an opportunity to resize the combo.
Then it will ask you if it should remember your choice, or store it in a field. You want to remember it.
You have to create a combo box on the form. Open the form in design view and go to View|Toolbox. First, verify that the "Wizard" button on the Controls Toolbox is selected. (It looks like a magician's wand.) Then find the Combo Box control in the toolbox and click on it, then draw it on the form. Once it's there, a wizard dialog will open, and ask you about where you want the combo to get its data(if you pick "I'll type it", you will hard code a list of values for the combo box. If you say "Table or Query", it will ask you which one, then which specific field from that table or query.
Then you'll have an opportunity to resize the combo.
Then it will ask you if it should remember your choice, or store it in a field. You want to remember it.
How do I do this?"
You have to create a combo box on the form. Open the form in design view and go to View|Toolbox. First, verify that the "Wizard" button on the Controls Toolbox is selected. (It looks like a magician's wand.) Then find the Combo Box control in the toolbox and click on it, then draw it on the form. Once it's there, a wizard dialog will open, and ask you about where you want the combo to get its data(if you pick "I'll type it", you will hard code a list of values for the combo box. If you say "Table or Query", it will ask you which one, then which specific field from that table or query.
Then you'll have an opportunity to resize the combo.
Then it will ask you if it should remember your choice, or store it in a field. You want to remember it.
Sorry-I just hit submit post by mistake.
Then it will ask you for a name. Call it "WasteDesc" (your name from above.
Then on the Data tab, you want to toggle the "Limit to List" property to "Yes", so your users can't add their own values to the combo, and must choose from the list you created.
Play with this, and then let me know where you're at.
Tranman
You have to create a combo box on the form. Open the form in design view and go to View|Toolbox. First, verify that the "Wizard" button on the Controls Toolbox is selected. (It looks like a magician's wand.) Then find the Combo Box control in the toolbox and click on it, then draw it on the form. Once it's there, a wizard dialog will open, and ask you about where you want the combo to get its data(if you pick "I'll type it", you will hard code a list of values for the combo box. If you say "Table or Query", it will ask you which one, then which specific field from that table or query.
Then you'll have an opportunity to resize the combo.
Then it will ask you if it should remember your choice, or store it in a field. You want to remember it.
Sorry-I just hit submit post by mistake.
Then it will ask you for a name. Call it "WasteDesc" (your name from above.
Then on the Data tab, you want to toggle the "Limit to List" property to "Yes", so your users can't add their own values to the combo, and must choose from the list you created.
Play with this, and then let me know where you're at.
Tranman
- Thread starter
- #8
Okay - I appreciate your patience. I understand everything you've posted above. I suppose I'm an intermediate Access user by now. I'm aware of ways to make forms based on select or design view queries.
The end product of what I'm trying to do is this: I want a user to hit a command button from a switchboard that invokes a query. I want them to be able to select query criteria from a drop down menu - not from a form, but in a query criteria...er, well I guess it is a form of sorts.
The list of options that I want to use is in a form, and is a value list. The value list is in the 'form!frmPSWwasteItems.ItemDesc' control box.
I don't know if any of this helps clarify what I'm trying to do, but thanks for helping!
The end product of what I'm trying to do is this: I want a user to hit a command button from a switchboard that invokes a query. I want them to be able to select query criteria from a drop down menu - not from a form, but in a query criteria...er, well I guess it is a form of sorts.
The list of options that I want to use is in a form, and is a value list. The value list is in the 'form!frmPSWwasteItems.ItemDesc' control box.
I don't know if any of this helps clarify what I'm trying to do, but thanks for helping!
I think I see what you mean now. What I would probably do is have the switchboard open a form with the combobox on it, then either in the afterupdate event of the combo, or more likely, in the click event of a commandbutton, I'd have code like this:
public sub <whichever event you choose>()
On Error GoTo Err_Command27_Click
Dim stDocName As String
stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click
End Sub
Then in the query itself, I'd have criteria like this:
[forms]![<formname>].[<comboname>]
I don't think you'll have much luck opening a form from the running of a query. At least, in the past, things I've tried, like having the query kick off a function, and having the function open a form, have not worked.
Let me know how it's going.
Tranman
public sub <whichever event you choose>()
On Error GoTo Err_Command27_Click
Dim stDocName As String
stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click
End Sub
Then in the query itself, I'd have criteria like this:
[forms]![<formname>].[<comboname>]
I don't think you'll have much luck opening a form from the running of a query. At least, in the past, things I've tried, like having the query kick off a function, and having the function open a form, have not worked.
Let me know how it's going.
Tranman
Thanks for the post (directly above)...
I'm doing the exact same thing... allowing users to set the criteria of a query using combo boxes placed on a form - HOWEVER what about if the user decides that they wanna see all records (ie without criteria), can I modify the combo somehow to give the user an 'All' option??
Anyone have any ideas??
I'm doing the exact same thing... allowing users to set the criteria of a query using combo boxes placed on a form - HOWEVER what about if the user decides that they wanna see all records (ie without criteria), can I modify the combo somehow to give the user an 'All' option??
Anyone have any ideas??
Have a look at
This is a FANTASTIC site for Access and there is an article explaining exactly that. Do a search for 'combo'.
This is a FANTASTIC site for Access and there is an article explaining exactly that. Do a search for 'combo'.
Hi
This thread is exactly what I was looking for .... almost. I have a query that is the basis for a report. It is for the configuration management group. The report is to status changes regarding the many different programs/projects. In the query I have a "Like[Enter program name]' statement. This calls up a little box asking for the Program Manager to enter the name (code) for his/her program. However, if they use "TD" instead of "TDS' they get a blank report. I would like to be able to include a drop down box in place of the "[Enter Program Name]" that lists the proper codes to use. Anybody have any ideas?
This thread is exactly what I was looking for .... almost. I have a query that is the basis for a report. It is for the configuration management group. The report is to status changes regarding the many different programs/projects. In the query I have a "Like[Enter program name]' statement. This calls up a little box asking for the Program Manager to enter the name (code) for his/her program. However, if they use "TD" instead of "TDS' they get a blank report. I would like to be able to include a drop down box in place of the "[Enter Program Name]" that lists the proper codes to use. Anybody have any ideas?
Yas
Instead of "Like[Enter program name]" use "Like {name of a combo box control on a form] then create the combo box control either on an existing form or a little popup stand-alone form that can go invisible once the code is selected (it has to stay loaded so the control remains available for the query parameter).
Fill the combo box with either a list of values by hand or (better) from a table or query that has the correct codes. Set limit to list at yes. You would launch the query (DoCmd OpenQuery)
There are other refinements but this will do the basics of what you want.
Instead of "Like[Enter program name]" use "Like {name of a combo box control on a form] then create the combo box control either on an existing form or a little popup stand-alone form that can go invisible once the code is selected (it has to stay loaded so the control remains available for the query parameter).
Fill the combo box with either a list of values by hand or (better) from a table or query that has the correct codes. Set limit to list at yes. You would launch the query (DoCmd OpenQuery)
There are other refinements but this will do the basics of what you want.
Hi guys,
I played with this a little bit after working with ehsguy. You *can* set the criterion to a function, then have the function open a form with a combo box on it. When the user selects a value from the combo, you set a global variable to the selected value, close the form, then at the end of the function, set the function to the value of the global variable.
Remember to pass an argument to the function, even if it's just a string literal.
I know, I know...but it really does work. The variable that stores the criterion has to be a global variable because the "selection form"(the one with the combo box) does not have access to the variables in the function.
Tranman
I played with this a little bit after working with ehsguy. You *can* set the criterion to a function, then have the function open a form with a combo box on it. When the user selects a value from the combo, you set a global variable to the selected value, close the form, then at the end of the function, set the function to the value of the global variable.
Remember to pass an argument to the function, even if it's just a string literal.
I know, I know...but it really does work. The variable that stores the criterion has to be a global variable because the "selection form"(the one with the combo box) does not have access to the variables in the function.
Tranman
OK
Here goes the confession ... I need the "for Dummies" version of the reply from ekim.
I tried this but it did not work. I changed the Like [Enter....] to Like [programs] (programs being the name of the combo box where the info is stored.) This is a combo box created from the 'programs' table on the data entry form called 'ECRTable'. But I do not understand the rest of it. Where do I set the limit to list at yes. And where do I launch the DoCmd OpenQuery from? Remember, this is a query itself, not a form and the report is based on the query.
Thanks for all your help.
Sue
![[hairpull]](/data/assets/smilies/hairpull.gif "[hairpull] [hairpull]")
Here goes the confession ... I need the "for Dummies" version of the reply from ekim.
I tried this but it did not work. I changed the Like [Enter....] to Like [programs] (programs being the name of the combo box where the info is stored.) This is a combo box created from the 'programs' table on the data entry form called 'ECRTable'. But I do not understand the rest of it. Where do I set the limit to list at yes. And where do I launch the DoCmd OpenQuery from? Remember, this is a query itself, not a form and the report is based on the query.
Thanks for all your help.
Sue
It can be a bit confusiing. I'm no expert but I have done this a few times exactly what you are trying to do...
Given that you are only selecting one value to use in the criterion you would reference the full name of the combo control -- [Forms]![your form name that has the combobox]![ComboboxName]. If you want to have multiple selections it is a bit more complicated and a different strategy as I have recently found out.
The Limit To List is one of the properties of the combo box -- go into design view and select the combo box and right click to get to the properties sheet. Limit to List is on the Data tab.
On the Events tab you would create an event proceedure in the After Update Event for the DoCmd.OpenQuery. The query will launch as soon as someone selects a new value in the combo box.
Hope this helps
Given that you are only selecting one value to use in the criterion you would reference the full name of the combo control -- [Forms]![your form name that has the combobox]![ComboboxName]. If you want to have multiple selections it is a bit more complicated and a different strategy as I have recently found out.
The Limit To List is one of the properties of the combo box -- go into design view and select the combo box and right click to get to the properties sheet. Limit to List is on the Data tab.
On the Events tab you would create an event proceedure in the After Update Event for the DoCmd.OpenQuery. The query will launch as soon as someone selects a new value in the combo box.
Hope this helps
Or since you said it was a report you could also lauch the report since the underlying query is already now filtered by your combo box value. Or just filter the report with the same value
The help file on DoCmd Open Report has some info:
OpenReport Method Example
The following example prints Sales Report while using the existing query Report Filter:
DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"
OpenReport Method
The OpenReport method carries out the OpenReport action in Visual Basic. For more information on how the action and its arguments work, see the action topic.
Syntax
DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]
The OpenReport method has the following arguments.
Argument Description
reportname A string expression that's the valid name of a report in the current database.
If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name first in the library database, then in the current database.
view One of the following intrinsic constants:
acViewDesign
acViewNormal (default)
acViewPreview
acViewNormal prints the report immediately. If you leave this argument blank, the default constant (acViewNormal) is assumed.
filtername A string expression that's the valid name of a query in the current database.
wherecondition A string expression that's a valid SQL WHERE clause without the word WHERE.
Remarks
The maximum length of the wherecondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).
You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify.
The help file on DoCmd Open Report has some info:
OpenReport Method Example
The following example prints Sales Report while using the existing query Report Filter:
DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"
OpenReport Method
The OpenReport method carries out the OpenReport action in Visual Basic. For more information on how the action and its arguments work, see the action topic.
Syntax
DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]
The OpenReport method has the following arguments.
Argument Description
reportname A string expression that's the valid name of a report in the current database.
If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name first in the library database, then in the current database.
view One of the following intrinsic constants:
acViewDesign
acViewNormal (default)
acViewPreview
acViewNormal prints the report immediately. If you leave this argument blank, the default constant (acViewNormal) is assumed.
filtername A string expression that's the valid name of a query in the current database.
wherecondition A string expression that's a valid SQL WHERE clause without the word WHERE.
Remarks
The maximum length of the wherecondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).
You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify.
This thread is exactly what I needed, and I am close but am having some problems getting this to work.
I just need, inestead of the option to enter the parameter (the fund type), a drop down box that lists the fund types in the funds table. I created a combo box (Funds1)on form Funds, and went to my query and placed in the criteria for funds the following:
Where is my mistake? I keep getting errors from Access.
I just need, inestead of the option to enter the parameter (the fund type), a drop down box that lists the fund types in the funds table. I created a combo box (Funds1)on form Funds, and went to my query and placed in the criteria for funds the following:
Code:
[SELECT Funds, Funds1 WHERE <Funds> = '" & forms!<Funds>.<Funds1> & "';"]Where is my mistake? I keep getting errors from Access.
-
1
- #20
- Status
Similar threads
- Locked
- Question
- Locked
- Question