list box not refreshing (DAO query based list box)

  • Thread starter Richard Hollenbeck
  • Start date
R

Richard Hollenbeck

The value of the second list box properly cascades on the value of the first
one as far as putting data into the second list box, but when I click on a
student without a value for the second list box it doesn't erase the value
from the previous student. I'm wondering what I'm doing wrong.

Here's the code:

Private Sub lstStudents_AfterUpdate()
On Error GoTo Err_lstStudents_AfterUpdate

Dim StrSQL As String
Dim dbStudents As DAO.Database
Dim rsStudents As DAO.Recordset

StrSQL = "SELECT tblExtraCredit.activityDescription AS Activity FROM
tblExtraCredit INNER JOIN tblExraCreditScores ON tblExtraCredit.ID =
tblExraCreditScores.ID WHERE tblExraCreditScores.studentID = " &
lstStudents.Column(0)
Set dbStudents = CurrentDb()
Set rsStudents = dbStudents.OpenRecordset(StrSQL, dbOpenDynaset)
rsStudents.MoveFirst
Set lstActivities.Recordset = rsStudents

' The following command (lstActivities.Requery) is intended
' to empty the lstActivities list box if a record is blank.
' However, it doesn't work. If I click on a student's name
' where there is no extra credit activity
' the lstActivities listbox will remain blank. Once I click
' on a student that DOES have scores
' the activity will appear in the list box. But then when
' I click on a student with no Extra
' Credit activity the list box will STILL SHOW the
' data from the previous student.

lstActivities.Requery ' This isn't working

'rsStudents.Close (disabled because it's causing problems)
'dbStudents.Close (disabled because it's causing problems)

txtScore.Enabled = True

Exit_lstStudents_AfterUpdate:
Exit Sub

Err_lstStudents_AfterUpdate:
Call myErrorHandler(Err.Number, Err.Description, Err.Source)
Resume Exit_lstStudents_AfterUpdate

End Sub
 
D

Dirk Goldgar

Richard Hollenbeck said:
The value of the second list box properly cascades on the value of
the first one as far as putting data into the second list box, but
when I click on a student without a value for the second list box it
doesn't erase the value from the previous student. I'm wondering
what I'm doing wrong.

Here's the code:

Private Sub lstStudents_AfterUpdate()
On Error GoTo Err_lstStudents_AfterUpdate

Dim StrSQL As String
Dim dbStudents As DAO.Database
Dim rsStudents As DAO.Recordset

StrSQL = "SELECT tblExtraCredit.activityDescription AS Activity
FROM tblExtraCredit INNER JOIN tblExraCreditScores ON
tblExtraCredit.ID = tblExraCreditScores.ID WHERE
tblExraCreditScores.studentID = " & lstStudents.Column(0)
Set dbStudents = CurrentDb()
Set rsStudents = dbStudents.OpenRecordset(StrSQL, dbOpenDynaset)
rsStudents.MoveFirst
Set lstActivities.Recordset = rsStudents

' The following command (lstActivities.Requery) is intended
' to empty the lstActivities list box if a record is blank.
' However, it doesn't work. If I click on a student's name
' where there is no extra credit activity
' the lstActivities listbox will remain blank. Once I click
' on a student that DOES have scores
' the activity will appear in the list box. But then when
' I click on a student with no Extra
' Credit activity the list box will STILL SHOW the
' data from the previous student.

lstActivities.Requery ' This isn't working

'rsStudents.Close (disabled because it's causing problems)
'dbStudents.Close (disabled because it's causing problems)

txtScore.Enabled = True

Exit_lstStudents_AfterUpdate:
Exit Sub

Err_lstStudents_AfterUpdate:
Call myErrorHandler(Err.Number, Err.Description, Err.Source)
Resume Exit_lstStudents_AfterUpdate

End Sub
Click to expand...

I'm not sure, but it could be that the SQL statement is malformed in
that case, so that you aren't getting a valid recordset back in
rsStudents. I suggest you step through the code and examine the values
of strSQL and rsStudents before you assign it.

Why are you going to all the trouble to dynamically assign a recordset
object to the list box? Why not just set your listbox's rowsource to

SELECT tblExtraCredit.activityDescription
FROM tblExtraCredit INNER JOIN tblExraCreditScores
ON tblExtraCredit.ID = tblExraCreditScores.ID
WHERE tblExraCreditScores.studentID =
[Forms]![YourFormName]![lstStudents];

(subsituting your form's name for "YourFormName"), and then reduce
lstStudents_AfterUpdate() to:

'----- start of suggested code -----
Private Sub lstStudents_AfterUpdate()

On Error GoTo Err_lstStudents_AfterUpdate

Me!lstActivities.Requery

Me!txtScore.Enabled = True

Exit_lstStudents_AfterUpdate:
Exit Sub

Err_lstStudents_AfterUpdate:
Call myErrorHandler(Err.Number, Err.Description, Err.Source)
Resume Exit_lstStudents_AfterUpdate

End Sub
'----- end of suggested code -----
 
D

david epsom dot com dot au

If you change a parameter in listbox rowsource, Access does
not always notice that the binary version of the query needs
to be updated. If that is what is happening here, checking
the RecordCount property may correctly update the listbox
rowsource, so that requery works correctly.

(david)
 
Top