Form load and filter question

Question

I'm doing a search form. On top, there are several comboboxes for users to choose a combination of criterias. Then I construct a Where string to filter a sub form displaying the results.

Me.sub.SourceObject = "subResultType_1"
Me.sub.Form.Filter = strWhere
Me.sub.Form.FilterOn = True

This code is in the "Search" button's click event.

The problem is, when Me.sub.SourceObject = "subResultType_1" is executed, the subform will display all the records. Then it gets filtered. But what I want is the subform displays nothing until it gets filtered. This is because my program will be used as front/back end on the rather slow network.

PS: I think when a SQL clause with the WHERE part, or a form with filter, it gets filtered on the back end. So only a little amount of data will be transmitted on the network to the front end. If I'm wrong on this, tell me...

Answer 1

An alternative is one that I often use, which is to save the subform with a recordsource that produces one blank, uneditable record. The SQL I usually use is something like this:

  SELECT TOP 1 Null As Field1, Null As Field2, 0 As Field3
  FROM MyTable;

This displays one blank record with Nulls for some fields, 0 for others (as appropriate). I find it cosmetically more attractive than the alternative.

When I'm ready to display a filtered set, I change the Recordsource instead of setting a filter.

Answer 2

You said "my program will be used as front/back end on the rather slow network".

What is your back end storage database? Is it an Access (Jet/ACE) database file?

If yes, you should be aware than networked Access databases are really only suitable on a fast, reliable, hard-wired LAN. If any of the following conditions are true, you should change your data storage to something other than Access.

1. wide area network (WAN)
2. wireless network connection
3. wired connection to an unreliable local area network (LAN)

The common risk in those situations is that a dropped connection can corrupt your Access database. It may not happen at every dropped connection, but eventually you will corrupt your Access database.