Can't figger this'un out.

[Jerry]

Hi,
I have a problem with a form I'm using for entering customer orders.
The main form ([Customer Orders]) contains customer info and 3 subforms;

1. [Orders] subform contains a list of orders for the current customer in
[Customer Orders].

2. [Order Details] subform contains details of the current order in [Orders]
subform.

3. [Availabitity] subform contains the results of a crosstab query which
shows remaining inventory available for orders.

The problem I'm having is with the [Order Details] subform. When I click on
a field to add or edit data in any of the records except the first record in
the subform, as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms. It
seems that the form is being refreshed as soon as I move the focus to a new
record, but I can't see why. I do have code,which I'll list below, in the
AfterUpdate event of each textbox and combo in the [Order Details] subform
to immediately update the display in the [Availability] subform to reflect
changes in the [Order Details] subform. When I first set this up, I tried
doing this in the Current event of the subform, and had problems with
endless loops so moved it to the AfterUpdate event which seems to work fine
in the first record in the subform but not for any subsequent records. I've
tried commenting the code out, even deleting it, saving and reloading the
form, but the behavior persists. I've even tried deleting the subform from
the main form and recreating it. I can't find any other code associated
with any of the forms. The [Order Details] form works OK when run by itself
(not as a subform of [Customer Orders]). What am I missing?

Option Compare Database
Private Sub Color_AfterUpdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub
Private Sub quantity_afterupdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub
Private Sub size_afterupdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub

 

[Ken Snell]

