Requery but stay in same record

[kevinhay]

What I have is a subform that is requeried whenever a new record i
added to the main form. And it's also requeried whenever the date i
updated in the subform. What happens though, as you can imagine, i
when the date is updated the selector automatically goes to the to
most record. What I'd like for it to do is to stay in the same recor
even after requerying.

Any suggestions

 

[Peter R. Fletcher]

Every time you need to requery, you will need first to save the
primary key (or other unique identifier) of the current record in the
subform, and then to use this to find the record again (in a Recordset
synchronised to the subform's Recordset) after the requery. As you may
have found, saving a Bookmark doesn't work because requerying makes
any saved Bookmark invalid. I can't think of any better or more direct
way of doing what you are trying to do.

 

[Mr.Smith]

Try

Function XX()

dim id as variant

id = Me.IDField
Me.FilterOn = false
Me.requery
Me.filter = "IDfield = " & id
Me.FilterOn = True

End Function

It's not the most elegant way but it works. BTW this is the format for
..mdb files. If you are using .adp's, see the ServerFilter prop in help
and modify accordingly.


CYah!

Every time you need to requery, you will need first to save the
primary key (or other unique identifier) of the current record in the
subform, and then to use this to find the record again (in a Recordset
synchronised to the subform's Recordset) after the requery. As you may
have found, saving a Bookmark doesn't work because requerying makes
any saved Bookmark invalid. I can't think of any better or more direct
way of doing what you are trying to do.

 

[Peter R. Fletcher]

That will position you correctly, but you will only see the one
record. I think the OP wanted to be able to stay on the same record
while retaining the "navigability" of the subform's Recordset.

Try

Function XX()

dim id as variant

id = Me.IDField
Me.FilterOn = false
Me.requery
Me.filter = "IDfield = " & id
Me.FilterOn = True

End Function

It's not the most elegant way but it works. BTW this is the format for
.mdb files. If you are using .adp's, see the ServerFilter prop in help
and modify accordingly.


CYah!

Every time you need to requery, you will need first to save the
primary key (or other unique identifier) of the current record in the
subform, and then to use this to find the record again (in a Recordset
synchronised to the subform's Recordset) after the requery. As you may
have found, saving a Bookmark doesn't work because requerying makes
any saved Bookmark invalid. I can't think of any better or more direct
way of doing what you are trying to do.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[kevinhay]

I'm not up to snuff on recordset programming. I tried using this cod
modified from VBA help:

Dim lngCurrent As Long
Dim strCurrent As String
Dim rst As Recordset

'Relates post-queried current record to pre-queried current record s
that
'user will be left in updated record despite requerying.
lngCurrent = Me.ID
strCurrent = Str(lngCurrent)
Form.Requery
Set rst = Me.RecordsetClone
rst.FindFirst = "ID=" & lngCurrent
Me.Bookmark = rst.Bookmark
rst.Close

And it gave me an error stating "Compile Error: Method or Data Membe
Not Found" and highlighting FindFirst.

Every time you need to requery, you will need first to save the
primary key (or other unique identifier) of the current record i
the
subform, and then to use this to find the record again (in
Recordset
synchronised to the subform's Recordset) after the requery. As yo
may
have found, saving a Bookmark doesn't work because requerying makes
any saved Bookmark invalid. I can't think of any better or mor
direct
way of doing what you are trying to do

 

[kevinhay]

bum

 

[Guest]

I managed to succeed in doing something like this... but I ended up having to think about a million and one possibilities to do with recordcounts etc.

Basically, I was filtering a recordset in a form which also had a sub-form in it. On filter I set me.tag=me.currentrecord
after filtering, to go back I used
Me.RecordsetClone.MoveLast
DoCmd.GoToRecord , , acGoTo, Me.Tag

It was a fair bit more complicated than I wrote here, and loads more stuff had to be considered, but I hope it might lead you in a better direction.

Basil

----- kevinhay wrote: -----


bump

 

[Peter R. Fletcher]

What version of Access are you using? If >= 2000 you must include an
explicit reference to the DAO library and explicitly Dim the Recordset
as a DAO.Recordset. You could well be getting this error from trying
to do a FindFirst on an ADODB.Recordset (the default in Access >=
2000), since ADO does not support this method.

Is the first "=" in the FindFrst line a typo - if not, it is an error!

you probably don't need the Bookmark, at all.

I'm not up to snuff on recordset programming. I tried using this code
modified from VBA help:

Dim lngCurrent As Long
Dim strCurrent As String
Dim rst As Recordset

'Relates post-queried current record to pre-queried current record so
that
'user will be left in updated record despite requerying.
lngCurrent = Me.ID
strCurrent = Str(lngCurrent)
Form.Requery
Set rst = Me.RecordsetClone
rst.FindFirst = "ID=" & lngCurrent
Me.Bookmark = rst.Bookmark
rst.Close

And it gave me an error stating "Compile Error: Method or Data Member
Not Found" and highlighting FindFirst.

Every time you need to requery, you will need first to save the
primary key (or other unique identifier) of the current record in
the
subform, and then to use this to find the record again (in a
Recordset
synchronised to the subform's Recordset) after the requery. As you
may
have found, saving a Bookmark doesn't work because requerying makes
any saved Bookmark invalid. I can't think of any better or more
direct
way of doing what you are trying to do.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher