Combo box selection to create report

[vanlanjl]

Form1"
combobox "cboModel"
Row Source

SELECT [tblModel].[ID], [tblModel].[Model] FROM tblModel ORDER BY [Model];

combobox "cboContactName"

SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name];

quick explanation: I have a table named tblCOntacts and in this table i have 3 fields "LastName" and "FirstName" and "Initial" becuase i have multiple employess that have the same last names and sometimes same first and last name. So I have a query titled "Quer1" with 2 feilds in it titled:
"File AS" and "Contact Name". Each of these fields combines the first and last name into one field. one does it first name last name and the other last name first name.

and then on form1 i have two cmdbuttons "cmdApplyFilter" and "cmdRemoveFilter"
Here is the 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
    If SysCmd(acSysCmdGetObjectState, acReport, "rptContacts") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If
' 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 [Query1].[ContactName] " & strContactName
' Apply the filter and switch it on
    With Reports![rptContacts]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
' Switch the filter off
    Reports![rptContacts].FilterOn = False
End Sub

So this is what happens.
I first open the report "rptContacts" then open the "form1"
i choose a model from the model box and leave the name area blank and click the "apply filter" button. this causes a small form "Enter parameter value" opens and wants me to input a model and then will do the same thing again for the contact name. If I enter values into it, the report comes up blank. Aslo the refresh button is not refreshing the filter. Any Ideas? If you need more info let me know.
Thanks

 

[dhookom]

I think this solution is backwards. I always have the form open first and allow the user to make filtering selections. Then use some code behind a command button to build the where condition and open the report like:

DoCmd.OpenReport "rptContacts", acPreview, , strFilter

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

Okay I did what I think you said

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

Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
' Switch the filter off
    Reports![rptContacts].FilterOn = False
End Sub

It does open the form but 1) it still has small pop up windows come asking for the same information 2.) the report is still blank
Thanks you

 

[dhookom]

If you are opening the report prior to making filtering selections then you are not doing what I suggested.

I would have the form open first prior to opening the report. The user would click a button to open the report. The code would look like:

Private Sub cmdOpenReport_Click()
    Dim strModel As String
    Dim strContactName As String
    Dim strFilter As String
    strFilter = "1=1 "
' Build criteria string for Office field
    If Not IsNull(Me.cboModel) Then
        strFilter  = " AND [Model] ='" & Me.cboModel.Value & "'"
    End If
' Build criteria string for Department field
    If Not IsNull(Me.cboContactName) Then
        strFilter = " AND [Contact Name] ='" & Me.cboContactName.Value & "'"
    End If
' Apply the filter and switch it on
  DoCmd.OpenReport "rptContacts", acPreview, , strFilter

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

get error:

Run Time Error '3075':
Syntax error (missing operator) in query expression ' AND [Model]='*620".

 

[vanlanjl]

Oh and I am opening the form first like you had suggested for the original code I had and then also for the one you provided.
Thanks for all your help thus far

 

[vanlanjl]

If I select both model and User name I get following error:

runtime error '3075':
Syntax error (missing operator) in query ' AND [ContactName]='Joshua Van".

 

[dhookom]

Please come back with your exact code. You are the only person who can see your mistake. I am 99% sure you are missing a single or double-quote but I can't be sure.

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

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

 

[vanlanjl]

Any help please?!

 

[dhookom]

Why didn't you use the code that I posted? My code opens the report as the last line in the procedure. Your code opens the code as the first executable line of code.

Why do you have "Like ..." in your code?


Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

Becuase your code comes back with an error:
Run-time error '3075
Syntax error (missing operator) in query expression 'AND [LocationCode] = 'OH01"

this is your code:

Private Sub Command7_Click()
Dim strModelName As String
Dim strLocationCode As String
Dim strFilter As String
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
  strFilter = " AND [ModelName] ='" & Me.cboModel & "'"
End If
If Not IsNull(Me.cboLocationCode) Then
  strFilter = " AND [LocationCode] ='" & Me.cboLocationCode & "'"
End If

DoCmd.OpenReport "tblContacts", acPreview, , strFilter
End Sub

this is the code i used that works (finally)

Private Sub Command7_Click()
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
  strWhere = strWhere & " AND [ModelName] =""" & 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