Form / subform focus problem

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I have a form 'Purchase Header' and a subform 'Purchase
Detail'. The subform has a text box 'units ordered' with
an on entry and on exit macros attached. If the user
leaves the focus in the 'units ordered' text box but uses
the navigation buttons in the 'Purchase Header' form,the
on exit macro does not run. Is there a way to disable
form record changes when the focus on still in the subform?

Also, this form opens slowely. I know that I can set the
form to only open a blank record but is there a way to
open the record in edit mode but only load a single record
at a time for quicker loading?

Thanks
 
Josh-

You don't say what it is you're trying to do in the Enter and Exit events.
Are you trying to do some validation before the row is saved? If that's the
case, then you should be using the form's BeforeUpdate event - and NOT using
macros. If the user tries to move to another record on the outer form,
Access will try to save the dirty row in the subform - and fire the
BeforeUpdate event to give you a chance to look at it. You can set Cancel =
True if you don't want the record saved.

As for a slow-loading form, there could be lots of causes. Are you using
combo boxes with thousands of rows in the Row Source? Do you have the Link
Master and Link Child properties of the subform control set properly? What
is the Record Source of the outer form (the SQL)? The subform? Do these
queries open slowly when you open them from the Database Window?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
First off thanks for the help John,

The Enter and Exit events compute a running total for the
total on order quantity of a part. We could order 50lbs
of a paint one day and 150lbs the next. The on enter will
subtract the current PO's on order from the total on
order. The Exit event will add the PO on order to the
total.

I do have two combo boxes, one in the form and one in the
subform. Both have over a thousand options. Is a list
box faster or what other option is there? The record
source for the main form is a table. The Link master and
Child properties are set to the PO# which is primary key
in tbl_purchase_order_header and is also a field in
tbl_purchase_order_detail. There is a relationship set in
the Tools->Relationships.

Thanks again,

Josh
 
Josh-

I'm having a hard time visualizing what it is you're doing in Enter and
Exit. Why recalculate if nothing has changed? Using either the form or
control AfterUpdate event would seem more appropriate. And where are you
displaying this total? How are you calculating the total? You should not
be storing a calculated value in the database anyway.

A common way to show a total for details in a subform is to design the form
inside the subform control in Continuous Form view. Create a form footer
and put a textbox there that calculates the total using the Sum function.
The Control Source will look something like: =Sum([OrderQty])

No, a list box won't be any faster. Although using a "lookup" combo box
with thousands of rows isn't a good idea, Access does try to optimize this
when you open a form that contains one. It doesn't try to resolve the
entire Row Source until you drop down the list for the first time. There
are various ways to limit the list by providing a filter on a category or
similar, but I suspect it's not the combo boxes that are causing the
performance problem.

