How do I get around the line limit in vba code?

JackieEVSC

Registered User.
Local time
Today, 01:58
Joined
Feb 24, 2012
Messages
26
I have an insert into statement that is 1,082 characters. Is there a way to get around the line limit? I've tried using " _" in the code to signal a continuation on the next line, but it doesn't like it. What are my options?

Thanks.
 
"It doesn't like it" isn't very descriptive. What is your attempted code? It should look like this:

Code:
    strSQL = "SELECT ResNum, ReqDateTime FROM tblReservations " _
           & "WHERE AcctID = " & Me.Cust_key & " AND ReqDateTime > Date() " _
           & "ORDER BY ReqDateTime"
 
What did you do exactly. NOt a story but show your attempt
 
The code that falls to the second line is red, and of course, that's where the code fails (that's what I mean by "it doesn't like it"). Even breaking the code with a " _" makes the subsequent lines red.

Here is my code ...

strSQL = "INSERT INTO RepairInfo(FirstName,LastName,StudentID,StudentSite,School,PrevTicketNum,AssetNumber,Ass,SvcTag,Svc,EquipDesc,DamageDesc,PrevBackFromDD,NotesDowntown,NotesDesktopDr,PrevDDWO,Drop,Spill,OtherType,LCD,LCDOther,Palmrest,Palm-Repair,Bezel,Keyboard,BottomPlas,Btm-Repair,TopPlas,Top-Repair,Harddrive,Webcam,Battery,Hinges,Screws,OtherDamage) VALUES(" & "'" & Text4 & "'," & "'" & Text12 & "'," & "'" & Text6 & "'," & "'" & Text83 & "'," & "'" & Text83 & "'," & "'" & Text1 & "'," & "'" & Text8 & "'," & "'" & Text8 & "'," & "'" & Text14 & "'," & "'" & Text14 & "'," & "'" & Text68 & "'," & "'" & Text55 & "'," & "'" & Text80 & "'," & "'" & Text71 & "'," & "'" & Text73 & "'," & "'" & DDWO & "'," & "'" & Check43 & "'," & "'" & Check45 & "'," & "'" & Check47 & "'," & "'" & Check19 & "'," & "'" & Check201 & "'," & "'" & Check21 & "'," & "'" & Check23 & "'," & "'" & Check25 & "'," & "'" & Check27 & "'," & "'" & Check29 & "'," & "'" & Check31 & "'," & "'" & Check33 & "'," & "'" & Check35 & "'," & "'" & Check37 & "'," & "'" & Check39 & "'," & "'" & Check41 & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Dim TicketMax As Long
TicketMax = DMax("[TicketNum]", "RepairInfo", "[AssetNumber] = Text8")

DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & TicketMax

DoCmd.Close acForm, "frm_RepairInfo"
 
Did you try breaking it up with line continuations as demonstrated in my first response?
 
Yes, I put the underline before the VALUES, and both lines of code turn red.

