Setting the Focus after a requery

[Lele]

I have a form "OrderEntry" with 2 subforms: "OrderLineItems" and "Yardage
Recalc"
I use the after update event to recalculate the "Yardage Recalc" subform
when certain values change on the "OrderLineItems" subform. It works ok,
except the set focus always returns the cursor to the first line of the
OrderLineItems sub, and not the line (record) where I changed the value, (the
OrderLineItems subform is in continuous record view.)

Here are the two lines I am using.

Forms![OrderEntry].Requery
Forms![OrderEntry]![OrderLineItems].SetFocus

Any help is appreciated.

 

[Dirk Goldgar]

I have a form "OrderEntry" with 2 subforms: "OrderLineItems" and
"Yardage Recalc"
I use the after update event to recalculate the "Yardage Recalc"
subform when certain values change on the "OrderLineItems" subform.
It works ok, except the set focus always returns the cursor to the
first line of the OrderLineItems sub, and not the line (record) where
I changed the value, (the OrderLineItems subform is in continuous
record view.)

Here are the two lines I am using.

Forms![OrderEntry].Requery
Forms![OrderEntry]![OrderLineItems].SetFocus

Any help is appreciated.

You need to save the primary key of the current record on the subform,
then do the requery, and then navigate the subform back to that record.
Here's an example:

'----- start of example code -----
Dim vKey As Variant

With Forms!OrderEntry!OrderLineItems.Form
vKey = !YourKeyField
.Parent.Requery
.Recordset.FindFirst "YourKeyField = " & vKey
End With
'----- end of example code -----

That assumes that the key field -- named "YourKeyField" in the
example -- is a numeric field. If it's text, the FindFirst criterion
needs to wrap quotes around the value of vKey.

Also, the above code only works in Access 2000 or later. Something very
similar can be done in Access 97 using the RecordsetClone and Bookmark
properties.

 

[Lele]

Hi. Thanks so much for the help. Unfortunately, I don't think I have
expressed myself clearly enough.

As you have suggested, my primary key appears on the form and both of the
subforms. I have called it the "orderNumber" field and it is formated as an
autonumber.
Again my form is called orders, and the two subforms are OrderLineItem and
YardageCalc

Lets say I am creating an order with 3 line items: a pillow, a curtain, and
a bedspread. After creating the order, my customer requests a change in the
length of the curtain and asks me to recalculate the required yardage. When
I use my 2 lines of code the YardageCalc subform is recalculated after I
update the value of the length field from the OrderLineItem subform. The
cursor moves to the YardageCalc subform while the recalc is performed and
then jumps back to the length field on the OrderLineItems subform from the
setFocus code. Unfortunately, the cursor goes to the FIRST line item (the
pillow) and not the SECOND line (the curtain) which is the one I made the
length adjustment to. I fear that this would be annoying at best to the user
and could easily be confusing and lead to errors at worst.

Is there a way I can return the cursor to the specific record (line) as well
as field, in the OrderLineItem Subform that I was working on before the
requery of the 2nd subform was calculated?

Thanks again for your thoughts and suggestions.
--
Lele

I have a form "OrderEntry" with 2 subforms: "OrderLineItems" and
"Yardage Recalc"
I use the after update event to recalculate the "Yardage Recalc"
subform when certain values change on the "OrderLineItems" subform.
It works ok, except the set focus always returns the cursor to the
first line of the OrderLineItems sub, and not the line (record) where
I changed the value, (the OrderLineItems subform is in continuous
record view.)

Here are the two lines I am using.

Forms![OrderEntry].Requery
Forms![OrderEntry]![OrderLineItems].SetFocus

Any help is appreciated.

You need to save the primary key of the current record on the subform,
then do the requery, and then navigate the subform back to that record.
Here's an example:

'----- start of example code -----
Dim vKey As Variant

With Forms!OrderEntry!OrderLineItems.Form
vKey = !YourKeyField
.Parent.Requery
.Recordset.FindFirst "YourKeyField = " & vKey
End With
'----- end of example code -----

That assumes that the key field -- named "YourKeyField" in the
example -- is a numeric field. If it's text, the FindFirst criterion
needs to wrap quotes around the value of vKey.

Also, the above code only works in Access 2000 or later. Something very
similar can be done in Access 97 using the RecordsetClone and Bookmark
properties.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi. Thanks so much for the help. Unfortunately, I don't think I have
expressed myself clearly enough.

No, your amplification matches my prior understanding. I don't think
you understood my response, though.

As you have suggested, my primary key appears on the form and both of
the subforms. I have called it the "orderNumber" field and it is
formated as an autonumber.

