• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Runtime error 2176

I'm trying to pull a list of values into a listbox and the concantenated length is greater than the listbox allows (something like 2000 characters I think).  I've read stuff that says to put it in a table but the underlying data changes daily so I suppose it has to be created dynamically.  Could somebody help me out with the syntax please?  Thanks! -j

strSQL = "SELECT DISTINCT a.CurrentPayorDesc "
strSQL = strSQL & "FROM RepGrp a ORDER BY a.CurrentPayorDesc;"
  Set rst = dB.OpenRecordset(strSQL)
  'PayorDD.RowSource = "All Payors"
  payorlist = "All Payors"

  i = 1
  Do Until rst.EOF
   tmp = rst.Fields(0)
   payorlist = payorlist & ";" & tmp
   'PayorDD.AddItem tmp, i
   i = i + 1
 PayorDD.RowSource = payorlist
  • 2
1 Solution
You should just put the SQL statement in the Row Source property of the listbox.

Right-click on the listbox in design view and select Properties to open up the Properties window.
Select the Data tab, select the Row Source Type property to be Table/Query, and put the following in the Row Source property:

SELECT DISTINCT CurrentPayorDesc FROM RepGrp ORDER BY CurrentPayorDesc

This should fill the listbox with items from the CurrentPayorDesc field in the table RegGrp.

If you need to do this in code:

PayorDD.RowSource = "SELECT DISTINCT CurrentPayorDesc FROM RepGrp ORDER BY CurrentPayorDesc"

If the listbox is not bound to a Control Source, you will need to make a call to the method ayorDD.Requery after setting the RowSource.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now