How do I create a report based off of several Combo Box selections

V

vanlanjl

Just need to know how to do this.
exaple:
tblModel
feilds: ModelID, ModelName

tblContacts
Feilds: ID, Company, LastName, FirstName, Initial, E-mailAddress, JobTitle,
BUisnessPhone, Address, City, Stat/Province, Zip, Country, UserName (PK),
ChargeCode, LocationCode

tblAssets
Fieilds: ID, AssetNumber, SerialNumber, ModelNumber, ComputerName,
DeploymentDate, Active, UserName(FK), OffficeName, OSName

tblOfficeVersion
Feilds: OfficeID, OfficeName

tblOSVersion
Feild: OSName

I hav a asset form (frmAsset) where computer asset information is entered,
this is conected/linked to the frmSearchUsers form. This is where you enter
the user information, both are linked by the "UserName" fields.

What i want to be able to do is have a form that has multiple combo boxes on
it, like one for "Model" and one for "OSName" and one for "OfficeName" so i
can run a search based of the criteria in those and it will open a report.
Example: FInd out how Many Dell 630s are running XP and Office 2007

returns the result
Thanks!
 
D

Duane Hookom

By "FInd out how Many" are you just looking for a number or do you want
details about those records?

I would create a report with a record source that contained all of the
desired fields. Then build a form with combo boxes for each of the possible
filters. For instance:
cboModel
cboOSName
cboOfficeName
Then add a command button on the form using the wizard that opens the
report. Modify the code to look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboOSName) Then
strWhere = strWhere & " AND [OSName] =""" & Me.cboOSName & """ "
End If
'--- etc ----
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere
 
V

vanlanjl

What will the record source for the combo boxes be?
cboModel = tblModel
cboLocation= tblContacts

Duane Hookom said:
By "FInd out how Many" are you just looking for a number or do you want
details about those records?

I would create a report with a record source that contained all of the
desired fields. Then build a form with combo boxes for each of the possible
filters. For instance:
cboModel
cboOSName
cboOfficeName
Then add a command button on the form using the wizard that opens the
report. Modify the code to look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboOSName) Then
strWhere = strWhere & " AND [OSName] =""" & Me.cboOSName & """ "
End If
'--- etc ----
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere


--
Duane Hookom
Microsoft Access MVP


vanlanjl said:
Just need to know how to do this.
exaple:
tblModel
feilds: ModelID, ModelName

tblContacts
Feilds: ID, Company, LastName, FirstName, Initial, E-mailAddress, JobTitle,
BUisnessPhone, Address, City, Stat/Province, Zip, Country, UserName (PK),
ChargeCode, LocationCode

tblAssets
Fieilds: ID, AssetNumber, SerialNumber, ModelNumber, ComputerName,
DeploymentDate, Active, UserName(FK), OffficeName, OSName

tblOfficeVersion
Feilds: OfficeID, OfficeName

tblOSVersion
Feild: OSName

I hav a asset form (frmAsset) where computer asset information is entered,
this is conected/linked to the frmSearchUsers form. This is where you enter
the user information, both are linked by the "UserName" fields.

What i want to be able to do is have a form that has multiple combo boxes on
it, like one for "Model" and one for "OSName" and one for "OfficeName" so i
can run a search based of the criteria in those and it will open a report.
Example: FInd out how Many Dell 630s are running XP and Office 2007

returns the result
Thanks!
Click to expand...
Click to expand...
 
V

vanlanjl

Well no luck on my side.
I created a form and had two combo boxes in it:
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];

Then created the 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

when i select both selections in the combo boxes and press the cmmand
button, i get a small form that open up asking me for the model again. i
enter the information in and get a blank form.
I am so frustrated with this. I have been posting about this issues for two
weeks on various forums and still no luck. I do appreciate the help though
Duane!!

Duane Hookom said:
By "FInd out how Many" are you just looking for a number or do you want
details about those records?

I would create a report with a record source that contained all of the
desired fields. Then build a form with combo boxes for each of the possible
filters. For instance:
cboModel
cboOSName
cboOfficeName
Then add a command button on the form using the wizard that opens the
report. Modify the code to look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboOSName) Then
strWhere = strWhere & " AND [OSName] =""" & Me.cboOSName & """ "
End If
'--- etc ----
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere


--
Duane Hookom
Microsoft Access MVP


vanlanjl said:
Just need to know how to do this.
exaple:
tblModel
feilds: ModelID, ModelName

tblContacts
Feilds: ID, Company, LastName, FirstName, Initial, E-mailAddress, JobTitle,
BUisnessPhone, Address, City, Stat/Province, Zip, Country, UserName (PK),
ChargeCode, LocationCode

tblAssets
Fieilds: ID, AssetNumber, SerialNumber, ModelNumber, ComputerName,
DeploymentDate, Active, UserName(FK), OffficeName, OSName

tblOfficeVersion
Feilds: OfficeID, OfficeName

tblOSVersion
Feild: OSName

I hav a asset form (frmAsset) where computer asset information is entered,
this is conected/linked to the frmSearchUsers form. This is where you enter
the user information, both are linked by the "UserName" fields.

What i want to be able to do is have a form that has multiple combo boxes on
it, like one for "Model" and one for "OSName" and one for "OfficeName" so i
can run a search based of the criteria in those and it will open a report.
Example: FInd out how Many Dell 630s are running XP and Office 2007

returns the result
Thanks!
Click to expand...
Click to expand...
 
D

Duane Hookom

Do you have two text fields in your report's record source named [Model] and
[LocationCode]?

What is the bound column of cboLocationCode? It should probably be the
second column since that is the value of LocationCode.

--
Duane Hookom
Microsoft Access MVP


vanlanjl said:
Well no luck on my side.
I created a form and had two combo boxes in it:
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];

Then created the 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

when i select both selections in the combo boxes and press the cmmand
button, i get a small form that open up asking me for the model again. i
enter the information in and get a blank form.
I am so frustrated with this. I have been posting about this issues for two
weeks on various forums and still no luck. I do appreciate the help though
Duane!!

Duane Hookom said:
By "FInd out how Many" are you just looking for a number or do you want
details about those records?

I would create a report with a record source that contained all of the
desired fields. Then build a form with combo boxes for each of the possible
filters. For instance:
cboModel
cboOSName
cboOfficeName
Then add a command button on the form using the wizard that opens the
report. Modify the code to look something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
End If
If Not IsNull(Me.cboOSName) Then
strWhere = strWhere & " AND [OSName] =""" & Me.cboOSName & """ "
End If
'--- etc ----
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere


--
Duane Hookom
Microsoft Access MVP


vanlanjl said:
Just need to know how to do this.
exaple:
tblModel
feilds: ModelID, ModelName

tblContacts
Feilds: ID, Company, LastName, FirstName, Initial, E-mailAddress, JobTitle,
BUisnessPhone, Address, City, Stat/Province, Zip, Country, UserName (PK),
ChargeCode, LocationCode

tblAssets
Fieilds: ID, AssetNumber, SerialNumber, ModelNumber, ComputerName,
DeploymentDate, Active, UserName(FK), OffficeName, OSName

tblOfficeVersion
Feilds: OfficeID, OfficeName

tblOSVersion
Feild: OSName

I hav a asset form (frmAsset) where computer asset information is entered,
this is conected/linked to the frmSearchUsers form. This is where you enter
the user information, both are linked by the "UserName" fields.

What i want to be able to do is have a form that has multiple combo boxes on
it, like one for "Model" and one for "OSName" and one for "OfficeName" so i
can run a search based of the criteria in those and it will open a report.
Example: FInd out how Many Dell 630s are running XP and Office 2007

returns the result
Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create a Report using values form multiple combo boxes 2

Top