Solved

Access listbox's rowsource: error Too many line continuations

Posted on 2014-11-19
9
254 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 119

Accepted Solution

by:
Rey Obrero earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ExpExchHelp
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ExpExchHelp
Comment Utility
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
Comment Utility
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
Comment Utility
Well, how could we know?
May I suggest you list your full requirements, not bit by bit.

/gustav
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now