Continue long list of code to next line

[jack_attridge]

Hi guys,

I have manipulated the duplicate record code found online to suit my purposes. It works for all the other subforms, however this subform has too many fields so the code goes onto the next line. How do I solve this? Online it says using "& _ " but I can't get it to work. Could anyone type a suitable example for me to used please? There's basically too many fields to fit on one line so I need to continue the code onto the next line

Code with end of line problems highlighted in red:

If Me.[subformEmissionsControlTarget].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblEmissionsControlTarget ( System_ID_No, EmissionsStandard, OtherEmissionsStandard, IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke, SteadyStateEngineeringLimitSmoke, SteadyStateLegislativeLimitNOx, SteadyStateEngineeringLimitNOx, SteadyStateLegislativeLimitHC, SteadyStateEngineeringLimitHC, SteadyStateLegislativeLimitHC+NOx, SteadyStateEngineeringLimitHC+NOx, SteadyStateLegislativeLimitCO, SteadyStateEngineeringLimitCO, SteadyStateLegislativeLimitCO2, SteadyStateEngineeringLimitCO2, SteadyStateLegislativeLimitPM, SteadyStateEngineeringLimitPM, TransientCycle, TransientEngineeringLimitType, TransientLegislativeLimitSmoke, TransientEngineeringLimitSmoke, TransientLegislativeLimitNOx, TransientEngineeringLimitNOx, TransientLegislativeLimitHC, TransientEngineeringLimitHC, [TransientLegislativeLimitHC+NOx],
[TransientEngineeringLimitHC+NOx], TransientLegislativeLimitCO, TransientEngineeringLimitCO, TransientLegislativeLimitCO2, TransientEngineeringLimitCO2, TransientLegislativeLimitPM, TransientEngineeringLimitPM, OtherCycle, OtherEngineeringLimitType, OtherLegislativeLimitSmoke, OtherEngineeringLimitSmoke, OtherLegislativeLimitNOx, OtherEngineeringLimitNOx, OtherLegislativeLimitHC, OtherEngineeringLimitHC, [OtherLegislativeLimitHC+NOx], [OtherEngineeringLimitHC+NOx], OtherLegislativeLimitCO, OtherEngineeringLimitCO, OtherLegislativeLimitCO2, OtherEngineeringLimitCO2, OtherLegislativeLimitPM, OtherEngineeringLimitPM ) " & _
"SELECT " & lngID & " As NewID, EmissionsStandard, OtherEmissionsStandard, IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke, SteadyStateEngineeringLimitSmoke, SteadyStateLegislativeLimitNOx, SteadyStateEngineeringLimitNOx, SteadyStateLegislativeLimitHC, SteadyStateEngineeringLimitHC, [SteadyStateLegislativeLimitHC+NOx], [SteadyStateEngineeringLimitHC+NOx], SteadyStateLegislativeLimitCO, SteadyStateEngineeringLimitCO, SteadyStateLegislativeLimitCO2, SteadyStateEngineeringLimitCO2, SteadyStateLegislativeLimitPM, SteadyStateEngineeringLimitPM, TransientCycle, TransientEngineeringLimitType, TransientLegislativeLimitSmoke, TransientEngineeringLimitSmoke, TransientLegislativeLimitNOx, TransientEngineeringLimitNOx, TransientLegislativeLimitHC, TransientEngineeringLimitHC, [TransientLegislativeLimitHC+NOx],
[TransientEngineeringLimitHC+NOx], TransientLegislativeLimitCO, TransientEngineeringLimitCO, TransientLegislativeLimitCO2, TransientEngineeringLimitCO2, TransientLegislativeLimitPM, TransientEngineeringLimitPM, OtherCycle, OtherEngineeringLimitType, OtherLegislativeLimitSmoke, OtherEngineeringLimitSmoke, OtherLegislativeLimitNOx, OtherEngineeringLimitNOx, OtherLegislativeLimitHC, OtherEngineeringLimitHC, OtherLegislativeLimitHC+NOx, OtherEngineeringLimitHC+NOx, OtherLegislativeLimitCO, OtherEngineeringLimitCO, OtherLegislativeLimitCO2, OtherEngineeringLimitCO2, OtherLegislativeLimitPM, OtherEngineeringLimitPM " & _
"FROM tblEmissionsControlTarget WHERE System_ID_No = " & Me.System_ID_No & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

Thank you for any help

 

[namliam]

when posting (portions of) code on the forum, please use the code wraps [ code ] code here [ /code ] without the spaces so it is displayed more appropriately.

The way you are doing it already is good but you probably need to do it more often...
or use the "other" way of concatting sql code... which has my personal preference.

       If Me.[subformEmissionsControlTarget].Form.RecordsetClone.RecordCount > 0 Then
            strSql = ""
            strSql = strSql & "INSERT INTO tblEmissionsControlTarget ( System_ID_No, EmissionsStandard, OtherEmissionsStandard,"
            strSql = strSql & " IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, "
            strSql = strSql & " EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke, "
            strSql = strSql & " SteadyStateEngineeringLimitSmoke, SteadyStateLegislativeLimitNOx, SteadyStateEngineeringLimitNOx, SteadyStateLegislativeLimitHC,"
            strSql = strSql & " SteadyStateEngineeringLimitHC, SteadyStateLegislativeLimitHC+NOx, SteadyStateEngineeringLimitHC+NOx, ...."
etc....

This IMHO keeps the code more readable and managable than using the & _ concatination method.

 

[jack_attridge]

Thanks, that was exactly what I needed. I agree that your way is much easier to read and consequently debug.