Roddy,

With 24,000 records your users need a method of selecting the data displayed
on the form. Your example "1 table and 1 form" makes this difficult - as
soon as you open the form it will require all the records on which it is
based, then you are changing the record source which causes a requery.

I always use an introductory form, with a combo or list box from which a
user can select the required record. The row source is a query which returns
only the list and the record ID, and because it is limited info it is fast.
After that, the method you use to display the selected record is up to you.
Wherever possible I use a where clause on the open form because it is faster
than changing the record source. Usually my introductory form is not based
on any table - in one of my aps it served as the start up screen with a
picture and operating instructions.

With 24000 records your users may need a stepped method of selecting a
record, ie a second combo box where the list depends on the users selection
in a prior combo box. But every situation is different and there are a
thousand ways to skin a cat.

David




-----Original Message-----
From: Roddy Campbell [mailto:scotiwis@b...]
Sent: Tuesday, March 26, 2002 9:55 PM
To: Access
Subject: [access] Re: best way of using forms on Acces Project - please
advise


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.

?

Thanks for your reply David.
That sounds like an intersesting idea, I may give it a shot.
Before that though - my method 3 I can't test for performance as I can't 
figure out the code.
I want the afterupdate of the combo box to pass the recordID to the stored 
procedure, and then change the recordsource of the form to the result of 
the stored procedure.

what I have:

after update of combobox:

Private Sub LotNoFind_AfterUpdate()
'Detail.Visible = True
'Dim rs As Object
'    Set rs = Me.Recordset.Clone
'    rs.Find "[CATSGoodsInid] = " & Str(Me![LotNoFind])
'    Me.Bookmark = rs.Bookmark
'    rs.Close
'    Set rs = Nothing

Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter

Set cmd1 = New ADODB.Command

With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "CATSspgGoodsInLookupLot"
    .CommandType = adCmdStoredProc
    Set prm1 = .CreateParameter("@GoodsInid", adInteger, adParamInput, 4)
    .Parameters.Append prm1
    prm1.Value = LotNoFind
End With

Dim rstTest As ADODB.Recordset
Set rstTest = New ADODB.Recordset
    rstTest.Open cmd1
    Me.RecordSource = rstTest     ****returns error "Type Mismatch"

End Sub


the stored procedure:

CREATE PROCEDURE CATSspgGoodsInLookupLot
	(
		@CATSGoodsInid  integer
	)

  AS

SELECT * FROM CATSGoodsIn WHERE CATSGoodsInid = @CATSGoodsInid
GO


The first few lines of the afterupdate combobox are my method 1, ignore 
them time being. I can't figure out why I get a Type Mismatch when running 
the after update on the combobox. Can you see what I'm doing wrong here ?

Hi

I think your basic problem is a missing 'Set':

    Set Me.RecordSource = rstTest

I would also recommend the following changes, just to ensure that the 
recordset is suitable for binding to a form:

     rstTest.CursorLocation = adUseClient
     rstTest.Open cmd1

On the wider issue, I'm (hopefully) coming to the end of a large Access 
Project\SQL Server job, and I went for Method 4!

As in your method 3, I'm using Stored Procedures to return single records, 
letting the server machine do all the hard work. The difference is, I'm 
using totally unbound forms to display the results. The downside is that 
you have to write the code to do all the stuff that Access would usually 
do, such as filling the forms controls and writing any changes back to the 
database. The advantage is that you've got the most scalable system 
possible. As there's no live connection to the database whilst the user is 
editing records you should be able to accomodate a lot of simultaneous 
users without a performance hit.

One last suggestion about the code below. You should be able to generalise 
the code so that you can use the same function to return recordsets for 
all your forms. All the Stored Procedures that I use for forms have a 
parameter named @intUID, which uniquely identifies the record I want. I 
then pass the Stored Procedure name and the UID to my general purpose 
function, which then returns the recordset I'm looking for.

Hope some of this helps!

Brian

