Create a Report using values form multiple combo boxes

  • Thread starter Thread starter vanlanjl
  • Start date Start date
V

vanlanjl

Question: How do I create a Report based of the values 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 ahve 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!
 
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.
 
I have replied to your question in another forum that you should not open the
report until you have built a WHERE CONDITION that can be applied in the
DoCmd.OpenReport method.

--
Duane Hookom
Microsoft Access MVP


vanlanjl said:
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.
Click to expand...
 
Back
Top