How to loop through table and display in Listbox?

[Ron]

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

Here is an example of the table:
USER ID TYPE PAYMENT
=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00

If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00

How would I do this?

thanks

 

[Stefan Hoffmann]

hi Ron,

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.

What do you mean exactly with "a table in SQL"?

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

lst.RowSource = "SELECT [TYPE], [PAYMENT] " & _
"FROM Table " & _
"WHERE [USER ID] = " & txtUserID.Value


mfG
--> stefan <--

 

[Ron]

hi Ron,

What do you mean exactly with "a table in SQL"?

I mean a table in sql server, i have a database setup in sqlserver and
have a table in there. I want to read all entries in the table for a
particular userID and then pop them in the listbox on my form in
access, I just have no idea how to do this.

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

lst.RowSource = "SELECT [TYPE], [PAYMENT] " & _
"FROM Table " & _
"WHERE [USER ID] = " & txtUserID.Value


mfG
--> stefan <--

 

[Guest]

Stefan's answer does exactly what you are looking for. You could do it using
recordset processing to loop through the table, create a string of the
entries you want divided by the ; the make that string the row source of your
list box, but that would be a lot more code and a lot slower.

hi Ron,

What do you mean exactly with "a table in SQL"?

I mean a table in sql server, i have a database setup in sqlserver and
have a table in there. I want to read all entries in the table for a
particular userID and then pop them in the listbox on my form in
access, I just have no idea how to do this.

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

lst.RowSource = "SELECT [TYPE], [PAYMENT] " & _
"FROM Table " & _
"WHERE [USER ID] = " & txtUserID.Value


mfG
--> stefan <--

 

[Van T. Dinh]

All you have to do is to create a Linked Table in your Access database that
points to the SQL Server Table and then follow Stefan's instruction.

If for some reason, you don't want to create the Linked Table, then create a
Recordset based on the Query / SQL and then set this Recordset as the
ListBox' Recorset.

 

[Guest]

How do you set the list box recordset to the ADO recordset?

rs_ADO.open = ...
me.listbox.recordset = rs_ADO

Corey.

 

[Van T. Dinh]

I think I have just replied to your thread "List Box rowsource = ADO query"
....