3134 Error - Code works for other forms??

[Heatshiver]

I am having a problem with the code from Allen Browne's copy and paste for the main & subform (http://allenbrowne.com/ser-57.html). I have used this code successfully in one of my other forms. The only difference is the subform table and fields. This is the code as it is right now:

'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.

'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
            !UserID = Me.UserID
            !ProjectID = Me.ProjectID
            'etc for other fields.
            .Update

            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !GenSumPK

            'Duplicate the related records: append query.
            If Me.[fsubFluidSum].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblFluidSum] ( GenSumFK, [Mud Wgt], PV, YP, LGS%, [SG Solids], [SG Base Fluid], [Vol% Solids], [Vol% Oil], [Vol% Water], [Salt CaCl], [Mud Temp], [Oil/Water Ratio], [Brine Wgt], BrineType, [Salt Content] ) " & _
                         "SELECT " & lngID & " As NewID, MudType, [Mud Wgt], PV, YP, LGS%, [SG Solids], [SG Base Fluid], [Vol% Solids], [Vol% Oil], [Vol% Water], [Salt CaCl], [Mud Temp], [Oil/Water Ratio], [Brine Wgt], BrineType, [Salt Content] " & _
                         "FROM [tblFluidSum] WHERE GenSumFK = " & Me.GenSumFK & ";"
                Debug.Print strSql
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Data duplicated, but no past date was chosen to copy..."
            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, , "chkOldValues_Click"
    Resume Exit_Handler

I at first did not have brackets around the fields with spaces and thought this was the issue. But after fixing that, it still gives me the error. I know the error stems from somewhere in "strSql", I am just not sure what is the problem...

Any help would be much appreciated!

 

[JANR]

strSql = "INSERT INTO [tblFluidSum] ( GenSumFK, [COLOR=blue]MudType,[/COLOR] [Mud Wgt], PV, YP, [COLOR=red][[/COLOR]LGS%[COLOR=red]][/COLOR], [SG Solids], [SG Base Fluid], [Vol% Solids], [Vol% Oil], [Vol% Water], [Salt CaCl], [Mud Temp], [Oil/Water Ratio], [Brine Wgt], BrineType, [Salt Content] ) " & _
                         "SELECT " & lngID & " As NewID, MudType, [Mud Wgt], PV, YP, [COLOR=red][[/COLOR]LGS%[COLOR=red]][/COLOR], [SG Solids], [SG Base Fluid], [Vol% Solids], [Vol% Oil], [Vol% Water], [Salt CaCl], [Mud Temp], [Oil/Water Ratio], [Brine Wgt], BrineType, [Salt Content] " & _
                         "FROM [tblFluidSum] WHERE GenSumFK = " & Me.GenSumFK & ";"

See corrections:

Red -> Bad field name in SQL
Blue -> Missing field in insert

JR

 

[Heatshiver]

Thank you, I should've noticed the MudType, but didn't know about % signs.

 

[Galaxiom]

It is a bad idea to include any special character in an object name. Most developers avoid spaces too. It means you won't need the square brackets and it heads off a lot of time wasted typing and tracking down bugs.

I even avoid underscores.