question regarding using SQL in MS Access to access a listbox

Question

I am trying to use SQL in Access to load a listbox by checking a field in one table against the current selection in another listbox.

The following is entered in the 'row source' property for the second listbox...

SELECT [resource].[ID], [resource].[TeacherID] FROM resource WHERE [Teachers].[ID] = [Forms] ! [frmmain] ! [List1];

The table names are 'resource' and 'Teachers'. The form is called 'frmmain' and the listbox is 'List1'

When the form is started and a selection made from the first listbox, nothing appears in the 2nd one.

I'm not a VB programmer so SQL is the simplest way for me to do this. Is it accessing the 1st listbox correctly, if so why doesn't it work?

Answer 1

Ok, First, I would NOT accept the default name for your PK fields. I always name my PKs as tablenameID. For example, your Teachers table should have the field TeachersID. This makes it easier to identify the Foreign Keys.

Second, If you are populating the Resource table in your form, then you should NOT be using the Resource table in the Rowsource of your list boxes. If you are trying to populate the Resource table then the Teacher listbox should have a Rowsource of:

SELECT TeacherID, Lastname & ", " & Firstname As Teacher

FROM Teachers;

Each other fields should also use listboxes (actually comboboxess are better) with RowSources querying the Student, Room and Subject tables.

Since there is NO relation then there is nothing to filter for in the lists.

Answer 2

Hello Scott, sorry for the late reply, I'm a teacher so was off for Easter with no Access at home.

I'm trying to make a small database to record extra lessons teachers give to SEN students.

So far I have the following tables:

Teachers (ID and Name)

Students (ID, name, year, form)

Subjects (ID, Name)

Rooms (ID, Name)

Times (ID, period) (schools here break the day into periods of 40 minutes)

Resource (ID, TeacherID, Student ID, RoomID, subjectID)

The Resource table can be populated by selecting from listboxes that reference the different tables, so I can select a teacher, and allocate them students, a room, a subject and a time. This bit seems to work ok.

I then want to query which students a teacher is taking and for this have a form 'querytable' with 2 listboxes. One (List1) shows the teacher's names and it gets these from the 'Teachers' table. When I select a teacher's name from List1, I want the second listbox, List14, to show all the students that teacher takes, information that is available from the 'Resource' table.

Apologies for the ropey naming of things, e.g. Teachers.Name and Resource.TeacherID both show the teacher's name. (I'm learning as I go along).

I thought this should work, i.e bring up a list of all the students assigned to a particular teacher, when that teacher is selected in List1.

The sample I put up earlier was just to see if I could get the 2nd listbox to display the same thing that was selected in the first listbox

SELECT Resource.StudentID FROM Resource WHERE Resource.TeacherID = [Forms]![querytables]![List1];

when run, it produces a box requesting a parameter be entered. Entering anything has no effect and making a new selection makes the request for a parameter come up again. The part after the = sign doesn't seem to be reading what is in List1 from what I can see. Are there any obvious mistakes?

Answer 3

Several points here. First, Access doesn't use VB it uses VBA which is a superset of VB. Second, as pointed out, your RowSource is an invalid SQL statement.

But the main point is I'm really not following what you are trying to do. It appears you are selecting a teacher in List 1. And, you then want to filter the list of resources in List 2 to resources that are assigned to the selected teacher. But your Rowsource for List 2, shows only the Resource ID and the TeacherID. Shouldn't there be more columns so the user can identify the Resource? Also, the Resource table doesn't seem like its set up right. Can a Resource be assigned to more than 1 teacher?

Answer 4

> SELECT [resource].[ID], [resource].[TeacherID]

FROM resource

WHERE [Teachers].[ID] = [Forms] ! [frmmain] ! [List1];

This statement is invalid. Paste it into a new query in SQL view and you will get an error. Why? Because the Where-clause references a table that is not in the From clause.

You probably meant:

where resource.TeacherID = [Forms] ! [frmmain] ! [List1];

Also, you need a mechanism to requery the list after a selection is made in List1. Consider this one-liner in the List1.AfterUpdate event:

Me.List2.Requery

Answer 5

Why do you need the Teachers table in the SQL? Does it work if you use

SELECT [ID], [TeacherID] FROM resource WHERE [TeacherID] = [Forms]![frmmain]![List1];