artgal
asked on
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
rst.MoveNext
Loop
PayorDD.RowSource = payorlist
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
rst.MoveNext
Loop
PayorDD.RowSource = payorlist
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"