Requery makes frmCustomers go back to record 1. 8

[scroce]

I have a form that shows customer info and their agent info.
If someone sees that the agent info is wrong, they can click a button which gives them a pop up screen to change the wrong information, or to add new information. They Click "OK" on the popup, and the underlying (opener) form needs to get updated to show the change/addition.

For that I'm using just plain ol'
frmCustomers.Requery

The problem is that after the underyling form is requeried, it always goes back to the first record in the underlying recordset. I want it to stay on the current record. I checked out the Bookmark property, but that doesn't work with Requery.

Can someone tell me how I get the form to stay on the current record?

thx.

SC

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[LarryDeLaruelle]

SC:

You are on the right track with bookmarks.

I have similar situations with my databases. What I do is capture the current record Primary Key before issuing the requery. I then use the Recordset/Bookmark to reset the display to the record displayed prior to the requery.

If you need it, I can provide the code that I use.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[scroce]

ah! - thanks Larry - that gives me a little more fortitude to press on... I'll try coding something on my own. I'll post back with success or failure!!!

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[zevw]

What I do is something similar!

Before updating I capture the recordid by setting a variable

curr_rec = me.currentrecord

after update

I requery

and use

DoCmd.GoToRecord , , acGoTo, curr_rec

 

[LarryDeLaruelle]

zevw:

I like that. Sweet and avoid messing with recordsets and bookmarks.

That's what I like about this site. There is always someone with a better mousetrap. This one is certainly worth a star.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[scroce]

OK, so i've got the ID of the record that I want to return to in a variable called intCustID, but then How do I plug that in to docmd.GoToRecord - i'm reading the help screen now even as I type this, but it's not immediately apparent.

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[GingerR]

follow zevw's instructions...

 

[scroce]

Yep - zevw that seems to work pretty well. I'm putting up a star on your post as well. thanks.

Ginger - as usual for me, I was trying to make something simple a lot more difficult than it had to be. I ought to read more carefully. Silly me.

One observation is that there is a noticable "blink" on the form while this command is processed - in other words, when I click the OK button I can actually see the underlying form go back to the first record for a split second, and then hop back to the original record.

I'm wondering if that's due to my processor speed, the size of my mdb file, or just something that's inherent to access.

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[zevw]

Scorce!

You posted the question you deserve the answer!

create an unbound hidden text box and store the recordid there
in the before update event procedure.
After updating you still have the value of your record id you want to return to.

 

[GingerR]

as for the blinking:
make first line of the code be

docmd.echo false

and the last line be

docmd.echo true

might not make much of a diff tho...

 

[ordendelfai]

Thank you, this really just helped me!!!

 

[scroce]

TGFTT - thank goodness for tek tips

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[janeatwork]

Hi zevw,

Really good idea.

Jane

 

[danvlas]

Sorry to disappoint you guys, but...I don't get it...

Bookmark property works just fine and I've been using it for ages:

varBkm = Me.RecordsetClone.Bookmark 'set the bookmark
Me.Requery 'requery the form
On Error Resume Next 'If record was deleted, skip the error
Me.Bookmark = varBkm 'Go to bookmark


That's it...






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BillPower]

Hi danvlas,

You're 100% right. Where have you been recently, missed your posts. Glad you're back.

Star for you.

Bill

 

[danvlas]

Thanks, Bill, nice to be remembered

I had a good 3 week vacation on the beach...hope everyone gets one




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Tranman]

Ditto to Daniel,
Bookmark has always worked fine for me. Also, AbsolutePosition

Dim lngCurPos as Long
Select Case intResponse
Case vbYes
lngCurPos = Me.Recordset.AbsolutePosition
strSQL = "Update LocalOnRoad set ON_ROAD_CODE = '" & strNewName & "' where ON_ROAD_CODE = '" & strCurName & "'"
DoCmd.RunSQL strSQL
Me.Requery
Me.Recordset.AbsolutePosition = lngCurPos
Case vbNo
Exit Sub
End Select

Tranman

 

[Poduska]

Close situation where I am not going to my "working record" after requery command. However I am using a form with a Subform which is based on a table (shows all the records in the table).

The I am using the "form in a form" so I can have the user select a record, click a button and some updating of records and fields occurs. When I requery to make the new values appear the active record moves to number 1. I have tried a lot of examples but mine seems a little unique due to the subform [child0]. I added DanVals code snipet and get the error


Runtime error 7951
You enetered an espression that has an invalid reference to the recordset clone property

I also added a Dim Clone as recordset and Set clone = Me.RecordsetClone as this is what my reference book hinted might be missing.

Here is the button code which i have shortened to remove the actual update procedure which works fine.

Is this because my subform is based upon a table and not a recordset or query?

Kind of new to VB and couldn't make it with out Tek-tips!



Private Sub Command2_Click()
Dim MyDB As Database, MyWS As Workspace
Dim Item_Num As String

Dim varBkm As String
Dim clone As Recordset

Set MyWS = DBEngine.Workspaces(0)
Set MyDB = MyWS.Databases(0)

Item_Num = Me![Child0].Form![Item]

‘
‘ Change a few records here and there
‘

Set clone = Me.RecordsetClone
varBkm = Me.RecordsetClone.Bookmark 'set the bookmark
Me.Requery 'requery the form
On Error Resume Next 'If record was deleted, skip the error
Me.Bookmark = varBkm 'Go to bookmark
End Sub

This would seem like a VERY common thing to do but I am stumped.

Thanks
David.

 

[Tranman]

David,
One thing is that you can't store a bookmark in a string, nor can you set a bookmark to any value other than the value of another bookmark (according to some hint I read somewhere).

I don't know if you can say something like
Dim bmk as Bookmark...I don't think you can, because bookmark is really a recordset property and not an object. Also, you could try storing the bookmark in a variant, but as I recall, that will not work either.

Probably the easiest (although not the most efficient) way is to make another recordset and set its bookmark to the one you want to store. You could immediately set it to nothing after you use it, so it would not be hogging resources.

Tranman

 

[wendyhnc]

You can store the bookmark as a Variant, this might help you out??