Hi I have a fairly large database set up on Access 2000 with linked tables 
to SQL 2000.
I am currently in the process of converting this to an Access 2000 Project.

What I need to know is what is the fastest and most efficient way of 
dealing with data on forms. As an example I have a form which draws in 
about 24000 (growing by hundreds every day) records directly from a table.

Seems the alternatives are:

1. recordsource of form set directly to SQL table. Combo box on form which 
uses "find record on form" to select a record from all the records to call 
it up on screen. Combo box after update is:
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.Find "[CATSGoodsInid] = " & Str(Me![LotNoFind])
    Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing
End Sub

2. recordsource of form is set to select 1 record from table using the no. 
of records to display on form property. Combo box then changes 
recordsource of from after update based on combo box value, ie record 
source of form becomes SELECT * FROM GoodsIn WHERE GoodsInID = (**combo 
box value)

3. recordsource of form is a stored procedure which only ever returns 1 
record, this value is what the form displays. Combobox after update sends 
its value as parameter to stored procedure which then returns the 1 record 
for the form to display.

Can you advise which is the fastest way to get lookup the records in the 
combobox and display them on the screen?

Many thanks.
R. Campbell

should also mention I have about 10-15 users accessing the system at any 
one time, if this makes any difference.

can anyone help on this please ?

On Tue, 26 Mar 2002 20:15:45 "Roddy Campbell" wrote:

> can anyone help on this please ?

Your question doesn't quite make sense. Forms are used 
to display data and to allow the user to enter data. Can 
you be more specific?

Paul

ok I'll try and be clearer.

Taking 1 table and 1 form as an example.

I have the form displaying the data from the table, users can select 
various records by using a dropdown combo box, amend data / add data using 
the form.

I have been told various things by various people regarding the best way 
to link the table to the form, what I want to know is which is the fastest 
method when selecting a record in the combo box for it to display on 
screen.

The alternatives are:

1. set the recordsource of the form directly to the table.
problems with this are that when the form is opened for the first time by 
a user all 24000 records are loaded into it, the combo box uses "find 
record on form" to search through the 24000 records and select one - this 
is the client/access side doing all the work.

2. set the recordsource of the form to "select * from [table] where 
recordID = combobox afterupdate recordID". The recordsource of the form is 
initially set to display 1 record when opened - "select * from [table] 
where recordID = 1" - this avoids the need to load 24000 records at 
startup - the combobox after update then changes the recordsource of the 
form to recordsource = "select * from [table] where recordID = combobox 
after update ID. This seems to still be unefficient as Access is 
performing the Select statement on the table rather than SQL.

3. set the recordsource of the form to a stored procedure. The stored 
procedure code is "select * from [table] where recordID = @inputRecordID". 
The table as with 2 opens with recordsource = "select * from [table] where 
recordID = 1", the on afterupdate of the combobox triggers the stored 
procedure sending it the afterupdate recordID, the returned record from 
the stored procedure is then set as the recordsource of the form - this 
way SQL is doing all the work and access is just passing parameter and 
displaying result on form.

What I want to know is what is best practice out of the 3 above, should 
you load all table's records into form then get access to "find a record 
on my form" with combo box as with 1, only ever have 1 record loaded into 
form and use Access to call SQL code to get a record and display on form 
as with 2, or only ever have 1 record loaded on form and get the stored 
procedure to return the value of the record you want after update of 
combobox.

?

On Tue, 26 Mar 2002 21:54:50 "Roddy Campbell" wrote:

> ok I'll try and be clearer.
> 
Oof. You are more advanced than I am, rather than being 
the beginner I thought after reading your first letter. I can't 
offer much help. The only large system I have seen is one 
that is owned by the state, and our staff do data entry using 
their forms, and can do extracts (using Access.) It's in 
Oracle. The forms provided have provision for a user to 
provide a name or SSN to search by. Either can be partial; 
wild card is assumed. The method is a mite clumsy. Good 
luck; I'll be reading any answers you get.

Paul

thanks anyway Paul. Anyone else shed some light?