Create Report Based Upon criteria selected in Combo boxes

[josh2008]

tblTitles
---------
Title_ID (pk)
titleName

tblArtist
---------
Artist_ID (pk)
aritstName

tblComics
---------
Comic_ID (PK)
Publisher_ID (FK)
Artist_ID (FK)
Writer_ID (FK)
Edition
Volume
Purchase Date
Publish Date
Market Value
Description
Attachment (Picture)

Comics (Form)
-------------
titleName (combo Box)
artistName (combo Box)
Submit (cmd button)

My Code
-------
Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, ,_ "(tblTitle.TitleName=[Forms]![Comic_List]![titleName]) and_ (tblArtist.artistname=[Forms]![Comic_List]![artistName])"

End Sub
-----------------------------------------------------------
When this is entered and I push the Submit button these pop up:
------------------------------------------------------------
Forms!Comic_List!titleName
(Prompts for the title Name- I enter it)
then
Forms!Comic_List!artistName
(Prompts for the artist name - I enter it)
Then the report is generated with the correct data. This Is the closest I have gotten it to work in the last two weeks
with multiple web searches and forum postings and hours of work with my teacher, so how do i get it to work with out the msg boxes popping up asking for the same criteria that i already have selected in the combo boxes?

 

[pbaldy]

Try

DoCmd.OpenReport "Comic_List", acViewPreview, , "tblTitle.TitleName='" & [Forms]![Comic_List]![titleName] & "' and tblArtist.artistname= '" & [Forms]![Comic_List]![artistName] & "'"


Paul
MS Access MVP 2007/2008

 

[josh2008]

Run time error 2450
Microsoft Office cant find the form 'Comic_List' referred to in a Macro expression or Visual Basic code
------------------------------------------------------------
That makes sense because 'Comic_List' is a report not a form
What now?

 

[pbaldy]

It looks like you had the wrong form name in your code. Try this:

DoCmd.OpenReport "Comic_List", acViewPreview, , "tblTitle.TitleName='" & [Forms]![Comics]![titleName] & "' and tblArtist.artistname= '" & [Forms]![Comics]![artistName] & "'"

Paul
MS Access MVP 2007/2008

 

[josh2008]

It Generates a blank report
The first thread i posted on here the code works as far as generating the correct report it just had those pop up boxes

 

[pbaldy]

I assume you entered the same values in the form controls before running the code, and there are records that match those criteria? Can you post a sample db?

Paul
MS Access MVP 2007/2008

 

[josh2008]

Im not sure what you mean by a sampl db. But to answer your question, yes i entered the same values in and it works justy right, i just dont want the msg boxes, i want it to recognize the values in the combo boxes

 

[pbaldy]

I meant attaching a sample of your db here; some things are easier to figure out when I can play with them myself. Do the combo's contain more than one column, and if so is the bound column the value expected by the code?

Paul
MS Access MVP 2007/2008

 

[josh2008]

Cant attach, im not paying to attach this to some file storage place sorry. Also the form is not bound

 

[pbaldy]

I wouldn't pay either. I'm new here and didn't realize you couldn't attach a file directly. You can email it to me if you want: pbaldy72 - yahoo - com

The form does not need to be bound. You didn't answer about the bound column though.

Paul
MS Access MVP 2007/2008

 

[pbaldy]

I don't see the code you've described here anywhere, but the "Comics" form has something similar that appears to work. I added another combo there and this appears to work:

DoCmd.OpenReport "Comic_List", acViewPreview, , "[Title_ID]=" & Me![titleName] & " AND Artist_ID = " & Me.cboArtist, acWindowNormal

Paul
MS Access MVP 2007/2008

 

[josh2008]

Where did you put that code? I pasted it into my Comics form and it says compile error or method not found

 

[pbaldy]

I put it in the comics form, but keep in mind I said I added a combo:

Me.cboArtist

So you would have to add that for the code to work.

Paul
MS Access MVP 2007/2008

 

[josh2008]

It isnt letting me past this part: ![titleName]
and why did you add a combo box artist when there was one in there?

 

[josh2008]

DUDE YOU ARE AWESOME!!!!!!!!!!!!!!
THANK YOU!!!THANK YOU!!!THANK YOU!!!THANK YOU!!!THANK YOU!!!
YOU ROCK!!!!!!!!!!!!!!

 

[pbaldy]

There was not one there. There were 2 combos in a form named "Comic_Filter", but the code behind that button was not what we had been talking about.

Paul
MS Access MVP 2007/2008

 

[pbaldy]

Ah, you got it. Glad we got it sorted out.

Paul
MS Access MVP 2007/2008

 

[josh2008]

I guess what I sent you wasnt what I had open, the changes must not have applied or something, But thank you so much, I have Been working on this for three weeks

 

[josh2008]

Okay one last question, How do I set the value of combobox 2 to be dependant on combo box 1.
example:
combobox 1 = titleName = X-Men
combobox 2 = artistName= should only be the artist that have done x-men but in mine it list all artist, so if i select a random artist and he didnt do x-men a blank report appears

 

[pbaldy]

The concept is generally called "cascading" combo boxes, if you want to search on that. I haven't looked at it but there's a FAQ here on the subject:

http://www.tek-tips.com/faqs.cfm?fid=4289

Paul
MS Access MVP 2007/2008