forms with empty recordsource

[Andy74]

Hello,

sometimes I have forms in which the underlying query can be very big, i.e. hundred thousands of records. Forms are designed as split forms with some filters in the header and the records as datasheet in the main body. I want to avoid a long delay when the form is opened so I open the form with empty recordsource and when the user select some filters then an SQL query is built programmatically and the recordsource is populated, thus showing only few records with a good speed.
My issue is this: when the form opens the first time the recordsource property is empty is it possible to avoid the "New?" writing that appears in every field?

Another question: I like this design of split forms, but I heard many bad stories about split forms. Anything really wrong in using them extensively?

thanks in advance

Andy

 

[MarkK]

The problem is that each control has a ControlSource property which binds it to the RecordSource of the form, but if the RecordSource doesn't exist, the controls complain about this apparent error.

You could clear the ControlSource property of each control in design view, and reset them programmatically when you apply your SQL. This is fairly easy if the controls are named the same as the fields in the recordset . . .

   dim fld as dao.field
   for each fld in me.recordset.fields
      me.controls(fld.name).controlsource = fld.name
   next

You could also write a very simple RecordSource SQL that is known to be fast, and runs when the form opens and until you apply the customized SQL . . .

SELECT [B]TOP 10[/B] *
FROM Table

 

[Andy74]

Thanks, I used your first solution and it works neatly, because when the form is loaded the fields are shown as blank which is what I was looking for! I just preferred to loop throught the controls in the detail section, because many fields were not used.

For Each ctrl In Me.Section(acDetail).Controls
        If ctrl.ControlType = acTextBox Then ctrl.ControlSource = ctrl.Name
Next

 

[MarkK]

Nice job! Thanks for posting back with your solution.

 

[arnelgp]

another approach would be to use a blank recordset:

private sub form_load()
me.recordsource="select * from table where (1=0);"
end sub

 

[MarkK]

Yes, that would work

SELECT * FROM Table WHERE False