Dim strSQL As String
strSQL = "INSERT INTO RepairInfo(FirstName,LastName,StudentID,StudentSite,School,PrevTicketNum,AssetNumber,Ass,SvcTag,Svc,EquipDesc,DamageDesc,PrevBackFromDD,NotesDowntown,NotesDesktopDr,PrevDDWO,Drop,Spill,OtherType,LCD,LCDOther,Palmrest,Palm-Repair,Bezel,Keyboard,BottomPlas,Btm-Repair,TopPlas,Top-Repair,Harddrive,Webcam,Battery,Hinges,Screws,OtherDamage) _
VALUES(" & "'" & Text4 & "'," & "'" & Text12 & "'," & "'" & Text6 & "'," & "'" & Text83 & "'," & "'" & Text83 & "'," & "'" & Text1 & "'," & "'" & Text8 & "'," & "'" & Text8 & "'," & "'" & Text14 & "'," & "'" & Text14 & "'," & "'" & Text68 & "'," & "'" & Text55 & "'," & "'" & Text80 & "'," & "'" & Text71 & "'," & "'" & Text73 & "'," & "'" & DDWO & "'," & "'" & Check43 & "'," & "'" & Check45 & "'," & "'" & Check47 & "'," & "'" & Check19 & "'," & "'" & Check201 & "'," & "'" & Check21 & "'," & "'" & Check23 & "'," & "'" & Check25 & "'," & "'" & Check27 & "'," & "'" & Check29 & "'," & "'" & Check31 & "'," & "'" & Check33 & "'," & "'" & Check35 & "'," & "'" & Check37 & "'," & "'" & Check39 & "'," & "'" & Check41 & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Dim TicketMax As Long
TicketMax = DMax("[TicketNum]", "RepairInfo", "[AssetNumber] = Text8")

DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & TicketMax

DoCmd.Close acForm, "frm_RepairInfo"
 
Look again at my example. You didn't end the string, or concatenate.
 
split into multiple sections, and concat. A string is virtually unlimited in length.

mystrg = "first bit "
mystrg = mystrg & " second bit "
mystrg = mystrg & " third bit "
 
I'm still getting a compile error ...
strSQL = "INSERT INTO RepairInfo(FirstName,LastName,StudentID,StudentSite,School,PrevTicketNum,AssetNumber,Ass,SvcTag,Svc,EquipDesc,DamageDesc,PrevBackFromDD,NotesDowntown,NotesDesktopDr,PrevDDWO,Drop,Spill,OtherType,LCD,LCDOther,Palmrest,Palm-Repair,Bezel,Keyboard,BottomPlas,Btm-Repair,TopPlas,Top-Repair,Harddrive,Webcam,Battery,Hinges,Screws,OtherDamage) " _
VALUES(" & "'" & Text4 & "'," & "'" & Text12 & "'," & "'" & Text6 & "'," & "'" & Text83 & "'," & "'" & Text83 & "'," & "'" & Text1 & "'," & "'" & Text8 & "'," & "'" & Text8 & "'," & "'" & Text14 & "'," & "'" & Text14 & "'," & "'" & Text68 & "'," & "'" & Text55 & "'," & "'" & Text80 & "'," & "'" & Text71 & "'," & "'" & Text73 & "'," & "'" & DDWO & "'," & "'" & Check43 & "'," & "'" & Check45 & "'," & "'" & Check47 & "'," & "'" & Check19 & "'," & "'" & Check201 & "'," & "'" & Check21 & "'," & "'" & Check23 & "'," & "'" & Check25 & "'," & "'" & Check27 & "'," & "'" & Check29 & "'," & "'" & Check31 & "'," & "'" & Check33 & "'," & "'" & Check35 & "'," & "'" & Check37 & "'," & "'" & Check39 & "'," & "'" & Check41 & "');"
 
As you can probably tell by my code, I'm not much of a programmer, so I'm not sure how to break it into multiple sections and concatenate.
 
Not to be late to and crap on the party, but I smell an improperly structured database. Tell me if I'm wrong and I'll leave, but..

LCD,L CDOther,Palmrest,Palm-Repair,Bezel,Keyboard,BottomPlas,Btm-Repair,TopPlas,Top-Repair,Harddrive,Webcam,Battery,Hinges,Screws,Othe rDamage

Those are all Yes/No fields right? And they designate a component that was damaged, right? If yes to both, you need a new table, and a new form setup.
 
I'm not sure how to break it into multiple sections and concatenate.

I showed how in post 2. Break it into smaller pieces, so you see each entire line on the screen. Note the quotes, line continuations and ampersands in my example.
 
When you insert a line continuation in the middle of a string, you need concatenation and double quotes on the next line
ie
.....Screws,Othe rDamage) " _
& "VALUES(" & "'" .......

And even if the code compiled, there are SQL errors with either typos or missing square brackets in the field list, which will give run time errors.

eg
....,StudentSit e,...
....,L CDOther,...
.....,Othe rDamage)
 
Pbaldy and Cronk, it's working now. Thanks for your help.

Plog ... yes, it's definitely structured improperly. I knew when I posted my code that I was going to be called out on it (and rightfully so!). I created this database several years ago when I knew even less than I do now! Every time I think I finally have time to work on it, something comes up and I have to stop.
 
Even with inflation and a weak dollar an ounce of prevention is still worth...
 

Users who are viewing this thread

Back
Top Bottom