Returning a Scolling Screen to a Previously Modified Record

[Brian]

I have created a database with a scrolling form that lists all of the items
on a single Purchase Order. The number of items on a Purchase Order is
almost always is greater than what can be displayed on the screen without
scrolling. On this scrolling screen I have an unbound text box into which
the user types the number of items recieved, which may or may not equal the
number of items ordered (usually not). I am using the AfterUpdate event to
process the data entered into the text box. The scrolling form is then
updated and returns to the top of the page.

I am looking for some VBA code that I can stick in the AfterUpdate event
that would force the screen to return to the next item on the list. What
happens now is that the user being forced to scroll down the list until she
gets to the next item on the list to receive.

Any suggestions would be great.

Thanks to everyone who posts!

 

[Klatuu]

Is this a Form/Subform where the PO header is the main form and the PO lines
are the subform?
When you say it goes to the top of the page, do you mean the first record in
the the subform, or does it return to some place on the main form?

What all is going on in the After Update event? Is there anything there
that may cause it to loose it's position in the recordset?

What is the Cycle property set to?

Sorry about all the questions, but to get a solution, we need info. The
normal situation would be for the focus to move to the next control in the
record or if it is the last control in the record, to move to the next record.

So either some code or some property setting is causing this.

 

[Brian]

To answer yor questions in order:

No, this is a single form where the default view is set to "Continuous
Forms". The PO form is on another screen. This form is just a scrolling
list of all the items.

The "top of the page" meaning that when the scrolling form is requeried (so
the updated data appears), the cursor is back to the first record.

AfterUpdate: The data the user typed is saved to a secondary table (not the
same table as the PO Items) along with other information such as date, user,
etc. Also, the VBA code checks to see if there are any lower level
components that were required to make this part and if so, deletes the
appropriate quantities from inventory.

The Cycle property is set to All Records

Thanks for your help. Hopefully this will aid in solving my dilema.

 

[Klatuu]

This answers the question:
when the scrolling form is requeried

Whenever you requery a form, it returns to the first record in the
recordset. What you need to do in the after update is to save the primary
key of the current record, do the requery, then reposition your form to the
record you were on. You may or may not need the Application.Echo statements
depending on how large the recordset is and the speed of your computer, etc.

I will assume, for example purposes, your primary key is an autonumber field
and we will call it PoPkey

Dim lntKeyVal as Long
Dim rst As Recordset

lngKeyVal = Me.txtPoPKey 'Save the key value of the current record
Application.Echo = False ' Turns of the screen updating to avoid fliker
'This is where you do the requery now
Me.Requery ' Update the table - now we are back at the first record
Set rst = Me.RecordsetClone 'The next 3 liines puts it back where it was
rst.FindFirst "[PoPKey] = " & lngKeyVal
Me.BookMark = rst.BookMark
Set rst = Nothing 'Destroy the object reference
Application Echo True 'Turn the screen back on

 

[Brian]

Thank You!!!
This worked great!

 

[Klatuu]

Glad I could help.
Please do others a favor and rate the post as answered. That way others
with a similar question will find it when they do a search.
Thanks