Is there a preferred way to create dynamic, form-based queries in MS Access?

Question

General question regarding the approach to take with a database I'm constructing. I plan on having a few forms that will allow DB business users to enter criteria, restrict their search via radio buttons, etc. and then run a query based on their selections.

For simple queries, where there's one or two filters, it seems easy enough to associate the data field's criteria to the respective form element in the query design view, but this approach fails (and seems very hard to read/edit) when dealing with nested if statements (e.g. If A then x, if B then y, if C then z, Else q).

I came across an article that described creating a separate table in the DB where one would store query names and corresponding SQL strings, which are created in VBA that runs after an onClick event, or some form-based trigger. The logic for determining the query criteria is all in the VBA, and once the code executes the system is left with a clean SQL statment, which is stored in the aforementioned table and then used to execute the query. I believe that each time the form is accessed/modified and a query requested, the SQL string in the table would be overwritten.

As this is the first serious DB I've worked on, I'm looking for some guidance regarding the approach to take. Is what I just described correct or a fairly standard/common way to handle the situation? Are there any major concerns? The one that immediately jumps to mind is what happens if two DB users are trying to run the same query, with different criteria, simultaneously? It should be noted, my user base is small -- perhaps 5 users that will only be accessing the DB in passing, for ad-hoc reporting and such.

Thanks in advance!

Edit: Here's the forum post that I was referring to re: SQL strings in a separate table

Edit 2: As a general example, let's say my DB has a table as follows:

ID......Sale_Date......Category

---

1....... 1/1/2013........Foo
2....... 1/3/2013........Bar
3....... 1/1/2013........Bar
4....... 1/7/2013........Bar

Now on my form, I'd have text boxes where the user can specify the date range, and this is filtered on the query as

Between [Forms]![myFrm]![FromDate] And [Forms]![myFrm]![ToDate]

which works just fine.

As for the category, I'd like to have checkboxes or some other form element where the user can specify the categories for inclusion/exclusion. This is where I ran into problems. I tried:

IIf([Forms]![myFrm]![Cat]=1,([tbl_data].[Category]) In ("Foo","Bar"),IIf([Forms]![myFrm]![Cat]=2,"Foo","Bar"))

...with Cat=1 representing 'All' and 2 and 3 representing Foo and Bar respectively. Access gives me an error that the query is too complex, but if I strip out the nested if (thus ignoring the option to search for both categories), it works.

Now, clearly this is a greatly simplified example, but it got me thinking about how to handle the form-driven queries as the DB grows and more canned queries are baked in. The thought with the VBA is that the code would run, evaluate the form data, and then construct the SQL as

SELECT...
FROM...
WHERE IN("Foo", "Bar")

Hopefully this helps clarify things a bit. Please excuse my ignorance... I'm still learning a lot of this as I go along. Thanks.

Answer 1

Regarding this point in your question ...

"what happens if two DB users are trying to run the same query, with different criteria, simultaneously?"

In a multi-user Access application, you should split the db into front end and back end db files. The BE db should contain the tables, indexes, and relationships. The FE db should include your queries, forms, reports, etc. (basically everything your application needs other than the tables) and links to the BE tables.

Place the BE db file on a network share accessible by all your application's users. Each user should receive their own copy of the FE db.

In that situation, each user can create and run their own custom ad-hoc queries without stomping on other users' queries.

If you need to store query criteria or entire SQL statements somewhere, you can store them in a local table in the FE or store them in a common BE table but include a field, perhaps user ID, so that each user's queries may be stored separately.

Beware that if you decide to store locally for each FE user, their saved settings would risk being discarded when you need to deploy a new FE version. In that case, a better approach is give each user an auxiliary db file where they can store their custom settings. And then you would be able to preserve their settings when you deploy new FE versions.

I found it difficult to get a handle on the "big picture" of your question. But I think you should consider it in the context of a split application.

For general "search form" suggestions, see what you can re-use from Allen Browne's detailed example: Search criteria

---

It occurs to me you might be able to use a simple approach.

Create a separate form for the search results. Based that form on a query which includes all the fields you want displayed. If you need a WHERE clause in that query, limit it to only the conditions which should apply to all search variations ... perhaps Active = True. If you can't identify any conditions which will apply to all searches, don't include a WHERE clause.

Then in your search form, evaluate the user's search criteria from the click event of the "Search Now" command button. Build up a WHERE string without the word WHERE and use that as the WhereCondition option to DoCmd.OpenForm.

If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere = " AND date_field >=" & _
        Format(Me.txtStartDate, "\#yyyy-m-d\#")
End If
If Not IsNull(Me.txtCategory) Then
    strWhere = strWhere = " AND category =" & Me.txtCategory
End If
If Len(strWhere) > 0 Then
    strWhere = Mid(strWhere, 6) ' discard leading " AND "
    DoCmd.OpenForm "frmSearchResults", WhereCondition:=strWhere
Else
    MsgBox "nothing to search for"
End If

If one of the search criteria will be 1 or more items from a set of choices, present those as a multi-select list box and loop through the list box's .ItemsSelected property to build a string such as some_field IN ("a", "b", "z"). If you have trouble with that piece, post a new question and we can work through it.

Examine Allen Browne's example I linked earlier. As I recall, he used similar techniques.