Insert Criteria Query

[netrusher]

I have a select query on a form. When I select the query to run I would like for it to require a certain location to be selected as part of the query. I really would like for it to select a dropdown box so the person could select the location from a dropdown box. I have the dropdown box on my form and it is called "LOCATIONS". The dropdown box is tied to a table called
"2-LocationsTbl". Can anyone help me with this.

Summary:

When the query is selected from the form I want to be able to select a location from a dropdown box as part of the query criteria.

Thanks for all help.

 

[Knicks]

Why not set up the query as an parameter query with the field from your form as the necessary parameter. The location field in your query should look something like this

[Forms]![frmWhatever]![combo2]

(its possible depending upon how you have your combo box on the form set up that further tweeks with an ending after [combo2].column(2) or something of that sort depending how data is stored from the lookup. You need to be in synch that New York is New York or 2 is 2 so you get a match, as lookups can be stored with their unique number or text)

Then simply add some code in the Location field on the form, using After Update....to open your query which is bound to that field.

 

[netrusher]

Knicks,

I can do that but when I run the query I want to be able to select the locations from 10 possible locations. They are all part of a combo box on the form.

The Form Name is: 1-StationVerifyFrm
The Combo box name is: Locations
It is linked to a table named: 2-LocationsTbl
The Field name in the 2-LocationsTbl is also called: Locations
Currently there are 10 difference locations available in the 2-LocationsTbl.

Again, what I am trying to achieve is when I run the Query (the query is also part of a Combo Box where multiple query's can be selected to run) I would like to be able to insert a certain location as criteria for the query. I do not want to have to type in one of the locations but would like to be able to select it from a dropdown if possible.

Sorry for bugging you and I sure thank you for any help.

 

[Knicks]

Why can't you have the query use a parameter on the criteria line called

[Forms]![1-stationverifyfrm]![locations]

That is then the combo box on your form, set the after update event of the combo box to something like

DoCmd.OpenQuery "Myquery"

The query then uses whatever you have in the locations combo box to satisfy the query. Like I said earlier you may to set the add a column property to interpret the data entered properly as it is a combo box and data can be stored a couple of different ways.

 

[netrusher]

Knicks,

That works fine but it does not give me what I need. I need to be able to select from up to 10 different locations. This only gives me the location that is on the current form. I need to be able to insert any of the 10 locations and I prefer not to have to type it but select it from the CBOLocations field that is on the form or from the Locations Table where all of the Locations are located.

Thanks for all help!

 

[Knicks]

You are saying that you want to have the ability to use more than 1 location at a time?

You could use a pop up form that is unbound and has 10 fields on it, all 10 being Combo boxes that lookup to your locations table. You'd then want to add a button with the on click event set to open your query. Personally, I'd have it open a pretty form that was linked to said query where you'd gather a bit more control and aesthetics.

You could then use the technique I mentioned prior but string it with multiple OR and just keep adding the combo box fields from the form to your criteria line in the query.

Yeah, you'd have to start typing a letter into the combo box that auto lookups to your location, you'd have to click something somewhere anyway. There is probably some fancy way to do all this in VBA, but I've had great success using an input form that links to the criteria line of a query...

 

[netrusher]

Knicks,

Thanks, I do not want to select multiple locations at one time. Just one location at a time but, any one of the 10 locations that are available and I do not want to have to type in the location but somehow use a CBOlist or whatever will work to input whatever Location into the Criteria Query. Thanks again for your help.

 

[lespaul]

I have a select query on a form

Do you mean that there is a query as the record source of a form or there is a query that is run when something happens on the form?

I can do that but when I run the query I want to be able to select the locations from 10 possible locations

So when this query is selected on the form you then need to open a SECOND form to get the locations that are required for the query you are trying to run?

Does that accurately describe what you are trying to do?











Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[netrusher]

I believe it does!

 

[netrusher]

Yes it does!

 

[netrusher]

As I replied I believe you stated my issue correctly. Now how do I accomplish that? Thanks for your help.

 

[lespaul]

how do you run the query on the form? a button?