> Thanks for your reply David.
T> hat sounds like an intersesting idea, I may give it a shot.
B> efore that though - my method 3 I can't test for performance as I can't 
f> igure out the code.
I>  want the afterupdate of the combo box to pass the recordID to the 
stored 
p> rocedure, and then change the recordsource of the form to the result of 
t> he stored procedure.

> what I have:

> after update of combobox:

> Private Sub LotNoFind_AfterUpdate()
'> Detail.Visible = True
'> Dim rs As Object
'>     Set rs = Me.Recordset.Clone
'>     rs.Find "[CATSGoodsInid] = " & Str(Me![LotNoFind])
'>     Me.Bookmark = rs.Bookmark
'>     rs.Close
'>     Set rs = Nothing

> Dim cmd1 As ADODB.Command
D> im prm1 As ADODB.Parameter

> Set cmd1 = New ADODB.Command

> With cmd1
 >    .ActiveConnection = CurrentProject.Connection
 >    .CommandText = "CATSspgGoodsInLookupLot"
 >    .CommandType = adCmdStoredProc
 >    Set prm1 = .CreateParameter("@GoodsInid", adInteger, adParamInput, 4)
 >    .Parameters.Append prm1
 >    prm1.Value = LotNoFind
E> nd With

> Dim rstTest As ADODB.Recordset
S> et rstTest = New ADODB.Recordset
 >    rstTest.Open cmd1
 >    Me.RecordSource = rstTest     ****returns error "Type Mismatch"

> End Sub

> 
t> he stored procedure:

> CREATE PROCEDURE CATSspgGoodsInLookupLot
	> (
	> 	@CATSGoodsInid  integer
	> )

>   AS

> SELECT * FROM CATSGoodsIn WHERE CATSGoodsInid = @CATSGoodsInid
G> O

> 
T> he first few lines of the afterupdate combobox are my method 1, ignore 
t> hem time being. I can't figure out why I get a Type Mismatch when 
running 
t> he after update on the combobox. Can you see what I'm doing wrong here ?

wow Brian thanks for that. I'll come back to you on some of what you said 
above, meantime have amended code as per your suggestion, code is now:

Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Set cmd1 = New ADODB.Command
With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "CATSspgGoodsInLookupLot"
    .CommandType = adCmdStoredProc
    Set prm1 = .CreateParameter("@GoodsInid", adInteger, adParamInput, 4)
    .Parameters.Append prm1
    prm1.Value = LotNoFind
End With
Dim rstTest As ADODB.Recordset
Set rstTest = New ADODB.Recordset
    rstTest.CursorLocation = adUseClient
    rstTest.Open cmd1
    Set Me.RecordSource = rstTest


when run the after update combo am now getting following compile error 
message:
invalid use of property - and it highlights ".Recordsource =" in the debug 
window.
Any ideas again ? Determined to get this to work.

Apologies Roddy. 

I was mistakenly looking through the Access XP help files last night - 
allowing you to run the code below is obviously one of the improvements 
they've made. In Access 2000, as far as I can figure out, you cannot 
assign the results of a stored procedure call to be the 
recordsource\rowsource of any access object.

What you have to do instead is to set the recordsource to a string that 
will call the relevant stored procedure. So your after_update code comes 
down to a single line:

Private Sub cmbFindRecord_AfterUpdate()

    Me.RecordSource = "EXEC CATSspgGoodsInLookupLot @GoodsInid=" & Me!
[cmbFindRecord]

End Sub

Thanks Brian, I'll try this when I get home later on today.
What I was wondering meantime is if this replaces all of the ADO code, 
does this mean this way of doing it doesn't use ADO at all? I was told 
that must always use ADO connection for fastest retrieval of data on 
Access Project.
Thinking of upgrading to XP as seems it has more data handling 
cababilities in general.

