duplicate record in from and subform (1 Viewer)

[theinviter]

hi guys :
i need help, have access data base containing form and subform, i want to creat a button that when i click on it it will duplicate the record in new record within the same form.
the form ( table2) contain following : ID , Field1 , Field2
and subform contain following : Code , S1 , S2

link master field is ID
link child fields is S2

can any one guide me please .
i tried the bellow code but does not work.


Private Sub Command9_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
' Dim lngID2 As Long 'Primary key value of the new record.
'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Field1 = Me.Field1
!Field2 = Me.Field2

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !ID
' lngID2 = [Field2]

'Duplicate the related records: append query.
If Me.[table2 subform1].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [table2] ( ID,Field1, Field2 ) " & _
"SELECT " & lngID & " As NewID , Field1, Field2 ,code , s1" & _
"FROM [table2] WHERE ID = " & Me.ID & ";"

Debug.Print strSql
DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler

End Sub

 

[MarkK]

... does not work.

If you think of us as Code Doctors, we need symptoms to make a diagnosis. "Does not work" is not enough info.

hth
Mark

 

[theinviter]

really do not know
i got this message "run time error 3061
too few parameter expected 3.
Im new to VBA

 

[theinviter]

if any one can help me please i will send him the data by email.

 

[MarkK]

Yes, that's a symptom, and at what line does that error pop up? Typically when the debugger breaks at a line it is highlighted in yellow. What line is that?

And this is a standard requirement to help you debug an error in code: 1) what is the error? 2) At what line does it occur?
hth
Mark

 

[pbaldy]

Also, your insert SQL specifies 3 fields in the destination table, but selects 5 from the source. Likely you have some misspellings. Post the result of the Debug.Print line, and you'll see the problem.

 

[theinviter]

DBEngine(0)(0).Execute strSql, dbFailOnError
The above line holighted yellow . But when I move the cursor on it it show this line
strSql = "INSERT INTO [table2] ( ID,Field1, Field2 ) " & _
"SELECT " & lngID & " As NewID , Field1, Field2 ,code , s1" & _
"FROM [table2] WHERE ID = " & Me.ID &

 

[jdraw]

inviter,

As Paul said, your SQL says insert 3 fields into a new record in table2
- ID,Field1, Field2 -
but you are selecting 5 fields

SELECT " & lngID & " As NewID , Field1, Field2 ,code , s1

SQL handler balks because of this difference

Also there is no space between s1 and FROM

 

[theinviter]

i done as you told , but got this message :
If Me.[table2 subform1].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [table2] (ID,Field1, Field2 ) " & _
"SELECT " & lngID & " As NewID , Field1, Field2 , code, s1 " & _
"FROM [table2] WHERE ID = " & Me.ID & ";"


Run time error 3346
Number of query value and destination field are not the same.

any advice please

 

[jdraw]

You are still trying to put 5 things into 3 spots.

Analogy, reserve 3 seats and try to put 5 people into those seats.

 

[theinviter]

can you please advise me how to solve this isssue .

 

[theinviter]

this is my email : the_inviter@hotmail.com
please any one ready to help will send him the data.

 

[isladogs]

As has already been explained more than once, you are trying to copy 5 fields into 3 fields which is why you get:

Run time error 3346
Number of query value and destination field are not the same.

If you only want 3 fields copied then use:

strSql = "INSERT INTO [table2] (ID, Field1, Field2 ) " & _
"SELECT " & lngID & " As NewID, Field1, Field2 " & _
"FROM [table2] WHERE ID = " & Me.ID & ";"

If you want all 5 fields copied then use

strSql = "INSERT INTO [table2] (ID, Field1, Field2, code, s1 ) " & _
"SELECT " & lngID & " As NewID, Field1, Field2, code, s1 " & _
"FROM [table2] WHERE ID = " & Me.ID & ";"

 

[theinviter]

i done as you said

strSql = "INSERT INTO [table2] (ID, Field1, Field2, code, s1 ) " & _
"SELECT " & lngID & " As NewID, Field1, Field2, code, s1 " & _
"FROM [table2] WHERE ID = " & Me.ID & ";"

but now got this error ;
too few parameter expected 3

please find attached picture

 

[isladogs]

I recommend you watch some of the excellent videos on programming in Access available free on You Tube. For examplehttps://www.youtube.com/watch?v=kogGwRIHH6o

I haven't got any time to assist you further on this matter

 

[pbaldy]

Did you check the spelling of your table and field names like I suggested? This may help:

