Looping and Duplicating Records Access 2007

[Kevin]

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)


For j = 1 To rstJP.RecordCount

DoCmd.RunSQL "INSERT INTO JobParts ([WorkorderID],[PartID]) VALUES ("
& rstWO![WorkorderID] & ", " & rstJP![PartID] & ")"



Next j

Next i



DoCmd.SetWarnings True

Set rstWO = Nothing
Set rstJP = Nothing

DoCmd.Close acForm, "New Work Order"

End Sub

 

[David H]

It looks like you're not actually looping through the records returned by
rstJP. Hence the RunSQL statement is only acting on the first record returned
if any. Encapsulate the statement with a loop as in

While Not rstJP.EOF

SQLStatement
rstJP.MoveNext

wend

*Do not forget the .MoveNext or you'll create an infinate loop.

However, you can write a SQL statement that selects records from one table
and inserts them into another all in a single SQL Statement. Thus eliminating
the need to go record by record.

Its been awhile since I've done that but I should be able to post a sample
SQL statement that you can adapt.

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)


For j = 1 To rstJP.RecordCount

DoCmd.RunSQL "INSERT INTO JobParts ([WorkorderID],[PartID]) VALUES ("
& rstWO![WorkorderID] & ", " & rstJP![PartID] & ")"



Next j

Next i



DoCmd.SetWarnings True

Set rstWO = Nothing
Set rstJP = Nothing

DoCmd.Close acForm, "New Work Order"

End Sub

 

[David H]

The following SQL Statement will select records from one table matching a
specific criteria and insert them all into the destination table in batch.
This will eliminate the need to loop through the child table.

Also, while ADO can obviously still be using in Access it never completely
replaced DAO. There are some Access developers out there still using ADO, but
the trend tends to be DAO.

INSERT INTO tblProducts_backup (ProductName, ProductCode) SELECT
ProductName, ProductCode From tblProducts WHERE ProductCode = 'TST'

INSERT INTO [DestinationTable] ([field1],[field2],...) SELECT
[field1],[field2],... From [OriginationTable] WHERE [fieldName] = [Criteria]

! Just be absolutely certain that the field order in the INSERT INTO and
SELECT are the same, otherwise the data will not be inserted into the correct
fields.

 

[Kevin]

David, Thanks for the quick response, As i stated in the original post I am
clueless with the codeing thing, unfortunatly i am more of a point and click
person. where would I enter the following in the code that I posted?

While Not rstJP.EOF

SQLStatement
rstJP.MoveNext

wend

Thanks Kevin

It looks like you're not actually looping through the records returned by
rstJP. Hence the RunSQL statement is only acting on the first record returned
if any. Encapsulate the statement with a loop as in

While Not rstJP.EOF

SQLStatement
rstJP.MoveNext

wend

*Do not forget the .MoveNext or you'll create an infinate loop.

However, you can write a SQL statement that selects records from one table
and inserts them into another all in a single SQL Statement. Thus eliminating
the need to go record by record.

Its been awhile since I've done that but I should be able to post a sample
SQL statement that you can adapt.

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)


For j = 1 To rstJP.RecordCount

DoCmd.RunSQL "INSERT INTO JobParts ([WorkorderID],[PartID]) VALUES ("
& rstWO![WorkorderID] & ", " & rstJP![PartID] & ")"



Next j

Next i



DoCmd.SetWarnings True

Set rstWO = Nothing
Set rstJP = Nothing

DoCmd.Close acForm, "New Work Order"

End Sub

 

[David H]

It looks like the underlying problem deals with looping through the records
in both the master and child table.

Also, are the Work Orders being properly duplicated? When you open up a
record and loop through them you typically have to execute a .MoveNext to
actually point to a new record which I don't see.

Let me know and we'll go from there as that'll change things.

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)


For j = 1 To rstJP.RecordCount

DoCmd.RunSQL "INSERT INTO JobParts ([WorkorderID],[PartID]) VALUES ("
& rstWO![WorkorderID] & ", " & rstJP![PartID] & ")"



Next j

Next i



DoCmd.SetWarnings True

Set rstWO = Nothing
Set rstJP = Nothing

DoCmd.Close acForm, "New Work Order"

End Sub

 

[Kevin]

Everything with rstWO is working fine in the Work Order table and it
duplicates the WorkOrderID and all other associated fields correctly.

