Fletch:
This requires some VBA coding and a familiarity with SQL syntax. If you are not conversant with VBA or SQL let me know and I'll try to walk you through it.
In my situation, the users want to be able to select a number of employees on which to produce a report. I created a multi select list box populated with the employee name and ID Numbers (hidden) and then use this code to identify which are selected, write those IDs to an array and then use the array to build the Where clause for the sql string. You'll need to dimension the variables.
'Identifies and records in intIDs all selected Therapists' ID Numbers
intCount = lstTherapist.ListCount
For intLoop = 0 To intCount - 1
If lstTherapist.Selected(intLoop) = True Then
intArrCnt = intArrCnt + 1
ReDim Preserve intIDs(intArrCnt)
intIDs(intArrCnt) = lstTherapist.Column(0, intLoop)
End If
Next intLoop
'Test the intArrCnt to determine that at least one Therapist has been selected.
If intArrCnt = 0 Then
MsgBox "You must select at least one Therapist", vbOKOnly + vbCritical, "TCC Fee Slip Error"
lstTherapist.SetFocus
Exit Sub
End If
'Reads the Therapist ID numbers out of the array and into the Where string for the query
For intLoop = 1 To intArrCnt
strWhere = strWhere & "(Employee.EmployeeID)= " & intIDs(intLoop) & " Or "
Next intLoop
'Truncates the last OR and spaces from the Where string and finishes the string
intLen = Len(strWhere)
strWhere = "(" & Left(strWhere, intLen - 4) & "
);"
'Creates the SQL string to select all necessary fields and criteria
strSQL = "SELECT --your select code-- FROM --your from code"
strSQL = strSQL & strWhere
'Runs the SQL to create the working table tblFeeSlipRpt used to create the report itself
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
In my case, this is a make table query and is initiated directly. You will probably need to do something like this to delete any previously saved query and add the new one. the query can then be used as the data source for the report.
If ObjectExists(acQuery, "Your Query Name"
Then DoCmd.DeleteObject acQuery, "Your Query Name"
Set db = CurrentDb()
db.CreateQueryDef "Your Query Name", strSQL
DoCmd.OpenQuery "Your Query Name"
ObjectExists is a function to determine, obviously, if a given object exists in the database and you'll need to add it. Here's the code:
Function ObjectExists(ObjType As Integer, ObjName As String) As Boolean
'Determines whether or not a given object exists in database
'If ObjectExists(acTable, "tblOrders"
then ...
Dim strTemp As String
Dim db As Database
Dim strContainer As String
On Error Resume Next
Set db = CurrentDb()
Select Case ObjType
Case acTable
strTemp = db.TableDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acQuery
strTemp = db.QueryDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acMacro, acModule, acForm, acReport
Select Case ObjType
Case acMacro
strContainer = "Scripts"
Case acModule
strContainer = "Modules"
Case acForm
strContainer = "Forms"
Case acReport
strContainer = "Reports"
End Select
strTemp = db.Containers(strContainer).Documents(ObjName).Name
ObjectExists = (Err.Number = 0)
End Select
End Function
I know this can look intimidating but if you take it a step at a time and test as you go, its not that difficult.
One of the best ways I've found for creating the strSQL is to actually create the query as you normally would and then look at the SQL code (from the view menu) and then copy and paste it into the event sub adding the quotation marks as needed.
Larry De Laruelle
ldelaruelle@familychildrenscenter.org