http://www.baldyweb.com/ImmediateWindow.htm

You should be able to attach the db here after compact/repair and zipping.

 

[theinviter]

i attached the file , if anyone can help and modify the code i will ve:bathankfully thankful.

 

[jdraw]

Watch some intro videos/tutorials as has been suggested.
You have Table2 as a table, a form, subform and a report.


What are you trying to do --plain English?
Nobody has a real database with tables named table1, table2.
And fields field1 and field2.

Tell us about the database.

 

[Orthodox Dave]

I have updated the code from the database you provided and it is below - and I tested it - it works.

I have added notes in capitals preceded by "***" to show where I have made changes.

You needed the primary key value of the Original table1 record, because this provides the link to the S2 field in table2 which contains the records you want to duplicate.

Private Sub Command9_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngOldID As Long '***PRIMARY KEY VALUE OF THE ORIGINAL RECORD (YOU NEED THIS TO GET AT THE ORIGINAL SUB-RECORD)
    Dim lngID As Long       'Primary key value of the new record.
   ' Dim lngID2 As Long 'Primary key value of the new record.
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
    
    lngOldID = Me.ID '***YOU GRAB THE ORIGINAL ID BEFORE CREATING THE NEW RECORD SO YOU CAN GET THE RELATED SUBRECORD
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !Field1 = Me.Field1
                !Field2 = Me.Field2
                
               
             
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = ![ID]
          
            
            
            'Duplicate the related records: append query.
            If Me.[table2 subform1].Form.RecordsetClone.RecordCount > 0 Then

'***HERE IS YOUR ORIGINAL CODE
'             strSql = "INSERT INTO [table2] (ID,Field1, Field2,  ) " & _
 '            "SELECT " & lngID & "  As NewID , code, s1  " & _
 '            "FROM [table2] WHERE ID = " & Me.ID & ";"
 
'***HERE IS THE NEW CODE
                strSql = "INSERT INTO Table2 ( s1, s2 ) " & _
                "SELECT Table2.s1, " & lngID & " AS Expr1 " & _
                "FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.s2 " & _
                "WHERE (((Table1.ID)=" & lngOldID & "));"

                  Debug.Print strSql
               'DBEngine(0)(0).Execute strSql, dbFailOnError
               
'***ON YOUR ORIGINAL CODE YOU NEVER ACTUALLY RAN THE SQL!
               DoCmd.SetWarnings False
               DoCmd.RunSQL strSql
               DoCmd.SetWarnings True
         
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

End Sub

 

[theinviter]

I have updated the code from the database you provided and it is below - and I tested it - it works.

I have added notes in capitals preceded by "***" to show where I have made changes.

You needed the primary key value of the Original table1 record, because this provides the link to the S2 field in table2 which contains the records you want to duplicate.

Private Sub Command9_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngOldID As Long '***PRIMARY KEY VALUE OF THE ORIGINAL RECORD (YOU NEED THIS TO GET AT THE ORIGINAL SUB-RECORD)
    Dim lngID As Long       'Primary key value of the new record.
   ' Dim lngID2 As Long 'Primary key value of the new record.
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
    
    lngOldID = Me.ID '***YOU GRAB THE ORIGINAL ID BEFORE CREATING THE NEW RECORD SO YOU CAN GET THE RELATED SUBRECORD
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !Field1 = Me.Field1
                !Field2 = Me.Field2
                
               
             
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = ![ID]
          
            
            
            'Duplicate the related records: append query.
            If Me.[table2 subform1].Form.RecordsetClone.RecordCount > 0 Then

'***HERE IS YOUR ORIGINAL CODE
'             strSql = "INSERT INTO [table2] (ID,Field1, Field2,  ) " & _
 '            "SELECT " & lngID & "  As NewID , code, s1  " & _
 '            "FROM [table2] WHERE ID = " & Me.ID & ";"
 
'***HERE IS THE NEW CODE
                strSql = "INSERT INTO Table2 ( s1, s2 ) " & _
                "SELECT Table2.s1, " & lngID & " AS Expr1 " & _
                "FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.s2 " & _
                "WHERE (((Table1.ID)=" & lngOldID & "));"

                  Debug.Print strSql
               'DBEngine(0)(0).Execute strSql, dbFailOnError
               
'***ON YOUR ORIGINAL CODE YOU NEVER ACTUALLY RAN THE SQL!
               DoCmd.SetWarnings False
               DoCmd.RunSQL strSql
               DoCmd.SetWarnings True
         
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

End Sub

Thanks

Done successfully .