Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating infor from one table on to another (Access 2000)

    I have an order table and an order details table. I also have a goods received table (because all an order may not be delivered at one time. )

    I have a form which has the order table as its main form and two sub forms - one with the order details and one to entered the goods received. You select the item on the order details form and this filters entries on the second sub form to goods received in respect of the order details selected.

    I would like two things to happen -

    One is that as a default the first row of the goods received picks up the number shown on the order details (a reducing running total in other entries would be nice but not essential)

    Secondly, be able to do something to update the goods received in one hit to show that the 4 banannas, 3 pears and 4 coconuts ordered from Fruity Fruits on Order 500 have been received - ie that there is no part order involved. Rather than having to select every single item on the ordr details form and update them individually.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating infor from one table on to another (Access 2000)

    1. >> as a default the first row of the goods received picks up the number shown on the order details
    When would you like this to happen? Surely you wouldn't want the number in the first record of Goods Received to change each time the user selects an order detail?

    2. >> to update the goods received in one hit ... (etc.)
    It's not clear to me what you want. Do you want to mark all items of the Order Details table belonging to one order as having been delivered? And what do you mean by "ie that there is no part order involved"?

    In general, can you supply more details about the tables you are using, in particular about the Goods Received table and how it is linked to the Orders and/or Order Details table?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updating infor from one table on to another (Access 2000)

    >>You select the item on the order details form and this filters entries on the second sub form to goods received in respect of the order details selected.<<
    What do you men by this statement? Does it mean that you create goods received records for each order detail record for the 2nd subform?
    .
    Both options would generate receive records for o/s order details and the Full option would be just to confirm it, whereas the Part option you would change the quantities or delete receive records before confirming them.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Updating infor from one table on to another (A

    I attach a sample which tries to do both these things.

    In the order details form, I have added an extra control "Outstanding" calculated as : [quantity]-nz(DSum("[quantityReceived]","tblGoodsReceived","[Orderdetailsid] = " & [orderdetailsid]),0)

    This is used as the default value for quantity received in the other subform , which has a default value
    =[Forms]![frmorders]![frmorderdetailssub].[Form]![outstanding]

    The goods received subform also has an orderdetailsID which has this as its default value.
    =[Forms]![frmorders]![frmorderdetailssub].[Form]![orderdetailsID]

    On the order form I have put a command button " Receive All" that runs an append query that creates Goods Received records for each order item, with a quanity equal to the number outstanding. ( I think I left out the criteria Outstanding >0 ).
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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