Brian tested code above and get "there was a problem accessing a property 
or method of the OLE object" runtime error. Tried it with:
Me.RecordSource = "exec CATSspgGoodsInLookupLot @GoodsInid=5" to see if 
that would work, get same error. Only works with:
Me.RecordSource = "CATSspgGoodsInLookupLot" but then prompts for parameter.
Any ideas? And where do I find help on this - is it msdn library on line, 
if so what do I search for or do you have other resources.
thanks

The recordsource property is expecting a string value like the SQL statement
that returned the recordset.

You could set it to

"SELECT * FROM TableName WHERE Criteria = " & cboBoxName.value

if the combobox has a string value in the first, probably hidden column,
then you must surround the cboBoxName.value with single quotes in the
returned string.  This might yeild good performance on the database if the
ID field is indexed and would not need a stored procedure to run.

|-----Original Message-----
|From: Roddy Campbell [mailto:scotiwis@b...]
|Sent: Wednesday, March 27, 2002 1:21 AM
|To: Access
|Subject: [access] Re: best way of using forms on Access Project - please
|advise
|
|
|wow Brian thanks for that. I'll come back to you on some of what you said
|above, meantime have amended code as per your suggestion, code is now:
|
|Dim cmd1 As ADODB.Command
|Dim prm1 As ADODB.Parameter
|Set cmd1 = New ADODB.Command
|With cmd1
|    .ActiveConnection = CurrentProject.Connection
|    .CommandText = "CATSspgGoodsInLookupLot"
|    .CommandType = adCmdStoredProc
|    Set prm1 = .CreateParameter("@GoodsInid", adInteger, adParamInput, 4)
|    .Parameters.Append prm1
|    prm1.Value = LotNoFind
|End With
|Dim rstTest As ADODB.Recordset
|Set rstTest = New ADODB.Recordset
|    rstTest.CursorLocation = adUseClient
|    rstTest.Open cmd1
|    Set Me.RecordSource = rstTest
|
|
|when run the after update combo am now getting following compile error
|message:
|invalid use of property - and it highlights ".Recordsource =" in the debug
|window.
|Any ideas again ? Determined to get this to work.
|

Well,I'm a bit puzzeled!

I copied this code straight from my database, where it worked perfectly.

Is your database small enough to email? I think I would be able to find 
the problem with a bit of 'hands on' work.

Help on Access Projects is extremly difficult to find. The Access help 
files are a joke. MSDN is slightly better, but you have to extrapolate 
from the VB articles.

I found Wrox's SQL Server with Access 2000 a good starting point. By no 
means perfect, but a good starting point. The Access Developers Handbook 
is said to have some excellent chapters on this, but I haven't updated my 
copy yet!

Brian

> Brian tested code above and get "there was a problem accessing a 
property 
o> r method of the OLE object" runtime error. Tried it with:
M> e.RecordSource = "exec CATSspgGoodsInLookupLot @GoodsInid=5" to see if 
t> hat would work, get same error. Only works with:
M> e.RecordSource = "CATSspgGoodsInLookupLot" but then prompts for 
parameter.
A> ny ideas? And where do I find help on this - is it msdn library on 
line, 
i> f so what do I search for or do you have other resources.
t> hanks

It's the equivalent of putting a query name in the recordsource.

The form must use it's own internal code to create a connection to the 
dataprovider, and presumably this is an ADO connection.

Brian

> Thanks Brian, I'll try this when I get home later on today.
W> hat I was wondering meantime is if this replaces all of the ADO code, 
d> oes this mean this way of doing it doesn't use ADO at all? I was told 
t> hat must always use ADO connection for fastest retrieval of data on 
A> ccess Project.
T> hinking of upgrading to XP as seems it has more data handling 
c> ababilities in general.

Well thanks again Brian, after much tooing and froing I've decided on 
method 5(!)
I've got recordsource as stored proc which returns all records where 
LocationID = Location combo on my form. I have 24000 records in total, and 
these are split into various locations, eg Goods into USA, Goods into 
Scotland etc. So when I update location combo it fires stored proc 
returning all records where Location=combo location, the input parameter 
of form set to the combobox id. That way I'm getting a recordset of about 
4-5000 at a time, which access then works on in its cache. This seemed in 
practice faster than calling stored proc for every record, even on unbound 
form.

