How to create report with Combo boxes selections?

[vanlanjl]

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,OSNa me
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

tblOfficeVersio n: 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!

[OldBirdman]

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

What are you trying that does not work?

tblContacts: ID, Company, LastName, FirstName, Initial, EMailAddress, JobTitle, BusinessPhone, Address, City, State, Zip, Country, Username(PK)

Using Username for a Primary Key (PK) implies that your table structure, and therefore the database design is not correct, and needs a complete revision. Is this what doesn't work? See Database Normalization and Table Structures for information about designing tables and creating relationships.

So initial i have tried to create a form with ...

You cannot create a form? Or you cannot create a form with comboboxes?

Please tell us what you have that works. Then what you have tried that doesn't work, for example, "My Report is always blank", or "My report shows all records, and ignores my comboboxes", or "My comboboxes don't work".

[vanlanjl]

Okay- 1.) the reason I use "username" as my primary key is becuase it links to the tblAssests. This way a computer asset is linked to a user by the username.

Okay the first time i tried this i used the following code "On Click" for cmdApplyFilter:

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

SELECT tblModel.[ModelID], tblModel.[ModelName] FROM tblModel ORDER BY [ModelName]; 
 

cboContactName:

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

Query1:

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 :

 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

SELECT [tblModel].[ModelID], [tblModel].[ModelName] FROM tblModel ORDER BY [ModelName]; 
 

cboLocationCode : row source

SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode]; 
 

command button "on click"

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.


Thank you for your quick respose OldBirdMan

[OldBirdman]

You can't get the report right until you get the form/comboboxes right.

The first problem is in the cboLocationCode . The drop down list in the combo box shows mutliple values of the same location.

Of course it does. Your query

SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode]; 

selects each user and location. If this query results in a location with multiple users, what did you want to happen to those UserNames.
I don't know how you developed this (and the other) SQL statements, but the first requirement for them is that they must do what you want when used in the Query Design window. Open this window from the database window Tab=Queries, then "Create query in Design view", choose SQL view, and paste the SELECT statement. Switch to DataSheet view, and this will show the results of this query. If it isn't what you want here, it isn't going to work as the RowSource for your combobox. Fix it using Design View, then DataSheet view, until it is correct. Switch to SQL view, and copy this as rowsource for your combobox.

[vanlanjl]

I assuming you mean to change the criteria in the desighn veiw? I have a Access 2007 book and it only shos me mathmatical criteria or exact search criteria like for specific words. Is there more that I ma missing?

[OldBirdman]

Can you go to the query design grid?

[vanlanjl]

SELECT tblContacts.UserName
FROM tblContacts
WHERE (((tblContacts.LocationCode)="OH01"))
ORDER BY tblContacts.LocationCode;
 

Im not sure if this it? This code has a specific location in it and i want the user to select the location. The location can always change and I would like it to at least sya the location once and then all the users or models or whatever

[vanlanjl]

Not sure what you mean by query design grid. My options are as follows:
design veiw
SQL veiw
Datasheet veiw
Pivottable veiw
Pivotchart veiw

[OldBirdman]

When you design forms, you use Form Design, and switch between Design View and Form View. When you design Reports, you design in Design View, and switch to Print View. You should be able to go to query design the same way.

[vanlanjl]

SELECT tblContacts.UserName, tblContacts.LocationCode
FROM tblContacts
ORDER BY tblContacts.LocationCode;
 

Would this work?