"Too many line continuations" error in MS Access 2010 for update

Question

I am having a problem with MS Access 2010 because an error "Too many line continuations" appear because of the limited 24 lines. What I am currently doing is a project with more than 24 lines in ms access vb for updating the record in the form.

I've already done some research but it is not related to what I am currently doing. How can I take off the limit of the lines in ms access vb? This is the sample code:

UPDATE:

I've already done what @Andre had recommended but an error occur that says "Syntax error in UPDATE statement". Any help? Here is the updated code.

Dim S As String, S1 As String, S2 As String, S3 As String, S4 As String, S5 
As String, S6 As String, S7 As String, S8 As String, S9 As String, S10 As 
String, S11 As String, S12 As String, S13 As String, S14 As String, S15 As 
String, S16 As String, S17 As String, S18 As String, S19 As String

S1 = "Update frm_NTE " & _
     " set EHRID = '" & Me.EHRID.Value & "'" & _
     ",NTEDate = '" & Me.NTEDate.Value & "'"
S2 = ",To = '" & Me.To.Value & "'" & _
     ",From = '" & Me.From.Value & "'" & _
     ",DatesWhenActsWasWereCommited = '" & Me.DatesWhenActsWasWereCommited.Value & "'"
S3 = ",DatesWhenActsWasWereDiscovered = '" & Me.DatesWhenActsWasWereDiscovered.Value & "'" & _
     ",PlacesWhereActsWasWereCommitted = '" & Me.PlacesWhereActsWasWereCommitted.Value & "'" & _
     ",DetailsOfTheActs = '" & Me.DetailsOfTheActs.Value & "'" & _
     ",cmdComboListt1 = '" & Me.cmdComboListt1.Value & "'"
S4 = ",Offense = '" & Me.Offense.Value & "'" & _
     ",ObjectOfViolation = '" & Me.ObjectOfViolation.Value & "'" & _
     ",ClassPenalty = '" & Me.ClassPenalty.Value & "'" & _
     ",CorrectiveActionPenalty = '" & Me.CorrectiveActionPenalty.Value & "'"
S5 = ",cmdComboListt2 = '" & Me.cmdComboListt2.Value & "'" & _
     ",Offense2 = '" & Me.Offense2.Value & "'" & _
     ",ObjectOfViolation2 = '" & Me.ObjectOfViolation2.Value & "'" & _
     ",ClassPenalty2 = '" & Me.ClassPenalty2.Value & "'"
S6 = ",CorrectiveActionPenalty2 = '" & Me.CorrectiveActionPenalty2.Value & "'" & _
     ",cmdComboListt3 = '" & Me.cmdComboListt3.Value & "'" & _
     ",Offense3 = '" & Me.Offense3.Value & "'" & _
     ",ObjectOfViolation3 = '" & Me.ObjectOfViolation3.Value & "'"
S7 = ",ClassPenalty3 = '" & Me.ClassPenalty3.Value & "'" & _
     ",CorrectiveActionPenalty3 = '" & Me.CorrectiveActionPenalty3.Value & "'" & _
     ",cmdComboListt4 = '" & Me.cmdComboListt4.Value & "'" & _
     ",Offense4 = '" & Me.Offense4.Value & "'"
S8 = ",ObjectOfViolation4 = '" & Me.ObjectOfViolation4.Value & "'" & _
     ",ClassPenalty4 = '" & Me.ClassPenalty4.Value & "'" & _
     ",CorrectiveActionPenalty4 = '" & Me.CorrectiveActionPenalty4.Value & "'" & _
     ",cmdComboListt5 = '" & Me.cmdComboListt5.Value & "'"
S9 = ",Offense5 = '" & Me.Offense5.Value & "'" & _
     ",ObjectOfViolation5 = '" & Me.ObjectOfViolation5.Value & "'" & _
     ",ClassPenalty5 = '" & Me.ClassPenalty5.Value & "'" & _
     ",CorrectiveActionPenalty5 = '" & Me.CorrectiveActionPenalty5.Value & "'"
S10 = ",cmdComboListt6 = '" & Me.cmdComboListt6.Value & "'" & _
      ",Offense6 = '" & Me.Offense6.Value & "'" & _
      ",ObjectOfViolation6 = '" & Me.ObjectOfViolation6.Value & "'" & _
      ",ClassPenalty6 = '" & Me.ClassPenalty6.Value & "'"
S11 = ",CorrectiveActionPenalty6 = '" & Me.CorrectiveActionPenalty6.Value & "'" & _
      ",cmdComboListt7 = '" & Me.cmdComboListt7.Value & "'" & _
      ",Offense7 = '" & Me.Offense7.Value & "'" & _
      ",ObjectOfViolation7 = '" & Me.ObjectOfViolation7.Value & "'"
S12 = ",ClassPenalty7 = '" & Me.ClassPenalty7.Value & "'" & _
      ",CorrectiveActionPenalty7 = '" & Me.CorrectiveActionPenalty7.Value & "'" & _
      ",IssuingManager = '" & Me.IssuingManager.Value & "'" & _
      ",AuthorizedHRPartner = '" & Me.AuthorizedHRPartner.Value & "'"
S13 = ",ApprovingManager = '" & Me.ApprovingManager.Value & "'" & _
      ",EmployeeName = '" & Me.EmployeeName.Value & "'" & _
      ",cmdFunction = '" & Me.cmdFunction.Value & "'" & _
      ",DateOfReceipt = '" & Me.DateOfReceipt.Value & "'"
