?
Solved

Access listbox's rowsource: error Too many line continuations

Posted on 2014-11-19
9
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 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 51

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 51

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

771 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