Bizarre ListBox Behavior

[Robert T]

Hello:

We have a many to many application used to register students in our classes.
One of the forms is One Class to Many Students. We have two list boxes on
this One to Many form used to register our students in a class.

The list box on the left shows all of the unregistered students in our
student table. When we click on a name or names, that moves them from the
unregistered listbox on the left to the listbox on the right, which is of
course the Registered box.

More than 90% of the time, it works perfectly. However, we've recently
noticed that if you click on 1 student's name in the left, unregistered the
box. 2 students are registered in the right, registered list box. At first I
thought the user was clicking between 2 names but that isn't the case. In
fact, it's registering the student's name the user clicks on and the 2nd name
is not adjacent, it usually is a student with a very different last name.

I'm stumped, I don't even know where to start so I would truly appreciate
any suggestions.

Thanks,
Robert

 

[John Vinson]

I'm stumped, I don't even know where to start so I would truly appreciate
any suggestions.

Please post the VBA code (in the AfterUpdate event of each listbox, I
would guess). This feature is not builtin to Access and someone
programmed it into these listboxes; evidently there is some error in
the code.

John W. Vinson[MVP]

 

[Robert T]

Here's the code. The really tough issue is the fact that sometimes it works
and sometimes 2 students are selected with 1 click.

Private Sub cmdAddStudent_Click()
On Error GoTo Err_cmdAddStudent_Click

Dim sUpdate As String
Dim vSelected As Variant

If Me.lstStudentsNotRegistered.ListIndex < 0 Then
MsgBox "Please select from the Not Registered list first.", vbCritical
Exit Sub
End If

DoCmd.SetWarnings False

For Each vSelected In Me.lstStudentsNotRegistered.ItemsSelected
sUpdate = "Insert Into tblLink (ClassNo, StudentID,CourseName,
Class_Date,Class_Time,Student_Email) " & _
" Values('" & Me.cboClass.Column(0) & "','" &
Me.lstStudentsNotRegistered.ItemData(vSelected) & _
"','" & Me.cboClass.Column(1) & "','" &
Me.cboClass.Column(2) & "','" & Me.cboClass.Column(3) & _
"','" & Replace(Me.lstStudentsNotRegistered.Column(2,
vSelected), "'", "''") & "')"
DoCmd.RunSQL sUpdate
Next

DoCmd.SetWarnings True

Me.lstRegStudents.Requery
Me.lstStudentsNotRegistered.Requery

For Each vSelected In Me.lstStudentsNotRegistered.ItemsSelected
Me.lstStudentsNotRegistered.Selected(vSelected) = False
Next

Exit_cmdAddStudent_Click:
Exit Sub

Err_cmdAddStudent_Click:
MsgBox Err.Description
Resume Exit_cmdAddStudent_Click

End Sub

 

[Robert T]

Hi TonyT:

Thanks for looking over the code. There are no duplicate StudentID numbers
because that's a primary key. In addition, I visually checked to see if there
was anything the two students had in common and there was nothing.

The row source is a query.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI] AS sName,
tblStudents.Student_Email
FROM tblStudents LEFT JOIN qryRegistered ON tblStudents.StudentID =
qryRegistered.StudentID
WHERE (((qryRegistered.StudentID) Is Null))
ORDER BY [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI];

Robert T

 

[Douglas J. Steele]

What's qryRegistered? Does it have duplicate student ids in it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi TonyT:

Thanks for looking over the code. There are no duplicate StudentID numbers
because that's a primary key. In addition, I visually checked to see if
there
was anything the two students had in common and there was nothing.

The row source is a query.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI] AS
sName,
tblStudents.Student_Email
FROM tblStudents LEFT JOIN qryRegistered ON tblStudents.StudentID =
qryRegistered.StudentID
WHERE (((qryRegistered.StudentID) Is Null))
ORDER BY [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI];

Robert T

Nothing obvious wrong there that I can see, so that leaves either
Duplicated
reference (poss StudentID) or an error in the rowsource of the listbox -
perhaps posting that too would help.

 

[TonyT]

