Change query criteria with visual basic

[smercer]

Hi all

I was wondering if there is a way to have a query to change criteria as user enters a values in multiple text boxes

Example: user enters a title for book that they are looking for, then enters a surname in different text box. I want the query to add to the criteria of the search to narrow it down using AND criteria when the user enters a value (I can't do it for before user types in each field because it will look for records with null values, or in my case not at all (See attachment). the values in the fields already are so that the query does not show null value field records as well, which will not work with AND criteria)

I have made a query for OR criteria for when the user wants to search for any value in any field and not be fussy. Now I am trying to have a button that will change the subform to a different query using AND criteria.

Am I on the right track about this? Or should I try something else?

I have the option of aborting this if it wont work

Thanks in advance to all

 

[KenHigg]

Could you build the sql string for the query from scratch when the user hit the button?

ken

 

[smercer]

Could you build the sql string for the query from scratch when the user hit the button?

ken

Hi ken

Wish I knew SQL. And I was hoping no one was going to mention SQL. I am just using the standard grid in query design.

But I do know little titbits of it.
1) Would I need to have a nested SQL query?
2) Would I need Union, Pass though, or Data Definition?

I am not sure where to start but I do have some tutorials on SQL on my hard drive that I downloaded that I have been putting off reading.

Thanks for helping Ken, Your help is great!!

 

[KenHigg]

I'll share my trick - Build something real close to what you want in the query builder - then use the sql statement it builds as a starting point to putting together the scratch built stuff.

???
ken

 

[ecniv]

How many records are you planning to use and is it going to be networked?
If its local, for one user, its not too bad a design

I ask cause you've bound the form and if you change the search criteria as the person types.... it'll be requerying a lot

If its on the search button its not too bad. I'd use a list box for display though, subforms are just crap. My opinion.


Vince

 

[Access_Person]

I am attempting something similar to what you are doing (see this thread).

What you could try (no guarantees since I have had no success myself yet) is to add the following code to the text box's on change event (open up properties, click on events, on change, and then go to code builder).

Me.SubFrmName.form.Filter = "[Field you want to search] like [Text Box Where you type in the word]"
Me.SubFrmName.form.FilterOn = True

Good luck!

Edit: Of course notto be requerying as much you can simply add a command button to the form and add the code to its OnClick event instead of to the text box's one.

 

[smercer]

How many records are you planning to use

20,000 records (this is for a second hand bookstore)

and is it going to be networked?
If its local, for one user, its not too bad a design

The user is going to use it on a laptop in the shop so he can take it home and plug it in at home and backup on his home computer each night.

***Edit***
After talking with bookstore owner, He has abandoned the idea of using the Australia wide database, because of broadband costs as well as costs to him for use with the other database.
***End edit***

I ask cause you've bound the form and if you change the search criteria as the person types.... it'll be requerying a lot

I didn't think of that. It better not take ten minutes every time the user presses a key. (perfect guess by the way)

Also the user is going to get a laptop with the minimum requirements for running the database. I'll ask this question even though I know what the answer is going to be: Is this feasible? What would be the minimum requirements for this? the database will grow and so will the requirements of the laptop. Is what I am saying true?

If its on the search button its not too bad. I'd use a list box for display though, subforms are just crap. My opinion.

I hope I do not have to resort to a search button. Something to think about.

Thanks for the help!!