Access: SQL SELECT Columns FROM Table

Question

I want to create two CombiFields in an Access Form:

the first one should show all Table Names of the DB in a dropdown(*)
and the second one should show all column names from the Table which has been choosed in the first Combifield.

Any ideas?

(*) I already have the first Code:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1));

Gustav · Accepted Answer · 2016-06-26 11:13:53Z

3

Set the RowSource property of the combobox to the name of the table (=ComboBoxTable).

Then set the RowSourceType property to: Field List

Private Sub ComboBoxTable_AfterUpdate()

    Me!ComboBoxFields.RowSource = Me!ComboBoxTable.Value

End Sub

edited Jun 26, 2016 at 11:13

answered Jun 26, 2016 at 10:09

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Flow74 Over a year ago

Thanks, but actually i dont really get it. Should i write "=ComboBoxTable" in the RowSource? Or is there also any SQL Code which works?

Gustav Over a year ago

No, use the AfterUpdate event of the table combobox. See edit.

nazark · Accepted Answer · 2016-06-26 11:19:59Z

0

try bellow code in AfterUpdate of first combo

Private Sub Combo0_AfterUpdate()
Dim TableName As String
TableName = Me.Combo0.Value
Dim rs As New ADODB.Recordset
Set rs = CurrentProject.Connection.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName))
Dim fldname As String
rs.MoveFirst
Do Until rs.EOF
fldname = fldname & rs!Column_Name & ";"
rs.MoveNext
Loop
Me.Combo2.RowSource = fldname
Me.Combo2.RowSourceType = "Value List"
Me.Combo2.Requery
End Sub

answered Jun 26, 2016 at 11:19

nazark

1,2402 gold badges10 silver badges15 bronze badges

4 Comments

Flow74 Over a year ago

Unfortunately I still cant get it running...To sum up: The first Combobox (Combo1) shows the TableNames so far. I add the AfterUpdate Code from nazark to that. And I add AfterUpdate Code from Gustav to the second Combobox (Combo2). Now I get a Compile Error for the Code "Dim rs As New ADODB.Recordset" Thanks in advance! :)

Gustav Over a year ago

You cannot mix and toss everything just like that. You need only and nothing more than my single line of code (above). The second combobox needs zero code.

nazark Over a year ago

you need to add Reference to microsoft activex Data Object library 2.6 from the tool menu of code windows

nazark Over a year ago

the code above is to get column name in the second combo

Collectives™ on Stack Overflow

Access: SQL SELECT Columns FROM Table

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related