Can I determine the position of the scroll bar in datasheet view?

[ulam]

I have a form shown in datasheet view, and I have some actions that require that form to be requeried with a delete operation, however number of rows in the datasheet view remains constant with a delete operation in this specific case.

I am trying to return the user back to the same row that he just selected the action on, and also at the same position on the screen - i.e. the same row and the same position of the scrollbar.

An example:

datasheet has 100 records (rows)

User is currently viewing records 45-60 (scrollbar is approximately half way down)

The user selects record 56 for the action.

The action finishes, the records are requeried to reflect the change (I have so far been unable to replicate this without the requery)

I am able to return the user to the record 56, but it is located at the bottom of the window now, not about halfway up as it was when it was selected. I also can figure out how to get it to be at the top of the list, but I want it to return the same position.

Anyone have any idea how to do this?

Thanks in advance!

[ulam]

Sorry forgot to mention - Access 2003

[JConsulting]

Sorry forgot to mention - Access 2003

Look into the Bookmark property.

if you set the bookmark at the current record number, do your delete action then reset the bookmark to that same number, you have effectively remained in place.

experiment also with refresh vs requery.

J

[ulam]

Look into the Bookmark property.

if you set the bookmark at the current record number, do your delete action then reset the bookmark to that same number, you have effectively remained in place.

experiment also with refresh vs requery.

J

I am currently using the bookmark to return the correct number. As I mentioned the problem is not in returning the the correct record. The problem is that when I return to that record it is not in the same row as it was before the operation.

I did try to use refresh after your suggestion with some success, but that resulted in some bizarre behavior that I can maybe get around but requery most certainly works better if I can figure out the scroll bar position.

