No data when opening a form

[Lester Lane]

I have read on various boards that some advanced developers create
forms/combos that don't pull any data until the user chooses some
record from a combo. Both the form and the combo have a recordsource
that is a recordset. Can someone explain in simple terms how this is
done? Thanks a lot

 

[akphidelt]

What exactly do you mean?

You can insert a Form header and add an Unbound Combobox which you can
choose a record that will then show on the form?

 

[Lester Lane]

What exactly do you mean?

You can insert a Form header and add an Unbound Combobox which you can
choose a record that will then show on the form?

Yes, that's part of it, only I think the form and the combo have no
query attached when first opened. Then when the combo is activated
the recordsource is set. The reason I am asking is because it appears
that Access will open all the data in the query even if the criteria
is set to be the combo in the form (which is currently blank). I also
feel that it runs loads of checks even though they were run when
saving the form in development.

 

[John Spencer]

Ok,

I have an unbound combobox with NO row source set.

In the combobox's change event, I get the length of the text in the combobox.
Once the length hits a specified length I execute code to populate the
combobox by assigning a row source to the combobox.

If Len(Me.Combobox.Text >=3 and Len(me.Combobox.RowSource) = 0 THEN
Me.Combobox.RowSource = "SELECT ID, LastName, FirstName FROM Persons WHERE
LastName like """ & Me.Combobox.Text & "*"""

End If

I can do something similar for the form's record source.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have read on various boards that some advanced developers create
forms/combos that don't pull any data until the user chooses some
record from a combo. Both the form and the combo have a recordsource
that is a recordset. Can someone explain in simple terms how this is
done? Thanks a lot

You can simply do this by basing the Form on a Query with a criterion that
returns no records - for instance, if you have an unbound combo in the form's
Header, just use a criterion of

=Forms!formname!comboboxname

as a criterion on the ID. Requery the form in the AfterUpdate event of the
comb so that the desired record will be shown when the combo is selected.

 

[Lester Lane]

You can simply do this by basing the Form on a Query with a criterion that
returns no records - for instance, if you have an unbound combo in the form's
Header, just use a criterion of

=Forms!formname!comboboxname

as a criterion on the ID. Requery the form in the AfterUpdate event of the
comb so that the desired record will be shown when the combo is selected.

OK, thanks. So if I set up my forms like this they should open
quickly. Although the subforms have queries with no criteria, this
should still be ok, right? The subform won't show any data (due to
the links not fulfilled) but will the subform populate behind the
scenes?

 

[John W. Vinson]

OK, thanks. So if I set up my forms like this they should open
quickly. Although the subforms have queries with no criteria, this
should still be ok, right? The subform won't show any data (due to
the links not fulfilled) but will the subform populate behind the
scenes?

No; the subform won't populate until the parent link is filled in. If the
mainform has no parent record, then Access cannot predict which child records
will be needed.

There are lots of things that can affect performance, not just form design -
do you have the right indexes defined, for instance? See
http://www.granite.ab.ca/access/performancefaq.htm for some suggestions.