Access 2007 Combobox's in Form to generate the Report

[josh2008]

Okay this is what Got

Form name=Comic_Filter
it has two comboboxes the 1st is "titleName" and the second is artistName. Both drop down boxes have a list of titles and artist in them. I would the user to be able to first select the title he wants then select the artist he wants, click the command Button and tada the "Comic_List" report i created pops up with that SPECIFIC information that was inputted. The following is my code for the command button (PS I am not a coder this is for a database I am designing in school, I have not been taught code just a dumb class on code design. Any help would be awesome!)
------------------------------------------------------------
Option Compare Database

Private Sub Command10_Click()
Dim DocName As String
Dim stFilter As String
Dim lgtitlename As String
Dim lgartistname As String

stDocName = "Comic_List"
stWhere = "[" & Me!titleName & "]=" & Me!titleName & ""
stWhere = "[" & Me!artistName & "] =" & Me!artistName & ""
DoCmd.OpenReport stDocName, acPreview, , strFilter = Forms!Comic_Filter!titleName & artistName
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
End Sub
------------------------------------------------------------
If i do not select any options in the comboboxes and push the command button the report will generate all data! If I select specific criteria "Superman" for titleName and then "Jim Lee" for artistName, the report will pop up but there is nothing in it. I am actually pleased I have gotten it to work thus far becuase like i said i am not a coder. Thanks in advance for any help I can get!
J.

 

[dhookom]

Private Sub Command10_Click()
  Dim DocName As String
  Dim stFilter As String
  Dim lgtitlename As String
  Dim lgartistname As String
  strFilter = "1=1 "
  If Not IsNull(Me.TitleName) Then
    strFilter = strFilter & " And [TitleName]=""" & _
       Me.TitleName & """ "
  End If
  If Not IsNull(Me.artistName ) Then
    strFilter = strFilter & " And [artistName ]=""" & _
       Me.artistName & """ "
  End If

  stDocName = "Comic_List"
  DoCmd.OpenReport stDocName, acPreview, , strFilter
Exit_Command10_Click:
   Exit Sub
Err_Command10_Click:
End Sub

Duane MS Access MVP

 

[josh2008]

OKay the code will bring up the report but it does not bring the data from the feilds selected in the titleName combobox and the artistName combobox. It just comes up blank like before, but yet again if i leave the comboboxes blank it WILL bring up the data for all the titleName and ArtistName data that is in the database. Am I supposed to be calling a query or a should i be calling the tblComics that had all the data listed in it with the titlID.titleName FK and artistID.artistNAme FK listed in it. UGH I am lost on how to get this to work!!again here is the code:
Private Sub Command10_Click()
'On Error Go To Err_Command10_Click
Dim DocName As String
Dim stFilter As String
Dim lgtitlename As String
Dim lgartistname As String
strFilter = "1=1"
If Not IsNull(Me.titleName) Then
strFilter = strFilter & " And [titleName] = "" & Me.artistName & "" "
End If
If Not IsNull(Me.artistName) Then
strFilter = strFilter & " And [artistName]="" & Me.artistName & "" "
End If

stDocName = "Comic_List"
'stWhere = "[" & Me!titleName & "]=" & Me!titleName & ""
'stWhere = "[" & Me!artistName & "] =" & Me!artistName & ""
DoCmd.OpenReport stDocName, acPreview, , strFilter = Forms!Comic_Filter!titleName & artistName
Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
End Sub
------------------------------------------------------------
Here is the qry's i have with the respective SQL code:
------------------------------------------------------------
qryArtist
SELECT tblArtist.artistName, tblTitle.titleName
FROM tblTitle INNER JOIN (tblPublisher INNER JOIN (tblArtist INNER JOIN tblComics ON tblArtist.Artist_ID = tblComics.Artist_ID) ON tblPublisher.Publisher_ID = tblComics.Publisher_ID) ON tblTitle.Title_ID = tblComics.Title_ID;

qryTitle
SELECT tblTitle.titleName
FROM tblTitle;

qryComics
SELECT tblTitle.titleName, tblComics.Edition, tblComics.Volume, tblArtist.artistName, tblWriter.writerName, tblPublisher.publisherName, tblComics.[Publish Date], tblComics.[Purchase Price], tblComics.[Market Value]
FROM tblWriter INNER JOIN (tblTitle INNER JOIN (tblPublisher INNER JOIN (tblArtist INNER JOIN tblComics ON tblArtist.Artist_ID = tblComics.Artist_ID) ON tblPublisher.Publisher_ID = tblComics.Publisher_ID) ON tblTitle.Title_ID = tblComics.Title_ID) ON tblWriter.Writer_ID = tblComics.Writer_ID;
------------------------------------------------------------
Here is a list of my tables with the feilds in them
------------------------------------------------------------
tblComics
Comics_ID (Priamry Key)
Title_ID (Foreign Key)
Publisher_ID (foreign Key)
Artist_ID (Foreign Key)
Writer_ID (Foreighn Key)
Edition
Volume
Publish Date
purchasePrice
marketValue
Description
picture1

tblArtist
artist_ID (Primary Key)
artistName

tblTitle
title_ID (primary Key)

tblpublisher
publisher_ID (priamry Key)
publisherName

tblWriter
writer_ID (Primary Key)
writerName
------------------------------------------------------------
And agin I would like a form that has two combo box'es on it one labeled titleName and the seconf labeled artistName. I want the drop down on the comboboxes to display the actuall names not the ID number and after selecting two choices from the combo box'es to push the cmdButton which will in turn open a report that I already have made, which display all the data from qryComics for only the selected titleName and artistName that we chose.Complicated isnt it. Again any help would be great, and dhookom thanks for the help but it does the same thing as before, and maybe that is my fualt for not giving all the information. Thanks
J

 

[dhookom]

I'm not sure why you used

   DoCmd.OpenReport stDocName, acPreview, , strFilter[b][red][s] = Forms!Comic_Filter!titleName & artistName[/s][/red][/b]

Try

   DoCmd.OpenReport stDocName, acPreview, , strFilter

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

Oh sorry I had that " ' " out. So the problem I stated above is the same with out "= Forms!Comic_Filter!titleName & artistName" in the code. It weird if i don't select any properties in the combo boxes then it will pull up 81 pages worthof data with everything listed the way the report was deisigned to work, but if select an actual value in either the titleName combo box and the artistName combo box the ony three pages will appear which display the fields in the report but no data.

 

[dhookom]

I expect your combo boxes might be bound to the artist and title IDs rather than the names. You need to decide if you want the combo boxes to return the name or the ID and then use the appropriate field in the strFilter. Keep in mind most quotes are not required if using a numeric field in the strFilter.

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

I would like them to return the name

 

[josh2008]

And I am afraid I dont understand what you mean, sorry

 

[dhookom]

What are these properties of each combo box:

Name:
Row Source:
Bound Column:
Column Count:

Can we assume your ID fields are numeric autonumbers?

Normally when I create combo boxes, I set the bound column to the Primary Key Autonumber field. Then I make sure the report record source contains the ID fields. My code then looks something like:

  strFilter = "1=1 "
  If Not IsNull(Me.TitleID) Then
    strFilter = strFilter & " And [TitleID]=" & _
       Me.TitleID
  End If
  If Not IsNull(Me.artistID) Then
    strFilter = strFilter & " And [artistID]=" & _
       Me.artistID
  End If

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

this is what i have in my row source for the artist combo box

SELECT [tblArtist].[Artist_ID], [tblArtist].[artistName] FROM tblArtist ORDER BY [artistName];

and like wise for the title combo box

SELECT [tblTitle].[Title_ID], [tblTitle].[titleName] FROM tblTitle ORDER BY [titleName];

My bound column for both of them is set to 1
and yes my primary keys for both tbltitle and tblartist are set to autonumber

 

[josh2008]

when i changed the code to what you suggested
---------------------------------------------------------------
"strFilter = "1=1 "
If Not IsNull(Me.TitleID) Then
strFilter = strFilter & " And [TitleID]=" & _
Me.TitleID
End If
If Not IsNull(Me.artistID) Then
strFilter = strFilter & " And [artistID]=" & _
Me.artistID
End If
---------------------------------------------------------------
I got this
Compile Error: Method or Data member not found
If Not IsNull(Me.Title_ID) Then

and the above part ".Title_ID" is highlighted blue

 

[josh2008]

okay when i changed the code to this:
---------------------------------------------------------------

Private Sub Command10_Click()
'On Error Go To Err_Command10_Click
Dim DocName As String
Dim stFilter As String
Dim lgtitleName As String
Dim lgartistName As String
strFilter = "1=1"
If Not IsNull(Me.titleName) Then
strFilter = strFilter & " And [Title_ID] = "" &_ Me.Title_ID & "" "
End If
If Not IsNull(Me.artistName) Then
strFilter = strFilter & " And [Artist_ID]="" &_ Me.Artist_ID & "" "
End If

stDocName = "Comic_List"
DoCmd.OpenReport stDocName, acPreview, , strFilter
Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
End Sub

-------------------------------------------------------------

a message boz appeared asking for the Title_ID. I entered 1 then a message box appeared asking for the artist_ID, and again I entered one becuase I know that these to numbers match together and should bring up the correct data. But yet again a blank report opened??

 

[dhookom]

I didn't want you to change the name of your control in the code. Your title combo box name is "titleName" try this code that assumes you have the TitleID and ArtistID fields in your report's record source:

  strFilter = "1=1 "
  If Not IsNull(Me.titleName) Then
    strFilter = strFilter & " And [TitleID]=" & _
       Me.titleName
  End If
  If Not IsNull(Me.artistName) Then
    strFilter = strFilter & " And [artistID]=" & _
       Me.artistName
  End If

This is all rather confusing since your combo boxes have values of the ID but you have chosen to name them with "...Name". At least they don't have names like "combo7"


Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

Same result message box appears asking for the title_ID and message box appears asking for artisr_ID then produces a blank report. If I dont put any values into the combo box's then the report will show all the data in my tblComics so it works like that but I cant select the names in the comboboxes to show on th report. I am pretty confused myself, and again i appreciate the help

 

[josh2008]

By the way there are multiple values in both combo box's in case that makes a difference. There are 55 title names in the tbltitle and 12 values in the tblartist. so when i click on the drop down in the combo box's i see all of them. choose my selection for both and a blank report appears. I don t know if that helps clarify things

 

[josh2008]

And this is my my Record Source for the actual Comic_List
----------------------------------------------------------------
SELECT tblComics.Comics_ID, tblTitle.titleName, tblComics.Edition, tblPublisher.publisherName, tblComics.[Purchase Price], tblComics.[Market Value], tblComics.Description, tblComics.[Publish Date], tblArtist.artistName, tblWriter.writerName, tblComics.Volume, tblComics.picture1 FROM tblWriter INNER JOIN (tblArtist INNER JOIN (tblPublisher INNER JOIN (tblTitle INNER JOIN tblComics ON

 

[josh2008]

SELECT tblComics.Comics_ID, tblTitle.titleName, tblComics.Edition, tblPublisher.publisherName, tblComics.[Purchase Price], tblComics.[Market Value], tblComics.Description, tblComics.[Publish Date], tblArtist.artistName, tblWriter.writerName, tblComics.Volume, tblComics.picture1 FROM tblWriter INNER JOIN (tblArtist INNER JOIN (tblPublisher INNER JOIN (tblTitle INNER JOIN tblComics ON tblTitle.Title_ID=tblComics.Title_ID) ON tblPublisher.Publisher_ID=tblComics.Publisher_ID) ON tblArtist.Artist_ID=tblComics.Artist_ID) ON tblWriter.Writer_ID=tblComics.Writer_ID;

---------------------------------------------------------------
Sorry got cut this is the whole thing

 

[dhookom]

try this code that assumes you have the TitleID and ArtistID fields in your report's record source

SELECT tblComics.Comics_ID, tblTitle.titleName, tblComics.Edition, tblPublisher.publisherName, tblComics.[Purchase Price], tblComics.[Market Value], tblComics.Description, tblComics.[Publish Date], tblArtist.artistName, tblWriter.writerName, tblComics.Volume, tblComics.picture1 FROM tblWriter INNER JOIN (tblArtist INNER JOIN (tblPublisher INNER JOIN (tblTitle INNER JOIN tblComics ON tblTitle.Title_ID=tblComics.Title_ID) ON tblPublisher.Publisher_ID=tblComics.Publisher_ID) ON tblArtist.Artist_ID=tblComics.Artist_ID) ON tblWriter.Writer_ID=tblComics.Writer_ID;

Fix this to add the fields that are expected to be there.

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

I don't understand. do you want me to place the SQL code that is in the record source for my Comic_List report into the record source for the combo boxes?

 

[dhookom]

No, I would expect you to add the fields Title_ID and Artist_ID to the report's record source query. You can't apply a filter based on the Title_ID unless the Title_ID is in the report's record source.

Just set your report's record source to:

SELECT [b][red]tblComics.Artist_ID, tblComics.Title_ID,[/red][/b]tblComics.Comics_ID, tblTitle.titleName, tblComics.Edition, tblPublisher.publisherName, tblComics.[Purchase Price], tblComics.[Market Value], tblComics.Description, tblComics.[Publish Date], tblArtist.artistName, tblWriter.writerName, tblComics.Volume, tblComics.picture1 FROM tblWriter INNER JOIN (tblArtist INNER JOIN (tblPublisher INNER JOIN (tblTitle INNER JOIN tblComics ON tblTitle.Title_ID=tblComics.Title_ID) ON tblPublisher.Publisher_ID=tblComics.Publisher_ID) ON tblArtist.Artist_ID=tblComics.Artist_ID) ON tblWriter.Writer_ID=tblComics.Writer_ID;

Duane
Hook'D on Access
MS Access MVP