Question loading form and filter

Question

I am doing a search form. At the top there are several lists for users who choose a combination of criteria. Then I build the Where line to filter out the subformat displaying the results.

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

This code is in the event of clicking the "Search" button.

The problem is that when Me.sub.SourceObject = "subResultType_1" executed, the subformation displays all the records. Then it is filtered. But I want the sub panel to not display anything until it is filtered. This is due to the fact that my program will be used as front / back in a rather slow network.

PS: I think when an SQL statement with a WHERE part or a form with a filter, it is filtered on the back panel. Thus, only a small amount of data will be transmitted over the network to the interface. If I am wrong about this, tell me ...

Answer 1

An alternative is the one that I often use to save a subform with a post source that creates one empty, unedited post. I usually use SQL, for example:

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

This shows one empty record with Nulls for some fields, 0 for others (depending on the situation). I find this cosmetically more attractive than the alternative.

When I am ready to display the filtered set, I modify the Writeource instead of setting the filter.

Answer 2

If you want to force users to filter the form before showing any results, do not bind SubForm to the request (leave the RecordSource form empty) and do it in code: you can simply set this in your Search button click event:

 Me.Sub.RecordSource = "subResultType_1" 

Still, make Subform invisible (set its Visible = No property in design mode) and show it as soon as the user clicks Search:

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

If you want to make 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 will allow you to build complex WHERE queries from code based on a bunch of user input.
For example, you may have several text fields in which the user can enter information on certain fields to narrow the search.

Then you create a WHERE clause from the contents of these text fields, for example:

This becomes as soon as the user enters some filtering criteria:

Answer 3

You said: "My program will be used as front / back in a rather slow network."

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

If so, you should be aware that Access network databases are really only suitable for a fast and reliable wired LAN. If one of the following conditions is true, you must change the data store to something other than Access.

• wide area network (WAN)
• wireless network connection
• wired connection to an untrusted local area network (LAN)

A common risk in such situations is that a lost connection can damage your Access database. This may not happen with every disconnected connection, but in the end you will corrupt your Access database.

Answer 4

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

Access SQL statement: WHERE