[orderNumber] would be the primary key of the Orders table, but *not* of
related tables, such as the table holding order line items. That table
must have its own primary key, though it will naturally contain the
[orderNumber] of the parent order record as a "foreign key". What is
the primary key of the OrderLineItems table? The instructions I posted
require that you use that key for relocating the original record on the
OrderLineItem subform.

 

[Lele]

I have now added a primary field called LineNumber to the OrderLineItems
Subform. I made it an autocounter because I don't have any unique fields on
this subform. Here is how the code looks now.

Private Sub FLength_AfterUpdate()
Dim vKey As Variant

With Forms!OrderEntry!OrderLineItems.Form
vKey = !LineNumber
..Parent.Requery
..Recordset.FindFirst "LineNumber = " & vKey
End With

End Sub

Here's what happens. When I enter a new finished length for a line on the
OrderLineItems subform, the cursor jumps to the YardageCalc subform, the
YardageCalc subform does it recalculations, and the cursor settles in the
first line and first field of the YardageCalc form and does not return to the
OrderLineItems subform at all not to mention the appropriate line and field
of the OrderLineItems subform. What I am doing wrong? Thanks again!
--
Lele

Hi. Thanks so much for the help. Unfortunately, I don't think I have
expressed myself clearly enough.

No, your amplification matches my prior understanding. I don't think
you understood my response, though.

As you have suggested, my primary key appears on the form and both of
the subforms. I have called it the "orderNumber" field and it is
formated as an autonumber.

[orderNumber] would be the primary key of the Orders table, but *not* of
related tables, such as the table holding order line items. That table
must have its own primary key, though it will naturally contain the
[orderNumber] of the parent order record as a "foreign key". What is
the primary key of the OrderLineItems table? The instructions I posted
require that you use that key for relocating the original record on the
OrderLineItem subform.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I have now added a primary field called LineNumber to the
OrderLineItems Subform. I made it an autocounter because I don't
have any unique fields on this subform. Here is how the code looks
now.

Private Sub FLength_AfterUpdate()
Dim vKey As Variant

With Forms!OrderEntry!OrderLineItems.Form
vKey = !LineNumber
.Parent.Requery
.Recordset.FindFirst "LineNumber = " & vKey
End With

End Sub

Here's what happens. When I enter a new finished length for a line
on the OrderLineItems subform, the cursor jumps to the YardageCalc
subform, the YardageCalc subform does it recalculations, and the
cursor settles in the first line and first field of the YardageCalc
form and does not return to the OrderLineItems subform at all not to
mention the appropriate line and field of the OrderLineItems subform.
What I am doing wrong? Thanks again!

You probably just need to set the focus back to the appropriate subform.
I don't know why it's jumping to the YardageCalc subform; is that
something your other code is doing? If so, it may or may not be
necessary, but you can have your code set the focus back there, by
adding the line (which you originally had)

Forms![OrderEntry]![OrderLineItems].SetFocus

before the line

With Forms!OrderEntry!OrderLineItems.Form

If that doesn't work, you can try putting the line after the

End With

statement instead. I don't have a really good mental picture of what's
going on with this form and its subform, and why the focus is jumping
around, so don't give up if that doesn't work. Just let me know how it
goes.

 

[Lele]

Hurrah!! Yippee!! I added the setfocus line after the with statement and it
worked perfectly. Thanks for sticking with me.
--
Lele

I have now added a primary field called LineNumber to the
OrderLineItems Subform. I made it an autocounter because I don't
have any unique fields on this subform. Here is how the code looks
now.

Private Sub FLength_AfterUpdate()
Dim vKey As Variant

With Forms!OrderEntry!OrderLineItems.Form
vKey = !LineNumber
.Parent.Requery
.Recordset.FindFirst "LineNumber = " & vKey
End With

End Sub

Here's what happens. When I enter a new finished length for a line
on the OrderLineItems subform, the cursor jumps to the YardageCalc
subform, the YardageCalc subform does it recalculations, and the
cursor settles in the first line and first field of the YardageCalc
form and does not return to the OrderLineItems subform at all not to
mention the appropriate line and field of the OrderLineItems subform.
What I am doing wrong? Thanks again!

You probably just need to set the focus back to the appropriate subform.
I don't know why it's jumping to the YardageCalc subform; is that
something your other code is doing? If so, it may or may not be
necessary, but you can have your code set the focus back there, by
adding the line (which you originally had)

Forms![OrderEntry]![OrderLineItems].SetFocus

before the line

With Forms!OrderEntry!OrderLineItems.Form

If that doesn't work, you can try putting the line after the

End With

statement instead. I don't have a really good mental picture of what's
going on with this form and its subform, and why the focus is jumping
around, so don't give up if that doesn't work. Just let me know how it
goes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hurrah!! Yippee!! I added the setfocus line after the with statement
and it worked perfectly. Thanks for sticking with me.

Excellent! You're most welcome.