Running queries in a sequence based on form - VBA

[JonAccess]

Let's say I have 10 saved update queries that I use to manipulate imported data. These queries update a "Cost" field in order of the quality of costs I have available. The query logic is as follows:

Update "Cost" if i have a cost in "Cost1"
If "Cost" is still empty, Update using "Cost2"
If "Cost" is still empty, Update using "Cost3"
...
" " "Cost 10"

BUT, "Cost1" isn't always the cost I want to use first!
Sometimes I need to say...

Update "Cost" if i have a cost in "Cost4"
If "Cost" is still empty, Update using "Cost2"
If "Cost" is still empty, Update using "Cost8"
...
" " "Cost 3"

What is the most simple/best method to use in a situation when I need to change the order of queries being run in VBA?

I use the method...

db.execute "qry_Cost1", dbFailOnError
db.execute "qry_Cost2", dbFailOnError
etc...

 

[JonAccess]

Maybe I should also say that I'm wanting to decide the sequence of the queries to run in VBA by selecting the order via a form. Ex. Putting a "1" beside the query i want to run first, etc.? I know there has to be a better way!

 

[HGMonaro]

your VBA code needs to be able to decipher the order of importance of what's supplied or selected, and that means you need to be able to have a rules (algorithm) that covers all possible scenarios.

If you're manually deciding what to run, then assigning numbers to each option then sorting that list and running in order would be a possibilty. If it was blank you'd ignore it (don't run that query)

 

[JonAccess]

I imagine that sorting the order list on a form is the way i may go. Is there a way i can do this with a listbox or something? Ex. Have a listbox with "Cost1", "Cost2", etc. and be able to drag and drop each cost into the order i want? And then in VBA be able to say "Run query for 1st item in list box, 2nd item, etc.?

 

[HGMonaro]

not sure about drag'n'dropping to rearrange, never seen or tried that but you could add them to another listbox then process that as a list (probably easier than numbering items as you'd then have to check for duplicates, etc)

 

[speakers_86]

You could use combo boxes to select the order of precedence. If you have 5 queries, then you could create 5 combo boxes, and each control has the options, 1st, 2nd, 3rd...Then there would be a submit button that carries out the queries in the given order.

edit-Better yet, you could make the drop down options to be the titles of your queries, with the respective labels being 1st, 2nd, 3rd...and if you wanted put in enough effort you could make the combo boxes cascade.

 

[MarkK]

What is the SQL text of a typical query you have there? I would most likely re-write the query SQL in code so that I can produce exactly the query I need rather than select one from a list.

Whats the logic you use to decide what should run in what order?

 

[JonAccess]

Good ideas. I think I'm going to try to use a listbox with the options being the name of my saved queries. Might add a "Move up"/"Move down" button to the listbox to help set the order of queries to execute. then run a loop with ElseIfs or Select Cases to run the queries in the order selected. Sound OK? Or is there something better out there?

 

[JonAccess]

lagbolt -

I don't have the SQL handy at the moment, but it would accomplish something of this sort...

UPDATE [Use_Cost] WHERE [Cost1] Is Not Null
UPDATE [Use_Cost] WHERE [Cost2] Is Not Null; [Use_Cost] Is Null
etc..

The logic is that in some parts of the world, Cost1 will be more accurate, therefor making it the primary cost to use (if Cost1 has a cost). Cost2 is second most accurate, therefor we want to use Cost2 where there was no Cost1. etc..
In other parts of the world, Cost6 is the most accurate cost, so it gets the first shot at using it's cost.

That is why the order changes... geographical reasons.

Make any more sense?