list box on subform

[Kevin Bruce]

I have put a list box on a subform, but am having a bit of trouble in
limiting the list items to those on the current form.

The list works fine if I set no parameters in the query that pulls the
list info together, but then it's a list of over 1200 items.

Here's how it works:

I work with school districts and schools.

The main form holds the ID for the district.

The subform holds all the schools within that district.

Each of the above is drawn from a separate table with a one-to-many
relationship respectively.

The list box, located on the subform, should list only those schools that
correspond to the district on the main form.

In attempting to limit the list to the District ID in a corresponding text
box, I get only those records for the first district listed in the table,
and the list does not update as a move to other District records.

Any ideas?

--
================================
Kevin Bruce
Program Coordinator
ArtStarts in Schools
301 - 873 Beatty Street
Vancouver, BC V6B 2M6

ph:604-878-7144 ext.3
fx: 604-683-0501

web: www.artstarts.com

 

[Jonathan Parminter]

-----Original Message-----

I have put a list box on a subform, but am having a bit of trouble in
limiting the list items to those on the current form.

The list works fine if I set no parameters in the query that pulls the
list info together, but then it's a list of over 1200 items.

Here's how it works:

I work with school districts and schools.

The main form holds the ID for the district.

The subform holds all the schools within that district.

Each of the above is drawn from a separate table with a one-to-many
relationship respectively.

The list box, located on the subform, should list only those schools that
correspond to the district on the main form.

In attempting to limit the list to the District ID in a corresponding text
box, I get only those records for the first district listed in the table,
and the list does not update as a move to other District records.

Any ideas?

--
================================
Kevin Bruce
Program Coordinator
ArtStarts in Schools
301 - 873 Beatty Street
Vancouver, BC V6B 2M6

ph:604-878-7144 ext.3
fx: 604-683-0501

web: www.artstarts.com

Hi Kevin, this is a fairly common post so you could
possibly search for other ideas...

1.in design view of the form view the properties of the
listbox. for the rowsource click the build button (...)

2. in the sql view ensure that the districtid field is in
the grid.

3. position the cursor in the criteria row of this column
and then click the build button.

4. expand forms, expand loaded forms. select the
districtid field. click paste and then ok.

you should see a criteria something like:
forms!mainformname!DistrictID

5. close out of the sql builder, click 'yes' to save the
change.

6. use the oncurrent event of the main form to requery the
listbox to show schools for the current district.

private Sub Form_OnCurrent()
subformcontrolname.form!listboxcontrolname.requery
end sub

Luck
Jonathan