I don't understand why that code is looking for qryRegistered,StudentID isNull.
If as you say StudentID is the Primary key in tblStudents, then why are
their records in the child table without reference to the parent table?

Or is this the code for the listbox displaying the unregistered students as
opposed to the registered students which was the one I was asking to see
previously, but didn't make very clear I'm afraid?

TonyT..

Hi TonyT:

Thanks for looking over the code. There are no duplicate StudentID numbers
because that's a primary key. In addition, I visually checked to see if there
was anything the two students had in common and there was nothing.

The row source is a query.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI] AS sName,
tblStudents.Student_Email
FROM tblStudents LEFT JOIN qryRegistered ON tblStudents.StudentID =
qryRegistered.StudentID
WHERE (((qryRegistered.StudentID) Is Null))
ORDER BY [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI];

Robert T

Nothing obvious wrong there that I can see, so that leaves either Duplicated
reference (poss StudentID) or an error in the rowsource of the listbox -
perhaps posting that too would help.

 

[John Vinson]

For Each vSelected In Me.lstStudentsNotRegistered.ItemsSelected

I think this is the culprit. If you're doing one student at a time,
might it not be best to make this listbox a single-select rather than
a multiselect? It appears that more rows are being selected than you
expect!

Or, you might want to unselect all the rows in the listbox in the
Current event of the form, to be sure you're starting with a clean
slate.

I'd try stepping through the code, setting a breakpoint, and seeing
what gets executed when.

John W. Vinson[MVP]

 

[Robert T]

Yes Tony, this is the code for the listbox displaying the unregistered
students.

John Vinson has an interesting thought. Although we want the ability to
select mulitple students at one, in reality, more often than not, we are only
registering one student at a time. So maybe I should change it to a single
select box? That should solve our problem.

qryRegistered is a 2-table query with tblStudents linked to tblLink via a
One to One relationship based on StudentID. Since it's a one to one link, I
would think it would still have unique studentIDs.

Here's the rowsource code [qryRegistered] for the registered students listbox.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] AS sName, tblLink.ClassNo
FROM tblStudents INNER JOIN tblLink ON tblStudents.StudentID =
tblLink.StudentID
WHERE (((tblLink.ClassNo)=[Forms]![frmClassReg]![cboClass]));

Robert




:

Or is this the code for the listbox displaying the unregistered students as
opposed to the registered students which was the one I was asking to see
previously, but didn't make very clear I'm afraid?

TonyT..

Hi TonyT:

Thanks for looking over the code. There are no duplicate StudentID numbers
because that's a primary key. In addition, I visually checked to see if there
was anything the two students had in common and there was nothing.

The row source is a query.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI] AS sName,
tblStudents.Student_Email
FROM tblStudents LEFT JOIN qryRegistered ON tblStudents.StudentID =
qryRegistered.StudentID
WHERE (((qryRegistered.StudentID) Is Null))
ORDER BY [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI];

Robert T

Nothing obvious wrong there that I can see, so that leaves either Duplicated
reference (poss StudentID) or an error in the rowsource of the listbox -
perhaps posting that too would help.

 

[TonyT]

as a sideline John, is the following code insufficient to guarantee starting
with a clean *unslected* listbox on next entry?;

For Each vSelected In Me.lstStudentsNotRegistered.ItemsSelected
Me.lstStudentsNotRegistered.Selected(vSelected) = False
Next

TonyT..

 

[TonyT]

Does this ever happen as the 1st selection made (single or multiple) on
entering the form from 1st starting the database?

TonyT..

Yes Tony, this is the code for the listbox displaying the unregistered
students.

John Vinson has an interesting thought. Although we want the ability to
select mulitple students at one, in reality, more often than not, we are only
registering one student at a time. So maybe I should change it to a single
select box? That should solve our problem.

qryRegistered is a 2-table query with tblStudents linked to tblLink via a
One to One relationship based on StudentID. Since it's a one to one link, I
would think it would still have unique studentIDs.

Here's the rowsource code [qryRegistered] for the registered students listbox.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] AS sName, tblLink.ClassNo
FROM tblStudents INNER JOIN tblLink ON tblStudents.StudentID =
tblLink.StudentID
WHERE (((tblLink.ClassNo)=[Forms]![frmClassReg]![cboClass]));

