Listbox

[Elaine]

Hi

I have a tabular formatted Form, which shows a list of student names.
I would like to add an unbound listbox to this form which should contain,
for each student name, a list of their Lecture codes.
I've added the listbox control, however, it displays, for all students, the
lecture codes of the first student listed on the form.

Is their another way around achieving the result Im after?

Many thanks!!

Elaine
(let me know if you want further info)

 

[John Vinson]

Hi

I have a tabular formatted Form, which shows a list of student names.
I would like to add an unbound listbox to this form which should contain,
for each student name, a list of their Lecture codes.
I've added the listbox control, however, it displays, for all students, the
lecture codes of the first student listed on the form.

Is their another way around achieving the result Im after?

Many thanks!!

Elaine
(let me know if you want further info)

I'm not sure just what you're asking, Elaine. Do you mean by "tabular"
a Datasheet view form? (View the form's Properties and check the
Default View property). If so you might want to change it to
Continuous.

What is the RowSource of your listbox? Might it not be simpler to have
a Form based on the student table, with a Subform based on the
enrollment table which links the Students table to the Courses table?

John W. Vinson[MVP]

 

[Elaine]

Hi John, thanks for replying.

Well, my form is a "Continuous form", and its data source is tblStudents.

In this same form, I would like to have a listbox containing a list of the
student's Lecture Codes (tblLectures)

tblStudents and tblLectures are related through a third table called
tblSTULEC, which contains only idStudent and idLecture.

The rowsource for the listbox is: Select * from tblSTULEC where
idStudent=Me!idStudent

This is supposed to be a form just to see a list of all students in the
database. The detailed information is entered on a separate form (which is a
Single form).

All works well in the single form (relationships, etc), but I couldnt get
the all data in the related tables to be displayed in the Continuous form.

I couldnt get it to work, so I've tried adding a subform, but I cant have a
subform in a Continuous form, and the Datasheet view is just not nice
looking enough....

Well, I hope I managed to explain myself better now., if not maybe I can
send screenshots?

Many thanks

Elaine

 

[John Vinson]

Hi John, thanks for replying.

Well, my form is a "Continuous form", and its data source is tblStudents.

In this same form, I would like to have a listbox containing a list of the
student's Lecture Codes (tblLectures)

The student currently selected on the Subform? That will need some
code.

tblStudents and tblLectures are related through a third table called
tblSTULEC, which contains only idStudent and idLecture.

The rowsource for the listbox is: Select * from tblSTULEC where
idStudent=Me!idStudent

Well, Me! is meaningful only in VBA code. It won't work in a Query.
And this will retrieve the idLecture value, not the name of the
lecture (unless the idLecture field IS the human-readable name). See
below.

This is supposed to be a form just to see a list of all students in the
database. The detailed information is entered on a separate form (which is a
Single form).

All works well in the single form (relationships, etc), but I couldnt get
the all data in the related tables to be displayed in the Continuous form.

Do you want ALL classes for ALL students listed simultaneously?
That'll a) be difficult to do and b) all but impossible to read...

I couldnt get it to work, so I've tried adding a subform, but I cant have a
subform in a Continuous form, and the Datasheet view is just not nice
looking enough....

Well, I hope I managed to explain myself better now., if not maybe I can
send screenshots?

Well, let's see if we can't get this done on the newsgroup instead - I
try to reserve EMail support to paying clients (I'm a self-employed
consultant donating time here), and it's not good form to upload
binaries.

Try basing the Listbox on a query like

Select tblSTULEC.idStudent, tblLectures.LectureName
FROM tblSTULEC INNER JOIN tblLectures
ON tblSTULEC.idLecture = tblLectures.idLECTURE
where tblSTULECT.idStudent =
Forms!YourMainForm!subformname.Form!idStudent
ORDER BY LectureName;

using your own table and fieldnames; "subformname" should be the Name
property of the subform control (which isn't necessarily the same as
the name of the form within that control).

You'll also need one line of VBA code: in the Current event of the
subform put

Private Sub Form_Current()
Parent!lstSTULEC.Requery
End Sub

where lstSTULEC is the name of the listbox.

This will let you scroll down the list of students; the listbox will
change with each move to reflect that student's enrollment.

John W. Vinson[MVP]

 

[Larry Linson]

are you sure that is a good idea?

The student currently selected on the Subform? That will need some
code.


Well, Me! is meaningful only in VBA code. It won't work in a Query.
And this will retrieve the idLecture value, not the name of the
lecture (unless the idLecture field IS the human-readable name). See
below.


Do you want ALL classes for ALL students listed simultaneously?
That'll a) be difficult to do and b) all but impossible to read...


Well, let's see if we can't get this done on the newsgroup instead - I
try to reserve EMail support to paying clients (I'm a self-employed
consultant donating time here), and it's not good form to upload
binaries.

Try basing the Listbox on a query like

Select tblSTULEC.idStudent, tblLectures.LectureName
FROM tblSTULEC INNER JOIN tblLectures
ON tblSTULEC.idLecture = tblLectures.idLECTURE
where tblSTULECT.idStudent =
Forms!YourMainForm!subformname.Form!idStudent
ORDER BY LectureName;

using your own table and fieldnames; "subformname" should be the Name
property of the subform control (which isn't necessarily the same as
the name of the form within that control).

You'll also need one line of VBA code: in the Current event of the
subform put

Private Sub Form_Current()
Parent!lstSTULEC.Requery
End Sub

where lstSTULEC is the name of the listbox.

This will let you scroll down the list of students; the listbox will
change with each move to reflect that student's enrollment.

John W. Vinson[MVP]

 

[John Vinson]

are you sure that is a good idea?

hmmm...

You're right - it should at the very least be an Unbound listbox. A
correlated Subform might be a better choice.

John W. Vinson[MVP]

 

[Elaine]

The student currently selected on the Subform? That will need some
code.


Well, Me! is meaningful only in VBA code. It won't work in a Query.
And this will retrieve the idLecture value, not the name of the
lecture (unless the idLecture field IS the human-readable name). See
below.


Do you want ALL classes for ALL students listed simultaneously?
That'll a) be difficult to do and b) all but impossible to read...

yes! that's exactly what I need to show, and in my case, it shouldnt be
impossible to read as each student will have only a max of 3 or 4 lecture
codes associated with them.
Anyway, I've already tried the solution you provided here, but it doesnt
look neat, as the listbox will display the lecture codes of the currently
selected student, and it will appear that all students in that continuous
form will have the same lecture codes....

Anyway I tried using the DSLOOKUP function in a textbox, but that will only
show the first lecturecode for that student in the query. Is there a way to
use DSLOOKUP to retrieve all related records?


Anyhow, many many thanks for you kind help.

ELaine

 

[John Vinson]

Anyway I tried using the DSLOOKUP function in a textbox, but that will only
show the first lecturecode for that student in the query. Is there a way to
use DSLOOKUP to retrieve all related records?

No. (and it's DLookUp, no S).

You might want to consider stringing the courses together into one
field (e.g.

Jimmy Brown Math, English, PL/SQL, Astrophysics

To do so see the sample code at

http://www.mvps.org/access/modules/mdl0004.htm

John W. Vinson[MVP]

 

[Elaine]

I shall try the code provided tomorrow.
many many thanks!

Elaine