|
Subject:
|
Append Query to current active records
|
|
Posted By:
|
markizan
|
Post Date:
|
9/25/2006 11:33:27 PM
|
I'm trying to use an append query to copy data to be further modified leaving the original tables in tact.
The data is to come from two tables 'orders' & 'orders details' and append to two tables 'delivery' & 'delivery details'.
Assume the following:
‘orders’ has ‘orderID-(AutoNumber)’, ‘customer’ & ‘cust_order’
‘delivery’ has ‘deliveryID-(AutoNumber)’ ‘orderID’ (combo), ‘customer’ & ‘cust_order’
‘order details’ has ‘orderID’, ‘item’, ‘description’, ‘qty’ & ‘price’
‘delivery details’ has ‘deliveryID’, ‘item’, ‘description’, ‘qty’
Both ‘orders’ & ‘delivery’ have forms that show the respective details tables in a sub form (i.e. for item 1,2,3...).
Both ‘orders’ & ‘delivery’ have (AutoNumber) on their primary index (required).
‘delivery’ form has a combo for selecting the relevant orders.orderID value to select the set of records to be appended.
I have created two append queries, one that appends required data from the ‘orders’ table to ‘delivery’ table, the second to append the data from the ‘orders details’ table to the ‘delivery details’ table. (The thought being to run these together in a macro via a command button)
The second one seems to run OK as it will append the required data from ‘order details’ into new records in the ‘delivery details’ sub form and is linked back to ‘delivery’ by ‘deliveryID’.
The problem I'm encountering is that when the first append query is run, I need the selected data from ‘orders’ to go into the current active records in ‘delivery’.
The result I'm getting is that the selected data does append into the correct table & fields, however in the next new record.
i.e. if I’m in ‘delivery - form’ and creating a new entry, all text boxes are blank exept ‘deliveryID’ which is (AutoNumber).
I select the ‘ordersID’ combo and enter or pick the required value from the dropdown list which sets the criteria for data in ‘orders’ to be appended (lets say ‘ordersID’ = 1234), after selecting this the ‘deliveryID’ will be given a value automatically (lets say ‘deliveryID’ = 9876).
At this point the idea is to have a command button run the append query so that the corresponding values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the current open records in ‘deliveryID’ = 9876 not the next/new records.
The result I'm getting is the values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the next or new record set, ‘delivery’ table, ‘deliveryID’ = 9877
‘deliveryID’ = 9876, ‘ordersID’ = 1234, ‘customer’ = blank & ‘cust_order’ = blank
‘deliveryID’ = 9877, ‘ordersID’ = blank, ‘customer’ = appended data & ‘cust_order’ = appended data.
Can this be fixed so that the appended data for ‘ordersID’ = 1234 goes into the text boxes of ‘deliveryID’ = 9876 ????
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
9/26/2006 7:02:34 AM
|
I am not sure why you are doing this, but more importantly, why you are using a second autonumber field in the delivery table. You can use the autonumber field from the orders table to populate a number field in the delivery table, and use the number field as an index, no duplicates and get the same result. Perhaps this autonumber field in delivery is causing the problem. There is no need for it.
Did this help?
mmcdonal
|
|
Reply By:
|
markizan
|
Reply Date:
|
9/26/2006 8:33:23 AM
|
To explain the reason why I am setting it out this way, the database must match the business admin trail.
Essentially at the beginning a quote is raised (tables ‘quotes’ & ‘quote details’).
If the quote is successful the data from that ‘quote’ is appended to the ‘orders’ & ‘orders details’ tables where additional information is added such as customer order number and production details for internal use.
The invoice process uses this same unaltered data later, no appending to separate tables required.
The problem is that a delivery docket will not usually have the exact same data as the order & invoice, and sometimes a delivery docket is required for miscellaneous items that are not related to an order.
i.e. quote, order & invoice may all read ‘Toyota sedan – model xyz, repair dent & paint’ in the details description, however the delivery docket will read ‘Toyota sedan – model xyz’ as you’re delivering the item not the work performed. Furthermore the customer you quoted & invoiced may not necessarily be the one the item is to be shipped to.
For this reason it is preferable to simply append in the data from orders, then edit as necessary, without changing the original data in ‘orders’ & ‘orders details’ which are required later for invoicing and historical record of work performed.
We must have unique numbering for all quotes, orders, invoices, delivery dockets, etc…
The orders primary autonumber only appears in the delivery form as a combo to select criteria for the append query to work.
The way it is setup now, the details tables part of the append query works very well both from ‘quote details to orders details’, and ‘orders details to delivery details’.
The one thing that is still giving me grief is to append from say ‘orders’ into the current records of ‘delivery’ instead of the next or new record.
|
|
