Do Loop Problem 1

[stupiet]

I'm having problems with using a Do Loop in VBA for Access. I'm really new to making sql statements in VBA but my application made me use it since it needs to be dynamic. However, since I'm so new I do not know what I'm doing wrong or if I'm even on the right track.

What I am trying to do is display an employee list in a listbox that is based on a supervisor that logs in. Different supervisors can only see employees from a given department. This is what I did so far. sqlsearchlist is what gets displayed in the listbox. sqlSV is to open the QSupervisorViews Query that generates after a person logs in which states which department he or she can see. After that it will loop through those department and will keep adding on sqlsearchlist until the sql statement is done.Finally it will display the results in the listbox. Can't get this to work though so please help:

sqlsearchlist = "SELECT DISTINCT EMPLNUM, LASTNAME, FIRSTNAME, SUPERVISOR, EMPLDEPT FROM ListEmployees WHERE"

sqlSV = "SELECT * FROM QSupervisorViews"

Set rst = ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open sqlSV, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

sqlsearchlist = sqlsearchlist & " EMPLDEPT = VDEPT"
rst.MoveNext
Do Until rst.EOF
sqlsearchlist = sqlsearchlist & " Or EMPLDEPT = VDEPT"
rst.MoveNext
Loop

Me!searchlist.RowSource = sqlsearchlist
Me!searchlist.Requery

 

[traingamer]

You might try

sqlsearchlist = sqlsearchlist & " EMPLDEPT = '" & rst.VDEPT & "'"

or just a nested select like

sqlsearchlist = "SELECT DISTINCT EMPLNUM, LASTNAME, FIRSTNAME, SUPERVISOR, EMPLDEPT FROM ListEmployees where EMPLDEPT in (Select Distinct VDEPT From QSupervisorViews)"

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[stupiet]

Thank you! Thank you!
It works. I used the nested version and it does the trick. Don't know why exactly but it works!

Thanks

 

[PHV]

And why not simply a JOIN ?
sqlsearchlist = "SELECT DISTINCT E.EMPLNUM,E.LASTNAME,E.FIRSTNAME,E.SUPERVISOR,E.EMPLDEPT" _
& " FROM ListEmployees E INNER JOIN QSupervisorViews S ON E.EMPLDEPT=S.VDEPT"


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