Require better understanding: record select, combo, forms 1

[DajTwo]

Hi all,

I have read as much as I could and I am still confused, so I am turning to the experts of this board.

I have a form [frm_cst] that is populated by a query [qry_cst]. Record selection is made by the user through a combobox [cmb_cst] with a RowSource [qry_cmb_cst] with the [ccan] field. All very straight forward.

What I would like to understand is the loading order.

When the form [frm_cst] is opened, the query qry_cst is executed and loads in memory all the records of the query and the 1st record shows on the form, then the user selects the record with the combobox [cmb_cst] and the proper record shows up.

Would it be better and much faster that the form [crm_cst]loads, THEN the user selects the record with [cmb_cst] THEN the query [qry_cst] is executed with the [CCAN] selected in the combobox and displays the proper record?

Am I missing something here?








If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[MajP]

You may enjoy this

http://www.fmsinc.com/MicrosoftAccess/Performance.html

However, it does not address what you are asking. There was on Tek-Tips a huge thread on this issue a while back. And yes in certain cases loading the recordsource after the form loads can bring giant performance increases. Regardless, as the paper will tell you always return the minimum amount of records and fields for any form. Never bind directly to a table.

So you can remove the recordsource and set it after the form loads. If you only want the user to navigate using the combo then only return a query matching the combo value. Then you only ever load a single record.

 

[DajTwo]

I have this paper on my desk for a whille now and try to apply as much as I can from it.

However I am not that experienced in VBA so your comment I am not sure how to accomplish it.

So you can remove the recordsource and set it after the form loads. If you only want the user to navigate using the combo then only return a query matching the combo value.

I will expand my research to 'unbound forms'.

Thanks



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[MajP]

Here is an example

http://www.4shared.com/file/84856022/abb0ecbc/LoadRecordsource.html

The first form is done the standard way by setting the recordsource equal to table "Orders". This binds 5K records when the form loads.

The second form sets the recordsource property to nothing.

When I pick a selection from the combobox I return a query with one record and use this as the recordsource. See the code.

In this case you do not see much performance difference, but with a million records and more fields the second would be substantially faster.

This is different then an unbound form. The form is bound because the controls are bound to a field in the underlying recordsource. The difference is that I set the recordsource after the form loads and after I choose a record from the combo.

 

[TheAceMan1]

How are ya DajTwo . . .

Logically ... you shouldn't lookup/goto a value from your combo [blue]unless all records are available in the form![/blue] This dictates whats called [blue]early binding[/blue], where the forms [blue]Recordsource[/blue] has a value (tablename, queryname, or SQL).

Late binding ([purple]assigning a value to the recordsource after the form opens[/purple]) can cause problems here,[purple] where the user selects a value from the combo while the forms recordsource is still loading![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I agree with what you are saying, but I do not think my example can cause any problem because I am not doing a "looking up/goto ". I am assigning a recordsource which is a query based on the value of the control. If while the query was loading the user could somehow reassign a value to the combo (which is probably not possible) it would force a reassignment of the recordsource.

 

[TheAceMan1]

Howdy MajP . . .

My post was in no way meant to thwart yours . . . Sir!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

AceMan,
I did not take it that way. What I probably should have said was late binding of forms and/or controls can improve efficiency in certain cases, but there is not one prescriptive approach. There is a lot of case dependencies depending on the complexity of the form and the record source. Sometimes late binding may improve speed/efficiency other cases it may hurt.

 

[TheAceMan1]

MajP . . .

Maybe I should've left off the exclamation in my post. I didn't mean it that way either!

So what happen to [blue]DajTwo?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

I actually took a weekend off for once. I will read the posts an experiment with MajP solution in a simple form and do testing on speed.

I might also try to separate the combobox from the form to load only one record.

Thanks guys, I will post back

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

MajP . . .

I took a look at the 4shared db. [blue]Excellent idea in returning a single record![/blue] ... No one needs to be told that a single record will load faster than a million. This is worth a pinky alone!

However ... there is the secondary issue of displaying a million ID's in the combobox. Even scrolling is an issue here

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

My first thought If I was actually doing that many users would be a bunch of tabs or buttons with letters on them (A, B, C, D..Z). Click on A and set the recordsource of the combo to a select query of employees with names starting with A. But now your still probably dealing with 30k records. So usually what you see is a second form with more tabs (Aa-Ac, Ad-Ae, Af-Ag, Ah-Aj...). Now you are probably talking 1k-2k which is pretty manageable. I would then do a find as your type search.

There are probably a bunch of other interfaces you could design. When you are dealing with really large amounts of records you probably are also making temp tables. So maybe you pull 30k records into a table, you then refine your query on this set of records vice requerying a million records.

 

[TheAceMan1]

MajP . . .

It doesn't matter ... 1, 2, or 3 records or 10,20,30 0r 100,200,300 or more loaded compared to a million or more will always be faster! ... and that is the [blue]epitome[/blue] of this thread!

[blue]Bravo MajP! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

crap..

The company's firewall does not allow me to get MajP's example. Will have to go home, make a copy, bring it back etc..

Sorry for the delay in replying..

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[MajP]

Originally I thought you were posing a more academic question not a specific problem. Do you actually have performance problems with your current form/database? If so you probably need to provide more specifics.
Size of recordsource
Number of fields
indexes
form controls and execution

Like I said before, it usually is not "one size fits all". Certain strategies depend on certain specifics.

 

[DajTwo]

It was an academic question.. and I am not sure I understand fully the load order. I thought better to look at the example, and see what it does. It might help me understand better then try to apply the concept.

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[MajP]

This will help. Very important when dealing with subforms.
Access form order of events

http://office.microsoft.com/en-us/access/HP051867611033.aspx