- Local time
- Today, 17:27
- Joined
- Feb 19, 2013
- Messages
- 16,978
if your form recordsource is based on an entire table, an entire table will be loaded. However once access has received a few records, it will display and continue to load the remainder of the records in the background - so it appears faster. But try and navigate/filter etc before all records are loaded will result in delaysIn addition, even if the entire table has to be transferred if the form is bound to the unfiltered table, then loading the form should be much slower, but once the form is loaded and the data is transferred and cached/loaded into RAM or wherever the data resides, retrieving one row from a table that's already been transferred should be comparable or even faster than retrieving the row from the server each time the user navigates.
You can see this with a large table - just open the table and wait until the record stats are loaded at the bottom. It doesn't know how many records are in the recordset until they are all loaded.
not true - if you have requested a single record in a query with appropriate criteria, then a single record will be returned. Note that the WHERE parameter of docmd.openform actually applies a filter to the form, not a criteria. I think you may be confusing applying certain dao queries (such as group by's) to linked tables but it will still only bring through the records limited by the WHERE criteria and is why you should use the WHERE criteria on grouped fields and only use the HAVING on summed/count etc fields. With SQL server of course, you should use pass through queries and make use of stored procedures so all the heavy lifting is done on the remote serverwhereas JET/ACE are simply acting as a file share, so requesting one row requires the entire table to be transferred and it's only the client computer doing the filtering to display the row we need. If that's the case, why is basing a form on a single row an advantage?
With regards implementation it does depend on how your front end works and the work patterns of the users. But assuming your form has a combo on it (and again, do you really want to populate with 10,000 customer names for the user to select one?) to select a record you can set the form recordsource to say
SELECT * FROM myTable WHERE False
no records are returned and the controls will not display #Error or similar
user selects a record. Instead of having the code
me.filter="PK=" & cboPK
me.filteron=true
your code would be
me.recordsource="SELECT * FROM myTable WHERE PK=" & cboPK
there is potentially a very small benefit in using a query so the query plan does not need to be determined, but in my experience it has not been an issue.
if the form is opened based on a previously selected PK, have the recordsource set as above to false
pass the PK as an openarg , not in the where parameter and in the form open event
Code:
if me.openargs<>"" then
me.recordsource="SELECT * FROM myTable WHERE PK=" & me.openargs
'or you can use me.recordsource=replace(me.recordsource,"False","PK=" & openargs)
else
me.dataentry=true
end if
oncurrent works exactly the same.
Subforms require a little bit more work. You still populate the linkchild/master properties so the relationship is maintained for new records but in the mainform current event you put
subform1.form.recordsource="="SELECT * FROM mySFTable WHERE FK=" & me.PK
or you can use the replace method if user is only acting on a single record (sometimes a user might want to open several records rather than just the one)
Last edited: