Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Exclamation Unanswered: Urgent ! to populate a list box

    Hi guys,

    I appreciate the support from this forum.I know i have raised the very same issue and got many helps,but could not integrate tem together.

    I have this situation where i need to populate a list box using a querry or Array.It works fine if i use a string (comma seperated with the diff values) to populate a list box,but this cannot go if there are lots of data.

    I could get the results to an array.

    But when i try to use this array to populate the list box i simply don't get them populated,no error too.

    I als tried directly from a recordset itself.Same result.
    I guess i need to chage something but cannot find it out.

    This is how i use them

    1.Using Arrays

    curr(i)--> array with the result.

    me.list1=curr(i)


    2.using Recordset

    me.list1.recordsourcetype="value List"
    me.list1.recordsource=rs.Field(0)

    Both these do not work.
    I would appreciate some urgent help.Please couls u update my code .

    Thanks
    Michael

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    me.list1.RowSource = Join(curr(i), ";")

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Rockey ---- wonderful!

    so simple, so nice, so useful, soooooo undocumented in the literature (but it is in the ignoble access help system if you think to look for "Join" in this context)

    thanks,

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Urgent ! to populate a list box

    Originally posted by mickykt
    Hi guys,

    I appreciate the support from this forum.I know i have raised the very same issue and got many helps,but could not integrate tem together.

    I have this situation where i need to populate a list box using a querry or Array.It works fine if i use a string (comma seperated with the diff values) to populate a list box,but this cannot go if there are lots of data.

    I could get the results to an array.

    But when i try to use this array to populate the list box i simply don't get them populated,no error too.

    I als tried directly from a recordset itself.Same result.
    I guess i need to chage something but cannot find it out.

    This is how i use them

    1.Using Arrays

    curr(i)--> array with the result.

    me.list1=curr(i)


    2.using Recordset

    me.list1.recordsourcetype="value List"
    me.list1.recordsource=rs.Field(0)

    Both these do not work.
    I would appreciate some urgent help.Please couls u update my code .

    Thanks
    Michael
    Mike,

    Where you went wrong on option to is that to use a Table/Query or recordset your RowSourceType must be "Table/Query" ... As for your 1st option ... There is a hard 2K limit for storage of values using the "Value List" This ALSO includes the overhead for Column names (if used) - RE: the 1st row is the column headers ...

    Rockey - That is a nifty trick indeed!!!!

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Check out this example using Join() and Split() functions:
    http://www.tek-tips.com/gfaqs.cfm/le...d/702/fid/4246

    Cheers - Matt

  6. #6
    Join Date
    Nov 2002
    Location
    Wyoming
    Posts
    48
    Excellent tip. I think I can find many uses for this!

    Thanks!!!
    Cheers,
    Ken

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    The functions Split and Join are under Access 2000 and higher

  8. #8
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi Guys,

    Thanks for the great responce.
    I got many new ideas.
    I can use a string ,or a Join() function to populate a list box.

    My database is pretty big and may have around 10000 records with no time.This methods have a 2K limitation as someone specified previously.Is there a way we can get rid of this.I ment to populate a listbox directly from the recordset while i do some quering while a button is clicked.

    I know this has been circling for long but when one comes up with a solution the other end never touches each other.

    Thanks
    Michael

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    Here is a sample........It takes a specific field item within a table and places this item (from all records) into a listbox. If you only want specific records then open the recordset with a query. You will have to play with it to suit your needs.

    Usage:
    Code:
    Me.myListControl.RowSourceType = "FillTableNames"
    Copy & paste the following function into the declarations section of your form:
    Code:
     
    Private Function FillTableNames(fld As Control, id As Variant, row As Variant, _
    col As Variant, code As Variant) As Variant
        Static LstTbls(127) As String, Entries As Integer
        Dim ReturnVal As Variant
    
        ReturnVal = Null
        Select Case code
            Case acLBInitialize             ' Initialize.
                Entries = 0
                Dim dbs As Database
                Dim rst As Recordset, MatchFound As Integer
                Dim str As String, X As Integer, i As Integer
                MatchFound = 0
                Set dbs = CurrentDb
                Set rst = dbs.OpenRecordset("myTableName")
                'On Error Resume Next
                rst.MoveLast
                rst.MoveFirst
                LstTbls(Entries) = rst!myTableFieldName
                For i = 1 To rst.RecordCount - 1
                   rst.MoveNext
                   For X = 0 To i
                       If rst!myTableFieldName = LstTbls(X) Then MatchFound = 1
                   Next X
                   If MatchFound = 0 Then
                      Entries = Entries + 1
                      LstTbls(Entries) = rst!myTableFieldName
                   End If
                   MatchFound = 0
                Next i
                rst.Close
                Set dbs = Nothing
                'On Error GoTo 0
                Entries = Entries + 1
                ReturnVal = Entries
            Case acLBOpen                       ' Open.
                ReturnVal = Timer               ' Generate unique ID for control.
            Case acLBGetRowCount                ' Get number of rows.
                ReturnVal = Entries
            Case acLBGetColumnCount     ' Get number of columns.
                ReturnVal = 1
            Case acLBGetColumnWidth     ' Column width.
                ReturnVal = -1                  ' -1 forces use of default width.
            Case acLBGetValue                   ' Get data.
                ReturnVal = LstTbls(row)
            Case acLBEnd                        ' End.
                Erase LstTbls
        End Select
        FillTableNames = ReturnVal
    End Function
    Hope this helps.

Posting Permissions

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