Code help: Automate Select Qry with variable criteria inputted from a form

[Ace2014]

Hi Everyone,

So my first experience in an Access Forum was amazing!

I'm still a VBA beginning, but I'm developing a few muscles lol if you know what I mean.

So here's my next task.

Objective:
-Accessing the information relevant to what they're searching for.
-Automating the select query with varying criteria process



I have a huge query linking various tables together. It's purpose is to segment a market data table.

The problem I have is a I have non-experienced Access users who will need to use the following function:

I want them to input the criteria they're looking for into a form with the 14 variables (that's the easy part)
They would then click a button.
The button would update the select query with the various criteria the users will be desiring.

I'm not really quite sure how to do this.
-Have a table, update said table with criteria, link it to select query? - What would connect the 2, do I need to connect the 2?

Please help

[orange]

Be cautious with the word "Update" in database related writing.
I think, and I may be missing your intent, that you want to modify your SELECT (Read Only) query to include the criteria entered by the user. These criteria specify which records/data to be returned by the Select query.

a huge query linking various tables together

Do you have a clear statement of WHAT you are trying to do?
Do you have a data model? Have you Normalized your data/tables?

[ranman256]

Depends.....
if you are asking the user to enter States he wants, yes, they would be entered into a table and that table would join to your query and only those states would retrieve.
BUT
if you are asking MANY different criteria
States, age, city, etc. THAT becomes tricky.

How many different criteria are you wanting>?

[Ace2014]

I have 14 different criteria at the moment

[ranman256]

Then you'd have to make your own WHERE builder.
theyd click available fields and enter their text.
(the conjunctions can be tricky)


tCriteria
[fld], [text] , [conj]

Name, "Smith"
State, "CA"
state, "KS"

once entred, you would construct the WHERE clause in the SQL statment.

[Ace2014]

Do you think there's an easier way to do it?

[orange]

What ranman has given is the common way to build a WHERE clause using vba. What you need may be easier, but we really don't know any detail about what you have; the user's knowledge of Access/requirements and familiarity with the application.
Some people build a number of queries and provide some means (a form with a list box) to let the user select the query to be executed.
Some people build a custom function to build the WHERE clause and to prompt the user for each criterion, validate the entry and continue. It depends on how sophisticated the developer, the user and the importance of errors in the query.Real easy to use interfaces often have had a lot of analysis, design and testing to make them "simple" to use.

see this for more details and samples: http://www.fontstuff.com/Access/acctut17.htm

[June7]

And more references:

http://www.datapigtechnologies.com/f...tomfilter.html

http://www.allenbrowne.com/ser-62code.html

[Ace2014]

Be cautious with the word "Update" in database related writing.
I think, and I may be missing your intent, that you want to modify your SELECT (Read Only) query to include the criteria entered by the user. These criteria specify which records/data to be returned by the Select query.

Do you have a clear statement of WHAT you are trying to do?
Do you have a data model? Have you Normalized your data/tables?

Sorry, I just saw this quote.

My entire objective is to build a knowledge database that can be added to, who's data can be easily limited to the scope the user would like to use, and then easily exported.
This means
-who's various tables can be uploaded by the click of a button and the selection of an Excel template - DONE
-a way for the users to input the scope of the data they'd like to demand from the knowledge database

Database description
Market Data that is relational to the client's segmentation it was researched for
The client segmentation then needs to be relational from one client to another so there is another segmentation relating client to client.
I have a market data table and then 4 tables segmenting the information within it.
I've created a query connecting all the information to get a super big database

Problematics
-essentially, all my tables have columns that essentially can have unlimited records
(on a form, pull down-boxes would be difficult due to the number of choices. Then, if the user is to type in their criteria, they could always make numerous mistakes)
-I also have 14 different variables they'd be interested in using.

User Knowledge
-Most are terrified of Access
-Some are excited/interested, still don't know anything

My "developer" knowledge
-Very beginning in VBA, but learning quickly and I have access to Lynda.com training videos, really really helpful!!, and naturally, you guys
-I know probably Access basic capabilities: developing and creating various queries, creating and linking tables, creating reports with a focus on formatting, etc
-I took a course about database construction on Lynda.com and it was amazing, so I've been following the normalization principals and how to manage relational data.

Possible solutions
-I'm wondering if just teaching them how to type in criteria into a select query would be easier.
-Maybe multiple queries asking for specific criteria, but then I'd have to be sure that if they don't want all the criteria, they have a way to skip it.
-I was thinking a form that would create a new table and then link to my query, but that seems to be harder than forseen

Here are some pictures, I'm not sure if it'll help

[Ace2014]

And more references:

http://www.datapigtechnologies.com/f...tomfilter.html

http://www.allenbrowne.com/ser-62code.html

Wow, those are incredible!! Thanks I'll see what I can do with them

[orange]

I would not recommend the Form you have shown.
My rationale:
The control names are cryptic (L1,L2..) Make these meaningful to the user (any level user - new..experienced)
You are depending on user input ( spelling variations, typos, terminology...) Make a list of known drop downs that can be selected. Will remove/reduce vetting/validation and eliminate typos. Suggest you get potential users to suggest the various data for drop downs; Use cascading combos if it helps focus the user selection(s).
Encourage users to suggest improvements/changes to drop down lists as experience is gained; you could do some analysis as to whether users are finding the "answers/details" they were searching for ---monitor and make changes to improve the functionality.
The more you can direct/suggest the user through your "system", the less problems for the user, but more design effort and testing on your part.

What do the prefixes on your proposed tables represent? I don't think these are necessary. They suggest to me that they are somehow sequential or process related.

I recommend you make a data model. Gather some test data to ensure your model properly represents your "business". Then, I'd mock up some forms to test and get feedback on interface options. I prefer to do some sample forms (even with fixed messages) to see user reactions/requests etc. The system must do what you need, but it must be useful and accepted by the users.

Good luck with your project.

[rpeare]

In addition to what orange has stated, you are going to face the AND or OR discussion as well, perhaps your user wants to view any record that matches L1 OR L2, perhaps they want the records that match L1 AND L2. Orange gave you some excellent advice about the process and development, pay particular heed to the combo boxes for selecting data rather than allowing people to type in anything, it will cut down on your erroneous results dramatically.

[Ace2014]

Great, thanks guys! This will most certainly be interesting!