Solved

Access listbox's rowsource: error Too many line continuations

Posted on 2014-11-19
9
266 Views
Last Modified: 2014-11-19
Experts:

I need assistance with fixing a VBA error "Too many line continuations".

In a form, I'm using a listbox which row source is shown in the code below.   Ideally, I'd like to display 42 values in the listbox.   It appears there a limit to the menu options (based on the line break ' & _ ').   That is, once I keep on adding additional row source values, the VBA error is thrown.

How can this be fixed?   Thanks!
EEH

Me.ListBox.RowSource =  "Q01;" & _
                                              "Q02;" & _
			                      "Q03;" & _
			                      "Q04;" & _
			                      "Q05;" & _
			                      "Q06;" & _
			                      "Q07;" & _
			                      "Q08;" & _
			                      "Q09;" & _
			                      "Q10;" & _
			                      "Q11;" & _
			                      "Q12;" & _
			                      "Q13;" & _
			                      "Q14;" & _
			                      "Q15;" & _
			                      "Q16;" & _
			                      "Q17;" & _
			                      "Q18;" & _
			                      "Q19;" & _
			                      "Q20;" & _
			                      "Q21;" & _
			                      "Q22;" & _
			                      "Q23;" & _
			                      "Q24;" & _
			                      "Q25;"

Open in new window

0
Comment
Question by:ExpExchHelp
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 40452158
wha t if you do it like this

Me.ListBox.RowSource =  "Q01;Q02;Q03;Q04;Q05;" & _
                              "Q06;Q07;Q08;Q09;Q10;" & _
                              "Q11;Q12;Q13;Q14;Q15;" & _
                              "Q16;Q17;Q18;Q19;Q20;" & _
                              "Q21;Q22;Q23;Q24;Q25;"
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 40452162
Or you could use something like:

For intLoop = 1 to 25
    strSource = strSource & ";Q" & Format(intLoop, "00")
Next
me.listbox.RowSource = Mid(strSource,2)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452189
You would use a callback function for this:
Public Function ListNumbers( _
  ctl As Control, _
  lngId As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant
  
  Dim varValue      As Variant

  Select Case intCode
    Case acLBInitialize
      varValue = True             ' True to initialize.
    Case acLBOpen
      varValue = Timer            ' Autogenerated unique ID.
    Case acLBGetRowCount          ' Get rows.
      varValue = 42               ' Set number of rows.
    Case acLBGetColumnCount       ' Get columns.
      varValue = 1                ' Set number of columns.
    Case acLBGetColumnWidth       ' Get column width.
      varValue = -1               ' Use default width.
    Case acLBGetValue             ' Get the data.
      varValue = Format(lngRow + 1, "\Q00")
    Case acLBGetFormat            ' Format the data.
      ' varValue = strFormat
    Case acLBEnd
      ' Do something when form with listbox closes or
      ' listbox is requeried.
  End Select
  
  ' Return Value.
  ListNumbers = varValue

End Function

Open in new window

Now, set for the ListBox the property:

  RowSourceType: ListNumbers

/gustav
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:ExpExchHelp
ID: 40452196
Roy:

Thanks... it looked like a very promising approach.   However, based on the approach (for which you assisted me last week), it won't work.

That is, in the ListBox_AfterUpdate() event, I'm using the following code as part of a strGenericField.

So, if the VBA is written as "Test Issue Q01; Test Issue Q02; Test Issue Q03; Test Issue Q04; Test Issue Q05;" & _"

then in the AfterUpdate event, the following line of code doesn't find the field (parameter error).

       Select Case Me.ListBoxStatistics
            Case "Test Issue Q01": strGenericField = "Q01"
            Case "Test Issue Q02": strGenericField = "Q02"
            etc. etc.

<Br>

Dale -- I'm checking out your solution right now.    Based on the above response, it may not work either... 'didn't anticipate this domino effect.

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 40452205
Dale:

Sorry... your proposed version probably would work under different conditions.   I thought I'd streamline the menu options (in the example posting).

However, the rowsource value do differ quite a bit.   For example, I also include a reference to test issues.   The next two lines show an actual example.

    Me.ListBoxStatistics.RowSource = "Test sub-issue '1.1.1.1' (Q07);" & _
                                                                 "Test sub-issue '1.1.2.1' (Q09);" & _
                                                                 etc. etc.
0
 

Author Comment

by:ExpExchHelp
ID: 40452208
Gustav -- thanks for chiming in... previous comment appears to apply to your recommendation as well.  Again, I didn't anticipate the trickle-down effect.

EEH
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452214
Well, how could we know?
May I suggest you list your full requirements, not bit by bit.

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40452229
<the following line of code doesn't find the field (parameter error).>

because it was not written properly per your scenario
0
 

Author Comment

by:ExpExchHelp
ID: 40452249
I understand... as mentioned before, I didn't anticipate the secondary effect.   I'll split up the test issues into 4 different listboxes.   Not ideal (with respect to maintenance) but not big deal.

Thanks for chiming in... I know your solution would have worked otherwise.

EEH
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question