Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to copy rows from one table and paste to another in access.

Status
Not open for further replies.

Layth

IS-IT--Management
Joined
Jun 7, 2005
Messages
44
Location
US
I am working on a single table database project. In this project I need to copy records that have a value in the "description" field greater than zero, and they need to be copied and inserted into a new table that many of times. Example, If a record has a value of 10 the entire record will be copied ten times and inserted into the new table ten times.
I have written code that searches through the intial table, finds the required records, multiplies the records, and moves to the next record until end of file is reached.
What I'm having problems with is getting the records that I have copied to be inserted in the new table. Here is the code below, I have used MsgBox() function to show the data, I believe Msg Box() should be replaced with new code to insert into the new table. I should add that the new table has the same type fields and same number of fields as the old table and that both tables are in the same database.


Here is the code:

code

Option Compare Database

Sub Layman()
Dim count As Integer
Dim db As Database
Dim recselection As String

Dim IDvar As RecordSet
Dim Biovar As RecordSet
Dim Dressyvar As RecordSet
Dim Addonevar As RecordSet
Dim Addtwovar As RecordSet
Dim Addthreevar As RecordSet
Dim cityvar As RecordSet
Dim statevar As RecordSet
Dim zipvar As RecordSet
Dim cartvar As RecordSet
Dim descriptionvar As RecordSet

Set db = CurrentDb
recselection = "Select * From Customer_table where description > 0"

Set IDvar = db.OpenRecordset(recselection)
Set Biovar = db.OpenRecordset(recselection)
Set Dressyvar = db.OpenRecordset(recselection)
Set Addonevar = db.OpenRecordset(recselection)
Set Addtwovar = db.OpenRecordset(recselection)
Set Addthreevar = db.OpenRecordset(recselection)
Set cityvar = db.OpenRecordset(recselection)
Set statevar = db.OpenRecordset(recselection)
Set zipvar = db.OpenRecordset(recselection)
Set cartvar = db.OpenRecordset(recselection)
Set descriptionvar = db.OpenRecordset(recselection)

While Not IDvar.EOF
While count > 0

' This where the code to insert to new table goes?
MsgBox(IDvar("ID")
MsgBox(Biovar("Bio_ID"))
MsgBox(Dressyvar("Addressee"))
MsgBox(Addonevar("Addressline1"))
MsgBox(addtwovar("Addressline2"))
MsgBox(addthreevar("Addressline3"))
MsgBox(cityvar("City"))
MsgBox(statevar("State"))
MsgBox(zipvar("Zip"))
MsgBox(cartvar("Cart"))
MsgBox(descriptionvar("description")

count = count - 1
Wend

IDvar.MoveNext
Biovar.MoveNext
Dressyvar.MoveNext
Addonevar.MoveNext
Addtwovar.MoveNext
Addthreevar.MoveNext
cityvar.MoveNext
statevar.MoveNext
zipvar.MoveNext
cartvar.MoveNext
descriptionvar.MoveNext

Wend

IDvar.Close
Biovar.Close
Dressyvar.Close
Addonevar.Close
Addtwovar.Close
Addthree.Close
cityvar.Close
statevar.Close
zipvar.Close
cartvar.Close
descriptionvar.Close

Set IDvar = Nothing
Set Biovar = Nothing
Set Dressyvar = Nothing
Set Addonevar = Nothing
Set Addtwovar = Nothing
Set Addthreevar = Nothing
Set cityvar = Nothing
Set statevar = Nothing
Set zipvar = Nothing
Set cartvar = Nothing
Set descriptionvar = Nothing

End Sub

/code




Any help with this would be greatly appreciated.
Thanks,
Layth
 
This is the simpliest I could think

Code:
Sub Layman()

Dim ManyTimes As Long
Dim MaxTimes As Long

MaxTimes=DMax("description", "Customer_table")

For ManyTimes = 1 To MaxTimes    
 CurrentDB.Execute "Insert Into NewTable " & _
                   "Select Customer_table.* " & _
                   "From Customer_table " & _ 
                   "Where description > " & _
                    ManyTimes -1 & ";"
Next ManyTimes 
End Sub
 
Hey, thanks this code pretty much takes care of what I was looking for, one problem though, I have many records that have a blank record for the "description" field, I still need these records to be inserted into the new table one time. I have been trying to modify the code and I'm having difficulty.

Your help is much appreciated.

Thanks,

Layth
 
Ok then,

Code:
Sub Layman()

Dim ManyTimes As Long
Dim MaxTimes As Long

CurrentDB.Execute "Insert Into NewTable " & _
                  "Select Customer_table.* " & _
                  "From Customer_table " & _ 
                  "Where description Is Null;"

MaxTimes=DMax("description", "Customer_table")

For ManyTimes = 1 To MaxTimes    
 CurrentDB.Execute "Insert Into NewTable " & _
                   "Select Customer_table.* " & _
                   "From Customer_table " & _ 
                   "Where description > " & _
                    ManyTimes -1 & ";"
Next ManyTimes 
End Sub
[code]

That will do!
 
Hey, Thanks again! That's exactly what I needed. It's been a while since I've programed and in the statement "Where description Is Null" I was trying to write, "Where description = Null". This is a great help to me.

Thanks,

Layth
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top