By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Create reports based on multiple combo box selections

P: 6
"Form1"
combobox "cboModel"
Row Source
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblModel].[ID], [tblModel].[Model] FROM tblModel ORDER BY [Model]; 
  2.  
combobox "cboContactName"
Expand|Select|Wrap|Line Numbers
  1. SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name]; 
  2.  
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 "Query1" 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"
I have tried two differant codes
Here is the 1st code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdApplyFilter_Click()
  5.     Dim strModel As String
  6.     Dim strContactName As String
  7.     Dim strFilter As String
  8. ' Check that the report is open
  9.   DoCmd.OpenReport "rptContacts", acPreview, , strFilter
  10. ' Build criteria string for Office field
  11.     If IsNull(Me.cboModel.Value) Then
  12.         strModel = "Like '*'"
  13.     Else
  14.         strModel = "='" & Me.cboModel.Value & "'"
  15.     End If
  16. ' Build criteria string for Department field
  17.     If IsNull(Me.cboContactName.Value) Then
  18.         strContactName = "Like '*'"
  19.     Else
  20.         strContactName = "='" & Me.cboContactName.Value & "'"
  21.     End If
  22. ' Combine criteria strings into a WHERE clause for the filter
  23.     strFilter = "[Model] " & strModel & " AND [ContactName] " & strContactName
  24. ' Apply the filter and switch it on
  25.     With Reports![rptContacts]
  26.         .Filter = strFilter
  27.         .FilterOn = True
  28.     End With
  29. End Sub
  30.  
  31. Private Sub cmdRemoveFilter_Click()
  32.     On Error Resume Next
  33. ' Switch the filter off
  34.     Reports![rptContacts].FilterOn = False
  35. End Sub
  36.  
Using this code i get a.) two small form popups asking for the model value and contactname value then it returns a blank report

Here is the seconf code I tried:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenReport_Click()
  2.     Dim strModel As String
  3.     Dim strContactName As String
  4.     Dim strFilter As String
  5.     strFilter = "1=1 "
  6. ' Build criteria string for Office field
  7.     If Not IsNull(Me.cboModel) Then
  8.         strFilter  = " AND [Model] ='" & Me.cboModel.Value & "'"
  9.     End If
  10. ' Build criteria string for Department field
  11.     If Not IsNull(Me.cboContactName) Then
  12.         strFilter = " AND [Contact Name] ='" & Me.cboContactName.Value & "'"
  13.     End If
  14. ' Apply the filter and switch it on
  15.   DoCmd.OpenReport "rptContacts", acPreview, , strFilter
  16.  
  17. End Sub
  18.  
For this i get the following error:
Run Time Error '3075':
Syntax error (missing operator) in query expression ' AND [Model]='*620".

Any help please!!
Feb 26 '09 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,411
A question would be a good starting point. What do you want to know?
Feb 26 '09 #2

P: 6
Well lets see the title is "Create reports based on multiple combo box selections". Im going to say, how do I do it? or maybe, why isnt my code working? I thought it was prety obivous
Feb 27 '09 #3

NeoPa
Expert Mod 15k+
P: 31,411
@jvan2008
I doubt you'll want help from someone who thought it was a rambling mess then.

Good luck.
Feb 27 '09 #4

P: 6
Well thanks for all your help sport
Feb 27 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.