Results 1 to 11 of 11
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    VBA statement has too many line continuations

    Hello Experts:

    I need some assistance with addressing a SQL statement -- when inserted into VBA -- results in "too many line continuations".

    First, allow me to provide the SQL statement which works fine when inserting five (5) fields: IncidentID, IncidentDate, Category, CommandName, UIC

    Again, the below example works fine!

    Code:
        strSQL = "INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, IncidentDate, Category, CommandName, UIC ) SELECT [000_tblRawData].IncidentID, [000_tblRawData].IncidentDate, [000_tblRawData].Category, [000_tblRawData].CommandName, [000_tblRawData].UIC FROM 000_tblRawData"
        
        'Build the IN string by looping through the listbox
        For i = 0 To ListBoxActions1.ListCount - 1
            If ListBoxActions1.Selected(i) Then
                If ListBoxActions1.Column(0, i) = "All" Then
                    flgSelectAll = True
                End If
                strIN = strIN & "'" & ListBoxActions1.Column(0, i) & "',"
            End If
         Next i
         
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Category] in (" & Left(strIN, Len(strIN) - 1) & ")"
        
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            strSQL = strSQL & strWhere
        End If
        
        MyDB.QueryDefs.Delete "qryAppendSelectedCategories"
        Set qdef = MyDB.CreateQueryDef("qryAppendSelectedCategories", strSQL)
    Now, I transitioned from this example code to my actual code. Ultimately, I need to append records from 38 TEMP table to a MASTER TABLE. Each of the 38 TEMP table has a different FIELD structure... that said, the MASTER table needs has nearly 235 fields as "place holders".



    Right now, when updating the above SQL code to include the 235 fields in the INSERT / SELECT statement, you only can imagine the length of the code. In an ASCII file, I use the underscore character "_" to separate the lines. However, when copying the VBA statement from Notepad into Access, I get the "too many line continuations" error.

    My question:

    The only thing that changes is the WHERE CLAUSE... is there a way that I simply save my "qryAppendSelectedCategories" (w/o deleting it) and when the users selects different categories from the Listbox, the "Append's" [CATEGORY] field is updated (w/o having to include the strSQL) statement in the VBA.

    Thanks for any recommendations.

    EEH
    Last edited by skydivetom; 02-02-2019 at 01:53 PM. Reason: Attachment

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Instead of QueryDefs modifying query object, just run the action query in VBA.

    CurrentDb.Execute strSQL
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7:

    Thank you for the response.

    Please see attached **sample** file "MultiSelect". This database example includes only the five fields (IncidentID, IncidentDate, Category, CommandName, UIC). Again, the actual file includes 230+ fields in the append query.

    So, how does the VBA change so that the "selection of categories" in form "frmSelectCategory" update the criteria in [CATEGORY] of qryAppendSelectedCategories?

    Thank you for your help in advance.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I am guessing you already attempted building SQL statement in VBA and got the 'too many line continuations' error. So the workaround is to build the SQL statement in multiple steps.

    Shorten the string by not repeating the table name in the SELECT:

    SELECT IncidentID, IncidentDate, Category, CommandName, UIC FROM 000_tblRawData

    Then, when the SQL string is constructed, execute it in VBA instead of using QueryDefs to modify query object.

    230 fields is a LOT. Never seen an INSERT use so many.

    AFAIK, cannot change the WHERE clause in a query object without replacing the entire query. But don't have to delete and recreate the object.

    CurrentDb.QueryDefs("qryAppendSelectedCategories") .SQL = strSQL
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Don't really understand the 38 TEMP table terminology or the 235 fields a place holders, but I can address the 'too many line continuations' in the sql string. Do this:
    Dim sSQLFinal as string
    Dim sSQL1 as string
    Dim sSQL2 as string
    Dim sSQL3 as string
    etc.
    sSQL1 = "build your string with a dozen or so continuations"
    sSQL2 = "continue the string entries with more continuations"
    ...and so on until all entries are completed, then
    sSQLFinal = ssql1 & ssql2 & ssql3 ....

    The resulting sSQLFinal will not have ANY continuations.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    It can be done with only 1 variable.

    strSQL = "SELECT field1, field2, field3, " & _
    "field4, field5, field6, "
    strSQL = strSQL & "field7, field8, field9, " & _
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by June7 View Post
    It can be done with only 1 variable.

    strSQL = "SELECT field1, field2, field3, " & _
    "field4, field5, field6, "
    strSQL = strSQL & "field7, field8, field9, " & _
    ...
    Yep, that'll work too.

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    davegri:

    Thank you for the great recommendation... at this time, I'm experiencing some syntax issues.

    Below code (copied into SQL query) executes fine:
    Code:
    INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, DTG, IncidentDate, IncidentTime, 
    Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate )
    SELECT IncidentID, DTG, IncidentDate, IncidentTime, 
    Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate
    FROM 000_tblRawData
    WHERE ((([000_tblRawData].Category) In ('Assault')));

    Now, I'm trying to convert this working SQL statements into multiple VBA lines to be concatenated. At this time, I'm testing the splitting and concatenation of SQL statements with only a few fields.


    Code:
        strSQL_00_Gen2 = "INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, DTG, IncidentDate, IncidentTime, " _
                       & "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate )"
        
        strSQL_00_Gen3 = "SELECT IncidentID, DTG, IncidentDate, IncidentTime, " _
                       & "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate "
        
        strSQL_00_Gen4 = "FROM 000_tblRawData"
        
        
        SQLFinal = strSQL_00_Gen2 & strSQL_00_Gen3 & strSQL_00_Gen4
    ... the above lines are written before the WHERE clause which never changed and should be working:

    Code:
        'Build the IN string by looping through the listbox
        For i = 0 To ListBoxActions1.ListCount - 1
            If ListBoxActions1.Selected(i) Then
                If ListBoxActions1.Column(0, i) = "All" Then
                    flgSelectAll = True
                End If
                strIN = strIN & "'" & ListBoxActions1.Column(0, i) & "',"
            End If
         Next i
         
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Category] in (" & Left(strIN, Len(strIN) - 1) & ")"
        
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            'strSQL = strSQL & strWhere
            SQLFinal = SQLFinal & strWhere
        End If

    Finally, now when selecting values from the form's listbox, a dialogue box popped up indicating "Item not found in this collection" (which is an error message that I coded).

    Unless I'm mistaking, I believe the error lies within the four concatenated SQL statements:
    SQLFinal = strSQL_00_Gen2 & strSQL_00_Gen3 & strSQL_00_Gen4

    What am I missing? Are there unnessary blank space or did I do the next line coding with underscore and ampersand incorrectly?

    Thanks,
    EEH

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you tried Debug.print with all your sql statements to see what they actually resolve to?

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You can keep the whole static string of WHERE clause out of code in a text file that you can input in your code with a few lines. Using wildcards in the text file, you can replace them with the actual values in your code with some easy String methods.

    To be clear, at your example...

    Copy and paste in Notepad this piece of text:
    WHERE ((([Category]) In ($$Category)));
    Save it as "WHERE.txt" into the folder of your sample project.

    Add a Texbox named Category in frmSelectCategory and use the code bellow for the cmdButtonSelectCategories:
    Code:
    Private Sub cmdButtonSelectCategories_Click()
    
        On Error GoTo Err_cmdButtonSelectCategories_Click
        Dim MyDB As DAO.Database
        Dim tdfFromTable As DAO.TableDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strIN As String
        Dim flgSelectAll As Boolean
        Dim varItem As Variant
        Dim intFile As Integer
    
        Set MyDB = CurrentDb()
        'Take an instance of table "000_tblRawData"
        Set tdfFromTable = MyDB.TableDefs("000_tblRawData")
        CurrentDb.Execute "DELETE * FROM 001_tblRawData_SelectedCategories;", dbFailOnError
        
        'Build the INSERT INTO and SELECT clause using the Fields collection of the table "000_tblRawData"
        With tdfFromTable
            For i = 0 To .Fields.Count - 1
                strSQL = strSQL & .Fields(i).Name & ", "
            Next i
        End With
        
        'strSQL = "INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, IncidentDate, Category, CommandName, UIC ) SELECT [000_tblRawData].IncidentID, [000_tblRawData].IncidentDate, [000_tblRawData].Category, [000_tblRawData].CommandName, [000_tblRawData].UIC FROM 000_tblRawData"
        strSQL = Left(strSQL, Len(strSQL) - 2)
        strSQL = "INSERT INTO 001_tblRawData_SelectedCategories ( " & strSQL _
                 & ") SELECT " & strSQL & " FROM " & tdfFromTable.Name
    
        'Build the IN string by looping through the listbox
        For i = 0 To ListBoxActions1.ListCount - 1
            If ListBoxActions1.Selected(i) Then
                If ListBoxActions1.Column(0, i) = "All" Then
                    flgSelectAll = True
                    Exit For
                End If
                strIN = strIN & "'" & ListBoxActions1.Column(0, i) & "',"
            End If
        Next i
        'Strip off the last comma
        strIN = Left(strIN, Len(strIN) - 1)
        'Keep the "IN" string in the a hidden textbox named Category
        Me.Category = strIN
    
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            intFile = FreeFile
            'Open the text file that contains the "WHERE" string with wildcards instead of values
            Open CurrentProject.Path & "\WHERE.txt" For Binary As #intFile
            strWhere = Space(LOF(intFile))
            'Get the whole "WHERE" string
            Get #intFile, , strWhere
            'Close the text file
            Close intFile
            'Strip off the "BOF" characters
            strWhere = Mid(strWhere, 4)
            'Loops in table's Fields collection again
            With MyDB.TableDefs("000_tblRawData")
                On Error Resume Next
                For i = 1 To .Fields.Count
                    'Replace the wildcards with the values of the form's controls
                    'with the same name of the table field (if exists).
                    'In this case, the wildcard $$Category with the value of the hidden Textbox "Category".
                    strWhere = Replace(strWhere, "$$" & .Fields(i).Name, Me.Controls(.Fields(i).Name))
                Next i
                On Error GoTo Err_cmdButtonSelectCategories_Click
            End With
            strSQL = strSQL & " " & strWhere
        End If
        'Run the action queary
        MyDB.Execute strSQL, dbFailOnError
    
        'Clear listbox selection after running query
        For Each varItem In Me.ListBoxActions1.ItemsSelected
            Me.ListBoxActions1.Selected(varItem) = False
        Next varItem
        'Inform for succed append
        MsgBox (DCount("IncidentID", "001_tblRawData_SelectedCategories")) _
               & " records were successfully appended!", _
               vbInformation, "Step 1 - Import Routine"
    Exit_cmdButtonSelectCategories_Click:
        Exit Sub
    Err_cmdButtonSelectCategories_Click:
        If Err.Number = 5 Then
            MsgBox "You must make a selection(s) from the list", , "Selection Required !"
            Resume Exit_cmdButtonSelectCategories_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_cmdButtonSelectCategories_Click
        End If
    End Sub
    Keep building the WHERE string in the WHERE.txt file as you need for the rest of the fields.

    Regards,
    John

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Try it with the space added:
    Code:
        strSQL_00_Gen2 = "INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, DTG, IncidentDate, IncidentTime, " _
                       & "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate ) "
        
        strSQL_00_Gen3 = "SELECT IncidentID, DTG, IncidentDate, IncidentTime, " _
                       & "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate "
        
        strSQL_00_Gen4 = "FROM 000_tblRawData"
        
             SQLFinal = strSQL_00_Gen2 & strSQL_00_Gen3 & strSQL_00_Gen4
    Last edited by davegri; 02-02-2019 at 09:56 PM. Reason: formatting

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-22-2017, 07:14 PM
  2. Replies: 4
    Last Post: 05-17-2015, 06:58 AM
  3. Replies: 2
    Last Post: 03-23-2015, 08:32 AM
  4. Replies: 10
    Last Post: 06-10-2014, 09:03 AM
  5. Insert Into Statement with Line Numbers
    By Cheshire101 in forum Programming
    Replies: 1
    Last Post: 10-11-2010, 02:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums