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

I think you answered your own question there. The where clause will filter the query on the SQL server.

Access SQL: WHERE clause

Answer 3

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.

Answer 4

If you want to force users to filter the form before showing any results, don't bind the SubForm to the query (leave the form's RecordSource empty) and do it in code instead: you can simply set that in your Search button click event:

Me.Sub.RecordSource = "subResultType_1"

Nicer still, make the Subform invisible (set its Visible = No property in Design mode) and show it once the user clicked Search:

Private Sub btSearch_Click()
  Me.Sub.RecordSource = "subResultType_1"
  Me.Sub.Visible = True
End Sub

If you want to do more complex filters, you can also do something like:

Private Sub btSearch_Click()
  Me.Sub.RecordSource = "SELECT * FROM subResultType_1 WHERE " + strWhere
  Me.Sub.Visible = True
End Sub

Something like this would allow you to construct complex WHERE queries from code based on a bunch of input from the user.
For instance, you could have multiple textboxes where the user can enter information relative to particular fields to narrow downs the search.

You then construct the WHERE clause from the content of these textboxes, like this:

That becomes this once the user has entered some filtering criteria: