Maneuvering Datasheet w/ SQL backend

SanFrantastico · August 5th, 2009, 11:31 AM

Hi,

I've been a Lotus Notes progammer for years, but I'm pretty new to VBA, the Access object model, SQL, etc. It's been a giant learning curve, but I think I'm getting it. Oh yeah! I'm new to this forum also.

Here's my problem:

I have a datasheet in a subform. Users can select rows in the datasheet and hit a button to add the selected records to a particular group. That works great. I then send a requery to update the datasheet, but doing so moves the user to the top of the view. I'd like to return the user to his previous location after running the action.

I have been able to do some maneuvering with recordset.Move, which allows me to set a record as the 'current record.' However, this method still seems kinda erratic. The current record appears at the bottom of the display area for the datasheet, where it was originally somewhere in the middle of the display area before the operation.

When looking at a SQL view through a datasheet, is there a technique for setting which records will appear in the display area for the datasheet? Yes, setting the current record is fine, but I'd like that record to appear in just the same place that it was before the operation.

Did that question make any sense??

TIA!!

SerranoG · August 6th, 2009, 12:10 PM

Welcome to Wrox!

What you'd do is before you copy that record to the other group, make a recordset clone of the records you're currently working on. Store the bookmark of the current record's clone. When the datasheet list refreshes, set the current bookmark to the clone's bookmark and your cursor will return to where it was.

SanFrantastico · August 6th, 2009, 12:30 PM

Thanks, Greg. That sounds like it could be the tip I'm searching for. I'll give it a try this afternoon.

Little tips like this are so helpful when trying to digest a new paradigm, which programming in Access is for me. I've prolly spent 4 hours fishing around trying to solve this one on my own. I came close, I learned a lot about ADO and the Access object model, but I never hit on the best solution.

SanFrantastico · August 14th, 2009, 02:25 PM

I'm still having this problem.

Rather than using a clone, I had created a variant to store the recordset's bookmark prior to requerying. After the requery, I reset the Bookmark. Like this:

Code:

Dim rs As ADODB.Recordset
Set rs = sf_FoodGroupView.Form.Recordset   'I'm grabbing the recordset from a subform datasheet

Dim varBookmark As Variant
varBookmark = rs.Bookmark

Call sf_FoodGroupView.Form.Requery   'This is way faster than calling rs.Requery for some reason.

Set rs = sf_FoodGroupView.Form.Recordset  'The old rs seems to die during the requery.
rs.Bookmark = varBookmark

The above gets me close.

Here's an image before the requery. I've selected Roquefort cheese and I'm about to hit the Add button, which will create a record adding this cheese to the Working Group subset of foods.

http://www.storn.com/Junk/WG-before.jpg

and after:

http://www.storn.com/Junk/WG-after.jpg

The requery correct adds 'WG' to the rightmost column, showing the addition of Roquefort to the Working group. I've navigated back to the bookmarked record, but it doesn't appear exactly as before.

I've tried a zillion variations, including snagging the bookmark from a cloned recordset, but so far this is as close as I've come.

Any ideas??

SerranoG · August 17th, 2009, 04:41 PM

Oh, not only do you want the cursor in the correct record, but you want the record to be in the same spot on-screen and you want the record to be highlighted, too.

That level of zeroing in where the record is sitting in the same spot like that is not something recorded. If you know that by going down, say, eight records and then going back to the bookmark places the record in the exact spot as it was before, then you can code that. But the eight (or whatever number) would have to be a trial-and-error guess from you.

SanFrantastico · August 17th, 2009, 05:03 PM

I *just* solved this problem about 5 minutes ago...

Rather than calling Requery, I should have called Resync. Resynch keeps the datasheet in the same place, while Requery destroys the recordset so the recordset and the location must be reconstructed.

When I first tried it, Resync had been doing *nothing* at all so I gave up on it. After some digging I found that I had to set the UniqueTable and ResyncCommand properties correctly in order to get rs.Resync to work. This is because I'm using an odd view from a SQL database as the RecordSource, so this info had to be set up manually. I'm greatly relieved now, and better edumacated too!

SerranoG · August 18th, 2009, 07:51 AM

Cool! Resync is something I've never dabbled with so I learned something, too. Thanks!