Code for Opening Query Based on User Criteria

[mikeTTI]

I would appreciate some comment on a solution I have come up with, which is described below.

I had a dialog form on which users couldchoose 1 of 12 queries to open.

Users justifiably found a list of 12 queries confusing, so I now have 4 option groups instead.

Every option on the dialog form has a unique option value and when option group values are summed, each selectable combination results in a unique number. I have those unique numbers listed in a table along with the name of the query they relate to.

I then have the following code:

...
Dim OptSum As String
Dim qname As String

OptSum = Me![opLevTyp] + Me![opRptTyp] + Me![opPara] + Me![opSppChs]

qname = DLookup("[qrynam]", "lstquery", "[Value] =" & FishVal)

DoCmd.OpenQuery qname, acViewNormal
End If
...

This opens the query that matches the options selected. It seems to work well, and the users really like it.

I am curious to know whether this is a sensible solution to the problem, or if there is a better or best practice solution I should be using.

 

[Guus2005]

No. Your solution is compact and dynamic. You have chosen not to mention the query names in your code but to retrieve them from a table instead. Which makes your solution easy to change.

 

[aikea]

You can use currentData.AllQueries to list all available queries to a listbox. Once user click a queryname in the listbox the query will open.

Dim qry As AccessObejct
For Each qry In currentData.AllQueries
if qry.name = 'Your criteria' then
listbox.item.add(qry.name)
endif
next

when the listbox is clicked

docmd.openquery listbox.value

The query you want to display perhaps have similar name like "qrySales2003, qrySales2004" for example. Change criteria so only matching query is loaded.

This is much easier than maintain a table of query names

 

[mikeTTI]

Thanks for feedback, it always helpful to ground truth a solution. When something works flawlessly for me I am always very suspicious that trouble is just around the corner. The calm before the storm so to speak.

Aikea, thanks for your suggestion, I was unaware of the AllQueries property, but I can see how it would be very useful.

However, on reading it I am struggling to see how

if qry.name = 'Your criteria' then

Will equate to a particular query.

Are you perhaps suggesting that 'Your Criteria' comes from a set of list boxes, and the values of those list boxes are concatenated and the result used as 'Your Criteria' which would then match the name of the approriate query? For example: -

MyCriteria = "List_1 &" & "List_2 &"  "List_3" etc

(probably I have my quote marks wrong!)

Currently I have 1 x 3 option group, and 3 x 2 option groups.

I am very much a novice on this stuff and I would be very grateful if you would explain how to link 'Your criteria' to a query in more detail, as your solution seems simpler and more expandable than mine.

 

[aikea]

I mean if the query you want to load have a naming pattern like "qry001, qry002" etc.

You can use instr(qry.name, parttern) to check whether the query name match the pattern you specified and decide what to do with it in later statement