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
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
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.
Thanks. I want to populate a combo box with field names, then get the data type. Does your method fits this?
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
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.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
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.
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
Ahh, I thought 'fro was short for Afro.just a title reading hat
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
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.I want to populate a combo box with field names, then get the data type. Does your method fits this?
you could then pass the rowsource and field as arguments to a generic public function.
something like this
I was too lazy to look up the other fieldtype constants but you could add them.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
If this helped, please click the star * at the bottom left and add to my reputation- Thanks
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??
rst is just the variable name I used, probably could have used a better name.Code:Function T(rst As String, fld As String) As String
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
heres an exampleCode:strSql = "select * from Query1"
If this helped, please click the star * at the bottom left and add to my reputation- Thanks
thanks very much