By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

How to create report with Combo boxes selections?

P: 46
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!
Mar 2 '09 #1
Share this Question
Share on Google+
30 Replies


100+
P: 675
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".
Mar 2 '09 #2

P: 46
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdApplyFilter_Click()
  5.     Dim strModel As String
  6.     Dim strContactName As String
  7.     Dim strFilter As String
  8. ' Check that the report is open
  9.   DoCmd.OpenReport "rptContacts", acPreview, , strFilter
  10. ' Build criteria string for Office field
  11.     If IsNull(Me.cboModel.Value) Then
  12.         strModel = "Like '*'"
  13.     Else
  14.         strModel = "='" & Me.cboModel.Value & "'"
  15.     End If
  16. ' Build criteria string for Department field
  17.     If IsNull(Me.cboContactName.Value) Then
  18.         strContactName = "Like '*'"
  19.     Else
  20.         strContactName = "='" & Me.cboContactName.Value & "'"
  21.     End If
  22. ' Combine criteria strings into a WHERE clause for the filter
  23.     strFilter = "[Model] " & strModel & " AND [ContactName] " & strContactName
  24. ' Apply the filter and switch it on
  25.     With Reports![rptContacts]
  26.         .Filter = strFilter
  27.         .FilterOn = True
  28.     End With
  29. End Sub
  30.  
My two cobo boxes:
cboModel
Expand|Select|Wrap|Line Numbers
  1. SELECT tblModel.[ModelID], tblModel.[ModelName] FROM tblModel ORDER BY [ModelName]; 
  2.  
cboContactName:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name]; 
  2.  
Query1:
Expand|Select|Wrap|Line Numbers
  1. 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.*
  2. FROM tblContacts
  3. 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]));
  4.  
The second cmd button cdmRemoveFilter:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdRemoveFilter_Click()
  2.  
  3.     On Error Resume Next
  4. ' Switch the filter off
  5.     Reports![rptContacts].FilterOn = False
  6.  
  7. End Sub
  8.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblModel].[ModelID], [tblModel].[ModelName] FROM tblModel ORDER BY [ModelName]; 
  2.  
cboLocationCode: row source
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode]; 
  2.  

command button "on click"
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command7_Click()
  2. strWhere = "1=1 "
  3. If Not IsNull(Me.cboModel) Then
  4.   strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
  5. End If
  6. If Not IsNull(Me.cboLocationCode) Then
  7.   strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
  8. End If
  9.  
  10. DoCmd.OpenReport "tblContacts", acPreview, , strWhere
  11. End Sub
  12.  
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
Mar 2 '09 #3

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 2 '09 #4

P: 46
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?
Mar 2 '09 #5

100+
P: 675
Can you go to the query design grid?
Mar 2 '09 #6

P: 46
Expand|Select|Wrap|Line Numbers
  1. SELECT tblContacts.UserName
  2. FROM tblContacts
  3. WHERE (((tblContacts.LocationCode)="OH01"))
  4. ORDER BY tblContacts.LocationCode;
  5.  
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
Mar 2 '09 #7

P: 46
Not sure what you mean by query design grid. My options are as follows:
design veiw
SQL veiw
Datasheet veiw
Pivottable veiw
Pivotchart veiw
Mar 2 '09 #8

100+
P: 675
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.
Mar 2 '09 #9

P: 46
Expand|Select|Wrap|Line Numbers
  1. SELECT tblContacts.UserName, tblContacts.LocationCode
  2. FROM tblContacts
  3. ORDER BY tblContacts.LocationCode;
  4.  
Would this work?
Mar 2 '09 #10

P: 46
I believe you are talking about datasheet veiw. That sthe veiw it goes to when you run the query
Mar 2 '09 #11

100+
P: 675
We posted simultaneously. Starting again:
We are going to use these:
design veiw
SQL veiw
Datasheet veiw
Select SQL view and paste your Select statement
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblContacts].[UserName], [tblContacts].[LocationCode] FROM tblContacts ORDER BY [LocationCode];  
Switch to Datasheet view. This is what you are selecting for your combobox = cboLocationCode.

Is this what you want?
Mar 2 '09 #12

P: 46
Actually no. lol. One reason is becuase this not a search i want at all. A good example of a search would be models by location.
example: What Model of computers are at eacj location/selected location

But that worked somewhat
Mar 2 '09 #13

P: 46
Expand|Select|Wrap|Line Numbers
  1. SELECT  tblContacts.LocationCode, tblModel.ModelName
  2. FROM tblContacts, tblModel
  3. ORDER BY tblContacts.[LocationCode];
  4.  
I tried this and in datasheet veiw it assigns each model to every location
example:
LocationCode ModelName
CA24 Mac Power Book
CA24 D630
CA24 E6400
CA24 IBM A35
CA24 IBM M42
CA24 IBM M51
CA24 Mac Pro
CA24 Mac Book Pro
CA24 Mac Book
CA24 D620
CA24 Power Mac G5
CA24 I Mac
CA24 Optiplex 320
CA24 Optiplex 330
CA24 Optiplex 360
CA24 T30
CA24 T41
CA24 T42
CA24 T43
CA24 T60
CA24 Power Mac G4
CA24 IBM M50

And this isnt true. In fact in my tblcontacts I only have one user in location in CA24 and an asset hasn't bben assigned (user was a test user in building stages)
Mar 2 '09 #14

100+
P: 675
You are getting ahead of yourself. We cannot use a combobox if we can choose from it, and we cannot choose from it if we can't fill it with correct information. You want to search by Location, but you cannot make a cbo that lists your locations.

You should be looking at the results of a query, with 2 columns, UserName and LocationCode. It will be in order by LocationCode. It has many rows (lines) for each location, because there are many users at each location.

Forget the report for now! Just the combobox named cboLocationCode. Is this what you want in your combobox?
Mar 2 '09 #15

P: 46
Expand|Select|Wrap|Line Numbers
  1. SELECT tblContacts.LocationCode, tblModel.ModelName
  2. FROM tblContacts INNER JOIN (tblModel INNER JOIN tblAssets ON tblModel.[ModelID] = tblAssets.[ModelName]) ON tblContacts.[UserName] = tblAssets.[UserName];
  3.  
  4.  
I did this using the query wiazrd and it gave me this:
LocationCode ModelName
F405 T60
F405 T60
OH01 D630
OH01 E6400
OH01 D620
CT10 T60
NV01 IBM M42
TX08 T42
OH01 D630
CA70 T60
OH01 Optiplex 320
OR10 T42
MI05 T60
WA02 T42
VA05 D630
OH01 T41
VA01 T60
MD15 Optiplex 360
OH01 IBM M50

This seems right. So your saying i should put this sql code in my row source for both cboModel and cboLocation?
Mar 2 '09 #16

P: 46
To your last reply - all i want in cboLocation is the locations
all i wan tin the cboModels is the list of the models
Mar 2 '09 #17

100+
P: 675
Good, and you said that cboModels was working, so you are still trying to load cboLocations. I don't know where this new SELECT statement came from, or what you are off doing, but you want to get a query to work that gives the information for cboLocations.
Post #12 I asked you to run a query. What happened.

1) Could you run it? Yes / No
2) Did you get what you want to see in cboLocations? Yes / No
3) What is wrong with the results you see in DataSheet View?
4) CAN YOU go back to Design View and fix it? Yes / No. Why Not?
Mar 2 '09 #18

P: 46
1. yes it runs
2. yes it does
3. i dont want a search a by user name i want to search by model and location
4.i think so
Expand|Select|Wrap|Line Numbers
  1. SELECT tblContacts.LocationCode, tblModel.ModelName
  2. FROM tblContacts INNER JOIN (tblModel INNER JOIN tblAssets ON tblModel.[ModelID] = tblAssets.[ModelName]) ON tblContacts.[UserName] = tblAssets.[UserName];
  3.  
results in datasheetveiw:
Expand|Select|Wrap|Line Numbers
  1. LocationCode    ModelName
  2. F405    T60
  3. F405    T60
  4. OH01    D630
  5. OH01    E6400
  6. OH01    D620
  7. CT10    T60
  8. NV01    IBM M42
  9. TX08    T42
  10. OH01    D630
  11. CA70    T60
  12. OH01    Optiplex 320
  13. OR10    T42
  14. MI05    T60
  15. WA02    T42
  16. VA05    D630
  17. OH01    T41
  18. VA01    T60
  19. MD15    Optiplex 360
  20. OH01    IBM M50
  21.  
Mar 2 '09 #19

P: 46
I think I know what you are getting at. For the cboLocation i just want to see each location listed once.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLocation.LocationCode
  2. FROM tblLocation;
  3.  
Expand|Select|Wrap|Line Numbers
  1. LocationCode
  2. AZ01
  3. CA15
  4. CA17
  5. CA24
  6. CA43
  7. CA45
  8. CA48
  9. CA60
  10. CA66
  11. CA70
  12. CO06
  13. CO07
  14. CT02
  15. CT10
  16. CTYNHH
  17. F288
  18. FL04
  19. FL06
  20. FL10
  21. FL16
  22. FL17
  23. FL29
  24. GA03
  25. GA06
  26. GA09
  27. GA11
  28. HI01
  29. IL10
  30. IL18
  31. IL20
  32. IN05
  33. IN08
  34. IN20
  35. KS02
  36. KY05
  37. LA02
  38. MA01
  39. MA10
  40. MD15
  41. MD25
  42. MI05
  43. MN02
  44. MN04
  45. MO07
  46. NC04
  47. NC15
  48. NJ05
  49. NJ09
  50. NJ16
  51. NV05
  52. NY06
  53. NY07
  54. NY12
  55. OH01
  56. OH07
  57. OH09
  58. OH20
  59. OH30
  60. OH32
  61. OH40
  62. OH44
  63. OH50
  64. OH60
  65. OK03
  66. OR10
  67. PA26
  68. PA30
  69. PA35
  70. PA50
  71. PR01
  72. RI05
  73. SC19
  74. TN10
  75. TN20
  76. TNNM
  77. TX06
  78. TX08
  79. TX13
  80. TX15
  81. TX35
  82. TX40
  83. TX45
  84. TXHMH
  85. UT02
  86. VA05
  87. VA07
  88. VA09
  89. VT03
  90. W11
  91. WA02
  92. WAWBG
  93. WI06
  94. WI20
  95. WI22
  96.  
