Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    64

    Unanswered: Update a field automatically

    Hi All,
    I have 2 tables: SalesOrder , ProductionOrder
    They both have the same Primary Key : order_num
    I have a form for entering data in to the: SalesOrder table.

    When I enter an order number in to the form I want the order number to copy in to the ProductionOrder table.

    The reason I want to do the above is so you dont have to type the order in to the sales order table and then again in to the production order table.

    Whats the best way to achieve what I want to do?

    Thanks

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You seem to be implying that when you create a sales order record you always want to create exactly one corresponding production order record. If the relationship is one-to-one, why not store all fields for sales order and production order in a single table (much easier to manage). You can then manage who sees what fields through seperate forms.

    If you really want to do it with seperate tables (the harder way) then you will need an "after update" event to create the new record e.g. by running a query that creates new records for all records in sales order that don't have a corresponding record in production order.

    Whilst creating an after update is in itself is straight forward, consider how you will manage someone cancelling an order or maybe editing it. How will you continually keep the corresponding production records in sync. Hence maybe better to use one table.

    hth
    Chris

  3. #3
    Join Date
    Nov 2004
    Posts
    64
    See your point but unfortunately I wrote this application a few years ago and since then it has grown to rely on the 2 tables. I simplified my origional post when indeed it goes a lot further. Basically, we have 1 form that the user has allways entered the order number twice, sales, production. I want to save them some work!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The table design sounds flaky to me, but thats your choice:-
    suggest you place some code in the forms after update event which copies the required information to the production table

    you may be able to do this via a docmd.execute, byut personally I'd use a ADO recrodset.

  5. #5
    Join Date
    Nov 2004
    Posts
    64
    Could you suggest an example?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes - try the help file

  7. #7
    Join Date
    Nov 2004
    Posts
    64
    Great, Thanks for the advise!

Posting Permissions

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