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