Code to modify query

[George Too]

Hi all,

In a query, a field has a criteria based on a combo box in a form; when the user selects an item on the list, the code in the after update procedure runs the query.

I need code that makes the query display results that are not identical to the item selected from the combo box. For example, if the item selected in the combo box is "Animals", then the query will display whatever animals there are in that particular culumn.

Can this be done? If not, what's a different approach?

 

[chrismcbride]

Should be as simple as changing the criteria expression from
= [whatever]

to

Not Like [whatever] - or <> [whatever]

No?

 

[George Too]

I'm not sure that approach will work the way I want it to. I believe that will require me to update the criteria manually or changing it with code which brings me back to the original question.

Thanks though

Anymore ideas anyone?

 

[WayneRyan]

George,

Try the following in design view or your query:

<>[Forms]![YourForm]![YourCombo]

hth,
Wayne

 

[George Too]

Yes, WayneRyan, my query works just fine the way you have it. The problem that I'm trying to solve is how do I change the query's criteria thru code so that it looks for values other than the one selected in the combo box.

I know that there is SQL code out there that can do that but I just dont know what it looks like...

Any more ideas?

Thanks

 

[WayneRyan]

George,

I don't understand, the criteria does change depending
on the contents of the combo box.

Need more info.

 

[George Too]

Ok, here is an example: If in the combo box, the user selects the item "cars", then the code will have the query look not for the wodr "cars" but for car models. If I was to use this "<>[Forms]![YourForm]![YourCombo]" then the query looks for the word "cars" and then it returns no values.

Is it a bit clearer now? Do you think that I have to take a different approach?

Thx

 

[simongallop]

Think that we are all confused here! Looking through this post I have come up with the following possible scenarios. Which, if any, are you trying to use?

For all of the scenarios a user selects an item from a combo box eg "CARS"

1/ You want the query to return all records which have CARS in a specified field.

2/ You want the query to return all records that DO NOT have CARS in the specified field

3/ You want the query to return all records that contain makes of cars.

Any other options?

 

[PaulSpell]

Ok, here is an example: If in the combo box, the user selects the item "cars", then the code will have the query look not for the wodr "cars" but for car models. If I was to use this "<>[Forms]![YourForm]![YourCombo]" then the query looks for the word "cars" and then it returns no values.

I'm as confused as everyone else here, but I'll have a go!

Is what you are saying that you have a table containing models of cars say:

VW Beetle
Ford Escort
Robin Reliant

And you want to select the category "cars" from a combo and have the query return the above list?

 

[Rich]

You need two tables to do this on a one to many basis, one which lists the Cat. and the second that lists all the items for the Cat.

 

[George Too]

You guys are amazin! Look at you, trying to make sense of my mess...

Harry, it's option 3: I want the query to return all records that contain makes of cars.

PaulSpell, that is exactly what I want.

Now, Rich's suggestion is something I was trying to do but could not work it the way I wanted to. I know it goes somewhere along those lines, and was thinking that maybe with code would be easier. If you guys can point to some samples. I think I saw a sample in the northwind database but I can't find it now.

Thank guys, you are the best!

 

[simongallop]

To be able to do it then where you store the make of cars in the same line of a record there must be something that says it is a car.

Depending on how your data is set up, then there are different ways to get the data. Am presuming that in your combobox you have choices of categories such as CARS, TRUCKS, BIKES etc...

Which of the following do you have?

1/ One table: Fields:
Type Make Model
Car Ford Escort
Bike Norton .....

2/ Two Tables:
First Table:
ID_Type, Description
1 Car
2 Truck
3 Bike

Second Table:
ID_Type, Make, Model
1 Ford Escort
3 Norton ........

3/ One table:
Make Model
Ford Escort
Norton .........

Which version do you use or do you have a totaly different table structure such as table cars, table bikes, table trucks?

 

[George Too]

I have 2 tables as follows:

Table 1: Models (with other fields in the same table)

Table 2: Makes (with other fields in same table)

I want to select a make and then have the query return all the models in the other table. I already tried the sample in the Northwind DB but that form requieres two imputs from the user. The make and the model. I just want the user to select the make.

Any other help will be much appreciated.

 

[simongallop]

OK! In your query design, show the 2 tables and link on the relevent field. Select the fields that you want to show and for the make set the criteria to be: [Forms]![YourFormName]![ComboboxName]

HTH

 

[George Too]

That's exactly how I have it but the query is not pulling the data the way I want it. Don't worry though, the project has been scrapped. They are moving to a different database.

Thanks to all.