Duplicate the record in form and subform

[Sarella]

Hi,

I'm new to vba coding (haven't a clue really!!), and realise that I probably haven't formatted anything in my DB in what I now realise is the formal way, however I wonder if someone an point me in the right direction as to why the code I've copied from allenbrowne.com/ser-57.html doesn't work correctly in my DB.

The table that the main form and button sits on holds purchase order information, and the subform holds the items ordered data.

The link fields are:
master fields :ID / Supplier
child fields O No / Supplier ID

The code seems to work correctly as far as creating a new record is concerned, but falls over when trying to put the sub form information in.


I keep getting" runtime error 3134 Sytax error in INSERT INTO statement.

When I click on Debug, it highlights yellow the line: DBEngine(0)(0).Execute strSql, dbFailOnError

This is my code (mainly taken from above website) for the command button:


Private Sub Command92_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

!OrderType = Me.OrderType
![ref no] = "**Update PO**"
![PO Date] = Date
!Supplier = Me.Supplier
!Email1 = Me.Email1
!Customer = Me.Customer
![EN No] = Me.[EN No]
![Delivery Address] = Me.[Delivery Address]
!Contact = Me.Contact
!Telephone = Me.Telephone
!MethodSent = Me.MethodSent
!POStatus = Me.POStatus
![Requested by] = Me.[Requested by]

'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 = [Supplier]

'Duplicate the related records: append query.
If Me.[PO Order Items Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [PO Order Items] ( PO No, Qty, Description, [Supplier ID], ChgComments, Combo8, Period, ChargeOn ) " & " SELECT " & lngID & ",Qty, Description, " & lngID2 & ", ChgComments, Combo8, Period, ChargeOn " & " FROM [PO Order Items] WHERE ([PO No] = " & Me.[ID] & " And [supplier id] = " & Me.[Supplier] & ");"

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

Any advice as to where I've gone wrong would be Very Very much appreciated

 

[Uncle Gizmo]

See on Allen Browne's web site HERE:

Duplicate the record in form and subform

 

[pbaldy]

Let's learn how to fish. Add this line right before the line that fails:

Debug.Print strSql

and run the code. That will print out the finished SQL to the VBA Immediate window. You can examine the SQL there and see if you spot the problem. If you don't, copy/paste the SQL into a blank query in SQL view and try to run it. You'll often get a more descriptive error there. If you're still stuck, post the SQL here.

 

[Sarella]

Thank you so much Paul!!

I didn't know that immediate window existed, and after using that and the Debug.Print strSql realised where my code was going wrong ( it needed to have all the fields in the table referenced).

Excellent. Ridiculously happy now!! lol

Thanks again :O)

 

[pbaldy]

Happy to help!