Re: Form / subform focus problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Viescas (JohnV_at_nomail.please)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 08:46:34 -0600

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)
"Josh" <anonymous@discussions.microsoft.com> wrote in message
news:12a101c3fba8$70a2a290$a501280a@phx.gbl...
>
> 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)
> >"Josh" <joshua.kampbell@valmont.com> wrote in message
> >news:0cc601c3fb1e$b2786090$a001280a@phx.gbl...
> >>
> >> 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
> >
> >
> >.
> >

Relevant Pages

  • Re: Synchronize 2 combo boxes
    ... I am sorry for not reading properly, Yes my table is called Equipment ... correct subform control name. ... but it opens adobe along with a web page that crashes the internet. ...
    (microsoft.public.access.forms)
  • Re: Event order when clicking away from a field
    ... I have found a rather neat way of making the control ... being held until the exit event is completed, ... Optimistic locking would be great if everybody had a good server, ... the active control in the subform in order to make another control ...
    (microsoft.public.access.forms)
  • Re: Form / subform focus problem
    ... You don't say what it is you're trying to do in the Enter and Exit events. ... Master and Link Child properties of the subform control set properly? ... "Running Microsoft Access 2000" ... > Also, this form opens slowely. ...
    (microsoft.public.access.forms)
  • Re: Need Help Creating Event
    ... >blank form and inserted a tab control with 6 pages. ... >inserted a subform using an existing form. ... >When I open the main form, it opens to first page and the first record of ... To get the ChartNumber and Patient automatically filled in, ...
    (microsoft.public.access.forms)
  • Re: Repost - Open Form
    ... The Tech combo box in the subform was unbound. ... made the control source Tech from the table backing form. ... in the table, but it still opens with second name selected, but first name's ... There is also a subform for list of jobs assigned to CboTech. ...
    (microsoft.public.access.forms)