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" wrote:
> 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
>
>
> --
> ~Erica~
Similar Threads
