Okay the first time i tried this i used the following code "On Click" for cmdApplyFilter:
Code:
Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()
Dim strModel As String
Dim strContactName As String
Dim strFilter As String
' Check that the report is open
DoCmd.OpenReport "rptContacts", acPreview, , strFilter
' Build criteria string for Office field
If IsNull(Me.cboModel.Value) Then
strModel = "Like '*'"
Else
strModel = "='" & Me.cboModel.Value & "'"
End If
' Build criteria string for Department field
If IsNull(Me.cboContactName.Value) Then
strContactName = "Like '*'"
Else
strContactName = "='" & Me.cboContactName.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Model] " & strModel & " AND [ContactName] " & strContactName
' Apply the filter and switch it on
With Reports![rptContacts]
.Filter = strFilter
.FilterOn = True
End With
End Sub
My two cobo boxes:
cboModel
Code:
SELECT tblModel.[ModelID], tblModel.[ModelName] FROM tblModel ORDER BY [ModelName];
cboContactName:
Code:
SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name];
Query1:
Code:
SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])) AS [File As], IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Contact Name], tblContacts.*
FROM tblContacts
ORDER BY IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])), IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]));
The second cmd button cdmRemoveFilter:
Code:
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptContacts].FilterOn = False
End Sub
So I open the form and select in cboModel D630 then I either leave the second one blank or select a Contact Name ( that I know has a Dell 630) and then I click the "Apply Filter" cmdbutton. A small form titled "Enter Parameter Value" open asking for a Model to be inputed and same for Contact name. I input correct values and or leave the contact name blank and I get a blank form.
This is the first code I tried and this is a horrible example becuase I really do not want to search users. The "Contact Name" combo box should be replaced with something like "OSVersion", "Location", or even OficeName". But i was just trying to get something to work.
-----------------------------------------------------------------------------------------------------------------
Form2
cboModel: row source
Code:
SELECT [tblModel].[ModelID], [tblModel].[ModelName] FROM tblModel ORDER BY [ModelName];
cboLocationCode: row source
Code:
SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode];
command button "on click"
Code:
Private Sub Command7_Click()
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboLocationCode) Then
strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
End If
DoCmd.OpenReport "tblContacts", acPreview, , strWhere
End Sub
So with this one there are a couple of problems. The first problem is in the cboLocationCode. The drop down list in the combo box shows mutliple values of the same location. For example there are multiple users that work in Location OH01, but I want the list in the combo box to list only once OH01.
After selecting values for each combo box I select the cmd button and again iget the small "Enter Parameter Value" but only for the Model and then I get a blank form.