It looks like the underlying problem deals with looping through the records
in both the master and child table.

Also, are the Work Orders being properly duplicated? When you open up a
record and loop through them you typically have to execute a .MoveNext to
actually point to a new record which I don't see.

Let me know and we'll go from there as that'll change things.

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)


For j = 1 To rstJP.RecordCount

DoCmd.RunSQL "INSERT INTO JobParts ([WorkorderID],[PartID]) VALUES ("
& rstWO![WorkorderID] & ", " & rstJP![PartID] & ")"



Next j

Next i



DoCmd.SetWarnings True

Set rstWO = Nothing
Set rstJP = Nothing

DoCmd.Close acForm, "New Work Order"

End Sub

 

[David H]

See below...

Basically its deleting out the J for...loop and replaceing it with the query
that I mentioned earlier. The query will select all of the records and insert
them in a batch.

Before you modify the actual code, copy and paste the SQL Statement into the
query builder in Access and change the field names and tables names
accordingly and test it to ensure that all of the child records are copied
over. You'll need to hard code the criteria.

INSERT INTO [DestinationTable] ([field1],[field2],...) SELECT
[field1],[field2],... From [OriginationTable] WHERE [fieldName] = [Criteria]

Once you have the query working in access then copy it as-is in place of the
J Loop and modify the [Critera] to reference the recordset that you're
looping through as in WHERE [fieldName] = rstWO![WorkorderID]

Once you've done that it should be fine, however there's one more step
that's an icing on the cake that we'll add once you've got that part working.

First of all thanks in advance for any ideas to help me resolve this problem.
I am a total novice when it comes to coding and have had a friend helping me
with this work project because I really do not understand the whole looping
thing and all of the coding. Here goes…

I have a work application that I am putting together that I need to
duplicate Work Orders “WOâ€. The following code is currently what I am using
and it duplicates the WO’s just fine in the table “work_orders†based on the
number of mowings, interval and workdate entered on the form along with all
required fields. The problem that I am having is that in a related table
“JobParts†I need it to enter the duplicated WorkOrderID along with up to 4
PartIDs that were entered with the original WO in the JobParts table and if
no parts are entered it will continue and not put any WorkOrderIDs or PartIDs
in the JobParts Table but still duplicate the WOs in the Work_Order table.

The current code is duplicating the WorkOrderIDs in the JobParts table but
is only entering the first PartID on all duplicated WorkorderIDs and is
generating an additional WorkOrderID. Example…

Work_Order table duplicating 1 work order 2 times
265000 - original
265001 - 1st duplicate
265002 – 2nd duplicate

JobParts table original records with 2 PartIDs
265000 PartID – 4001
265000 PartID – 4002
265001 PartID – 4001
265001 PartID – 4001
265002 PartID – 4001
265002 PartID – 4001
265003 PartID – 4001 Extra unwanted record
265003 PartID – 4001 Extra unwanted record

Clear as mud? here is the cuurrent code that I have been trying

Private Sub Create_Lawn_WorkOrder_Click()

Dim rstWO As ADODB.Recordset
Dim rstJP As ADODB.Recordset
Dim i As Integer, iInterval As Integer, iNumMowings As Integer
Dim dtStart As Date
Dim j As Integer

Set rstWO = New ADODB.Recordset
Set rstJP = New ADODB.Recordset

rstWO.Open "SELECT * From [Work_Orders]", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
rstJP.Open "SELECT * From [JobParts] WHERE [WorkOrderID] = " &
rstWO![WorkorderID], CurrentProject.Connection, adOpenStatic, adLockOptimistic

iInterval = Me.Interval
iNumMowings = Me.NumMowings
dtStartDate = Me.WorkDate

DoCmd.SetWarnings False

For i = 1 To iNumMowings
rstWO.AddNew
rstWO![CustomerID] = Me.Select_Customer
rstWO![CatagoryID] = Me.CatagoryID
rstWO![ChemWO] = Me.ChemWO
rstWO![StatusID] = Me.Status
rstWO![EmplID] = Me.employee
rstWO![WorkTBD] = Me.WorkTBD
rstWO![WorkDate] = DateAdd("d", i * iInterval, dtStartDate)

DoCmd.RunSQL INSERT INTO [DestinationTable]
([field1],[field2],...) SELECT
[field1],[field2],... From [OriginationTable] WHERE [fieldName] = [Criteria]