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.

 

[josh2008]

OHHH wow so close. Okay put that in the row Source of the report Comic_List. then ran the form and selected the values for both the titleName and the ArtistName, and instead of a blank report coming up the report displayed all the data in my database. So something happened, but still not filtering just the values selected in the combo Boxes

 

[dhookom]

What is your code now?

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

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

 

[josh2008]

Should my report's record source be pointing to my qryComics (query) or should it be the:
SELECT tblComics.Artist_ID, tblComics.Title_ID, 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;

 

[dhookom]

The fields in your report's record source that match the values in your combo boxes are [red]Title_ID and Artist_ID[/red].

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 [red][b][title_ID][/b][/red] = " & Me.titleName
   End If
   If Not IsNull(Me.artistName) Then
      strFilter = strFilter & " And [red][b][artist_ID][/b][/red] = " & Me.artistName
   End If
         
   stDocName = "Comic_List"
   DoCmd.OpenReport stDocName, acPreview, , strFilter
   
Exit_Command10_Click:
   Exit Sub
   
Err_Command10_Click:
 End Sub

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

Still does the same thing, pulls up all the records in the report not the specfic ones selected in the combo boxes

 

[josh2008]

actually it doesnt work
Run time Error '3464': Data type mismatch in criteria expression

this is what is highlighted:
DoCmd.OpenReport stDocName, acPreview, , strFilter

 

[dhookom]

Are you using the exact code from my posting on 29 Feb 08 18:27? What are the data types of your title_ID and artist_ID fields?

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

Yes I tried the code from that posting it didn't run though, it gave the error that i posted above. The data types for Title_ID and Artist_ID are Auto-number.

 

[dhookom]

Add this line in your code:

   stDocName = "Comic_List"
   debug.Print "strFilter: " & strFilter
   DoCmd.OpenReport stDocName, acPreview, , strFilter

Then press Ctrl+G to see the value of strFilter.


Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

strFilter: 1=1 And [titleName] = " & Me.titleName & "

 

[josh2008]

In another post you said something about the combo box being multi select? If what you mean by that is that when you click the drop down arrow on the combo box that you have multiple options like for example my titleNmae combo box has 55 different choices to choose from , in other words there are 55 titles. And my artistName combo box there are 12 different options to choose from, in other words there are 12 different artist names listed. Please help!!! lol I am dying here

 

[dhookom]

This [tt][red]strFilter: 1=1 And [titleName] = " & Me.titleName & " [/red][/tt] suggests your code is still wrong. Please reply back with your code.

Combo boxes are never multi-select. A list box can be multi-select.

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

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.titleName.Column(1) & " ' "
End If
If Not IsNull(Me.artistName) Then
strFilter = strFilter & " And [artistName]="" & Me.artistName "" "
End If

stDocName = "Comic_List"
Debug.Print "strFilter: " & strFilter
DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
End Sub

 

[dhookom]

Why don't you try the solutions I suggest? It seems like you are getting other advise from somewhere else and are attempting to combine the two sources.

I feel like I am wasting my time helping when you seem to be ignoring my suggestions.


Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

I got this little code to work to open one combo box selection to generate the right report.But dont know how to add a second combobox selection to it. Also created a qry that calls the titlename and artist name. No one seems to know how to do this. I have not been able to find the answer to this anywhere.


Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, , "[Title_ID]=" & Me![titleName], acWindowNormal
End Sub

 

[dhookom]

No one seems to know how to do this. I have not been able to find the answer to this anywhere.

Why doesn't the code you posted on 1 Mar 08 11:27 look like my recommended solution on 29 Feb 08 18:27?

Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

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.titleName
End If
If Not IsNull(Me.artistName) Then
strFilter = strFilter & " And [Artist_ID] = " & Me.artistName
End If

stDocName = "Comic_List"
Debug.Print "strFilter: " & strFilter
DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
End Sub
------------------------------------------------------------
The above code does not work. It brings the report up with27 pages worth of data, it should only have 6 pages of data. the only code that seems to do anything right is:
-------------------------------------------------------------
Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, , "[Title_ID]=" & Me![titleName], acWindowNormal
End Sub
----------------------------------------------------------
but that is for only one combobox

Im sorry if your getting frustrated I am too, but I do appreciate all your help thus far, but something is wrong with my old code.
I just want to be able to select criteria in one combo box then the criteria in the next combo box to generate the report of the selected criteria

 

[dhookom]

What do you see in the debug window after opening the report? Press Ctrl+G to see the result of
Debug.Print "strFilter: " & strFilter


Duane
Hook'D on Access
MS Access MVP

 

[josh2008]

I am not using that code any more, I am using this one because it works better then the other one:
--------------------------------------------------------------
Private Sub Submit_Click()
DoCmd.OpenReport "Comic_List", acViewPreview, ,_ "(tblTitle.TitleName=[Forms]![Comic_List]![titleName]) and_ (tblArtist.artistname=[Forms]![Comic_List]![artistName])"
End Sub
----------------------------------------------------------