Need vba hel - copying rows from one table to another on same database

[ccorley]

I've set-up an order form where information is entered into two "temporary" tables: tempExpense and tempLine. The tempExpense table contains the data for the order details and the tempLine table contains the line items for the order. I'd like the user to be able to fill out the order form and then press the "submit" button after which the information is then transferred to the corresponding tables Expense and Line that contain identical fields/field names to tempExpense and tempLine. There may be multiple rows in the tempLine/Line tables that are linked to each row in the tempExpense/Expense tables.

I started a DAO database and am able to add/copy the data from the tempExpense to Expense, however I'm having trouble copying the tempLine rows over to the Line data. The tables are linked by the fields ExpenseID in the Expense tables and LineExpenseID in the Line tables. The code below is what I have so far to call up the tempLine rows for the order but the recordset is only calling up the first record. Then I suppose I still have to do some sort loop to copy the other rows? This way might not even be the best way, so please help! Thanks in advance for your help.

Dim SQL As String
Dim Count As Integer


Dim dbs2 As DAO.Database
Dim rst2 As DAO.Recordset
Set dbs2 = CurrentDb


SQL = "Select * from tempLine WHERE LineExpenseID = " & Me.ExpenseID
Set rst2 = dbs2.OpenRecordset(SQL)
Count = rst2.RecordCount
MsgBox ("Record count = " & Count)

Cynthia

[ItsMe]

When dreaming up names for stuff, you want to avoid using reserved words. In the example below, I have used intCount as a variable name. When you open a DAO.Recordset, the default type is dbopendynaset, which is type dynaset. You will want to populate the entire Recordset prior to getting a RecordCount. In the example below, I have added a MoveLast method. Before retrieving data from a specific record, you may want to MoveFirst or...

Dim SQL As String
Dim intCount As Integer

Dim dbs2 As DAO.Database
Dim rst2 As DAO.Recordset
Set dbs2 = CurrentDb
SQL = "Select * from tempLine WHERE LineExpenseID = " & Me.ExpenseID
Set rst2 = dbs2.OpenRecordset(SQL)
rs2.movelast
intCountCount = rst2.RecordCount
MsgBox ("Record count = " & Count)

[Bulzie]

Seems a complicated way to set up your database. Looks like you have Order information(includes expense info) and Line Item information that is linked on OrderID? So maybe:

tblOrder:
OrderID
OrderDesc
Expense Amt
...

tblLineItem:
LineItemID
OrderID
LineItemTitle
LineDate
...

Why use temp stuff, just have main form of Order info and Subform of ItemLine info?

[John_G]

If your Temp tables are empty before you enter any data, i.e. they contain data only for the current order, you can do this:

currentdb.Execute "Insert into Expense Select * from TempExpense", dbfailonerror

and

currentdb.Execute "Insert into Line select * from TempLine", dbfailonerror

If the temporary table contain data for more than one order, it is only a bit more complicated - you just need to include a where clause with the Order number:

currentdb.Execute "Insert into Expense Select * from TempExpense Where Order_ID = " & me![orderID], dbfailonerror

and

currentdb.Execute "Insert into Line select * from TempLine" Where Order_ID = " & me![orderID], dbfailonerror

change the field and control names to match what you are using.

[ccorley]

I want to keep the requester order information separate so they can start an order and save it then come back to their orders if needed or resubmit an order they have previously submitted. I've already got tons of data on the main Expense form where I have lots of queries already set-up to get order totals, balances, etc. And I just want to include only the submitted orders in the calculations.

[ccorley]

Thank you. I will have data for other orders in the tempExpense and tempLine tables so I will have to use your latter example. I'll give it a try.

[ccorley]

I'm getting the error below for the first line (Expense table):

Runtime error 3825
Select * cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field.

One of my fields is an attachment type (Field name = ExpenseSupportingDocs). Could that be the issue? Others are just date, number, text, and yes/no. If so, there are no attachments on the tempExpense table so I don't need to copy that field.

