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!

[vanlanjl]

that should be all that comes in the cboLocation combo box

[OldBirdman]

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.

[vanlanjl]

In response to #22 refer to #20
This is right right?

[OldBirdman]

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.

[vanlanjl]

Holy mother of God!
Okay I inserted the following select statement int cboModels:

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

and into cboLocation:

SELECT [tblLocation].[LocationCode] FROM tblLocation ORDER BY [tblLocation].[LocationCode];
 

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!!!!

[OldBirdman]

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

[vanlanjl]

i did in message #25
cmdbutton

Option Compare Database
 
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
 

I am geting an error in the last line of code:

DoCmd.OpenReport "tblContacts", acPreview, , strWhere
 

strWhere ="1=1 AND [Model]="630" And [LocationCode] ="OH01" "

[OldBirdman]

  strWhere = strWhere & " AND [Model] =""" & Me.cboModel & """ " 
 

Have you got a field called [Model]?

[vanlanjl]

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!!

[OldBirdman]

You're welcome
I hope you learned something. That was what this exercise was about.