When you requery a form's recordsource, and that form is in continuous forms
view, the form will move back to the first record in the form's recordsource
after the requery. The trick is to capture the value of the primary key of
the current record in a variable, and then move the recordset back to that
record after the requery. For example, something like this (if I'm
understanding your form's setup):


Private Sub Color_AfterUpdate()
Dim varBook As Variant
varBook = [Form_Customer Orders]![Availability].[NameOfPrimaryKey].Value
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
[Form_Customer Orders]![Availability].Form.RecordsetClone.FindFirst _
"[NameOfPrimaryKey]=" & varBook
[Form_Customer Orders]![Availability].Form.Bookmark = _
[Form_Customer Orders]![Availability].Form.RecordsetClone.Bookmark
End Sub




--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a problem with a form I'm using for entering customer orders.
The main form ([Customer Orders]) contains customer info and 3 subforms;

1. [Orders] subform contains a list of orders for the current customer in
[Customer Orders].

2. [Order Details] subform contains details of the current order in [Orders]
subform.

3. [Availabitity] subform contains the results of a crosstab query which
shows remaining inventory available for orders.

The problem I'm having is with the [Order Details] subform. When I click on
a field to add or edit data in any of the records except the first record in
the subform, as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms. It
seems that the form is being refreshed as soon as I move the focus to a new
record, but I can't see why. I do have code,which I'll list below, in the
AfterUpdate event of each textbox and combo in the [Order Details] subform
to immediately update the display in the [Availability] subform to reflect
changes in the [Order Details] subform. When I first set this up, I tried
doing this in the Current event of the subform, and had problems with
endless loops so moved it to the AfterUpdate event which seems to work fine
in the first record in the subform but not for any subsequent records. I've
tried commenting the code out, even deleting it, saving and reloading the
form, but the behavior persists. I've even tried deleting the subform from
the main form and recreating it. I can't find any other code associated
with any of the forms. The [Order Details] form works OK when run by itself
(not as a subform of [Customer Orders]). What am I missing?

Option Compare Database
Private Sub Color_AfterUpdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub
Private Sub quantity_afterupdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub
Private Sub size_afterupdate()
[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh
End Sub

 

[John Vinson]

as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.

 

[Jerry]

Thank you for the information on how to handle the requery, however the
thing that really is bothering me is that I don't seem to be able to delete
the requery. I understand that the requery SHOULD do what it's doing, but
it's the [Availability] subform that's being requerried, not the [Order
Details] subform where the problem (and the bouncing cursor) is. Also the
code is in the AfterUpdate event handler, which should not get hit just by
moving the focus to a new record, unless the record losing focus has been
altered. (From the help file; "BeforeUpdate and AfterUpdate macros and
event procedures run only if you change the data in a control.") I assume
the problem here is that the line that says "[Form_Customer Orders].Refresh"
kicks the main form (and subsequently all the subforms) to refresh. The
reason I did this is that requerying the [Availability] subform does not
update the display in the subform - which is what I was really after, and
the [Availability] subform has no Refresh method of it's own to call. What
I really do not understand though is that when I completely remove this code
(either the Refresh call or ALL VB code) from the project, the behavior
persists.

as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.

 

[Ken Snell]

Where is the code running? In the subform? If yes, then just use Me.Requery
for the requery. You should not need to use Refresh.
--

Ken Snell
<MS ACCESS MVP>

Thank you for the information on how to handle the requery, however the
thing that really is bothering me is that I don't seem to be able to delete
the requery. I understand that the requery SHOULD do what it's doing, but
it's the [Availability] subform that's being requerried, not the [Order
Details] subform where the problem (and the bouncing cursor) is. Also the
code is in the AfterUpdate event handler, which should not get hit just by
moving the focus to a new record, unless the record losing focus has been
altered. (From the help file; "BeforeUpdate and AfterUpdate macros and
event procedures run only if you change the data in a control.") I assume
the problem here is that the line that says "[Form_Customer Orders].Refresh"
kicks the main form (and subsequently all the subforms) to refresh. The
reason I did this is that requerying the [Availability] subform does not
update the display in the subform - which is what I was really after, and
the [Availability] subform has no Refresh method of it's own to call. What
I really do not understand though is that when I completely remove this code
(either the Refresh call or ALL VB code) from the project, the behavior
persists.

as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.

 

[Jerry]

The code is in the AfterUpdate event of the controls (Textbox and combos) of
the [Order Details] subform, which is not the subform being requerried. I'm
using the requerried [Availability] subform as a guide to what's available
in the [Order Details] subform, so need to trigger the update from there.
Just using the Requery event of the subform seems to update the underlying
data, but it isn't displayed unitl something causes the form to be refreshed
or until you hit it the second time.

Where is the code running? In the subform? If yes, then just use Me.Requery
for the requery. You should not need to use Refresh.
--

Ken Snell
<MS ACCESS MVP>

Thank you for the information on how to handle the requery, however the
thing that really is bothering me is that I don't seem to be able to delete
the requery. I understand that the requery SHOULD do what it's doing, but
it's the [Availability] subform that's being requerried, not the [Order
Details] subform where the problem (and the bouncing cursor) is. Also the
code is in the AfterUpdate event handler, which should not get hit just by
moving the focus to a new record, unless the record losing focus has been
altered. (From the help file; "BeforeUpdate and AfterUpdate macros and
event procedures run only if you change the data in a control.") I assume
the problem here is that the line that says "[Form_Customer Orders].Refresh"
kicks the main form (and subsequently all the subforms) to refresh. The
reason I did this is that requerying the [Availability] subform does not
update the display in the subform - which is what I was really after, and
the [Availability] subform has no Refresh method of it's own to call. What
I really do not understand though is that when I completely remove this code
(either the Refresh call or ALL VB code) from the project, the behavior
persists.

as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.

 

[Ken Snell]

OK - is Availability the name of the main form's subform control (the
control that holds the subform, which may or may not be the same name as the
source object of that control)? If not, change to the actual name of the
subform control.

Then, try this slight difference in syntax:

Forms!FormName!Subform.Form.Requery


--

Ken Snell
<MS ACCESS MVP>

The code is in the AfterUpdate event of the controls (Textbox and combos) of
the [Order Details] subform, which is not the subform being requerried. I'm
using the requerried [Availability] subform as a guide to what's available
in the [Order Details] subform, so need to trigger the update from there.
Just using the Requery event of the subform seems to update the underlying
data, but it isn't displayed unitl something causes the form to be refreshed
or until you hit it the second time.

Where is the code running? In the subform? If yes, then just use Me.Requery
for the requery. You should not need to use Refresh.
--

Ken Snell
<MS ACCESS MVP>

Thank you for the information on how to handle the requery, however the
thing that really is bothering me is that I don't seem to be able to delete
the requery. I understand that the requery SHOULD do what it's doing, but
it's the [Availability] subform that's being requerried, not the [Order
Details] subform where the problem (and the bouncing cursor) is. Also the
code is in the AfterUpdate event handler, which should not get hit

just

by

moving the focus to a new record, unless the record losing focus has been
altered. (From the help file; "BeforeUpdate and AfterUpdate macros and
event procedures run only if you change the data in a control.") I assume
the problem here is that the line that says "[Form_Customer Orders].Refresh"
kicks the main form (and subsequently all the subforms) to refresh. The
reason I did this is that requerying the [Availability] subform does not
update the display in the subform - which is what I was really after, and
the [Availability] subform has no Refresh method of it's own to call. What
I really do not understand though is that when I completely remove

this
code

(either the Refresh call or ALL VB code) from the project, the behavior
persists.


as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.

 

[Jerry]

Yes, Availability is the name of the subform control with it's Source Object
property set to 'Query.Inventory Available', which is a crosstab query.
I've also tried making a separate form with it's source set to the same
query and then using this form as the source for the subform control, with
the same results. I've tried the alternate syntax you suggested on both
arrangements of the form/subform and both give the same results - the
requery alone does not update the display in the Availability subform
without the line that says "[Form_Customer Orders].Refresh".
HOWEVER!!!!
After using your alternate systax in the AfterUpdate event handler of the
combo box that I use to select Size of the item in the Order Details
subform, the problem of not being able to move the focus to any record in
the list without it immediately returning to the first record has apparently
been solved - even after I replace it with the same line of code I had
originally!!!!! This with the form in the mainform's subform control or
just with the subform control pointed to the query. Now why could I not
remove that problem by just removing the code altogether?

SO....when the value in any of the controls in the Order Details subform are
changed, the AfterUpdate event runs the 2 lines of code that say

[Form_Customer Orders]![Availability].Requery
[Form_Customer Orders].Refresh

and the Availability subform immediately reflects whatever change was made
in the Order Details subform.

Life is gooood!

Thanks much,

Jerry

OK - is Availability the name of the main form's subform control (the
control that holds the subform, which may or may not be the same name as the
source object of that control)? If not, change to the actual name of the
subform control.

Then, try this slight difference in syntax:

Forms!FormName!Subform.Form.Requery


--

Ken Snell
<MS ACCESS MVP>

The code is in the AfterUpdate event of the controls (Textbox and

combos)

of
the [Order Details] subform, which is not the subform being requerried. I'm
using the requerried [Availability] subform as a guide to what's available
in the [Order Details] subform, so need to trigger the update from there.
Just using the Requery event of the subform seems to update the underlying
data, but it isn't displayed unitl something causes the form to be refreshed
or until you hit it the second time.

Where is the code running? In the subform? If yes, then just use Me.Requery
for the requery. You should not need to use Refresh.
--

Ken Snell
<MS ACCESS MVP>



Thank you for the information on how to handle the requery, however the
thing that really is bothering me is that I don't seem to be able to
delete
the requery. I understand that the requery SHOULD do what it's

doing,
but

it's the [Availability] subform that's being requerried, not the [Order
Details] subform where the problem (and the bouncing cursor) is.

Also
the

code is in the AfterUpdate event handler, which should not get hit

just

by
moving the focus to a new record, unless the record losing focus has been
altered. (From the help file; "BeforeUpdate and AfterUpdate macros and
event procedures run only if you change the data in a control.") I assume
the problem here is that the line that says "[Form_Customer
Orders].Refresh"
kicks the main form (and subsequently all the subforms) to refresh. The
reason I did this is that requerying the [Availability] subform does not
update the display in the subform - which is what I was really

after,
and

the [Availability] subform has no Refresh method of it's own to call.
What
I really do not understand though is that when I completely remove this
code
(either the Refresh call or ALL VB code) from the project, the behavior
persists.


as soon as I release the mouse button, the focus moves back to
the first record - in both the [Order Details] and [Orders] subforms.

That's exactly what a Requery does - it goes back to the tables,
reruns the query, and displays the result, focused on the first
record.

If you need to do the requery, you'll want to add code to record the
Primary Key of the current record in a variable, and then find that
record after the requery.