Delay loading records of subform

Question

I have a form with search fields and then the search results are shown in a subform below the search fields.

By default the subform loads all records prior to any search criteria being entered.

As this database grows the number of possible records to search will get quite large, so I don't really want the subform to load all records before the user attempts to makes a search.

What's the most performance-friendly way of loading the search results subform without showing any of the records to begin with?

I've considered setting the subform recordsource SQL to search for something I know will never be in the results... but I'm thinking that still requires the records to be loaded first and then filtered (might be wrong about this though).

Ideally I'd like the search results subform to load with the field names of the recordset only, but with no records until the user attempts a search.

HansUp HansUp · Accepted Answer · 2013-11-13T15:58:39

Set the subform's record source to a query which returns a single manufactured row.

SELECT
    0 AS id,
    '' AS fname,
    '' AS lname,
    '' AS email

That will not pull any records from your table.

After you gather the user's search criteria, build the new SELECT and assign it as the record source.

Delay loading records of subform

1 Answers