List Box to create a Report based on crosstab query
in [Forms]
|
Prev: Form_Delete event can't fire requery in other form
Next: Database security with read-only Vs Read\write access
From: Daryl S on 27 May 2010 15:53 Gntlhnds - You will need some VBA code to pull multi-select values from the list box, and put them into an IN clause with a list of values separated by commas. To build this ClassNbrList from the list box, you will need code like this to loop through the selected items in the combo box and append them into the list (this goes in your Private Sub PreviewGradeReport_Click() code before the DoCmd: Dim varItem As Variant Dim ClassNbrList as String ClassNbrList = "" With Me.Combo9 For Each varItem In .ItemsSelected If ClassNbrList = "" Then ClassNbrList = .ItemData(varItem) Else ClassNbrList = ClassNbrList & "," & .ItemData(varItem) End If Next varItem End With Then use this in your DoCmd.OpenReport line: DoCmd.OpenReport "rptGrades", 5, , "[ClassNumber] IN (" & ClassNbrList & ")" -- Daryl S "Gntlhnds" wrote: > This worked out great. Thanks. Next question, how would I get mulitiple > selections in the list box to properly populate the report? I get an error > when I try it (run-time error '3075': Syntax error (missing operator) in > query expression '[ClassNumber]=') > > "Daryl S" wrote: > > > Gntlhnds - > > > > It sounds like your list box is bound to field in the form. If the list box > > has the row source equal to the query, but the list box is not a bound field, > > then you should be able to select any item in the list box, and then proceed > > with your query. > > > > -- > > Daryl S > > > > > > "Gntlhnds" wrote: > > > > > I have a form with a list box to select a value, and a button that is pressed > > > to create a report based on the value selected in the list box. The problem > > > is the query used to create the report and populate the values in the list > > > box is a crosstab query, which is not updateable of course. > > > > > > Here is the SQL for my query: > > > > > > TRANSFORM Avg(Val([tblCourseGrades].[Grade])) AS AvgOfGrade > > > SELECT TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last Name], > > > TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1 > > > FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON > > > tblCourses.CourseID=tblCourseGrades.CourseID) ON > > > TblStudents.StudentID=tblCourseGrades.StudentID > > > WHERE (((IsNumeric(tblCourseGrades.Grade))<>False)) Or > > > (((tblCourseGrades.Grade) Is Null)) > > > GROUP BY TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last > > > Name], TblStudents.[First Name] > > > ORDER BY TblStudents.[ClassNumber], TblStudents.[Last Name] > > > PIVOT tblCourses.Course; > > > > > > Here is the code for the button that opens the report: > > > > > > Private Sub PreviewGradeReport_Click() > > > If Combo9.ItemsSelected.Count = 0 Then > > > Beep > > > MsgBox "No Item Selected", 48 > > > Exit Sub > > > Else > > > DoCmd.OpenReport "rptGrades", 5, , "[ClassNumber]=" & Me.Combo9 > > > End If > > > DoCmd.Close acForm, "frmSelectClassGrades", acSaveYes > > > End Sub > > > > > > Since this method will not work for me (I can't change the selection in the > > > list box without the query being updateable), what other ways are there > > > available to me to accomplish this task I'm trying? Thanks for your help.
From: Gntlhnds on 27 May 2010 17:25
You're a life saver. It worked just as I wanted it to. Thank you. "Daryl S" wrote: > Gntlhnds - > > You will need some VBA code to pull multi-select values from the list box, > and put them into an IN clause with a list of values separated by commas. To > build this ClassNbrList from the list box, you will need code like this to > loop through the selected items in the combo box and append them into the > list (this goes in your Private Sub PreviewGradeReport_Click() code before > the DoCmd: > > Dim varItem As Variant > Dim ClassNbrList as String > > ClassNbrList = "" > With Me.Combo9 > For Each varItem In .ItemsSelected > If ClassNbrList = "" Then > ClassNbrList = .ItemData(varItem) > Else > ClassNbrList = ClassNbrList & "," & .ItemData(varItem) > End If > Next varItem > End With > > Then use this in your DoCmd.OpenReport line: > > DoCmd.OpenReport "rptGrades", 5, , "[ClassNumber] IN (" & ClassNbrList & ")" > > > -- > Daryl S > > > "Gntlhnds" wrote: > > > This worked out great. Thanks. Next question, how would I get mulitiple > > selections in the list box to properly populate the report? I get an error > > when I try it (run-time error '3075': Syntax error (missing operator) in > > query expression '[ClassNumber]=') > > > > "Daryl S" wrote: > > > > > Gntlhnds - > > > > > > It sounds like your list box is bound to field in the form. If the list box > > > has the row source equal to the query, but the list box is not a bound field, > > > then you should be able to select any item in the list box, and then proceed > > > with your query. > > > > > > -- > > > Daryl S > > > > > > > > > "Gntlhnds" wrote: > > > > > > > I have a form with a list box to select a value, and a button that is pressed > > > > to create a report based on the value selected in the list box. The problem > > > > is the query used to create the report and populate the values in the list > > > > box is a crosstab query, which is not updateable of course. > > > > > > > > Here is the SQL for my query: > > > > > > > > TRANSFORM Avg(Val([tblCourseGrades].[Grade])) AS AvgOfGrade > > > > SELECT TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last Name], > > > > TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1 > > > > FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON > > > > tblCourses.CourseID=tblCourseGrades.CourseID) ON > > > > TblStudents.StudentID=tblCourseGrades.StudentID > > > > WHERE (((IsNumeric(tblCourseGrades.Grade))<>False)) Or > > > > (((tblCourseGrades.Grade) Is Null)) > > > > GROUP BY TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last > > > > Name], TblStudents.[First Name] > > > > ORDER BY TblStudents.[ClassNumber], TblStudents.[Last Name] > > > > PIVOT tblCourses.Course; > > > > > > > > Here is the code for the button that opens the report: > > > > > > > > Private Sub PreviewGradeReport_Click() > > > > If Combo9.ItemsSelected.Count = 0 Then > > > > Beep > > > > MsgBox "No Item Selected", 48 > > > > Exit Sub > > > > Else > > > > DoCmd.OpenReport "rptGrades", 5, , "[ClassNumber]=" & Me.Combo9 > > > > End If > > > > DoCmd.Close acForm, "frmSelectClassGrades", acSaveYes > > > > End Sub > > > > > > > > Since this method will not work for me (I can't change the selection in the > > > > list box without the query being updateable), what other ways are there > > > > available to me to accomplish this task I'm trying? Thanks for your help. |