Combobox Select All option

[bullfrog83]

I have an unbound combobox (cboFilterMatri cYT) to filter records on a form. I've added the "All" selection to the combobox's rowsource using UNION. In the AfterUpdate event of the combobox I have the following:

    Me.RecordSource = "SELECT Disc.* FROM Disc INNER JOIN MatricYT ON Disc.MatricId=MatricYT.MatricId WHERE Disc.MatricId = " & Me.cboFilterMatricYT & " ORDER BY MatricYT.MatricYear DESC , MatricYT.MatricTerm, Disc.SortOrder;"
    Me.Requery

What I can't figure out is how to get this to process the All selection. Any suggestions?

[patjones]

You could try something like this:

Me.RecordSource = "SELECT Disc.* FROM Disc INNER JOIN MatricYT ON Disc.MatricId = MatricYT.MatricId ORDER BY MatricYT.MatricYear DESC , MatricYT.MatricTerm, Disc.SortOrder;"

If Me.cboFilterMatricYT <> 'All' Then
     Me.Filter = "Disc.MatricId = " & Me.cboFilterMatricYT
     Me.FilterOn = True
End If

Me.Requery

You'll need to replace 'All' with whatever ID you assigned to that entry in the combo box. There are certainly other ways to do this, I would add.

Pat

[OldBirdman]

If conditions change, you need an "Else" clause to ignore the old filter or deactivate it.

If Me.cboFilterMatricYT <> 'All' Then 
     Me.Filter = "Disc.MatricId = " & Me.cboFilterMatricYT 
     Me.FilterOn = True 
Else
     Me.FilterOn = False 
     'Note: Me.Filter = "" will work here also
End If 

Me.Requery

[bullfrog83]

You could try something like this:

Me.RecordSource = "SELECT Disc.* FROM Disc INNER JOIN MatricYT ON Disc.MatricId = MatricYT.MatricId ORDER BY MatricYT.MatricYear DESC , MatricYT.MatricTerm, Disc.SortOrder;"

If Me.cboFilterMatricYT <> 'All' Then
     Me.Filter = "Disc.MatricId = " & Me.cboFilterMatricYT
     Me.FilterOn = True
End If

Me.Requery

You'll need to replace 'All' with whatever ID you assigned to that entry in the combo box. There are certainly other ways to do this, I would add.

Pat

Thanks! That worked. However, now I need to know how to do this in VBA because I need to query a recordset. So, now its:

lngMatricIdFrom = Me.cboFilterMatricYT
rst = CurrentDb.OpenRecordset("SELECT * FROM DegReq WHERE MatricId = " & lngMatricIdFrom, dbOpenSnapshot)

How would I get this to process all MatricId's if "All" is selected?

[patjones]

If conditions change, you need an "Else" clause to ignore the old filter or deactivate it.

If Me.cboFilterMatricYT <> 'All' Then 
     Me.Filter = "Disc.MatricId = " & Me.cboFilterMatricYT 
     Me.FilterOn = True 
Else
     Me.FilterOn = False 
     'Note: Me.Filter = "" will work here also
End If 

Me.Requery

Quite right you are. Thank you!

[bullfrog83]

If conditions change, you need an "Else" clause to ignore the old filter or deactivate it.

If Me.cboFilterMatricYT <> 'All' Then 
     Me.Filter = "Disc.MatricId = " & Me.cboFilterMatricYT 
     Me.FilterOn = True 
Else
     Me.FilterOn = False 
     'Note: Me.Filter = "" will work here also
End If 

Me.Requery

Thanks Birdman! When I was testing out the code I saw that I needed that Else clause.

[patjones]

Try this (replace 99 with whatever your ID for the All option is):

Dim rst As DAO.Recordset
Dim strSQL As String, strFilter As String

lngMatricIdFrom = Me.cboFilterMatricYT

strFilter = IIf(lngMatricIdFrom = 99, "", "WHERE MatricId = " & lngMatricIdFrom)
strSQL = "SELECT * FROM DegReq " & strFilter

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

What OldBirdman and I posted previously is VBA, by the way. This is what I meant in saying that there are several ways to do this.

Pat

[bullfrog83]

Try this (replace 99 with whatever your ID for the All option is):

Dim rst As DAO.Recordset
Dim strSQL As String, strFilter As String

lngMatricIdFrom = Me.cboFilterMatricYT

strFilter = IIf(lngMatricIdFrom = 99, "", "WHERE MatricId = " & lngMatricIdFrom)
strSQL = "SELECT * FROM DegReq " & strFilter

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

What OldBirdman and I posted previously is VBA, by the way. This is what I meant in saying that there are several ways to do this.

Pat

I suspected it might be something to that effect. Now that I reread my question I realize that I incorrectly stated it as I did understand what you and OldBirdman provided was vba. The Me.Filter worked great for form filtering; clean and efficient. I just wasn't sure how to do the same thing for a query in vba. Thanks again!

[NeoPa]

Filter strings in Access are generally equivalent to the WHERE clause of a SQL query, but missing the actual word WHERE.

Hence, if you have a string variable (EG. strFilter) you canm use this as is, to pass as a filter parameter for opening forms or reports, but you can also add it into the SQL for a simple (non-filtered) query by using code similar to :

strSQL = strSQL & " WHERE " & strFilter