Perhaps I wasn't clear, so let me try again,
I have a query that returns 100 records.
In the datasheet view (it's a subform so viewing area is limited) there is room for approx. 25 records to be seen at a time.
Therefore to see the record in row 57 the user must scroll down to it by some mechanism. However that is done the scroll bar does advance to reflect which portion of the total 100 records the currently seen 25 records are from.

If we assume the user uses the mouse to scroll down to record 57, its safe to assume it unlikely the user will scroll until record 57 is exactly the record in the 25th row of the viewable area. For the sake of example, lets say the records in view after the scroll down are records 51 through 75. So in row 1 is record 51 and in row 25 is record 75. The record to be changed is #57 which is in row 7.

After the edits are made - (it's only in one specific case, not just any edit, but I must handle this special case as well), a requery is called.

If I save a bookmark and return to the same record, or by saving the selTop and resetting it (same effect for both). After the requery and reset of position I am left with:
The user is viewing records 33 through 57, with the editied record now in row 25.
What I am wishing to accomplish is after the requery reestablish the state of the scrollbar not just the recordnumber. So after the requery the user is still viewing records 51-75 and the edited record is still located in row7.

As far as I am aware the bookmark property isn't going to solve this one, am I missing something?

Is there really no way to find out which records are in view, or some indication of the position of the scrollbar?

[ADezii]

I have a form shown in datasheet view, and I have some actions that require that form to be requeried with a delete operation, however number of rows in the datasheet view remains constant with a delete operation in this specific case.

I am trying to return the user back to the same row that he just selected the action on, and also at the same position on the screen - i.e. the same row and the same position of the scrollbar.

An example:

datasheet has 100 records (rows)

User is currently viewing records 45-60 (scrollbar is approximately half way down)

The user selects record 56 for the action.

The action finishes, the records are requeried to reflect the change (I have so far been unable to replicate this without the requery)

I am able to return the user to the record 56, but it is located at the bottom of the window now, not about halfway up as it was when it was selected. I also can figure out how to get it to be at the top of the list, but I want it to return the same position.

Anyone have any idea how to do this?

Thanks in advance!

1. In the AfterUpdate() Event of the Form and prior to the Requery, calculate the PercentPosition Property of the underlying Recordset.
2. Perform the Requery.
3. Set the PercentPosition Property to the value (SINGLE) between 0.0 and 100.00 that was previously calculated.
4. The Scroll Bar should dynamically adjust and be positioned exactly where it was.
5. Is this what you were looking for?

[JConsulting]

1. In the AfterUpdate() Event of the Form and prior to the Requery, calculate the PercentPosition Property of the underlying Recordset.
2. Perform the Requery.
3. Set the PercentPosition Property to the value (SINGLE) between 0.0 and 100.00 that was previously calculated.
4. The Scroll Bar should dynamically adjust and be positioned exactly where it was.
5. Is this what you were looking for?

Sounds like you wish to be able to delete a record, perform the requery, then reposition things as if the record had simply disappeared...the same record as before is at the top of your viewing area...and you are poitioned at the "next" record (obviously because the one you were on is gone)..so it appears to be a seamless operation. Returning to the bookmark works but returns that record as record 1 in your viewing area...but really record 1 should be 7 records up?? so you need a way to figure out not only what record you're on, but also what relative position you were in to calculate what is the first record to show at the top of your viewing area. If that's the case, ADz's solution may work...I'll play around with this as well...just in case it doesn't.
J

[dima69]

I'm sorry but I don't see how ADezii's solution can work.
I can suggest two things here, which can give only partial solution.
1. If all you need to do is delete one record, use

docmd.RunCommand acCmdDeleteRecord

This way you don't have to requery, so everything stays where it was.
2. If your list is a subform on the main form, requery the subform control instead of requerying the form, i.e. from the main form:

Me![SubFRM].Requery

where SubFRM is the name of subform control, or from the subform:

Me.Parent![SubFRM].Requery

However, this works only if no ordering is applyed on the form, otherwise it works like a form requery, jumping to the first record.

[ulam]

Thanks for the suggestions! I will give ADezii's method a try first.

For anyone that is playing around with this - some more info will help you recreate the scenario.

The form (it will be a subform - but for now just getting this functionality in a form is sufficient) is based on a left join query. There are two fields, the first is a checkbox (yes/no or whatever) that is calculated. The second field is an ID field - number.

i.e.
select tableb.field1, tablea.field1 from tablea left join tableb on (tablea.id = tableb.id)

The ID's correspond to all of the records in the left side of the left join.
The calculated checkbox is merely checking for nulls on field on the right side of the join.

The interface allows the user to change the checkbox from checked to not checked (which requires a delete from tableb) or to place a check in the checkbox (which requires an insert into tableb)

Adding a new record by typing in a new ID in a new record of the recordset inserts a new record into tablea, but does nothing to tableb.

I am currently utilizing the On Mouse Down event to detect a 'click' in the checkbox to handle the delete/insert in tableb.

I have the insert (add a checkbox) working, and it does not require a refresh or requery.

It is the delete that causes the problems. When I used the refresh, the checkbox does disappear. But if I click that same box a second time to then do an insert (even if clicking other boxes between the 1st and 2nd time) The insert to tablea appears, but the checkbox does not get a check. So it's entirely possible to click it a third time and a duplicate insert is attempted - which I could catch and just skip, but the missing check in the checkbox is the actual problem.

So the list of ID's never changes, consequently even after a delete the row still exists - due to the left join.

I can make this easier by changing the tables a little - but I preferred this arrangement if I can work out this last little quirk in repositioning the scrollbar after a requery.

[ulam]

1. In the AfterUpdate() Event of the Form and prior to the Requery, calculate the PercentPosition Property of the underlying Recordset.
2. Perform the Requery.
3. Set the PercentPosition Property to the value (SINGLE) between 0.0 and 100.00 that was previously calculated.
4. The Scroll Bar should dynamically adjust and be positioned exactly where it was.
5. Is this what you were looking for?

I tried this and landed one record worse than using bookmarks. I'm not sure what you mean by calculating the PercentPosition Property so perhaps I didn't do this correctly. The PercentPosition has a value that is approximately correct prior to the requery, so I just saved that value and then reset it after the the requery. If there was more to it, could you kindly explain what else you intended, please.


I have also ran across this link:
http://www.lebans.com/setgetsb.htm
but am so far unsuccessful getting this to work in WindowsXP with Access 2003.

[ADezii]

I tried this and landed one record worse than using bookmarks. I'm not sure what you mean by calculating the PercentPosition Property so perhaps I didn't do this correctly. The PercentPosition has a value that is approximately correct prior to the requery, so I just saved that value and then reset it after the the requery. If there was more to it, could you kindly explain what else you intended, please.


I have also ran across this link:
http://www.lebans.com/setgetsb.htm
but am so far unsuccessful getting this to work in WindowsXP with Access 2003.

The PercentPosition property or returns a value indicating the approximate location of the current record in the Recordset object based on a percentage of the records in the Recordset. It return value is a Single that is a number between 0.0 and 100.00. The pseudo code for the calculation of PercentPosition would be:

((CurrentRecordNumber) / (TotalNumber of Records in Recordset)) * 100

[ulam]

The PercentPosition property or returns a value indicating the approximate location of the current record in the Recordset object based on a percentage of the records in the Recordset. It return value is a Single that is a number between 0.0 and 100.00. The pseudo code for the calculation of PercentPosition would be:

((CurrentRecordNumber) / (TotalNumber of Records in Recordset)) * 100

Correct me if I am wrong, but that doesn't seem like it is going to work. A quick example.


TotalNumber of records: 100
CurrentRecordNumber = 40
The record #40 is displayed row 20 (meaning records 21-45 are visible)
40 / 100 * 100 = 40

If I slide the scrollbar in such a way as to move the location of the current record from row 20 to row 10 (meaning records 31-55 are visible)
the calculation is still 40 / 100 * 100 = 40.

As you can see this does nothing to set a specific location for the scroll bar, it only aids in selecting a specific record - just like a bookmark, and the SelTop property (perhaps inadvertently) does.

[smofi]

Hi Ulam

I just have been finding a solution for the same problem you describe. Maybe it helps you too.

I use SelTop. But I do not set it directly after the requery but I force the form creeping down step by step:

 Dim i, oldRecNum As Integer
oldRecNum = Me.SelTop
 
Me.Requery
 
For i = 1 To oldRecNum
    Me.SelTop = i
Next i

If you do so, scroll behaviour is different to setting SelTop directly. Like this, in my environment it works fine. Hope that helps. I have the Ide from another thread within this forum:

http://www.thescripts.com/forum/thread210398.html


Cheers
Smofi

[ulam]

Hi Ulam

I just have been finding a solution for the same problem you describe. Maybe it helps you too.

I use SelTop. But I do not set it directly after the requery but I force the form creeping down step by step:

 Dim i, oldRecNum As Integer
oldRecNum = Me.SelTop
 
Me.Requery
 
For i = 1 To oldRecNum
    Me.SelTop = i
Next i

If you do so, scroll behaviour is different to setting SelTop directly. Like this, in my environment it works fine. Hope that helps. I have the Ide from another thread within this forum:

http://www.thescripts.com/forum/thread210398.html


Cheers
Smofi

Thanks for the idea! That doesn't quite work for my situation either, it seems to scroll back so the record is at the bottom of the form still same as if I set selTop directly. I believe the library I linked to above that is also linked to in the thread you linked to will solve my problem once I get a chance to get it working for me.

[ulam]

Well thanks to everyone that posted. It seems there is no hook in vba to get the scrollbar, and all the tricks presented do not work in my situation, although I think we have successfully explored a whole slew of convulated ways to retain a record number!

This library does provide the solution:
http://www.lebans.com/setgetsb.htm
This code that hooks into the windows api to save and re-adjust the scroll bar does work after tweaking my code a bit.

So my form (soon to be a subform) is working exactly how I was hoping to get it to, and also how the client was asking for.

Good luck to all future endevours!

[james milam]

The trick is to get the .seltop of the topmost record on the screen and the .seltop of the current record that you're on. Then requery the form. Then go to the last record. Then go to the topmost record on the form and only then go to the record that was the current record. Something like this:

Dim lngSelTopCurrentRecord As Long
Dim lngRowsFromTop As Long
Dim lngSelTopUppermostRecord As Long
 
lngSelTopCurrentRecord = Form_frmAwayToInHouseAndCreditMaint.SelTop
Form_frmAwayToInHouseAndCreditMaint.Painting = False
 
'240 twips is the distance of the top left corner of the topmost record
'from the top of the form if the form is in datasheet view
Do While Form_frmAwayToInHouseAndCreditMaint.CurrentSectionTop <> 240
    lngRowsFromTop = Form_frmAwayToInHouseAndCreditMaint.CurrentSectionTop / Form_frmAwayToInHouseAndCreditMaint.RowHeight
    With Form_frmAwayToInHouseAndCreditMaint.Recordset
        .Move -1 * (lngRowsFromTop - 1)
    End With
Loop
 
lngSelTopUppermostRecord = Form_frmAwayToInHouseAndCreditMaint.SelTop
 
Form_frmAwayToInHouseAndCreditMaint.Requery
With Form_frmAwayToInHouseAndCreditMaint.Recordset
    .MoveLast
End With
Form_frmAwayToInHouseAndCreditMaint.SelTop = lngSelTopUppermostRecord
Form_frmAwayToInHouseAndCreditMaint.SelTop = lngSelTopCurrentRecord
Form_frmAwayToInHouseAndCreditMaint.Painting = True

[Dave Stuart]

This one worked for me. My Form is a dataview Form. Thanks to James for a great example! My RowHeight is set at 300 so I just used that since the default RowHeight is set to -1, which means the "Default" RowHeight. It appears that once you set the RowHeight, it's a done deal. So setting the RowHeight back to the default is just Me.RowHeight = -1.

Private Sub RepositionToUpdatedRow()
 
    Dim lngSelTopCurrentRecord As Long
    Dim lngRowsFromTop As Long
    Dim lngSelTopUppermostRecord As Long
 
    lngSelTopCurrentRecord = Me.SelTop
    Me.Painting = False
 
    lngRowsFromTop = Me.CurrentSectionTop \ 300
    Me.Recordset.Move -1 * (lngRowsFromTop - 1)
 
    lngSelTopUppermostRecord = Me.SelTop
 
    Me.Requery
    Me.Recordset.MoveLast
 
    Me.SelTop = lngSelTopUppermostRecord
    Me.SelTop = lngSelTopCurrentRecord
    Me.Painting = True
 
End Sub
 

Usage is like this:
Private Sub DateIssued_AfterUpdate()
UpdateSomething!
RepositionToUpdatedRow
Me.DateIssued.SetFocus
End Sub

Thanks
Dave

[siliconxu]

I have the same issue and came across this post when I was googling. Then I found a solution while I read about usging the SelTop here. Instead of using the ways described above, I think I can simply make the SelTop low enough and when I do a FindFirst to return to the record before I requery, I can make the current record to the top after the requery. The code looks like:

lngCurrentID = txtID
lngSelTop = Me.SelTop
'open the form in dialog mode
Me.Requery
Me.SelTop = lngSelTop + 100 '(100 is a number that I think large enough to move the current record to the bottom)
Me.Recordset.FindFirst "[ID]=" & lngCurrentID
 

I tested this and it workds for me.