Software

Ask something
Discussion Locked

Restrict data in listbox on a accessform

0 helpful
5 replies

by anthony_sargent

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I've been trying to restrict data in a list box using the following sql statment entered in the row source property for the list box

SELECT DISTINCTROW Contacts.ContactID, [Firstname] & ", " & [Lastname] AS FirstN, Contacts.WorkPhone
FROM Contacts
WHERE (((Contacts.School)=[forms]![frmschools]![me]![school_id]) AND ((Contacts.[Contact Status])='AC - ACTIVE'));

Basically I want to restrict the contacts show in the list box to that of the school currently accessed on the form. The above code works in SQL view as it manually prompts me for the school id. However it doesn't work on the form, i.e its not picking up the school id on the currectly accessed record.

Any ideas on what I'm doing wrong?

Thanks

Anthony Sargent

5 replies

by nicholasp

What happens on the actual form?
It seems to me that you are referencing the field incorrectly. It should be:
[Me]![school_id] or
[forms]![frmschools].[school_ID]

by donq

Anthony,

Lets assume your form is named (frmSchools) and the field on the form containing the school is (school_id).

1. Build a query to produce EVERY contact as you did in the example. - test the thing!

2. Enter "[Forms]![frmSchools].[school_id]" excluding the quotation marks under the school_id Fields Criteria and test the query (you may have to manually enter a school_id to get it to work this early). Once everything works properly save and name your query. (MySchoolFilter).

3. Now switch to SQL view and copy [Ctrl]+C the script to the clipboard.

4. Open frmSchools in design view and paste [Ctrl]+V the clipboard into the list boxed data source property and save.

Close, reopen and everything should work automaticallyfor any school now without any entry beyond the initial school_id entry.

Good luck

This discussion is closed to new replies.