Results 1 to 6 of 6
  1. #1
    jhalstead is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    3

    Updating 2 related tables from 2 different related tables ie Work orders to invoices

    Here is the scenario:

    Service Order Header and Service Order Detail tables linked with a One to Many relationship and referential integrity and cascading rules enabled. Once the Service Orders are completed I'd like to Copy them (some values) to the Invoices Header and Invoices Detail tables to be used for generating Invoices. Invoices header and Invoices Detail are also linked with one-to-many and referential integrity and cascading rules enabled.

    I know this must be a common scenario but I can't easily find the solution. Rather than beating my head against it only to devise an improper solution I'm asking this forum for assistance.

    Thank you in advance for your assistance.

    Regards,
    Jason

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't understand any of that. Never run into anything like it. Why is detail data copied to 'Header' and 'Detail' tables? Why isn't data calculated on report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jhalstead is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    3
    Sorry if my post was a little vague. Let me try to restate it. The orders come in and are entered as Service Orders in a multi-part form (Header and Details). Once the Service Orders are completed I'd like to create the invoices by appending to the Invoices table some of the values from the Service Order Header table and all of the values from the Service Order Details table to the Invoice Detail table. Below is the movement of the data

    Service Order Header Information (Order Date, Deadline, Completion Date, Order Number, etc) ---> Invoice Header Table
    Service Order Detail Information (Product, Cost, Quantity, etc)---> Invoice Detail Table

    This would allow me to maintain a history on all received Service Orders as well as track all Invoices.

    Thanks,
    Jason

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, that seems familiar now. Sounds like you really need to use form/subform arrangement which would eliminate this need for code to save data to separate tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jhalstead is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    3
    Yep. I do have a form/sub-form for the Service Request Header/details. I'm just not sure how to get the data from the Service Request header/details copied into the Invoice Header/details table with the linkages intact. Assume I have a button on the Service Request Header/Details form called "Complete Service Request and Generate Invoice" that I would like use to initiate the transaction.

    What code, macro or other option could I use to accomplish the aforementioned goal?

    Thanks,
    jason

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ooops, not what I thought was happening. Think I get it now. ServiceOrder needs to be invoiced (billed). So need record in Invoices linking to the ServiceID.

    Either open form/subform for Invoices/InvoiceDetails and select Service items (which I think is the more common practice) or run SQL INSERT actions. Trick is figuring out what event to put code in, perhaps a button click.

    Example SQL, assuming Order Number is text:

    CurrentDb.Execute "INSERT INTO [Invoice Header]([Order Number], [Order Date], Deadline, [Completion Date]) VALUES('" & Me.[Order Number] & "', #", & Me.[Order Date] & "#, #" & Me.Deadline & "#, #" & Me.[Completion Date] & "#)"


    BTW, advise no spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. If used, must enclose in [].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populating unbound related tables
    By JoelBR in forum Access
    Replies: 11
    Last Post: 11-17-2012, 06:59 AM
  2. A problem with a query with related tables
    By Evdokimos in forum Queries
    Replies: 3
    Last Post: 10-26-2012, 04:16 AM
  3. One Form to Update 2 Related Tables?
    By zannix in forum Forms
    Replies: 3
    Last Post: 01-26-2012, 08:46 AM
  4. Not able to make Form from related Tables
    By a1y1a1y1 in forum Forms
    Replies: 5
    Last Post: 01-02-2010, 12:33 PM
  5. 3 Related Dependent Tables
    By jbarrum in forum Access
    Replies: 0
    Last Post: 11-17-2009, 11:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums