Create report with multiple combo box selections form form?

vanlanjl · #1 03-02-2009, 07:29 AM

Question: How do I create a Report based off the values/selections of mutliple combo boxes in a form?

I have tried this several times with several failures and have used multiple
codes to try this and each has been unsuccesful.

I will try to explain my database and its contents
Tabels and Fields ((PK) indicates the primary Key):
tblAssets: ID(PK), AssetNumber, SerialNumber, ModelName, ComputerName,
DeploymentDate, Active, UserName, OfficeName,OSName
tblContacts: ID, Company, LastName, FirstName, Initial, EMailAddress,
JobTitle, BusinessPhone, Address, City, State, Zip, Country, Username(PK)
ChargeCode, LocationCode
tblLocation: LocationCode(PK)

tblModel: ModelID(PK), ModelName

tblOfficeVersion: OfficeID(PK), OfficeName

tblOSVersion: OSName(PK)

So initial i have tried to create a form with two combo boxes. cboModel and
cboLocation and with a cmdbutton that open rptModel. Upon opening I want to
see how many of each model is at each location.

Exaple: Location: OH01
Model: Dell 630 = 20
IBM M50 = 15

I have tried multiple codes with no success. Please help I have been posting
this problem for 3 weeks with very little help. Not that people have tried
but it seems that this is an issue that isnt covered very often. Thank you
for any help and if there are any questions please ask. I can also provide a
code if that helps, but like I said I have tried various differant codes with
no success.Thanks!

vanlanjl · #2 03-02-2009, 08:54 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can I use Access to create a single form that feeds 8 other forms?	jmd	Import/Export Data	2	02-10-2009 09:02 AM
Form Combo problems	Honeytree	Forms	0	10-05-2008 11:32 AM
Filtering subform combo from main form combo	MUKUDU99	Forms	0	08-17-2008 10:19 AM
How to create a Password Form	heman85	Forms	0	08-08-2008 07:24 PM
Using combo box to open another form	ladyairj23	Forms	0	06-02-2006 05:03 AM