Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Inconsistent Update Dynasets

    I have developed a form called Work Order that is based on 5 different tables and three queries. The end result is a large form with over 100 possible data entry controls. Basically it is an Invoice form used to track Customer information, parts sold (including 15 separate entry rows for part #, part item, cost etc. This form when originally developed was working great, until the customer asked if I could make the [part item] and [part cost] auto-fill based on the inserted [part number]. Using a table and query to maintain the 10,650 individual part numbers I was able to set up this method quite easily. But, in doing so I have exceeded the number of underlying tables to make the form a true dynaset. Leaving it in this format gives me the

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updating Inconsistent Update Dynasets

    If you have 15 separate entry rows for part number, etc., you need a subform instead for that part of the form. Any form based on 5 tables probably needs redesign. You would be better advised to have a parent form that would be based on something like Customer or Work Order, with subforms for each of the specific types of records in the other tables. Beyond about 3 tables in the data source query, you're building problems for yourself rather than solutions for your users.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Inconsistent Update Dynasets

    Orignally this form contained two subforms, but due to the nature of the form layout it was changed. Subforms "grow" as new rows are added. However for this Work Order, which is printed and sent to the customer as an Invoice, the customer wants to maintain the structure and feel of a "true" invoice look. Early on I imported over 2200 records from Excel that populate this form. There has been no "lack of performance" issues with the underlying queries and tables. It's the secondary lookup option which had now made it more difficult to show the immediate update.

    Trust me, having done my fair share of database design, both easy and those more challenging, I would have much rather used the sub-forms myself. I guess maybe I am pushing the limits of Access while still trying to satisfy the paying customer.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updating Inconsistent Update Dynasets

    You can still use a subform but limit the number of records it will allow through code. I try to persuade my clients that is isn't really necessary for the screen to look like the printed form, as long as what they print has the format they want. Granted, it sometimes requires quite a bit of persuasion, but I hate building in "down the line" problems up front. Frankly, I don't want the phone calls when just one more "improvement" breaks the whole thing.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •