[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • 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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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