Require better understanding of process flow

Daj · February 6th, 2009, 05:58 PM

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.

Access 2003: 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?

mmcdonal · February 9th, 2009, 08:28 AM

Are you having issues with the speed with which the form loads? Too many records in the query?

You can give your user the option to select a record before the form opens and limit the form to that one record, then use the combo to select another record. That means the form only has one record open at a time. That can work quickly. You could also give the user a limited recordset by selecting only the top 100 from the initial query (those records last used) and then give them the combo to look up more reocrds, etc.

Did any of that help?

Daj · February 9th, 2009, 09:11 AM

You are right on the money...

There not much issues with local acess but with VPN, it's murder..

How would I go with having the user select a record before the forms open, then enable the combobox?

mmcdonal · February 9th, 2009, 09:19 AM

Just create a combo box that looks up one or more fields in the table similar to the one that is already on the form for record navigation. Then use the button wizard to create a button to launch your form, and if it asks to limit the records based on a control on the current form, point it to the combo box.

It may be that you will have to create a different copy of your current form so that it does not load its own recordset. Just let it load the query with no parameters. The button that opens the form will supply the recordset, like:

Dim sPK As String
Dim sLink As String
Dim sForm As String

sForm = "MyForm"
sPK = Me.cboPKCombo 'check for a selection here

sLink = "[PKField] = '" & sPK & "'"

DoCmd.OpenForm sForm, , , sLink

This will open the form with one record. Then the user can use the combo on the form to look for other records. The after update event of the combo will need to refresh the recordset.

Did that help?

Daj · February 9th, 2009, 09:48 AM

I understand the concept.. great idea.. thanks..

Will make tests to see how the database reacts on VPN after that..

Thanks again