Only another prob has cropped up! I need the form to initially load with 
main location data showing as most people will use this. The OnOpen 
property of form sets Location combobox to 1 (main location) but only 1 
record comes up, the stored proc works fine once location combo is 
updated, but its as if when the form opens the stored proc fires before 
the combo is set to 1. Any ideas here ? (again!)

The form loads its underlying data before any events can run - which 
explains why it's not working.

I can think of a couple of things to try:

1) Leave the recordsource of the saved form blank. Then set it in the Open 
event, after you've set the combobox value.

2) In your stored procedure, set a default on the comco cox parameter. 
Presumably the combobox is NULL when your form requests data from the 
stored procdeure. If you set a default, the stored procedure will use this 
value if passed a null and should return the recordset you want.

Brian

> Well thanks again Brian, after much tooing and froing I've decided on 
m> ethod 5(!)
I> 've got recordsource as stored proc which returns all records where 
L> ocationID = Location combo on my form. I have 24000 records in total, 
and 
t> hese are split into various locations, eg Goods into USA, Goods into 
S> cotland etc. So when I update location combo it fires stored proc 
r> eturning all records where Location=combo location, the input parameter 
o> f form set to the combobox id. That way I'm getting a recordset of 
about 
4> -5000 at a time, which access then works on in its cache. This seemed 
in 
p> ractice faster than calling stored proc for every record, even on 
unbound 
f> orm.

> Only another prob has cropped up! I need the form to initially load with 
m> ain location data showing as most people will use this. The OnOpen 
p> roperty of form sets Location combobox to 1 (main location) but only 1 
r> ecord comes up, the stored proc works fine once location combo is 
u> pdated, but its as if when the form opens the stored proc fires before 
t> he combo is set to 1. Any ideas here ? (again!)

thanks, got that to work and tested thoroughly.
made no difference to speed, so think I'm going to stick with on form open:
recordsource = select * from GoodsIn where Location = 1
after update of location, recordsource becomes select * from GoodsIn where 
Location = combobox location id.
Tested this is the fastest and least complicated - unless anybody knows of 
a faster alternative ?

my next query:
I hear from many sources that stored procedures are the fastest possible 
way of retreiving data from a table. Should I use stored procedures 
instead of views? My view for the combo box for selecting individual 
records within the location is based on several tables. Would it be faster 
to take the SQL from this view and enter it as the code for a stored 
procedure and make the combo box based on this stored procedure (no 
parameters involved). Or do views exceed stored procedures when its just 
combining tables and selecting records?

And Brian, seeing as how you're coming to the end of a fairly large access 
project do you have any other tips for someone that is just starting out 
on one? Basically we've been running on Access2000 with linked tables to 
SQL2000 for few years - have a different mdb for each location we have and 
every time we took on a new location I had to duplicate the mdb and 
rewrite the code. Now combined all into one and started using Access 
project. Have about 20-30 tables, hundreds (help!) of queries which I'll 
have to re-write into SQL, and about 200 reports. We have such a 
complicated business the whole mdb's codes are written with a million 
different "after update on such and such a field do this", "if value on 
this form equals value on another form" etc etc, I reckon its going to 
take me best part of 6 or 7 months to re-write from scratch (problem being 
it was never designed in the first place - it just "evolved"). Anyway have 
reviewed the entire structure of the system and setup tables accordingly, 
its just the absolute mass of transferring all the 
queries/forms/reports/modules/code etc into access project that's the 
nightmare.
If you have any suggestions/tips would be most appreciated - I want this 
to be the be all and end all of systems that takes care of itself and 
might actually allow me a holiday for the first time in 3 years!
Priority from users is SPEED - instant retrieval of any data.