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 ????
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 ????
