Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Boxes Data Values

    I'd like to insert a list box into a form listing all the fields being part of a table in an Access 2000 file. How can I do that considering that usually list boxes seem to accept table fields but not table themselves as Row Source?

  2. #2
    franz.strele
    Guest

    Re: List Boxes Data Values

    set the "RowSourceType"-Property of the listbox to "Field List" and set the "RowSource"-Property of the listbox to the name of the table

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Great Stuff!

    Thanks Franz.
    Let's say I want to list all the table's fields except the first two. Is there a way?

  4. #4
    franz.strele
    Guest

    Re: Great Stuff!

    theres no easy way i know of... you will have to use code similar to this one:

    '***** code start
    Dim oField As DAO.Field
    Dim oTable As DAO.TableDef
    Dim sFields As String

    sFields = ""

    Set oTable = CodeDb.TableDefs.Item("MyTable")

    For Each oField in oTable.Fields
    If (oField.Name <> "DoNotListThisField") Then
    sFields = sFields + oField.Name + ";"
    End If
    Next oField

    lstListbox.RowSource = sFields
    lstListbox.RowSourceType = "Value List"
    '***** code end

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hmm... another good hint

    But I'll have to work on it because I'm using ADO.
    By the way, why do most people still cling onto DAO?!

  6. #6
    franz.strele
    Guest

    Re: Hmm... another good hint

    * why use DAO?
    ADO (better the Jet-Provider) is not complete yet so sometimes you'll have to use DAO to use all the Jet-features...

    * fields-listbox
    with ado you can use code similar to this one:

    ' **** begin code
    Dim rs As ADODB.Recordset
    Dim sFields As String

    sFields = ""

    Set rs = CodeProject.Connection.OpenSchema(adSchemaColumns, Array(Empty, Empty, "tPermissions", Empty))
    Do Until rs.EOF

    If (rs.Fields.Item("COLUMN_NAME").Value <> "DoNotListThisField") Then
    sFields = sFields + rs.Fields.Item("COLUMN_NAME").Value + ";"
    End If

    rs.MoveNext
    Loop

    rs.Close

    lstListbox.RowSource = sFields
    lstListbox.RowSourceType = "Value List"
    '**** end code

  7. #7
    franz.strele
    Guest

    Re: Hmm... another good hint

    you'll have to replace the "tPermissions"-string with your tablename of course!!!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hey! You packed quite a punch there!

    Looking at your code and before implementing it, one question comes to mind:
    Your code gives a sFields string; should I substitute it for the table name in the Row Source property box of the listbox property sheet, keeping the RowSourceType Property set to "Field List"?

  9. #9
    franz.strele
    Guest

    Re: Hey! You packed quite a punch there!

    the "sFields" is a string which collects the names of the fields to display in the listbox, separated with semicolons. you then set the ".RowSource"-property of the listbox to this string. the ".RowSourceType"-property has to be "Value List" to tell access that i'm not passing a tablename in the string but the list itself... the tablename from which you want to list the fields is in the ".OpenSchema"-call (the 3rd argument of the "Array")... i hope this is clearer now and good luck

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cristal Clear!

    Thanks again Franz, the last two lines of your code slipped my notice <img src=/S/doh.gif border=0 alt=doh width=15 height=15> before I sent you my previous message. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    That Pesky ADO!

    I've implemented your code (see attached file), yet the "Year" and "Month" fields keep on showing up in the list box. Any idea why?
    Attached Files Attached Files

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: That Pesky ADO!

    Hi,
    You need to change the Or in your If statement to an And - at the moment you're getting the year and month fields because when you evaluate the If statement, you get If ("Year" <> "Year" OR "Year" <> "Month") is True (and vice versa for "Month") so you're not excluding either of them.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Doh! Sorry Ado, no fault of your own.

    Thanks Rory for your lesson in logics.
    True OR False = True
    True AND False = False

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One more question:

    Is the variable represented by the listbox local or global?
    I mean, if the listbox is part of a form, can I reference the listbox value in a textbox in a report? Because I tried to do that but the line
    Reports!Report2!Text18 = "Town of " & cat.Tables("vax").Columns(List10).Attributes("desc ription")
    inserted in a Private Sub Report_Activate() gives me the error message "Compile error: variable not defined" with List10 highlighted.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: One more question:

    Hi,
    Your form needs to be open (I presume it is?) and you should then be able to use the syntax Forms!formname!List10 in your code. I think you may also need to change it to read:
    cat.Tables("vax").Columns("" & Forms!formname!List10).Attributes("description")
    It's probably easier to store the value of the listbox in a variable when an item in the listbox is chosen - so you end up with something like:
    cat.Tables("vax").Columns("" & strFieldName).Attributes("description")
    I hope that helps.
    (PS As far as the And/Or goes, it was nice to finally find some use for doing logic in my philosophy degree! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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