S14 = ",TimeOfReceipt = '" & Me.TimeOfReceipt.Value & "'" & _
      ",EndorsedNTENoticeToHR = '" & Me.EndorsedNTENoticeToHR.Value & "'" & _
      ",EndorsedNTENoticeToHRLastUser = '" & Me.EndorsedNTENoticeToHRLastUser.Value & "'" & _
      ",EndorsedNTENoticeToHRLastDateModified = '" & Me.EndorsedNTENoticeToHRLastDateModified.Value & "'"
S15 = ",EndorsedNTENoticeToHRLastTimeModified = '" & Me.EndorsedNTENoticeToHRLastTimeModified.Value & "'" & _
      ",EndorsementOfNTEToIs = '" & Me.EndorsementOfNTEToIs.Value & "'" & _
      ",EndorsementOfNTEToISLastUser = '" & Me.EndorsementOfNTEToISLastUser.Value & "'" & _
      ",EndorsementOfNTEToISLastDateModified = '" & Me.EndorsementOfNTEToISLastDateModified.Value & "'"
S16 = ",EndorsementOfNTEToISLastTimeModified = '" & Me.EndorsementOfNTEToISLastTimeModified.Value & "'" & _
      ",DateReceivedNTEByTheEmployee = '" & Me.DateReceivedNTEByTheEmployee.Value & "'" & _
      ",DateReceivedNTEByTheEmployeeLastUser = '" & Me.DateReceivedNTEByTheEmployeeLastUser.Value & "'" & _
      ",DateReceivedNTEByTheEmployeeLastDateModified = '" & Me.DateReceivedNTEByTheEmployeeLastDateModified.Value & "'"
S17 = ",DateReceivedNTEByTheEmployeeLastTimeModified = '" & Me.DateReceivedNTEByTheEmployeeLastTimeModified.Value & "'" & _
      ",WrittenExplanationDueDate = '" & Me.WrittenExplanationDueDate.Value & "'" & _
      ",WrittenExplanationDueDateLastUser = '" & Me.WrittenExplanationDueDateLastUser.Value & "'" & _
      ",WrittenExplanationDueDateLastDateModified = '" & Me.WrittenExplanationDueDateLastDateModified.Value & "'"
S18 = ",WrittenExplanationDueDateLastTimeModified = '" & Me.WrittenExplanationDueDateLastTimeModified.Value & "'" & _
      ",SubmissionOfWEtoIS = '" & Me.SubmissionOfWEtoIS.Value & "'" & _
      ",SubmissionOfWEtoISLastUser = '" & Me.SubmissionOfWEtoISLastUser.Value & "'" & _
      ",SubmissionOfWEtoISLastDateModified = '" & Me.SubmissionOfWEtoISLastDateModified.Value & "'"
S19 = ",SubmissionOfWEtoISLastTimeModified = '" & Me.SubmissionOfWEtoISLastTimeModified.Value & "'" & _
      "Where CaseIDNo = " & Me.CaseIDNo.Value & ""

S = S1 & S2 & S3 & S4 & S5 & S6 & S7 & S8 & S9 & S10 & S11 & S12 & S13 & S14 & S15 & S16 & S17 & S18 & S19
Debug.Print S
CurrentDb.Execute S

That is a horrible query. Use parameters. This won't work like you intend it to. — Erik A, Apr 05 '18 at 09:37
_I've already done some research but it is not related to what I am currently doing._ I know this is probably lost in translation, but that sentence made me laugh. Had visions of you researching the plastic in the oceans problem rather than the line continuation problem. — Darren Bartrup-Cook, Apr 05 '18 at 09:38
That limit is present to prevent writing of incomprehensible code. — Gustav, Apr 05 '18 at 09:56
@DarrenBartrup-Cook actually this is my first time to use MS Access and I am not familiar with Visual Basic and I have no experience of creating any database until now that is why I am doing everything from scratch. The one I researched is about creating a table that has many strings and not to update and I have no idea what I am doing but as this is my requirement, I am trying my best. :) — New Guy, Apr 06 '18 at 01:45
@ErikvonAsmuth Actually at first, there is no problem because they only required me a few fields and it is with in the allowed lines. But as the system progress, they want to add more fields and I have no knowledge or formal education about programming so please don't judge my codes. Hahaha. :) — New Guy, Apr 06 '18 at 01:48
@NewGuy There's no problem in testing, but there will be when you need to enter single quotes in any field. See [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/7296893) for starters on using parameters, you should pretty much always use them. This whole long query can be made easier to maintain by just storing it as a query using form values as parameters, and that will be way easier to maintain. — Erik A, Apr 06 '18 at 10:11

score 0 · Answer 1 · answered Apr 05 '18 at 09:05

0

You can't remove that limit... you can just do less newlines and make longer lines

(and as an addon, your code is vulnerable to SQL injections as it is, but that would belong to a different question)

answered Apr 05 '18 at 09:05

Jcl

27,696
5
61
92

score 0 · Answer 2 · answered Apr 05 '18 at 09:22

If you really must do this, break it up into multiple strings.

Dim S As String, S1 As String, S2 As String, S3 As String ' ...

S1 = "Update frm_NTE " & _
     " set EHRID = '" & Me.EHRID.Value & "'" & _
     ",DateTodayy = '" & Me.DateTodayy.Value & "'" & _
     ",EHRID = '" & Me.EHRID.Value & " '" 
S2 = ",DateTodayy = '" & Me.DateTodayy.Value & " '" & _
     ",To = '" & Me.To.Value & " '" & _
     ",From = '" & Me.From.Value & " '"
     ' etc. pp.

S = S1 & S2
Debug.Print S
CurrentDb.Execute S

Note that you have several & " '" which are typos or bugs. Or do you want to append spaces?

I've done what you've recommended but a error occurred that says "Syntax error in UPDATE statement. — New Guy, Apr 12 '18 at 03:54

"Too many line continuations" error in MS Access 2010 for update

2 Answers2