List Box to create a Report based on crosstab query

G

Gntlhnds

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

Douglas J. Steele

Sorry, but you can't change the selection in the list box with WHICH query
being updatable?

The only query you showed is the crosstab query, which by definition is
never updatable. Not only that, but it doesn't refer to the list box!
 
D

Daryl S

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

Gntlhnds

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
P

Piet Linden

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]=')
Click to expand...

Filter the form with selections from a multi-select listbox?
this should do it...
"Use Multiselect listbox to limit records in report"
http://www.mvps.org/access/reports/rpt0005.htm
 
D

Daryl S

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gntlhnds

You're a life saver. It worked just as I wanted it to. Thank you.

Daryl S said:
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 said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AVG crosstab query with one field containing text 7
Trying to create a report that will list unmatched records by Ticket Primary Key 0
2nd list box not writing to text box 1
Report based on Crosstab Query 3
Create filters for report based on list box 3
Crosstab report - handling Null fields 4
add SQL query to report or otherwise enable to produce a report fr 4
Sub report based on Crosstab query 4

Top