Solved

Access listbox's rowsource: error Too many line continuations

Posted on 2014-11-19
9
264 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

777 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