Mar 2 '09 #20

P: 46
that should be all that comes in the cboLocation combo box
Mar 2 '09 #21

100+
P: 675
1) I'm happy you can run this SELECT statement in the query design window. This is an important Tool for you to use.
2) Wrong Answer. Your answer in 3) says you are not getting the results you want. So the answer to 2) is NO, it is NOT what is wanted in this cbo.
3) You are ahead of yourself again. You are NOT trying to do your report yet. You are trying to get the form right that will allow selection of specified models at specified locations.
4) Until you forget the report query, and get the form controls working, you are not going to get either done.

You have a form with 2 comboboxes, cboModels and cboLocations. This form MUST be able to show a Model in the text portion of cboModels and a Location in the text portion of cboLocations. To do so, you must have a list of Locations in the list portion of cboLocations, so you can choose one.

I think cboModels works, but cboLocations doesn't. You need to work with a table that has the information you want. You are going to SELECT...FROM this table. This is the table that should be used in your query design. Now, what field or fields do you need. In other words, SELECT What? This field(s) should be in the table on your query design. Drag the field to the design grid (in Design View). Change to DataSheet View. This should be what you want. Add a sort in the "Sort:" row of the design grid. This should be the source for your cbo.
Mar 2 '09 #22

P: 46
In response to #22 refer to #20
This is right right?
Mar 2 '09 #23

100+
P: 675
Very Good!

Lets finish cboLocations. Don't rely on the order being correct. Add the sort to the design grid, which will add an ORDER BY to the SQL statement. Please notice that by using the Query Design Tools, you can add features and check results until your results are correct.

Now, we are going to work on the query for the report. Forget the form for now, and the report too. Until we get the query correct, we are not going to attempt to use the cbo values from the form.

What table(s) contain the fields you want. Can you, in the Query Design, display all those fields. Notice you are not counting yet. Just listing the fields and their values. This is step 1.
Mar 2 '09 #24

P: 46
Holy mother of God!
Okay I inserted the following select statement int cboModels:
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblModel].[ModelName], [tblModel].[ModelName] FROM tblModel ORDER BY [ModelName]; 
  2.  
and into cboLocation:
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblLocation].[LocationCode] FROM tblLocation ORDER BY [tblLocation].[LocationCode];
  2.  
and it works except for one thing. It still has a pop up window "Enter Parameter values" form and asks for the model. I entered "D630"
and pushed enter and the report was created properaly.
Why is that pop up form coming up and how do i get rid of it?
And thank you so much so far!!!!
Mar 2 '09 #25

100+
P: 675
it works except for one thing. It still has a pop up window "Enter Parameter values" form and asks for the model. I entered "D630"
and pushed enter and the report was created properaly.
Why is that pop up form coming up and how do i get rid of it?
And thank you so much so far!!!!
Post the SQL as you have it for this try
Mar 2 '09 #26

P: 46
i did in message #25
cmdbutton
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Command7_Click()
  4. strWhere = "1=1 "
  5. If Not IsNull(Me.cboModel) Then
  6.   strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ "
  7. End If
  8. If Not IsNull(Me.cboLocationCode) Then
  9.   strWhere = strWhere & " AND [LocationCode] =""" & Me.cboLocationCode & """ "
  10. End If
  11.  
  12. DoCmd.OpenReport "tblContacts", acPreview, , strWhere
  13. End Sub
  14.  
I am geting an error in the last line of code:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "tblContacts", acPreview, , strWhere
  2.  
strWhere ="1=1 AND [Model]="630" And [LocationCode] ="OH01" "
Mar 2 '09 #27

100+
P: 675
Expand|Select|Wrap|Line Numbers
  1.   strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ " 
  2.  
Have you got a field called [Model]?
Mar 2 '09 #28

P: 46
Duh! I should have changed that a long time ago its should be [ModelName]

And it works.

You are awesome!! Thank you for spending the time with me to figure this out!!
Mar 2 '09 #29

100+
P: 675
You're welcome
I hope you learned something. That was what this exercise was about.
Mar 2 '09 #30

P: 46
Definitly. I have alot more of those to make and more cumbersome. Thank you very much!!
Mar 2 '09 #31

Post your reply

Sign in to post your reply or Sign up for a free account.