Secondly, if I comment out the "insert into expense" line, I get the following error for the "insert into line" line. On the tempLine table, only the key is indexed the rest are text and number fields except for one that is calculated. One the Line table, the Key and LineExpenseID are also indexed but the LineExpenseID is set to allow duplicates.

Runtime error 3022
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationships. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries.

[John_G]

Select * cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field.

Talk about a misleading error message. Yes, it is the attachment field; I tried it and got the same error. You can fix that by using an explicit field list instead of
Select * :

currentdb.Execute "Insert into Expense Select field1, field2, ... from TempExpense Where Order_ID = " & me![orderID], dbfailonerror

For the other error - what is the key? If it is just the order number and is indexed with no duplicates, it won't work because there can be multiple lines for any one order. In that table, the PK will have to be two fields - Order number + line Number.

Please give more details on the structure of your Line table, i.e. what the fields are, what you have set as the Key, and what other indexes (if any) you have.

[ccorley]

Thank you for you help with the Expense table.

In reply to post regarding the line table:

temp Line table:
LineID - autonum (key)
LineExpenseID - number (indexed and allow duplicates. This field links to the ExpenseID key of the tempExpense table)
LineDescription - text
LinePartNumer - text
LineUnitMeasure - text
LineQuantity - number
LineAmount - number
LineTotal - calculated

Line table:
LineID - autonum (key)
LineExpenseID - number (indexed but allow duplicates)
LineDescription - text
LinePartNumer - text
LineUnitMeasure - text
LineQuantity - number
LineAmount - number
LineTotal - calculated

[John_G]

I think the error was cause by including the LineID (automatically by Select *). Just do the same for Line as you did for Expense, i.e. use an explicit field list, but without the LineID. Since LineID is autonum, Access will automatically generate the LineID in the Line table.

[ccorley]

Thanks! I'll give that a try.

[ccorley]

The transfer of the tempExpense data worked. However, I'm still getting the same error for the tempLine. Here's the code I'm using:
CurrentDb.Execute "Insert into Line SELECT LineExpenseID, LineDescription, LinePartNumber, LineUnitMeasure, LineQuantity, LineAmount, LineTotal from TempLine Where LineExpenseID = " & Me![ExpenseID], dbFailOnError

[ccorley]

I forgot to ask if it is possible to change one of the field values to another text when I do the insert. The reason I ask is because the LineExpenseID field is linked to the ExpenseID (key) of the Expense table. When I insert into the Expense table it will have a new number since the ExpenseID field is autonum type. I will need to change the LineExpenseID value to the new ExpenseID value. Maybe I can use the DLast function to call the last expense ID? I hope this makes sense.

You have been really helpful John. I really appreciate all your help!

[John_G]

It sounds as if your use of Autonumber in the Expense table is causing a problem. Autonumber fields should never have any real meaning - they should serve only to uniquely identify table records. This is one case where Autonum isn't very useful, because you need to know in advance what the ExpenseID is.

If you change the ExpenseID from autonum to Number - Integer, you can easily determine the next ExpenseID value and use that:

'
' Get the current maximum value and add 1
'
NextID = dmax("ExpenseID","Expense") + 1
'
' Replace values in the temporary tables
'
currentdb.Execute "Update tempExpense set ExpenseID = " & nextID & " Where Order_ID = " & me![orderID], dbfailonerror
currentdb.Execute "Update tempLine set ExpenseID = " & nextID & " Where Order_ID = " & me![orderID], dbfailonerror
'
' Now transfer to the main tables as before
'
currentdb.Execute "Insert into Expense Select field1, field2, ... from TempExpense Where Order_ID = " & me![orderID], dbfailonerror
currentdb.Execute "Insert into Line Select field1, field2, ... from TempLine Where Order_ID = " & me![orderID], dbfailonerror


If you still get the duplicate record error when adding to Line, there is probably an indexing issue.

Question: What if any is the difference between OrderID and ExpenseID? You use order number in the Me![OrderID] reference, but it isn't in tempLine or Line, so those Insert and Update statements will give you an error.

[ssanfu]

PMFJI,

You might want to read these

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm