Append to multiple tables

[mike6271]

Hi

I have in my sales database tblOrders and tblOrderDetails.

In tblOrders there is an option to flag this order to be repeated yearly until further notice.

The trigger will be a button to generate this years repeat orders.

Please can someone advise the ideal way to create these new records - an append query is limited to one table.

Thank You

 

[scalextric59]

Perhaps something like this:

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("select tblorder.*, tblorderdetails.* from tblorder inner join tblorderdetails on tblorder.<field1> = tblorderdetails.<field1> and ....", dbOpenDynaset)

Do Until RS.EOF
Set RS1 = DB.OpenRecordset("tblOrder", dbOpenTable)
'put here your primary key fields....
rs1.AddNew
rs1!<PKfield1> = rs!<PKfield1>
....

'here the rest of the fields...
For x = 0 To rs1.Fields.Count - 1
If rs1.Fields(x).Name <> <primary key fields> then
rs1.Fields(x) = rs.Fields(x)
end if
Next

rs1.update

then repeat the code for the other table

 

[rapsr59]

Hi Mike6271!

Scalextric59 is Spot On!

In my order system I use the following code in a click event to generate a repeat order once I have established an order number in a list control.

[FONT=Times New Roman][SIZE=1]Private Sub cmdDuplicateOrder_Click()[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]Dim dbs As DAO.Database[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim rstOrderID As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim rst1 As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim rst2 As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim rstProducts As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim lngOrderID As Long[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim strSQL As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Dim x As Long[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]Set dbs = CurrentDb[/SIZE][/FONT]
[SIZE=1]'Get the last OrderID so the new OrderID can be computed[/SIZE]
[FONT=Times New Roman][SIZE=1]Set rstOrderID = dbs.OpenRecordset("tblOrders")[/SIZE][/FONT]
[SIZE=1][FONT=Times New Roman]With rstOrderID [/FONT][FONT=Times New Roman]  .MoveLast[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=1]  lngOrderID = ![OrderId] + 1              'Increment OrderID by 1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]End With[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]'-----------------------------------------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]'This portion of code writes the new order information into the Orders table[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]'-----------------------------------------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]strSQL = "SELECT * " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]"FROM [tblOrders] " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]"WHERE [OrderID] = " & Me.lstOrdersToDuplicate.Column(2)   'Column 2 contains the original OrderID #[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rst2 = dbs.OpenRecordset("tblOrders")[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]rst2.AddNew[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]For x = 0 To rst1.Fields.Count - 1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  If rst1.Fields(x).Name = "OrderID" Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      rst2.Fields(x) = lngOrderID          'Enter new OrderID[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  ElseIf rst1.Fields(x).Name = "OrderDate" Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      rst2.Fields(x) = Date                'Update and enter todays date[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  Else[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      rst2.Fields(x) = rst1.Fields(x)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Next x[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]rst2.Update[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]'--------------------------------------------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]'This portion of code writes the existing order details into the tblOrderDetails table[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]'--------------------------------------------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]strSQL = "SELECT * " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]"FROM [tblOrderDetails] " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]"WHERE [OrderID] = " & Me.lstOrdersToDuplicate.Column(2)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]strSQL = "SELECT * " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]"FROM [tblOrderDetails];"[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rst2 = dbs.OpenRecordset(strSQL)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]While Not rst1.EOF[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]  rst2.AddNew[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  For x = 0 To rst1.Fields.Count - 1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      If rst1.Fields(x).Name = "OrderID" Then         'Enter new OrderID[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          rst2.Fields(x) = lngOrderID[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      'This portion of code checks for recent price changes[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      ElseIf rst1.Fields(x).Name = "Price" Then[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          strSQL = "SELECT [ProductID], [Price] " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          "FROM [tblProducts] " & _[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          "WHERE [ProductID] = " & rst1![ProductID][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          Set rstProducts = dbs.OpenRecordset(strSQL, dbOpenSnapshot)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          rst2.Fields(x) = rstProducts![Price][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]      Else[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]          rst2.Fields(x) = rst1.Fields(x)[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]      End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  Next x[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]  rst2.Update[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]rst1.MoveNext[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Wend[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]'--------------------------------------------------------------------------[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]Set rst1 = Nothing[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rst2 = Nothing[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rstProducts = Nothing[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=1]Set rstOrderID = Nothing[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=1]End Sub[/SIZE][/FONT]

Hope this helps you!


Richard

 

[Pat Hartman]

You have two examples that add the child table a row at a time. I do it the other way around. DAO to insert the order so I can pick up the new OrderID autonumber and then an append query with an argument to insert the OrderDetails.

 

[mike6271]

Thank you for your help guys.
Seems straightforward (but then it always does Will try implemeting.........

Mike

 

[mike6271]

Re: Append to multiple tables
You have two examples that add the child table a row at a time. I do it the other way around. DAO to insert the order so I can pick up the new OrderID autonumber and then an append query with an argument to insert the OrderDetails.

If one would do it this way round i.e. update each table separately, how would you reference the OrderDetails to the new order? Basically; how would the tblOrderDetails know which order it related to? Unless you added a new PK for repeat orders......?

 

[Pat Hartman]

When you insert a row using DAO, you can retrieve the newly generated autonumber. You then use that as an argument for the append query so that the rows link to the correct record. So the append query selects from "old" foreign key value and appends with "new" foreign key value.

 

[rapsr59]

Regarding Primary Key and the Foreign Key objects.

In my business I have seen many different Invoice Number formats on computer-generated orders from venders.

For example:

1. A20595
2. 00048768
3. 175878

The vender that uses the A prefix example uses the A prefix during the first 15 days of the month and uses a B prefix during the remainder of the month starting the 16th of month until the end of the month. These are the two billing periods that become due on specific dates. These invoice (OrderID) numbers would require a Text Field to accommodate the A/B prefix and would require a computed PK.

The 00048768 generated invoice (OrderID) number would require a Text Field to accommodate the preceding 0’s. (Zeros). In this example, if the Pk field were a numeric field access would strip the preceding 0’s and save only the 48768 part of the number. These also require a computed PK.

The third example 175878 is the Invoice numbering system I use in my order system. The field is a number field with a field size of Long Integer.

Although my order numbers are numeric, and not alpha-numeric in nature, I decided to not to use an Auto Number Field for my OrderID field for several reasons. My Invoice numbers are computed for each invoice using the previous OrderID value to compute the next OrderID.

Some of my reasons are:

1. The user selects the wrong account and begins an order, or even completes an order, when he realizes he selected the wrong account.
2. The user inadvertently finds himself in the order form and presses a key that sets the next OrderID when an Auto Number field is used and then exits the form.

The fact that these occurrences would trigger the next Auto Number and, when the order is deleted from the system or cancelled, would result in invoice numbers that don’t exist.

I feel it is important to track every one of my orders and that they remain in sequence. When an OrderID – Invoice number – is missing from the order system, which would be the case of an Auto Number created OrderID that is cancelled, you begin to wonder if you are missing an invoice that somehow got deleted from the system. That being the case you begin to think of the money that invoice represents and so on… and so on… and so on…

One very important point I would like to make about keeping all of your Ducks In A Row. In an audit by the state (Sales Tax) or in an audit by the federal government (IRS) they may indulge you with “I can’t find this invoice, please provide it!” (It’s happened to me)

My advice is to use a computed OrderID number instead of an Auto Number for peace of mind. I try to keep this in mind whenever I create my applications and I use a computed PK instead of Auto Number wherever necessary.

Richard

PS: In my cash register application I use 20090108-183824-0002 as the Primary Key. This contains the date, time and computer used for generating the sale. This is also the receipt number that shows on a printed receipt.