Filter listbox/combo box based on value of field?

[RRT]

I have an existing table which describes Streets and sections of streets
between intersections:

Table 1: Streets by Intersections
Street Area
Ann St. Main to Jackson
Ann. St. Jackson To Summer
Ann St. Summer to End
Joe St. Aberdeen to Mike
and so on for 1200 records

My second table is:
Table 2: Work by Street
Project Street Area Project
Description
2003-01 Ann St. Jackson to Summer Sanitary Renewal

In table 2 I'd like to be able to pick "Area" from a listbox and only have
the entries that relate to that particular street showing. In the case of
Ann St that would be 3 choices rather than trying to wade through 1200.

Is there an easy way to filter listbox based on the value in a field? Hope
this makes sense.

Thanks

RRT

[Larry Linson]

Certainly, you can simply, using a fully-qualified reference, refer to the
Control on the Form in the Criteria of the Query. Whenever the corresponding
Control (in your case, the listbox) changes, you need to requery the target
List or Combo Box... so, do so in the afterupdate event of the Listbox.

Larry Linson
Microsoft Access MVP

"RRT" <NO******************@miramichi.org> wrote in message
news:Gt********************@ursa-nb00s0.nbnet.nb.ca...

I have an existing table which describes Streets and sections of streets
between intersections:

Table 1: Streets by Intersections
Street Area
Ann St. Main to Jackson
Ann. St. Jackson To Summer
Ann St. Summer to End
Joe St. Aberdeen to Mike
and so on for 1200 records

My second table is:
Table 2: Work by Street
Project Street Area Project
Description
2003-01 Ann St. Jackson to Summer Sanitary Renewal

In table 2 I'd like to be able to pick "Area" from a listbox and only have
the entries that relate to that particular street showing. In the case of
Ann St that would be 3 choices rather than trying to wade through 1200.

Is there an easy way to filter listbox based on the value in a field? Hope this makes sense.

Thanks

RRT

[Bob Quintal]

"RRT" <NO******************@miramichi.org> wrote in
news:Gt********************@ursa-nb00s0.nbnet.nb.ca:

I have an existing table which describes Streets and sections
of streets between intersections:

Table 1: Streets by Intersections
Street Area
Ann St. Main to Jackson
Ann. St. Jackson To Summer
Ann St. Summer to End
Joe St. Aberdeen to Mike
and so on for 1200 records

My second table is:
Table 2: Work by Street
Project Street Area
Project Description
2003-01 Ann St. Jackson to Summer Sanitary
Renewal

In table 2 I'd like to be able to pick "Area" from a listbox
and only have the entries that relate to that particular
street showing. In the case of Ann St that would be 3 choices
rather than trying to wade through 1200.

Is there an easy way to filter listbox based on the value in a
field? Hope this makes sense.
In the Current Event for your form, change the rowsource of the
listbox to a filtered one, and requery.

sub Form_current()
dim strSQL as string
strSQL = "SELECT Street, Area from [table 1] " & _
"Where street = '" & me.street & "'"
me.listbox.recordsource = strSQL

end sub
Thanks

RRT

[RRT]

>

I have an existing table which describes Streets and sections
of streets between intersections:

Table 1: Streets by Intersections
Street Area
Ann St. Main to Jackson
Ann. St. Jackson To Summer
Ann St. Summer to End
Joe St. Aberdeen to Mike
and so on for 1200 records

My second table is:
Table 2: Work by Street
Project Street Area
Project Description
2003-01 Ann St. Jackson to Summer Sanitary
Renewal

In table 2 I'd like to be able to pick "Area" from a listbox
and only have the entries that relate to that particular
street showing. In the case of Ann St that would be 3 choices
rather than trying to wade through 1200.

Is there an easy way to filter listbox based on the value in a
field? Hope this makes sense.

In the Current Event for your form, change the rowsource of the
listbox to a filtered one, and requery.

sub Form_current()
dim strSQL as string
strSQL = "SELECT Street, Area from [table 1] " & _
"Where street = '" & me.street & "'"
me.listbox.recordsource = strSQL

end sub

I should of mentioned I don't use forms, I enter information directly into
tables. So I guess I could use SQL on the rowsource. What would I need to
enter?

SELECT Street, Area from [Streets By Intersections]
WHERE Street=' & street &'

Just guessing....