Hi Ken,
I don't do a lot of query by form stuff, but it is very useful in some
situations. Like you, I have a form that I use to generate custom report
output.
The form has quite a few different list boxes and check boxes, so the total
code is kind of long. So, I'll try to give representative samples rather
than dumping the whole thing on you.
In my case, I have a number of different list boxes to allow users to select
project reports by category, project manager, project title, and various
other options. For each of these, I have a corresponding check box above it.
By default, I use the form open event to disable all list boxes and set all
check boxes to false. I use the after update event of each check box to
enable the list box if true, or disable it and clear all selections if false.
Such as:
Private Sub chkChoosePM_AfterUpdate()
Me.lstPMs.Enabled = Me.chkChoosePM
If Not Me.chkChoosePM Then
If Me.lstPMs.ItemsSelected.Count > 0 Then
Dim intI As Integer
For intI = 0 To Me.lstPMs.ListCount - 1
Me.lstPMs.Selected(intI) = False
Next intI
End If
End If
End Sub
Then, I have a command button to create the query sql and generate the
report. It basically attaches to the report's query, assigns the sql to a
variable, strips the where condition, then recreates the where condition by
reading the check boxes and list box values (for those which the check boxes
have been checked), such as:
Private Sub cmdViewReport_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim lngWherePos As Long
Dim strWhere As String
Dim strAND As String
Dim varItm As Variant
Dim strList As String
Set db = CurrentDb
'set our query object = to our report query
Set qdf = db.QueryDefs("qr Wkshp Rpt")
'assign the query sql to our variable
strSQL = qdf.SQL
lngWherePos = InStr(1, strSQL, "WHERE", vbTextCompare)
If lngWherePos > 0 Then
strSQL = Left(strSQL, lngWherePos - 1)
End If
strWhere = ""
strAND = ""
'These first three conditions do not have corresponding list boxes
If Me.chkAutomationOnly = True Then
strWhere = strWhere & strAND & " tMaster.InclInAutomationRpts=True"
strAND = " AND"
End If
If Me.chkInclArchived <> True Then
strWhere = strWhere & strAND & " tMaster.Archive<>True"
strAND = " AND"
End If
If Me.chkInclNonYr1 <> True Then
strWhere = strWhere & strAND & " tMaster.[Incl in Yr 1 Book]=True"
strAND = " AND"
End If
If Me.chkChooseCategories Then
If Me.lstCategories.ItemsSelected.Count > 0 Then
strList = ""
'create a list of category id's
For Each varItm In Me.lstCategories.ItemsSelected
strList = strList & Me.lstCategories.ItemData(varItm) & ", "
Next varItm
'strip the trailing comma and space
strList = Left(strList, Len(strList) - 2)
'add the condition to the WHERE clause
strWhere = strWhere & strAND & " tMaster.Category_ID In (" & strList
& ")"
strAND = " AND"
Else
MsgBox "You did not select any categories, so the report will not be
filtered by them"
End If
End If
'various other similar loops for other list boxes removed
'now append the where clause to the strSQL variable
'if any conditions were created
If strWhere <> "" Then
strSQL = strSQL & " WHERE " & strWhere
End If
'assign the revised sql
qdf.SQL = strSQL
'close the query
qdf.Close
'free our objects
Set qdf = Nothing
Set db = Nothing
'open the report based on our query
DoCmd.OpenReport "r WCIP Wkshp", acViewPreview
One other thing, you'll need to be sure to set your list box multi select
property to allow multiple selections (or not, as appropriate). The
multi-select property in on the "Other" properties tab.
HTH, post back if you run into any problems.
-Ted Allen
