Microsoft Access
--
Questions
--
Followers
Top Experts
List table names and corresponding columns in a form in ACCESS 2K
I had this problem of finding the 95 percentile and I solved it comfortably. I was wondering how dynamic it could be made. Is it possible to list all the table and corresponding column names in a combo box ie I create a form and populate a combo box with all tables in the database. As soon as the user chooses a table, the corresponding columns get populated in another combo box. Further, is it possible to filter out only the Integer type columns or memo type columns, to be shown in this second combo box?? Just a thought .. was not able to solve it - but would be grateful if someone pointed me in the right direction. I would love to do it through VBA if possible - or is there a property that can be set ?? I wonder!! Any inputs are appeciated.
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
This code will list all of your tables, minus the system tables, in a combo box. Place the code on your form load event:
Private Sub Form_Load()
Dim db As Database
Dim tdf As TableDef
Dim strTables As String
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" Then
strTables = strTables & tdf.Name & "; "
End If
Next tdf
Me.Combo0.RowSourceType = "value list"
Me.Combo0.RowSource = Left(strTables, Len(strTables) - 2)
db.Close
Set db = Nothing
End Sub
I'll get back to ya on the other part (List only Integer Field Types).
Private Sub Form_Load()
Dim db As Database
Dim tdf As TableDef
Dim strTables As String
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" Then
strTables = strTables & tdf.Name & "; "
End If
Next tdf
Me.Combo0.RowSourceType = "value list"
Me.Combo0.RowSource = Left(strTables, Len(strTables) - 2)
db.Close
Set db = Nothing
End Sub
I'll get back to ya on the other part (List only Integer Field Types).
Hey thanks never thought of looking into db properties. Works like a charm. Wonder if the columns can be similarly listed out too!!
You can list the fields in a table or query in a listbox or combo by:
- setting the Row Source Type of the control to 'Field List'
- setting the Row Source to the name of the table or query
Hope this helps...
JB
- setting the Row Source Type of the control to 'Field List'
- setting the Row Source to the name of the table or query
Hope this helps...
JB
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hey HTH, I think there is some confusion here. I've got the table names in the combo box. What I want in the second combo box is the names of all the columns in the selected table. If that is possible THEN I want to know the data type for a selected column. You see the end objective is to do some caluclations on the Integer type fields. So I wanted to give the user the option to drill down.
Choose table (already done by you) -> Choose Integer columns from the table (combo box to display all integer column names) -> Do your calculations on this field (Taken care of by me). Hope that clears the requirement. Cheers
Choose table (already done by you) -> Choose Integer columns from the table (combo box to display all integer column names) -> Do your calculations on this field (Taken care of by me). Hope that clears the requirement. Cheers
Duh! Sorry .. I was able to sort that one out myself .. but you did all the difficult work for me. For anyone else who's interested, you get the field names by saying tdf.Fields(n).Name. Put that in a semi-colon separated loop and populate the combo box! Thanks a ton to HTH (dds110). Merry Christmas and a Happy New Year 2004!
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.