You need to set up a form with unbound boxes - combo, text, check etc and a search button.
In the 'on-click' event of the search button you need to assign the code.
You will also need a blank query set up with the source table for your info that you are interrogating. I lump everything I need into one table called 'tblAllinfo' in my query. My query, which has no fields selected is called 'DynamicQuery99'. You see this referred to in the code below.
Set up the query, prepare the form and assign this code as described above (copy and paste). Go through teh code and substitute my object names and field names with yours. Add more in the same format if you need to and then give it a whirl.
Serach for 'QBF' and 'Query By Form' on the forum as well. There's probably lots of posts from me learning how to it.
Private Sub btnPreview_Click()
Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant
Dim varItm As Variant
Dim Answer As String
Dim F, T As Variant
On Error GoTo HandleErr
Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
' TO DO: Turn normal error handler on when this condition is finished.
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query99")
' TO DO: Turn normal error handler on when this condition is finished.
On Error GoTo 0
'Make sure hidden Status control is null
'If Not IsNull(Me.Status) Then Me.Status = Null
'Put Status numbers in hidden Status control on form for use by code later on.
'If Me.cboStatus.ItemsSelected.Count >= 1 Then
' For Each varItm In cboStatus.ItemsSelected
' Answer = Answer & cboStatus.Column(0, varItm) & ","
' Next varItm
' Me.Status = Left(Answer, Len(Answer) - 1)
'End If
where = Null
If Not IsNull(Me.[cboCategory]) Then
where = where & " AND [Category] = " & Me.[cboCategory]
End If
If Not IsNull(Me.[cboBusiness]) Then
where = where & " AND [BusinessName] = " & Me.[cboBusiness]
End If
If Not IsNull(Me.[cboBusinessUnit]) Then
where = where & " AND [BusinessUnit] = " & Me.[cboBusinessUnit]
End If
If Not IsNull(Me.[cboLocation]) Then
where = where & " AND [Location] = " & Me.[cboLocation]
End If
If Not IsNull(Me.[cboDept]) Then
where = where & " AND [Department] = " & Me.[cboDept]
End If
If [chkDSE] = -1 Then
where = where & " AND [DSEUser]= " & "True"
End If
'LITTLE PAL - The query below is what I made up. You can use your qryWeeklyOrderStatusRpt query but be
'sure that you have the MCID, ODPart and ODStatus in that query or the code above will not work...
Set QD = db.CreateQueryDef("Dynamic_Query99", _
"Select * from qryAllInfo " & (" where " + Mid(where, 6) & ";"))
'If selections did not return any results then let user know
If DCount("*", "Dynamic_Query99") = 0 Then
MsgBox "No records to display."
Exit Sub
End If
'DoCmd.OpenQuery "Dynamic_Query99"
DoCmd.OpenReport "rptRRStatus", acViewPreview
ExitHere:
Exit Sub
' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 11-15-2005 14:45:26 'ErrorHandler:$$D=11-15-2005 'ErrorHandler:$$T=14:45:26
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmRRStats.btnPreview_Click" 'ErrorHandler:$$N=Form_frmRRStats.btnPreview_Click
End Select
' End Error handling block.
End Sub
