Using form combo boxes apply a filter to a query

0
votes

I think I am close to this one, but can't work out the filtering process.

tblIndex(PrimaryCat,SubCat,UserID,Year)
tblResults(SubCat,UserID)

My Form has two combo boxes and a button. ComboBox1 has tblIndex.PrimaryCat values and ComboBox2 has tblIndexYear values.

What I want is when the command button in the form is pressed, tblResults opens showing the list of SubCat and UserID values when the combobox values are used as a filter on tblIndex.

Does this make sense?

I have the recordsource of the form set to tblResults. I'm using this, just need to add in filtering somehow:

Private Sub cmdGo_Click()

Dim strSQL As String

strSQL = "SELECT SubCat, UserID " & _
         "FROM tblIndex " & _
         "WHERE PrimaryCat = [strCat] AND Year = [strYear] " & _
         "GROUP BY SubCat, UserID"

DoCmd.OpenQuery "strSQL"

End Sub

EDIT:

I'm not sure if I am allowed to answer my own question but I worked out a solution. I >used INTO to put the results into a temp table I can further manipulate using:

Private Sub cmdGo_Click()

Dim strSQL As String

strSQL = "SELECT SubCat, UserID INTO tblTemp " & _ "FROM tblIndex " & _ "WHERE PrimaryCat = '" & cboPrimaryCat.Value & "' AND Year = '" & >cboYear.Value & _ "' GROUP BY SubCat, UserID"

DoCmd.RunSQL strSQL

End Sub

2
sqlformsms-accessvba

2 Answers

1
votes

Worked it out. Can't run SQL without first storing it in a query. Solution is:

Private Sub cmdGo_Click()
    Dim qdfCurr As DAO.QueryDef
    Dim strSQL As String

    strSQL = "SELECT SubCat, UserID " & _
             "FROM tblIndex " & _
             "WHERE PrimaryCat  = '" & strCat.Value & "' AND Year = '" & strYear.Value & _
             "' GROUP BY SubCat, UserID"

    On Error Resume Next
    Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
    If Err.Number = 3265 Then
        Set qdfCurr = CurrentDb.CreateQueryDef("TempQuery")
    End If

    qdfCurr.SQL = strSQL
    DoCmd.OpenQuery "TempQuery"
End Sub
-1
votes

I think that this sould work. Working with temp tables is more complicated. Imagine that you have 50 queries with their corresponding temp table!

Private Sub cmdGo_Click()

Dim strSQL As String

strSQL = "SELECT SubCat, UserID " & _
         "FROM tblIndex " & _
         "WHERE PrimaryCat = " & Forms!FormName![strCat] & " AND Year = " & Forms!FormName![strYear] " " & _
         "GROUP BY SubCat, UserID"

DoCmd.OpenQuery strSQL

End Sub