Subform requery returns to first record

  • Thread starter Thread starter gsnidow via AccessMonster.com
  • Start date Start date
G

gsnidow via AccessMonster.com

Greetings folks. I have a Main form and sub form, linked by pk of main form
table. The pk of the sub's record source is an identity field. I have
navigation buttons on the sub so users can scan through all the records. The
problem is that there is a command button on the sub that does a requery of
the sub. After the requery is complete the user is returned to the first
record in the sub's set of records. I would like the sub to remain on, or
return to the current record after the refresh. I put a text box, txtID on
the sub that is bound to the sub's ID field. Then I put an unbound text box,
txtIDHolder, right next to it. Just before the me.requery action of the sub,
I populate txtIDHolder with the value in txtID. So far this seems to work.
Let's say I was on sub record having ID = 5, where there are records 1
through 5. After I do the requery the sub settles on record 1, but my
txtIDHolder still says 5. My question is, how can I make the sub return to
record 5 after the requery?

Greg
 
Use a macro to GoToRecord and value from the unbound text box, txtIDHolder
using the OffSet argument.
 
gsnidow said:
Greetings folks. I have a Main form and sub form, linked by pk of main form
table. The pk of the sub's record source is an identity field. I have
navigation buttons on the sub so users can scan through all the records. The
problem is that there is a command button on the sub that does a requery of
the sub. After the requery is complete the user is returned to the first
record in the sub's set of records. I would like the sub to remain on, or
return to the current record after the refresh. I put a text box, txtID on
the sub that is bound to the sub's ID field. Then I put an unbound text box,
txtIDHolder, right next to it. Just before the me.requery action of the sub,
I populate txtIDHolder with the value in txtID. So far this seems to work.
Let's say I was on sub record having ID = 5, where there are records 1
through 5. After I do the requery the sub settles on record 1, but my
txtIDHolder still says 5. My question is, how can I make the sub return to
record 5 after the requery?
Click to expand...


Add code like:

Me.Recordset.FindFirst "ID=" & txtIDHolder
 
Thanks Karl. Macros are new to me, but I think I get the jist of it. The ID
is not going to be the same as the record number, so I am trying it with a
dlookup. like this:

Macro
Action
GoToControl = txtIDHolder
Action
FindRecord = DLookUp(["id"],"FORMS_frmSearchEWO_Roadblocks")

Then, before and after the requery I have:

Me.txtIDHolder = Me.txtID
Me.requery
DoCmd.RunMacro macro1

I am getting an error though, that just says the macro failed. I am not sure
what to do now. I read in an access book you should not use dlookup for more
than a couple thousand records. Is there another way?

Greg


KARL said:
Use a macro to GoToRecord and value from the unbound text box, txtIDHolder
using the OffSet argument.
Greetings folks. I have a Main form and sub form, linked by pk of main form
table. The pk of the sub's record source is an identity field. I have
Click to expand...
[quoted text clipped - 12 lines]
Greg
Click to expand...
Click to expand...
 
gsnidow via AccessMonster.com said:
Greetings folks. I have a Main form and sub form, linked by pk of main form
table. The pk of the sub's record source is an identity field. I have
navigation buttons on the sub so users can scan through all the records. The
problem is that there is a command button on the sub that does a requery of
the sub. After the requery is complete the user is returned to the first
record in the sub's set of records. I would like the sub to remain on, or
return to the current record after the refresh. I put a text box, txtID on
the sub that is bound to the sub's ID field. Then I put an unbound text box,
txtIDHolder, right next to it. Just before the me.requery action of the sub,
I populate txtIDHolder with the value in txtID. So far this seems to work.
Let's say I was on sub record having ID = 5, where there are records 1
through 5. After I do the requery the sub settles on record 1, but my
txtIDHolder still says 5. My question is, how can I make the sub return to
record 5 after the requery?

Greg

--
Message posted via AccessMonster.com


Why aren't you using the bookmark property? I'm sure it will do the job.
Click to expand...
 
Back
Top