Bookmark for recordsets object not co-ordinating with bookmark of the form

[AshikHusein]

Could anyone tell m what is wrong with this VBA code snippet:



Dim rec as DAO.recordset
set db = CurrentDB

strSQL = "some SQL"

set rec =db.openrecordset(strSQL,dbOpendynaset)

rec.Bookmark = Me.Bookmark (where "Me" is a form)

I know it all works with the recordsetclone object. The problem is that with the recordsetclone object there is a lot of data being transferred across the network. What I am trying to do is to open a recordset having a fewer records and then co-ordinating the form's bookmark and the recordset's bookmark. In DAO, "bookmark" appears as a method for a recordset and so I thought it would work. Although I do not get any error messages, it does not appear to work since the form does not go to to the record specified by the SQL statement, but to a different record. Please provide me with an understanding of what I may be doing wrong here for whoever understands this. Thanks.

 

[The Stoat]

Hi,

As far as i understand it a bookmark is an index that access creates when it opens a recordset. So if you open a form up the bookmark index is created. It doesn't actually relate to specific data in recordset i.e. If you create two identical recordsets by using two identical forms then the bookmark properties can be different. This is why you can't do what your trying Your SQL recordset and form recordset have different bookmarks.

What you need to do is make a clone of the forms recordset. Use a find statement to goto the record in the recordset with the data you want. And then bookmark the forms recordset using the cloned recordsets bookmark.

If you want to open a form with fewer records then you can set criteria in the recordset of the form i.e

me.recordsource = "Select Tbl_stuff.* from Tbl_stuff where(((Tbl_stuff.Stuff_Name)= 'FLuffy'));"

or in the query that feeds the form.

If you have a backend database on a server then the front end should be on your desktop. To achieve maximum effciency with this set up you should only bring records across the network that you need by using criteria.

Once you have opened a form all of the data specified by the forms recordsource query has been bought across the network anyway. The recordsetclone method will be running locally on your machine if the frontend is on your desktop.





HTH

TS

 

[AshikHusein]

I appreciate you response.

So what you are saying then is that when a form has bound controls and the table that feeds the data to the form is on a back end on a network you cannot avoid huge amounts of data transfer across the network. Would that be correct? Thank you.

 

[The Stoat]

Yes,

Your forms should be based on queries, which are based on your tables. Your queries should be designed to pull the minimum required data for the user. As an example a user wants to view sales they made to a customer. Firstly you could ask the user to select the customer from a drop down list. So you may have to bring across all the customers from the customer table -via a query - but only select one or two fields such as Name Company when they may be 20 fields in that table.

Then you get all 20 customer fields from the server based on the users selection but only 1 record. If they want to view the sales made to the customer you can use the customerID and you could give them the option of specifing dates or amounts or the sale ID then only collect the sales that meet the criteria.

Many people get all the records from the server and then filter them on the desktop. This is very inefficient. You can always give the user the option of selecting all the records but they will soon learn it is faster to make a predefined selection.

Of course if you test your connection speed and their is very little difference between pulling across all the records or just a few then you can make your own decision about which is better.

In truth it is a balance between making life easy for the user and producing efficient code/queries etc. Ask them lots of questions and they tend to get fed up. Make a slow database and they also get fed up.


HTH

TS