Putting records from a table into a list box, using VBA code

[itmasterw]

Hi,
I am trying to take the records that are created in the first table, that I create here ( which does work), and enter the records into a listbox. With all the things I tryed I did once get the first rcord in, but that was it; and now I cannnot seem to gret that anymore. Any idesas on what I am soing wrong here, would be appreaciated.
Thank You

Here is the code I have so far.


Dim sList As String
Dim sQuery As String
Dim r1 As Recordset

On Error Resume Next
Err.Number = 0
CurrentDb.Execute ("DROP TABLE [testTable]")
Err.Number = 0

CurrentDb.Execute ("SELECT [Survey database].Customer, [Survey database].User, [User Id].Name, [Survey database].Department INTO testTable FROM [Survey database] INNER JOIN [User Id] ON [Survey database].User = [User Id].[User ID]WHERE [User Id].[User ID] = '" & [Combo2] & "'")

Set r1 = CurrentDb.OpenRecordset("SELECT [Survey database].Customer, [Survey database].User, [User Id].Name, [Survey database].Department FROM [Survey database] INNER JOIN [User Id] ON [Survey database].User = [User Id].[User ID]WHERE [User Id].[User ID] = '" & [Combo2] & "'")
Do Until r1.EOF
Me!lstOPut.RowSourceType = "Tables/Query"
Me!lstOPut = r1!Customer & "" & r1!User &" "& r1!Name &" "& r1!Department
Me!lstOPut.Requery
Loop

 

[RoyVidar]

In stead of opening the recordset, assign the sql:

[tt]Me!lstOPut.RowSource = "Select [Survey database].Customer, [Survey database].User ... and the rest of the select... [/tt]

BTW - I think the rowsourcetype is table/query (singular) not tables/query (plural).

Roy-Vidar

 

[itmasterw]

Thank you that worked but it is ony puting in the the first field (the Customers name). How come it is not listing all of the fields?

 

[RoyVidar]

Have a look at the column count and column width properties of the list (4 and 1,1,1,1?)

Roy-Vidar

 

[itmasterw]

I eill look into that, but I also noticed that whaile this did give me the right set of records, If I click on the combobox again it will no give the records associated with that click, it just stays on the orinal set. If I close out and do it again it works, buit then it sits there again. Any ideas on how I can rest it or something.
Thanks

 

[PHV]

Set Me!lstOPut.RowSource in the AfterUpdate event procedure of Combo2.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244