Duplicating records in a form and subform (Allen Browne)

  • Thread starter Thread starter Eka1618
  • Start date Start date
E

Eka1618

This question is towards Allen Browne, but if anyone knows the answer, please
let me know....

I took your script for duplicating records on forms and subforms. everything
is working well actually. What is not working is a loop that I have. I am
getting two sets of records when I try to duplicate a form.

for instance: One REQUEST can have many TESTS
If I have a request that has 2 tests, it duplicates fine, but instead of two
tests being duplicate, they are each duplicated twice. So now the new REQUEST
has 4 TESTS.
I loo[ through a query to determine which subforms I want to duplicate, and
my query return 2 records which is what I want.

My code is in a module, so I've passed the variable 'lngID' so my subforms
get the correct REQUEST_NO (PK in tblRequest).

If anyone knows whats going on with my code, please let me know, Thank You!

Here is the code:

Public Sub duplicateTest(frm As Form, LID As Long)
Dim tf As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim testType As String
Dim strSql As String 'SQL statement.
Dim longID As Long 'Primary key value of the new record.

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTestTypes")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset
longID = LID

While Not rs.EOF
testType = rs(1).Value
With frm
If testType = "IMPACT" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
A_M, SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, LOOKOUT,
TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS, A_M,
SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "LHANDLE" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
A_M, SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, WRENCH_NO, RPM, LOOKOUT,
TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS, A_M,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, WRENCH_NO, RPM, LOOKOUT,
TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "OFFSET" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, MIN_TORQ, SHROUD, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, MIN_TORQ, SHROUD, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "GENERAL" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, DESCRIPTION, LOOKOUT, TEST_TYPE)
" & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, DESCRIPTION, LOOKOUT, TEST_TYPE "
& _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "PROOF LOAD" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, ACID, LOAD, MIN_LOAD, CUST_SPEC, LOOKOUT, TEST_TYPE) " &
_
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, ACID, LOAD, MIN_LOAD, CUST_SPEC, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "SECURITY" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, INSTALL_TRQ, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, INSTALL_TRQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "STATIC" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, STRAIGHT_FAIL, BOTH_DIRECTIONS, INC_FAIL, START_PT,
INCREMENT, MIN_TORQ, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, STRAIGHT_FAIL, BOTH_DIRECTIONS, INC_FAIL, START_PT,
INCREMENT, MIN_TORQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, LPS, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3,
TRQ_PT4, TRQ_PT5, TRQ_PT6, RUNDOWN, THRESHOLD, TIGHT_SPEED, DWELL_ON,
DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER, TAKE_TO_FAILURE, STUD_PER_TEST,
STUD_PT_NO, CUST_SPEC, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, LPS, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3,
TRQ_PT4, TRQ_PT5, TRQ_PT6, RUNDOWN, THRESHOLD, TIGHT_SPEED, DWELL_ON,
DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER, TAKE_TO_FAILURE, STUD_PER_TEST,
STUD_PT_NO, CUST_SPEC, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
End With
rs.MoveNext
Wend
End Sub
 
I think in my SQL STatement, I need to say WHERE TEST_TYPE = testType.... I'm
pretty sure this is going to solve my problem...

Thank's anyway!
--
~Erica~


Eka1618 said:
This question is towards Allen Browne, but if anyone knows the answer, please
let me know....

I took your script for duplicating records on forms and subforms. everything
is working well actually. What is not working is a loop that I have. I am
getting two sets of records when I try to duplicate a form.

for instance: One REQUEST can have many TESTS
If I have a request that has 2 tests, it duplicates fine, but instead of two
tests being duplicate, they are each duplicated twice. So now the new REQUEST
has 4 TESTS.
I loo[ through a query to determine which subforms I want to duplicate, and
my query return 2 records which is what I want.

My code is in a module, so I've passed the variable 'lngID' so my subforms
get the correct REQUEST_NO (PK in tblRequest).

If anyone knows whats going on with my code, please let me know, Thank You!

Here is the code:

Public Sub duplicateTest(frm As Form, LID As Long)
Dim tf As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim testType As String
Dim strSql As String 'SQL statement.
Dim longID As Long 'Primary key value of the new record.

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTestTypes")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset
longID = LID

While Not rs.EOF
testType = rs(1).Value
With frm
If testType = "IMPACT" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
A_M, SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, LOOKOUT,
TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS, A_M,
SAMPLE_NO, PROVIDED, CYCLE_NO, CYCLE_TIME, TEST_TORQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "LHANDLE" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
A_M, SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, WRENCH_NO, RPM, LOOKOUT,
TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS, A_M,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, WRENCH_NO, RPM, LOOKOUT,
TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "OFFSET" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, MIN_TORQ, SHROUD, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, MIN_TORQ, SHROUD, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "GENERAL" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, DESCRIPTION, LOOKOUT, TEST_TYPE)
" & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, INSTALL_TRQ, DESCRIPTION, LOOKOUT, TEST_TYPE "
& _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "PROOF LOAD" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, ACID, LOAD, MIN_LOAD, CUST_SPEC, LOOKOUT, TEST_TYPE) " &
_
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, ACID, LOAD, MIN_LOAD, CUST_SPEC, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "SECURITY" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, INSTALL_TRQ, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, INSTALL_TRQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
ElseIf testType = "STATIC" Then
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, STRAIGHT_FAIL, BOTH_DIRECTIONS, INC_FAIL, START_PT,
INCREMENT, MIN_TORQ, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, STRAIGHT_FAIL, BOTH_DIRECTIONS, INC_FAIL, START_PT,
INCREMENT, MIN_TORQ, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
strSql = "INSERT INTO tblTest( REQUEST_NO, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, LPS, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3,
TRQ_PT4, TRQ_PT5, TRQ_PT6, RUNDOWN, THRESHOLD, TIGHT_SPEED, DWELL_ON,
DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER, TAKE_TO_FAILURE, STUD_PER_TEST,
STUD_PT_NO, CUST_SPEC, LOOKOUT, TEST_TYPE) " & _
"SELECT " & longID & " As NewTEST_ID, UNITS,
SAMPLE_NO, PROVIDED, CYCLE_NO, LPS, TRQ_SHUTOFF, TRQ_PT1, TRQ_PT2, TRQ_PT3,
TRQ_PT4, TRQ_PT5, TRQ_PT6, RUNDOWN, THRESHOLD, TIGHT_SPEED, DWELL_ON,
DWELL_OFF, WHEEL, WHEEL_NO, TEST_WASHER, TAKE_TO_FAILURE, STUD_PER_TEST,
STUD_PT_NO, CUST_SPEC, LOOKOUT, TEST_TYPE " & _
"FROM tblTest WHERE REQUEST_NO = " & .REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
End With
rs.MoveNext
Wend
End Sub
Click to expand...
 
I actually think that I do not need all those statements... so I'm going to
get rid of them, and see what happens.
 
Back
Top