New form instance - suppress record retrieval?

[KeithWilliams]

Hi,

I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:

Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True

The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.

I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.

Any suggestions would be much appreciated.

Thanks,
Keith.

 

[Jerry Stoner]

This should work. Make a query for each combination of data you want to display. Use a select case to determine which query to use for your Record Source and pass the name of the query to strSelect then open the form like this. DoCmd.OpenForm stDocName, , , , , , , OpenArgs:=strSelect

 

[KeithWilliams]

Thanks for the suggestion, I should have explained, I need the user to be able to open more than one instance of the same form using this method. If you call DoCmd.OpenForm and the named form is already open, the form is just brought into focus and the new WHERE clause applied, rather than a second instance opening.

Using the code I pasted in does allow multiple instances to open, but with the performance penalty because the form is retrieving records twice.

Thanks,
Keith.