Loading Listbox on Userform from Access recordset: Plese Help

  • Thread starter Thread starter staspe
  • Start date Start date
S

staspe

Put Result from query in Array; then take data from array and place in
listbox on userform




Dim w As Long
Dim k As Long
rs.MoveLast
w = rs.RecordCount
rs.MoveFirst
k = 0
frmList.ListBox1.ColumnCount = 2
Do Until rs.EOF
frmList.ListBox1.List(k, 0) = rs.Fields(0)
frmList.ListBox1.AddItem rs.Fields(0)
frmList.ListBox1.List(k, 1) = rs.Fields(1)
frmList.ListBox1.AddItem rs.Fields(1)
k = k + 1
If k = w Then
frmList.Show vbModal ' <============== show me a form
holding the data containing more than one record.
Else
rs.MoveNext
End If
Loop


But it is still returning

first 4 are correct

31248 barnes
31243 barnes
31245 barnes
31243 msc
31248 <-------------- these are not
barnes
31243
barnes
31245
barnes
31243
msc


Thanks
fordraiders
 
Put Result from query in Array; then take data from array and place in
listbox on userform




Dim w As Long
Dim k As Long
rs.MoveLast
w = rs.RecordCount
rs.MoveFirst
k = 0
frmList.ListBox1.ColumnCount = 2
Do Until rs.EOF
frmList.ListBox1.List(k, 0) = rs.Fields(0)
frmList.ListBox1.AddItem rs.Fields(0)
frmList.ListBox1.List(k, 1) = rs.Fields(1)
frmList.ListBox1.AddItem rs.Fields(1)
k = k + 1
If k = w Then
frmList.Show vbModal ' <============== show me a form
holding the data containing more than one record.
Else
rs.MoveNext
End If
Loop


But it is still returning

first 4 are correct

31248 barnes
31243 barnes
31245 barnes
31243 msc
31248 <-------------- these are not
barnes
31243
barnes
31245
barnes
31243
msc


Thanks
fordraiders
Click to expand...

It looks like you are using a built-in list box, but the AddItem
method is only used for a command bar combo box (an ActiveX control
which displays a combo box on a toolbar or menu). Access built-in list
boxes are not the same as Visual Basic list boxes, which do have such
a method, IIRC.

In order to populate the list box, you need to set the RowSource and
RowSourceType properties of the list box accordingly. In this case, it
would make sense to create a select query which returns the two
columns you intend to display, save the query and set the RowSource to
the name of that query, and RowSourceType to "Table/Query".
Alternatively, you can enter an SQL "SELECT..." statement directly as
the RowSource property.
 
Bob Hairgrove said:
It looks like you are using a built-in list box, but the AddItem
method is only used for a command bar combo box (an ActiveX control
which displays a combo box on a toolbar or menu). Access built-in list
boxes are not the same as Visual Basic list boxes, which do have such
a method, IIRC.
Click to expand...

Actually, Bob, the ListBox in Access 2003 supports AddItem. I can't remember
whether it did in Access 2002 as well.
 
Gentlemen, Here is a ruff scenario.
I'am using excel 2003..
I type a value in a cell(that is my variable)
I press the enter key
The variable is passed to an sql statement to open n access database.
What I need:
Once the sql statement is issued it brings the result into a listbox on
a Excel 2003 Userform.
Hope this helps!
Thanks for the replys very much...
 
Back
Top