Combo Box that lists a table's field names (1 Viewer)

fredalina

Registered User.
Local time
Today, 06:27
Joined
Jan 23, 2007
Messages
163
My hope is to be able to systematically compare a forecast with historical data. We will receive each in the form of a table and import them.

The first step is a combo box that lists the names of the tables in the database, and the user will then select which table is the forecast for comparison. This part is done, and the combo box is called cboForecast. A typical forecast contains an ID number and 6 forecast periods (months).

i need the user to select the fields that are the time periods (i.e. each month's forecast field) and specify the associated period (i.e. 20090601). The forecasts are 6 months of data each, so i will need 6 combo boxes, each of which lists the field names from the table that was selected in the above paragraph, the name of which table is now the value of cboForecast. There will also be 6 text boxes for the user to enter the period (20090601).

This is the part i need help with. Can you help me with code that will list a table's field names in a combo box? Thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:27
Joined
Aug 30, 2003
Messages
36,118
You don't actually need code. One of the options for the Row Source Type property of the combo is Field List.
 

fredalina

Registered User.
Local time
Today, 06:27
Joined
Jan 23, 2007
Messages
163
Sorry, but that doesn't work. I need to know how to put in the Row Source section the value of a Combo Box. It doesn't seem to want to accept me.listObjects.Value (listObjects is the name of the combo box where the user selects the table). The combo box for the field names is blank.

Thanks!
 

honda882000

Registered User.
Local time
Today, 07:27
Joined
Apr 16, 2009
Messages
62
Do you mean you need the combo box to list the field names, and not the field values? In the example below, I attached the code to a button, but you can use it on the open even for the form which contains the combo. Would this work for you?:

Code:
Private Sub Command2_Click()
'My combo box in this example is called 'combo'
'My table name in this example is 'Table1'
    
    Dim i As Integer 'Index for loop
    
    With Me.combo
        .RowSourceType = "Value List" 'Set rowsource type
        .RowSource = "" 'Clean combo contents
    End With
    
    'Loop through field names of table and add them to your combo:
    For i = 1 To CurrentDb.TableDefs("Table1").Fields.Count
        
        Me.combo.AddItem (CurrentDb.TableDefs("Table1").Fields(i - 1).Name)
        
    Next i

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:27
Joined
Aug 30, 2003
Messages
36,118
I'm not sure you did it correctly then, or I'm misunderstanding. If the row source type is Field List and the row source is the name of a table, the dropdown will display the table's field names. If you're changing the table dynamically, you would need to set that in code rather than referring to a listbox.
 

honda882000

Registered User.
Local time
Today, 07:27
Joined
Apr 16, 2009
Messages
62
Yes, 'Table1' in the example would be replaced by the text/combo box which contains the name of the table. So fredalina would replace wherever I have 'Table1' with Me.combo_with_table_name.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:27
Joined
Aug 30, 2003
Messages
36,118
I believe you only need one line of code:

Me.ComboName.RowSource = Me.listObjects

Presuming that contains the name of a table. If the row source type is field list, the combo should then display the field names for that table.
 

Users who are viewing this thread

Top Bottom