Results 1 to 8 of 8
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    Populating Combobox rowsource with two columns

    In the past i have populated single column combo boxes by using the additem method but now i'm trying to populate a 2 column combo box and I'm not having any luck. My column count is 2, the widths are 1" each and the rowsource is a value list. I've checked all the usual problems but all I'm getting is just the first item that was added and not the following 4. does anyone have any experience or reference syntax that could help?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you use the Combo BOx Wizard to help you create the CBO?

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    No wizard. using code. Please excuse the mess, i was still trying to get things working and not worried to much about how it looks yet

    Code:
    Private Sub cboloc_AfterUpdate()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strloc As String
    Dim strdept As String
    Dim strtemp As String
    Dim strrs2 As String
    Dim strrow As String
        strloc = Me.cboloc
        strdept = ""
        strrow = ""
        
        Me.cbodept.RowSource = "value list"
        Me.cbodept.RowSource = ""
        Me.cbodept.ColumnCount =2
        
        Set db = CurrentDb
        Set rs1 = db.OpenRecordset("LibraryT", dbOpenDynaset, dbSeeChanges)
        
        
        With rs1
         
        If Not .EOF And Not .BOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
            If Left(rs1!libraryid, 3) = strloc Then
                strtemp = Mid(rs1!libraryid, 4, 3)
                If strtemp <> strdept Then
                    strdept = strtemp
                    strrs2 = "SELECT DeptID, Department " & _
                        "FROM 4DepartmentT " & _
                        "WHERE DeptID='" & strdept & "'" ' & strdept
                    Set rs2 = db.OpenRecordset(strrs2, dbOpenDynaset, dbSeeChanges)
    '                Debug.Print (rs2!deptid)
                    Dim str1 As String
                    Dim str2 As String
                    str1 = rs2!deptid
                    str2 = rs2!department
    
                    strrow = strrow & str2 & ";" & str1 & ";"
    
                End If
            End If
            .MoveNext
            Wend
        End If
            
        
        End With
        Set rs1 = Nothing
        Set rs2 = Nothing
        Set db = Nothing
    '    Debug.Print (strrow)
        strrow = Left(strrow, Len(strrow) - 1)
    '    Debug.Print (strrow)
        Me.cbodept.AddItem (strrow)
        Me.cbodept.Visible = True
        
    End Sub
    I'm not sure why its not working, when debugging my rowsource string(Del;001;Vise;002;Lifting;003;General;004;Ut ilities;005) prints the full string but i'm only getting the first item.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'll start by stating you have a: With rs1 and yet you are using rs1 in the While loop.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To be honest, I've not played with lists much. Do you want to try zipping up your db and posting it so we have something to play with?

  6. #6
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thanks, like I said its not pretty, this was just the rough version trying to get the flow working, I'll correct that now. Any ideals why I'm not getting all my all my records to show in my combo?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you need to set the rowsourcetype = "Value List"
    Then ,use a semicolon separated list of the values as the rowsource

    From Msoft:
    Once you build up the string, make sure that the RowSourceType property is set correctly, and then insert the new RowSource string.


    example: (untested)
    ...
    ...
    strlist ="Manufacturing";"Fabrication";"Assembly";"Quality Control"
    cboDepts.RowSourceType = "Value List"
    cboDepts.RowSource = strList

  8. #8
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thanks Orange, i changed the rowsourcetype(bad oversight on my part) but then changed the rowsource to using the string like your example instead of the additem method i was using. its working as planned now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-29-2013, 06:44 PM
  2. No Results on Requery for Combobox RowSource
    By dccjr in forum Programming
    Replies: 3
    Last Post: 05-10-2013, 06:08 PM
  3. Replies: 1
    Last Post: 03-07-2013, 05:12 PM
  4. combobox rowsource per row on a subform
    By kowalski in forum Access
    Replies: 2
    Last Post: 12-05-2012, 01:49 AM
  5. combobox rowsource
    By dirkvw in forum Forms
    Replies: 3
    Last Post: 06-20-2011, 05:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums