Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Populating a listbox to sort it

    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?
    Last edited by weyrman; 2014-05-18 at 23:41.
    "Heading for the deep end"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    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;"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •