Prev: Form_Delete event can't fire requery in other form
Next: Database security with read-only Vs Read\write access
From: Gntlhnds on 25 May 2010 13:43
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: Douglas J. Steele on 25 May 2010 14:09
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!

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Gntlhnds" <Gntlhnds(a)discussions.microsoft.com> wrote in message
news:968D3B7A-696C-4252-B972-37DC85836A26(a)microsoft.com...
>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: Daryl S on 25 May 2010 14:36
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 25 May 2010 15:03
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: Piet Linden on 26 May 2010 15:42
On May 25, 2:03 pm, Gntlhnds <Gntlh...(a)discussions.microsoft.com>
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]=')
>

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
 |  Next  |  Last
Pages: 1 2
Prev: Form_Delete event can't fire requery in other form
Next: Database security with read-only Vs Read\write access