Thanks, Ken.
I tried lstRoster.Value="" , and lstRoster.Value=Null, etc., and
lstRoster.RowSource=Null, lstRoster.RowSource="", etc., and
lstRoster.RecordSet=Nothing. None of them seem to do anything. So I made a
little subroutine for each listbox creating an empty RecordSet and setting
them to a list box. Here. I'll paste the entire code for the form,
including the subs to erase the list boxes. I have them disabled for now
because I'm looking at possible other solutions. Here's my klunky code:
Option Explicit
Private Sub Form_GotFocus()
DoCmd.Maximize
End Sub
Private Sub Form_Load()
DoCmd.Maximize
Dim StrSQL_Courses As String, dbCourses As DAO.Database, rsCourses As
DAO.Recordset
StrSQL_Courses = "SELECT courses.courseCode AS [Course],
courses.courseDescription AS [Description] from courses;"
Set dbCourses = CurrentDb()
Set rsCourses = dbCourses.OpenRecordset(StrSQL_Courses, dbOpenDynaset)
lstCourse.RowSourceType = "Table/Query"
Set lstCourse.Recordset = rsCourses
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
Private Sub lstActivities_Click()
Set Me.lstRoster.Recordset = Nothing 'doesn't do anything
Dim StrSQL_Roster As String
Dim dbRoster As DAO.Database
Dim rsRoster As DAO.Recordset
StrSQL_Roster = "SELECT students.StudentNumber AS Student, [lName] & ',
' & [fName] AS Name, studentScores.score FROM (students INNER JOIN
studentsInCourses ON students.studentID = studentsInCourses.studentID) INNER
JOIN studentScores ON students.studentID = studentScores.studentID WHERE
studentScores.activityID = " & lstActivities & " ORDER BY [lName] & ', ' &
[fName];"
Set dbRoster = CurrentDb()
Set rsRoster = dbRoster.OpenRecordset(StrSQL_Roster, dbOpenDynaset)
lstRoster.RowSourceType = "Table/Query"
Set lstRoster.Recordset = rsRoster
End Sub
Private Sub lstCourse_Click()
'clearGroups '(sub at bottom to clear listbox) turned
off
'clearActivities '(sub at bottom to clear listbox) turned
off
'clearRoster '(sub at bottom to clear listbox) turned
off
Set Me.lstRoster.Recordset = Nothing 'doesn't do anything
Dim StrSQL_Groups As String
Dim dbGroups As DAO.Database
Dim rsGroups As DAO.Recordset
StrSQL_Groups = "SELECT groups.groupID AS [Group],
groups.GroupDescription AS [Description] FROM groups WHERE groups.courseCode
= '" & lstCourse & "' ORDER BY [groups].[groupOrder];"
Set dbGroups = CurrentDb()
Set rsGroups = dbGroups.OpenRecordset(StrSQL_Groups, dbOpenDynaset)
lstGroups.RowSourceType = "Table/Query"
Set lstGroups.Recordset = rsGroups
End Sub
Private Sub lstGroups_Click()
'clearActivities '(sub at bottom to clear listbox)
turned off
'clearRoster '(sub at bottom to clear listbox)
turned off
Dim StrSQL_Activities As String
Dim dbActivities As DAO.Database
Dim rsActivities As DAO.Recordset
StrSQL_Activities = "SELECT activities.activityID as [Activity],
activities.activityDescription AS [Description]"
StrSQL_Activities = StrSQL_Activities & "FROM activities "
StrSQL_Activities = StrSQL_Activities & "WHERE activities.groupID = " &
lstGroups & " ORDER BY [activities].[activityOrder];"
Set dbActivities = CurrentDb()
Set rsActivities = dbActivities.OpenRecordset(StrSQL_Activities,
dbOpenDynaset)
lstActivities.RowSourceType = "Table/Query"
Set lstActivities.Recordset = rsActivities
End Sub
Private Sub clearRoster() 'These are working, though they may be a little
rinky-dink
Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
StrSQL = "select students.* from students where fname = 'This will never
be a first name. It is just not a name'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
lstRoster.RowSourceType = "Table/Query"
Set lstRoster.Recordset = rs
Set rs = Nothing
End Sub
Private Sub clearActivities() 'These are working, though they may be a
little rinky-dink
Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
StrSQL = "select students.* from students where fname = 'This will never
be a first name. It is just not a name'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
lstActivities.RowSourceType = "Table/Query"
Set lstActivities.Recordset = rs
Set rs = Nothing
End Sub
Private Sub clearGroups() 'These are working, though they may be a little
rinky-dink
Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
StrSQL = "select students.* from students where fname = 'This will never
be a first name. It is just not a name'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
lstGroups.RowSourceType = "Table/Query"
Set lstGroups.Recordset = rs
Set rs = Nothing
End Sub
Ken Snell said:
Your first post said .Value, this one says .RowSource.
Normally, to do what you're wanting to do, which is to remove all the items
from the list box, you want to set the Row Source to an empty string.
Me.lstRoster.RowSource = ""
However, your code is not using the Row Source of the listbox to set the
list of items. It's using the Recordset of the listbox. Thus, try this:
Set Me.lstRoster.Recordset = Nothing
integer.
I
removed the quotes and BingBangBam! It started working correctly.
Now I'm trying to clear the cascaded list boxes in the event that the
user
changes a selection further up the line. I'll look into it. I tried
setting lstRoster.Value="" but that didn't work; the VBA editor demanded
an
object. I'll go to my trusty Access 2002 Desktop Developer's Handbook
which
just came in from Amazon to see what I can learn about text boxes. This
is
a great book, but it's TOO much information. Slowly-but-surely I think
this
book will begin to save my butt! It'll take a long time to get through
it.
If I can't find the answer, I'll look on the web then if I still can't
find
it I'll make a new post here. Otherwise, Hey! Thanks a million, Doug
Steele and Ken Snell, for your help!
Rich Hollenbeck
< DOH ! >
It was just before bedtime when I read and answered this post, so
you're
close, Doug, as to why I missed it! Thanks for the backup.
--
Ken Snell
<MS ACCESS MVP>
message
Ken: Time to get new glasses! <g> You missed the fact that Richard's
putting
quotes around the value returned from lstActivities.
If ActivityID is a Long Integer, here's what you need, Richard:
StrSQL_Roster = "SELECT students.studentNumber AS [ID],
students.lname
&', '& students.fname AS [Student], studentScores.Score FROM
students,
studentScores WHERE studentScores.activityID = " &
CLng(lstActivities)
(FWIW, the semi-colon is never actually required)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Yes, your assessment is probably correct. List boxes and combo
boxes
will
convert other columns to strings in the row sources. You
should
be
able
to
fix this by casting the lstActivities with the CLng function:
StrSQL_Roster = "SELECT students.studentNumber AS [ID],
students.lname
&', '& students.fname AS [Student], studentScores.Score FROM
students,
studentScores WHERE studentScores.activityID = '" &
CLng(lstActivities)
&
"';"
--
Ken Snell
<MS ACCESS MVP>
message
I have three list boxes. Depending on what is selected in the
first
list
box, the second will display different data. Whatever is
selected
in
the
second will affect the third. Now I have a fourth list box
that's
data
depends on the third. The first three work beautifully. The
forth
is
a
problem for me because it's pulling data from two different
tables.
The
data in the fourth list box should look something like this:
StudentID STUDENT SCORE
123 Doe, John 75
234 Blow, Joe 67
456 Rubble, Barney 78
678 Flintstone, Fred 89 etc. . .
The ID and Student data comes from the [students] table,
but
the
score
comes
from the [studentScores] table. [studentScores] has a composite
key
of
[studentScores].[studentID] and [studentScores].[activityID],
and
a
third
column, [studentScores].[score]. [activityID] is the bound
column
in
the
third list box lstActivities.
MY PROBLEM:
I'm getting the run-time error 3464 (data type mismatch in
criteria
expression) I think it might be because [activityID] is a Long
Integer
and
lstActivities may return a String? Or maybe I need to
write
an
INNER
JOIN
into the query? Any ideas? Many thanks. Here's my code:
Private Sub lstActivities_Click()
Dim StrSQL_Roster As String
Dim dbRoster As DAO.Database
Dim rsRoster As DAO.Recordset
StrSQL_Roster = "SELECT students.studentNumber AS [ID],
students.lname
&', '& students.fname AS [Student], studentScores.Score FROM
students,
studentScores WHERE studentScores.activityID = '" &
lstActivities
&
"';"
Set dbRoster = CurrentDb()
Set rsRoster = dbRoster.OpenRecordset(StrSQL_Roster,
dbOpenDynaset)
lstRoster.RowSourceType = "Table/Query"
Set lstRoster.Recordset = rsRoster
End Sub