You say the Record Source for the outer form is a table. What about the
Subform? You also say there's a relationship defined on PO# - did you also
turn on Referential Integrity in the relationship? When you do that, Access
builds an index on the foreign key (PO#) in the "many" table to make joining
and filtering faster. How many rows are there in each table?

Finally, how many macros do you have linked into either form? Does the form
open more quickly without the macros? If any of your macros run slowly,
that could be the problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
The subform is a query made up of the table Purchase Order
Detail and the Products table. The Products table holds
our inventory data including how much is on order and on
hand for each item. The Form's Enter event will subract
what is in the subforms On order from the Product
table 'total on order'. Then the Exit event will add the
subforms On order to the Product tables 'total on order'.
I did this so that when a PO is changed the 'total on
order' will be correct. This is computing the numbers
correctly but if the focus is left in the subforms 'on
order' and the form's record is change the Enter event
does not run on the next form.

I did not turn on the referential integrity but I will do
so now. The Purchase header table currently has 1500
records and the detail table has 6000.
-----Original Message-----
Josh-

I'm having a hard time visualizing what it is you're doing in Enter and
Exit. Why recalculate if nothing has changed? Using either the form or
control AfterUpdate event would seem more appropriate. And where are you
displaying this total? How are you calculating the total? You should not
be storing a calculated value in the database anyway.

A common way to show a total for details in a subform is to design the form
inside the subform control in Continuous Form view. Create a form footer
and put a textbox there that calculates the total using the Sum function.
The Control Source will look something like: =Sum ([OrderQty])

No, a list box won't be any faster. Although using a "lookup" combo box
with thousands of rows isn't a good idea, Access does try to optimize this
when you open a form that contains one. It doesn't try to resolve the
entire Row Source until you drop down the list for the first time. There
are various ways to limit the list by providing a filter on a category or
similar, but I suspect it's not the combo boxes that are causing the
performance problem.

You say the Record Source for the outer form is a table. What about the
Subform? You also say there's a relationship defined on PO# - did you also
turn on Referential Integrity in the relationship? When you do that, Access
builds an index on the foreign key (PO#) in the "many" table to make joining
and filtering faster. How many rows are there in each table?

Finally, how many macros do you have linked into either form? Does the form
open more quickly without the macros? If any of your macros run slowly,
that could be the problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
First off thanks for the help John,

The Enter and Exit events compute a running total for the
total on order quantity of a part. We could order 50lbs
of a paint one day and 150lbs the next. The on enter will
subtract the current PO's on order from the total on
order. The Exit event will add the PO on order to the
total.

I do have two combo boxes, one in the form and one in the
subform. Both have over a thousand options. Is a list
box faster or what other option is there? The record
source for the main form is a table. The Link master and
Child properties are set to the PO# which is primary key
in tbl_purchase_order_header and is also a field in
tbl_purchase_order_detail. There is a relationship set in
the Tools->Relationships.

Thanks again,

Josh
Enter
and Exit events. Do
you have the Link Database
Window?
Click to expand...


.
Click to expand...
 
You should be using the control's BeforeUpdate event. You can reference the
..OldValue property to find out what the value was before the user changed
it. To do what you want, subtract .OldValue and add the new value. This
event fires regardless of what the user chooses to do in the outer form -
whereas Enter/Exit do not as you have discovered.

By the way, keeping calculated values like this between two tables can be
very tricky. You should write a "verify" routine to periodically check and
correct the amonts on order in purchase orders with the amount reflected in
your Products table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Josh said:
The subform is a query made up of the table Purchase Order
Detail and the Products table. The Products table holds
our inventory data including how much is on order and on
hand for each item. The Form's Enter event will subract
what is in the subforms On order from the Product
table 'total on order'. Then the Exit event will add the
subforms On order to the Product tables 'total on order'.
I did this so that when a PO is changed the 'total on
order' will be correct. This is computing the numbers
correctly but if the focus is left in the subforms 'on
order' and the form's record is change the Enter event
does not run on the next form.

I did not turn on the referential integrity but I will do
so now. The Purchase header table currently has 1500
records and the detail table has 6000.
-----Original Message-----
Josh-

I'm having a hard time visualizing what it is you're doing in Enter and
Exit. Why recalculate if nothing has changed? Using either the form or
control AfterUpdate event would seem more appropriate. And where are you
displaying this total? How are you calculating the total? You should not
be storing a calculated value in the database anyway.

A common way to show a total for details in a subform is to design the form
inside the subform control in Continuous Form view. Create a form footer
and put a textbox there that calculates the total using the Sum function.
The Control Source will look something like: =Sum ([OrderQty])

No, a list box won't be any faster. Although using a "lookup" combo box
with thousands of rows isn't a good idea, Access does try to optimize this
when you open a form that contains one. It doesn't try to resolve the
entire Row Source until you drop down the list for the first time. There
are various ways to limit the list by providing a filter on a category or
similar, but I suspect it's not the combo boxes that are causing the
performance problem.

You say the Record Source for the outer form is a table. What about the
Subform? You also say there's a relationship defined on PO# - did you also
turn on Referential Integrity in the relationship? When you do that, Access
builds an index on the foreign key (PO#) in the "many" table to make joining
and filtering faster. How many rows are there in each table?

Finally, how many macros do you have linked into either form? Does the form
open more quickly without the macros? If any of your macros run slowly,
that could be the problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
First off thanks for the help John,

The Enter and Exit events compute a running total for the
total on order quantity of a part. We could order 50lbs
of a paint one day and 150lbs the next. The on enter will
subtract the current PO's on order from the total on
order. The Exit event will add the PO on order to the
total.

I do have two combo boxes, one in the form and one in the
subform. Both have over a thousand options. Is a list
box faster or what other option is there? The record
source for the main form is a table. The Link master and
Child properties are set to the PO# which is primary key
in tbl_purchase_order_header and is also a field in
tbl_purchase_order_detail. There is a relationship set in
the Tools->Relationships.

Thanks again,

Josh

-----Original Message-----
Josh-

You don't say what it is you're trying to do in the Enter
and Exit events.
Are you trying to do some validation before the row is
saved? If that's the
case, then you should be using the form's BeforeUpdate
event - and NOT using
macros. If the user tries to move to another record on
the outer form,
Access will try to save the dirty row in the subform -
and fire the
BeforeUpdate event to give you a chance to look at it.
You can set Cancel =
True if you don't want the record saved.

As for a slow-loading form, there could be lots of
causes. Are you using
combo boxes with thousands of rows in the Row Source? Do
you have the Link
Master and Link Child properties of the subform control
set properly? What
is the Record Source of the outer form (the SQL)? The
subform? Do these
queries open slowly when you open them from the Database
Window?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I have a form 'Purchase Header' and a subform 'Purchase
Detail'. The subform has a text box 'units ordered' with
an on entry and on exit macros attached. If the user
leaves the focus in the 'units ordered' text box but
uses
the navigation buttons in the 'Purchase Header' form,the
on exit macro does not run. Is there a way to disable
form record changes when the focus on still in the
subform?

Also, this form opens slowely. I know that I can set
the
form to only open a blank record but is there a way to
open the record in edit mode but only load a single
record
at a time for quicker loading?

Thanks


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top