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...t he 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