Populating a listbox to sort it

Topic

New Reply

I have a listbox that is populated by the action of a combobox using the following rowsource:

Code:

SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments
FROM tblAllocate
WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False))
ORDER BY tblAllocate.Priority;

with RowSourceType set to Table/Query

I want to use this code:

Code:

Private Sub cmdMoveDown_Click()
Dim tempItem As String, tempIndex As Integer
 
    tempItem = lstJobs.Value
    tempIndex = lstJobs.ListIndex
 
    lstJobs.RemoveItem lstJobs.ListIndex
    lstJobs.AddItem tempItem, tempIndex + 1
    
    Call ToggleButtons(tempIndex + 1)
    
End Sub

Private Sub cmdMoveUp_Click()
Dim tempItem As String, tempIndex As Integer
 
    tempItem = lstJobs.Value
    tempIndex = lstJobs.ListIndex
 
    lstJobs.RemoveItem lstJobs.ListIndex
    lstJobs.AddItem tempItem, tempIndex – 1
    
    Call ToggleButtons(tempIndex – 1)
    
End Sub

to shuffle the items in the list as desired but I get a RowSourceProperty needs to be set to ValueList

OK, so I set it to ValueList and want to use this code to populate it.

Code:

Private Sub cboEmployees_AfterUpdate()
Dim dbs As DAO.Database
Dim strSQL As String

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ORDER BY tblAllocate.Priority;”

Set rst = CurrentDb.OpenRecordset(strSQL)
    With rst
       .MoveFirst
       Do Until rst.EOF
       lstJobs.AddItem tblAllocate.JobID & “;” & tblAllocate.Dept & “;” & tblAllocate.Hours & “;” & tblAllocate.Location
       .MoveNext
       Loop
    End With

End Sub

but I get a Too few parameters Expected 1 error on the set rst line.

So, where am I going wrong or is there a better way?

Reply | Quote

Replies

Change your sql to
strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate WHERE (((tblAllocate.Emp)=” & [Forms]![frmJobAllocationPage]![cboEmployees] & “) AND ((tblAllocate.Completed)=False)) ORDER BY tblAllocate.Priority;”

Reply | Quote