VBA to Requery Continous Form and Maintain Bookmark (1 Viewer)

[sross81]

I have this code:

Public Sub cmdRequery_Click()
Dim vFlag As String
vFlag = Me![EncounterNbr]
Me.Requery

With Me.Recordset
.FindFirst "[EncounterNbr] = '" & vFlag & "'"
Me.Bookmark = .Bookmark
End With


The user starts on a continous form and opens a record, makes some changes, and then when that form closes it triggers this public sub.


The code does what I want it to do in that it returns to the last encounter number that was selected (now on the continous form again) and it requeries and shows anything that was changed about the record on the form that was closed before requery.......

but it always resorts and moves it and I want it to stay in the same spot unless the user sorts. Is there any way to make that happen?

Thank you,
Sherri

 

[JHB]

Do you need to requery it, and if yes why?
You can't control how far down a record is showed in a continues form's grid, (not even using VBA-code).

 

[Galaxiom]

You must use an Order By clause in the RecordSource query or the Order By property of the form.

Otherwise the records just appear wherever Access feels like placing them. Tables have no intrinsic order.

 

[Galaxiom]

BTW There is no need to requery to show changed records if the form uses a Dynaset recordset. A Refresh will do.

Requery is only required to show new records. (And changed records if you have used a Snapshot recordset.)

 

[sross81]

The reason I chose to requery is because the change that is bad on the form that is closed and triggers the requery doesn't appear on the continuous form unless I requery. I did try me.refresh because I read that this would update the changed record without reloading the form, but for some reason nothing happens at all when I do that. It gets to that point in the code and there are no errors, but it never updates the record with the changes in the continuous form unless I close that form and go back in.

The form is Dynaset and if I remove the Order By on the form it does fix the problem, but if the user sorts the form and it saves that order by it will be locked in again.

Maybe I need to figure out why a me.refresh doesn't work instead??

 

[sross81]

I tried to just requery a specific field on the form instead of the entire form (tried refresh too). Refresh does nothing. Requery gives me errors saying that object doesn't support this method or property on the close event of the form that triggers the requery.

 

[pbaldy]

You can't control how far down a record is showed in a continues form's grid, (not even using VBA-code).

Actually you can (if I understand correctly). I had users asking that if they were working on a record that was halfway down the screen, they wanted it to return to the same position. I found SetGetScrollBars from Lebans:

http://www.lebans.com/SelectRow.htm

 

[sross81]

I think I got the return piece working now......it just doesn't refresh the changed records unless I requery the entire form and when I do that it will lose my bookmark at that point. I am trying to just requery individual fields that they update but nothing happens when I do that until I close and reopen the form entirely.

 

[sross81]

For now I just moved this to the forms activate event:


Dim vFlag As String
vFlag = Me![EncounterNbr]
Me.Requery

With Me.Recordset
.FindFirst "[EncounterNbr] = '" & vFlag & "'"
Me.Bookmark = .Bookmark
End With


It will do what I want....keep my bookmark and get the updates to the changed fields....the only time there is a problem is when they change a field that ends up resorting the form. I removed all sort criteria from the query and the form which helps, but if they manually sort the continuous form which they like to do then it will resort when a requery changes something that should move it to a different group area. For now they can live with that.

 

[JHB]

Actually you can (if I understand correctly). I had users asking that if they were working on a record that was halfway down the screen, they wanted it to return to the same position. I found SetGetScrollBars from Lebans:

http://www.lebans.com/SelectRow.htm

This code could have been useful for me for some years ago - so I need to eat my own claim, yes it is possible.