2 Recordsets update 1 list box ... Do I loop?

[tinymind]

Hello All,

I have a button on a form that populates a list box on the finding of a recortset.

'Start of process ...

Dim DB as Database
Dim SName as String
Dim rstOne As Recordset

Set DB = CurrentDb

SName = "Joe Bloggs"

Set rstOne = Db.OpenRecordset("Select FullName, Supervisor from tblUser where Supervisor = '" & Sname& "' &quot

Me.Listbox1.RowSource = ("Select Fullname, Supervisor from tblUser where Supervisor = '" & rstOne("Fullname&quot& "' &quot

'End of process ...

The problem is when this runs the Listbox only contains data for one of the records found within rstOne!

I need to show all the records that are found within the rstOne recordset that match the supervisor and show them within the listbox.

Do I loop ... if i do how do I append the records into the Listbox ... ???

I am fairly new to VBA so I could do with some guidance ...

Tiny Perfection is Everything
If it worked first time we wont be here!

 

[KenReay]

Hi

I do not follow why you need to open the recordset?

By doing this and using just that one row in your next SELECT which then returns just one record

Dim DB as Database
Dim SName as String
Dim rstOne As Recordset

SName = "Joe Bloggs"

Me.Listbox1.RowSource = ("Select Fullname, Supervisor from tblUser where Supervisor = '" & sName & "' &quot

'End of process ...

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[jebry]

Hi!

To get the result you are asking for set the rowsource to the first SQL string instead of opening a recordset with it.

That said, what exactly are you trying to accomplish here? If you are setting up a method for dynamically listing the employees under a certain supervisor (or some similar problem) then there is probably a better way to do it.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[tinymind]

Thanks for the replies ...

Sorry I don't think I have been clear enough ...

rstOne creates a list of more than one list of Users with supervisors ... that I can do!

Data Example:
User Super
Dave Paul
John Paul
Steve John
Emma Dave
Jane John

Take Paul for example ...

Paul is the supersor of Dave and John, but John is also a supervisor for Steve and Jane and Dave is Emma's supervisor

I want the list box to show Steve, Emma and Jane ...

I hope this is clearer?!

Tiny... Perfection is Everything
If it worked first time we wont be here!