Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 25th, 2006, 11:33 PM
Registered User
Points: 27, Level: 1
Points: 27, Level: 1 Points: 27, Level: 1 Points: 27, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Append Query to current active records

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 With Quote
  #2 (permalink)  
Old September 26th, 2006, 07:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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 With Quote
  #3 (permalink)  
Old September 26th, 2006, 08:33 AM
Registered User
Points: 27, Level: 1
Points: 27, Level: 1 Points: 27, Level: 1 Points: 27, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query bright_mulenga Access 1 January 3rd, 2007 01:33 PM
Append Query Cybersurfer Access 1 February 13th, 2006 01:02 PM
Append records with 1 incrementing field Mitch SQL Language 3 May 26th, 2005 08:40 AM
append query? bph Access 2 November 23rd, 2004 12:44 PM
append query stoneman Access 2 November 12th, 2003 09:17 PM



All times are GMT -4. The time now is 07:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.