How to Get a Field from Table into Combo box
In this How To, I will demonstrate how to get a list of field name from a table into a combo box. In previous How To, I already guided you how to load the list of table name into a combo box when form is loaded. Now I want to show the list of field name of the table name that selected from the table list combo box step by step below.
- Create a combo box below the Table list combo box.
- Click and Name it as cboField under the Property Sheet, Other tab.
- Select Field List for Row Source Type under the Data tab of Property Sheet
- Select Table List Combo box
- Select Event Procefure for the After Update under the Event tab of Property Sheet of Table List combo box
- Click on Three Dots (…) at the end of After Update Event Procedure
- Enter code below for the After Update Event Procedure of Table List combo box
Private Sub Combo3_AfterUpdate() Dim SQL As String SQL = "select * from [" & Me.Combo3 & "]" Me.cboField.RowSource = SQL End Sub
- Save and close the design form.
- Re-open the form
- Select the Table from the Table List combo box
- Click on the drop-down combo box of Field List, now you will see the list of field name of the selected table from the table list combo box as shown in the pictures below.
Some of you may have a question that why we need the bracket [..] before and after the table list combo box (Me.Combo3). The answer is we need a bracket if all tables are one word or no space on the table name.
|Table Name||Need Bracket [..]||SQL Code|
|tbl_Customer||No||SQL = “select * from ” & Me.Combo3 & “”|
|tbl Customer||Yes||SQL = “select * from [” & Me.Combo3 & “]”|
|Customer||No||SQL = “select * from ” & Me.Combo3 & “”|
For example below, there is no bracket […] used in the code. However, the table name is tbl Customer which has a space between tbl and Customer. After you select the tbl Customer table from the table list drop-down list. There is no list of field name for this table. For a safety code, you should use a bracket […] on the referent combo box if some tables have a space on the table name.
Private Sub Combo3_AfterUpdate() Dim SQL As String SQL = "select * from " & Me.Combo3 & "" Me.cboField.RowSource = SQL End Sub