Programatically Change Recordsource SQL of a form

[HLCruz via AccessMonster.com]

I am wondering if it is possible to have the record source of a form be the
SELECT query of a table that would be "defined" by the entry the user makes
on a field in the form.

Example -

3 tables all have the same fields, they are named:
55091
55104
55116

The user opens the form and enters the ZipCode they are working with in a
field - 55901. I'd like to program the form so that the record source
becomes SELECT * From 55901.

It seems like that would be something simple ... I would also welcome any
feed back as to whether this would be a wise set up.

A little background on why I'm trying this: I work for a non-profit with an
old server; we are using Access to schedule donation pickups. It's a split
DB with the BE on the network and the FE on each PC. We currently have 300,
000+ addresses in one table and the form to schedule the pick up is very slow.
It works great when I take the BE off the network, so I am wondering if we'd
see better performance if I have a table for each Zip, the max records in a
zip is 15,000 or so.

Thank you.

 

[Marshall Barton]

I am wondering if it is possible to have the record source of a form be the
SELECT query of a table that would be "defined" by the entry the user makes
on a field in the form.

Example -

3 tables all have the same fields, they are named:
55091
55104
55116

The user opens the form and enters the ZipCode they are working with in a
field - 55901. I'd like to program the form so that the record source
becomes SELECT * From 55901.

It seems like that would be something simple ... I would also welcome any
feed back as to whether this would be a wise set up.

A little background on why I'm trying this: I work for a non-profit with an
old server; we are using Access to schedule donation pickups. It's a split
DB with the BE on the network and the FE on each PC. We currently have 300,
000+ addresses in one table and the form to schedule the pick up is very slow.
It works great when I take the BE off the network, so I am wondering if we'd
see better performance if I have a table for each Zip, the max records in a
zip is 15,000 or so.

That is not a good way to do that. You should have one
table with a zip code field. If the forms record source
query takes too long to find the records for the specified
zip code, there are a couple of thing that should help.

First, do not open the form with all 300K records. Instead,
open the form with no records by using code like:

DoCmd.OpenForm "the form", , , "ZipCode = '00000' "

Use the AfterUpdate event of the control where users enter
the desired zip code to set the form's Filter:

Me.Filter = "ZipCode = '" & Me.txtZip & "' "
Me.FilterOn = True

The second thing that should make a big difference is to
create an Index for the table's zip code field.