Use a form to run multiple queries.

[pikkhuanloy]

Hi all the Access experts,

As title tells, I would like to use a form to run multiple queries.
It's just that I want the form to be "flexible" (sorry for my bad english, i couldn't think of another word to express).

Here is my idea.

I have created multiple queries saved in the queries list.
And the end users need to select which queries they want. For example,
"Step 1 - Age (Annual Mode)
Step 1 - Age (Monthly Mode)
Step 2 - Premium (Annually)
Step 2 - Premium (Monthly)"

Now, let's say the user wanna choose Annually Method.
So, he will choose to run Step 1 - Age (Annual Mode) & Step 2 - Premium (Annually)
and delete the other 2.
Of course, you may say "Why don't you just double click?"
I don't do so because there are like 10+ Steps to do.

So i need the form to run these queries 1 by 1 according to the sequence.
And it needs to be flexible as the users may choose different combinations of steps to run.
Is it a way that i could use the form to run these queries?

Thank you in advance.

 

[llkhoutx]

Use a combo box by which the user select the desired query and on the combo bo AfterUpdate event, execute the appropriate query using a case statement.

 

[pikkhuanloy]

Hi

THanks for your reply.
Is it possible just to use a button instead of combo box?

 

[Galaxiom]

Is it possible just to use a button instead of combo box?

Of course. One event is the same as any other to run a process.

The more important factor is the selection. You have implied you want to use the multiselect capabilities of the listbox. Aside from setting the properties to allow one of two different types of multiselection, this means you must loop through the ItemsSelected Collection to process the list of records from the RowSource that have been selected.

You will need to read and understand the Listbox object model in multiselect mode. If you have only just started coding it will be a steep learning curve.

 

[pikkhuanloy]

Of course. One event is the same as any other to run a process.

The more important factor is the selection. You have implied you want to use the multiselect capabilities of the listbox. Aside from setting the properties to allow one of two different types of multiselection, this means you must loop through the ItemsSelected Collection to process the list of records from the RowSource that have been selected.

You will need to read and understand the Listbox object model in multiselect mode. If you have only just started coding it will be a steep learning curve.

Hi Galaxim,

Thanks for your reply.

Sorry for my poor english, I don't really get you. Is the Listbox you mentioned a kind of form in Access? Attached image is showing the queries i have done. I am wondering if i could create a form button only with VB Script that run all the Queries in the queries list without knowing the queries names. Is it possible?

 

[pikkhuanloy]

Anyone can help?

 

[plog]

I can help, but not with your question. My advice is to redesign your process.

Everytime I see action queries (MAKE TABLE, UPDATE, APPEND, etc.) my first thought is that whoever designed it has a poor grasp of how databases can work. My guess is you can either roll this process into a series of subqueries where the final SELECT query gives you the data you want without going through a process which requires such heavy user oversight.

Even if that's not the case, I am certain you can roll these action queries up into a process that is controlled by VBA. So instead of them selecting each step to run they just choose 'Report 1' and a table holds what queries to run in what order for 'Report 1'.

Again, though, highly skeptical of the need for all your action queries.