Macro code: Automate Select Query Sequence

[Ace2014]

Hi Everyone,



So after the posts from my previous postCode help: Automate Select Qry with variable criteria inputted from a form

I was feeling a little overwhelmed and down. But I've kept brainstorming processes I thought would work and be easy to use for Accessphobes lol

I've decided on a new process. I'm looking for feedback as to if you think it'll work.
I'm still lacking the coding experience to get it started.


Objective:
Accessing scope of data users are interested in extracting from a knowledge database (data mining interface)

Process:
Automation of Select Queries for users with little knowledge of Access

Defining scope through by the segmentation:
This segmentation combines relational data

Select Level 1 Data: form with a drop down box
Run Select Query
(I could have a table, "Level1" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the market data query. 
They would be linked 1 to many so when the select query ran, it would produce only the possible Level 2 options) 

Prompt to select Level 2 Data: form with a drop down offering only Level 2 options in the Level 1 previously selected 
Run Select Query
(I could have a table, "Level2" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the "Level 1" query. 
They would be linked 1 to many so when the select query ran, it would produce only the possible Level 3 options) 

Prompt to select Level 3 Data: form with a drop down offering only Level 3 options based off previous selections 
Run Select Query
(I could have a table, "Level 3" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the "Level 2" query. 
They would be linked 1 to many so when the select query ran, it would produce only the data on the same finest level) 


Prompt to select Dates no older than X in format "mmm/year"
or bypass to next criteria 
or go directly to export data to excel (.xls or .xlsx)
Run Select Query/Proceed to next criteria/Export
(I could have a table, "Date" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the "Level 3" query. 
They would be linked 1 to many so when the select query ran, it would produce only the data on the same level with dates greater than X) 


Prompt to select multiple datatypes in another criteria level (named Brand) from a drop down that only allows choices based off the previous selections
or bypass to next criteria 
or go directly to export data to excel (.xls or .xlsx)
Run Select Query/Proceed to next criteria/Export
(I could have a table, "Date" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the "Date" query or Level 3 if Date was bypassed
They would be linked 1 to many so when the select query ran, it would produce only the data on the same level with dates greater than X and specific Brands) 


Prompt to select multiple datatypes in another criteria level (named Market) from a drop down that only allows choices based off the previous selections
or bypass to next criteria 
or go directly to export data to excel (.xls or .xlsx)
Run Select Query/Proceed to next criteria/Export
(I could have a table, "Brand" and it gets filled with the criteria. 
There would be a select query also pre-made with this table linked to the "Brand" query, Date, or Level 3 depending on which data were bypassed, if bypassed
They would be linked 1 to many so when the select query ran, it would produce only the data on the same level with dates greater than X, specific Brands, and specific markets) 


After Export, clear tables

I hope this will be possible!
Thanks in advance

[aytee111]

The replies to your previous thread holds all the answers, although it seems you need help to get started. This would be the VBA code required to build the SQL for the query.

First, create the query in the usual way and fill in criteria as if each field on the form has an entry.
Copy the SQL into VBA to use as your model (comment it out).
In VBA, build the SQL using lots of "IF" statements such as IF L1<>"" then strSQL=strSQL & " AND L1='" & me!L1 & "'", etc - using the model as your basis.
Once you have built the SQL string, use Debug.Print to display it, then copy it to an empty query and try to run it, that way you can get it right throught trial and error.

[Ace2014]

The replies to your previous thread holds all the answers, although it seems you need help to get started. This would be the VBA code required to build the SQL for the query.

First, create the query in the usual way and fill in criteria as if each field on the form has an entry.
Copy the SQL into VBA to use as your model (comment it out).
In VBA, build the SQL using lots of "IF" statements such as IF L1<>"" then strSQL=strSQL & " AND L1='" & me!L1 & "'", etc - using the model as your basis.
Once you have built the SQL string, use Debug.Print to display it, then copy it to an empty query and try to run it, that way you can get it right throught trial and error.

Great, thank you, that will definitely come in handy once I get started.