Robert




:

Or is this the code for the listbox displaying the unregistered students as
opposed to the registered students which was the one I was asking to see
previously, but didn't make very clear I'm afraid?

TonyT..

Hi TonyT:

Thanks for looking over the code. There are no duplicate StudentID numbers
because that's a primary key. In addition, I visually checked to see if there
was anything the two students had in common and there was nothing.

The row source is a query.

SELECT tblStudents.StudentID, [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI] AS sName,
tblStudents.Student_Email
FROM tblStudents LEFT JOIN qryRegistered ON tblStudents.StudentID =
qryRegistered.StudentID
WHERE (((qryRegistered.StudentID) Is Null))
ORDER BY [tblStudents].[Student_LastName] & ", " &
[tblStudents].[Student_FirstName] & " " & [tblStudents.Student_MI];

Robert T

Nothing obvious wrong there that I can see, so that leaves either Duplicated
reference (poss StudentID) or an error in the rowsource of the listbox -
perhaps posting that too would help.

 

[Robert T]

I think I figured out what was wrong. However, before getting into what was
causing our problem, let me state I decided to go with John Vinson's
suggestion. We have approximately 5,000 students in our database, so it was
rare when we had more than one student on the same screen. Therefore,
selecting only 1 student at a time was in reality OK.

On the flip side, in our One Student to Many Classes Form, the list box
always works perfectly. In fact, we use that form far more often because one
student usually wants to register for multiple classes. We never have any
problems with that list box and we use it about 90% of the time.
-------------------------
Cause of the problem?
-------------------------
I decided to watch another user register students and saw what was
happening. Since there are almost 5,000 students in the table, moving from
the letter B, to let's say the letter R, required lots of work. So he would
click a blank spot in the list box to activate the box, type in the letter R,
and move to the Rs and then select the student. Although it wasn't displayed
on the screeen, apparently that initial click was selecting another student
who wasn't registering for the class. Therefore, changing it to a Single
Select box solved the problem.

We could resolve the initial problem if that ListBox had a type in box above
it just like a combo box. Then the user could simply type the letter R and
move to the Rs, and he/she wouldn't have to first click in the listbox.

Robert

 

[John Vinson]

I decided to watch another user register students and saw what was
happening. Since there are almost 5,000 students in the table, moving from
the letter B, to let's say the letter R, required lots of work. So he would
click a blank spot in the list box to activate the box, type in the letter R,
and move to the Rs and then select the student. Although it wasn't displayed
on the screeen, apparently that initial click was selecting another student
who wasn't registering for the class. Therefore, changing it to a Single
Select box solved the problem.

Great detective work... and a nasty data entry glitch that was
obviously hard to catch and hard to prevent!

We could resolve the initial problem if that ListBox had a type in box above
it just like a combo box. Then the user could simply type the letter R and
move to the Rs, and he/she wouldn't have to first click in the listbox.

That's doable - just use an unbound textbox and change the listbox's
Row Source in its AfterUpdate event. If you ever have a list or combo
box that has too many rows (too many for decent performance or
<shudder> over the 65536 row limit), this is essential.

John W. Vinson[MVP]

 

[Robert T]

John Vinson wrote:

That's doable - just use an unbound textbox and change the listbox's
Row Source in its AfterUpdate event. If you ever have a list or combo
box that has too many rows (too many for decent performance or
<shudder> over the 65536 row limit), this is essential.

I'm still relatively new to Access, would you be kind enough to show me how
to code that unbound textbox and the afterupdate event in the Row Source?

Thanks,
Robert

 

[John Vinson]

John Vinson wrote:

That's doable - just use an unbound textbox and change the listbox's
Row Source in its AfterUpdate event. If you ever have a list or combo
box that has too many rows (too many for decent performance or
<shudder> over the 65536 row limit), this is essential.

I'm still relatively new to Access, would you be kind enough to show me how
to code that unbound textbox and the afterupdate event in the Row Source?

Thanks,
Robert

There's sample code you can adapt at

http://allenbrowne.com/ser-32.html

John W. Vinson[MVP]