4

Note: Please bear with me as I'm relatively new to Access... everything I have learned has been through searching Google as necessary.

So I am creating a form with the sole purpose of searching through a database. In one use-case, the user has the option to select multiple options to filter a search by, for privacy reasons we can say "Blue, Green, Red, Brown". The user can select any combination of those colors, including all or none. In the database table, there is a primary key (in our case, an ItemID). The other columns in the table are the colors above (Blue, Green, Red, Brown). Due to the structuring of this data (note: I cannot change it for multiple reasons), I have to create this SQL query in VBA rather than by using what Access provides.

Here is the code I am using to create and run the query when the "Search" button is pressed:

Set qdf = Nothing

*Logic to create SQL query... variable to hold query is called sqlStr*

Set qdf = CurrentDb.CreateQueryDef("TemporaryQuery", sqlStr)
DoCmd.OpenQuery qdf.Name

As a result of much searching, this is the only way I have found, aside from using ADO, that I can create a SQL query IN VBA and then run it in VBA. The downside to this method, while it works, is that it creates a query, which has to be closed and deleted before the query can be ran again. This is not acceptable for the users that will be using this.

All examples I have found (please bear with me) use ADO, which I have not been able to get to work. I have two databases, one for the queries and forms, and one for the data itself. The database containing the data has it's tables linked to the database with the queries and forms. I'm not sure if ADO can work in that setup, but if it can, I haven't been able to figure it out.

Coming from a Java/Webdev background using Access has been a bit frustrating to me as it just seems to be making things more complicated (in my opinion) that it should be. Can anyone help me get this working correctly?

Thank you for your time... it is greatly appreciated!

Improve this question
10
  • If your results always contain the same fields but different data based on your filters, you could use a sub form set up in datasheet view. Commented Jun 26, 2013 at 19:54
  • @Zaider do you happen to have a good tutorial for working with sub forms? Commented Jun 26, 2013 at 20:13
  • @MikeRinehart Do user's just need to view this data, or have edit capabilities, if just from a view standpoint then you could use a simple list box on a form, you can set the Rowsource property of list boxes to an SQL string Commented Jun 27, 2013 at 12:32
  • 1
    Yes you can like so Me.Subform.SourceObject = "sbfSearchAuthorizations" Commented Jun 27, 2013 at 19:08
  • 1
    @MikeRinehart Listboxes can display multiple columns, check the listbox properties for the columns and column widths options. Commented Jun 28, 2013 at 16:48

1 Answer 1

Reset to default
6

I'm puzzled by these statements ...

"The downside to this method, while it works, is that it creates a query, which has to be closed and deleted before the query can be ran again. This is not acceptable for the users that will be using this."

I don't understand why that is a concern for the users. They should not have to manually delete the query --- you can do that for them with VBA using DoCmd.DeleteObject. And actually, it sounds like you may only need to revise the query. If so, you don't need to first delete the old version, and then create a new query. You can simply change the query's .SQL property.

If the users' issue is that they don't want to even see TemporaryQuery in the Navigation panel (too much clutter?), change its name to USysTemporaryQuery. That way they would only see it if they have their panel properties set to display system objects.

I'll offer a code sample which still may not be what you want. But perhaps someone else will find it useful.

Const cstrQueryName As String = "TemporaryQuery"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlStr As String

'*Logic to create SQL query... variable to hold query is called sqlStr*
' Apparently you have that piece worked out.  I'll use a simple query ...
sqlStr = "SELECT * FROM Dual;"

Set db = CurrentDb
If Not QueryExists(cstrQueryName) Then
    Set qdf = db.CreateQueryDef(cstrQueryName)
Else
    Set qdf = db.QueryDefs(cstrQueryName)
End If
qdf.sql = sqlStr
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery cstrQueryName

If you still want to later discard the saved query, do this ...

If QueryExists(cstrQueryName) Then
    DoCmd.DeleteObject acQuery, cstrQueryName
End If

This is the helper function for the above code ...

Public Function QueryExists(ByVal pName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim blnReturn As Boolean
    Dim strMsg As String

On Error GoTo ErrorHandler

    blnReturn = False ' make it explicit
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pName)
    blnReturn = True

ExitHere:
    Set qdf = Nothing
    Set db = Nothing
    QueryExists = blnReturn
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 3265 ' Item not found in this collection.
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure QueryExists"
        MsgBox strMsg
    End Select
    GoTo ExitHere

End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.