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

  • Thread starter Thread starter vanlanjl
  • Start date Start date
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!
 
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
 
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...
 
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...
 
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...
 
Back
Top