Results 1 to 11 of 11
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    Get field names fro table/query

    Hi,


    i'd like to get the field names and put it in a recordset, then i'd like to know for every field the type
    thx

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    add the fields into a query. just dbl-click the field to add it into the query.

    to see the types, put the table in design mode and print the list.

  3. #3
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    Thanks. I want to populate a combo box with field names, then get the data type. Does your method fits this?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You want to get the field names from where (sorry, my mind reading hat is broken )?
    Query? Table? Recordset? Combo rowsource property?

    You could loop over the fields of a table or query and get their Name property value such as
    Code:
    Sub GetFieldNamesProps()
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim fld As Field
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tableNameHere")
    For Each fld In rs.Fields
       Debug.Print "Field Name: " & rs.Name & " - " & fld.Type
    Next
    Set db = Nothing
    Set rs = Nothing
    
    End Sub
    This will give you the numeric value for the type. AFAIK, there is no vb constant (i.e. a word describing the field type) so you will need to look up those up. You could add the constants to your code and show the text value of your constant instead of a number like 10.
    Last edited by Micron; 11-18-2020 at 02:24 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    You don't need a mind reading hat, just a title reading hat 😂😂
    However I need to get names from both queries and table.
    Thanks tomorrow I'll try your solution

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    just a title reading hat
    Ahh, I thought 'fro was short for Afro.
    I had but lost a method (code) to enumerate the numeric type values to text so I went looking for it again. Here's what would probably be the best, given the author.

    https://www.everythingaccess.com/tut...-VBA-%28DAO%29

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I want to populate a combo box with field names, then get the data type. Does your method fits this?
    To show field name in a combobox you can set the rowsource type to fieldlist. this will show all the fields in the combos rowsource.

    you could then pass the rowsource and field as arguments to a generic public function.
    something like this

    Code:
    Private Sub Combo0_AfterUpdate()
    
    
    Me.Text4 = T(Me.Combo0.RowSource, Me.Combo0)
    
    
    End Sub
    
    
    Function T(rst As String, fld As String) As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSql As String
    
    
        strSql = "select * from  " & rst
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSql)
    
    
        If rs.BOF And rs.EOF Then
            GoTo MyExit
        End If
    
    
        Select Case rs.Fields(fld).Type
    
    
        Case 1
            T = "Boolean"
        Case 4
            T = "Long"
        Case 5
            T = "currency"
        Case 8
            T = "Date"
        Case 10
            T = "text"
        Case 19
            T = "Numeric"
        Case 12
            T = "Memo"
    
    
        End Select
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Function
    I was too lazy to look up the other fieldtype constants but you could add them.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks everyone, i think i have enough stuff to go ahead

    ps :
    strSql = "select * from " & rst"

    this means that i can make a query based on recordset??

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by diegomarino View Post
    thanks everyone, i think i have enough stuff to go ahead

    ps :
    strSql = "select * from " & rst"

    this means that i can make a query based on recordset??
    Code:
    Function T(rst As String, fld As String) As String
    rst is just the variable name I used, probably could have used a better name.
    I am passing the recordsource of the combobox to the function. It could be a table name or a query name.
    fld is the field I am passing to the function

    so if I were to pass the rowsource as Query1 the sql would resolve to
    Code:
    strSql = "select * from  Query1"
    heres an example
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks very much

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

Similar Threads

  1. Field names in new table
    By Josen in forum Queries
    Replies: 8
    Last Post: 10-17-2019, 03:19 PM
  2. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  3. Replies: 3
    Last Post: 04-13-2017, 09:47 AM
  4. Replies: 2
    Last Post: 02-18-2013, 09:58 AM
  5. Replies: 1
    Last Post: 05-17-2010, 12:21 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