List field names in table from combo box value

[deadyankee]

Hi all,

I have a combo box which lists the tables in my database. I would like a second combo box to list the field names contained in the table that the user selects in the first. For example, the user could select the table 'Media' in the first combo box and be given the list of magazines (the field names contained in 'Media') in the second.

Any suggestions gratefully recieved!!!

 

[jebry]

Hi!

In the second combo box set the Row Source Type to Field List and then use the following event procedure for the first combo box:

Private Sub lstTableNames_DblClick(Cancel As Integer)
'This procedure will fill in a list box with the fields from the
'selected table

'Declare local variables
Dim TableName As String
Dim SelectedTable As Variant

'Determine which table is selected and store the table name
For SelectedTable = 0 To Me!lstTableNames.ListCount - 1
If Me!lstTableNames.Selected(SelectedTable) = True Then
TableName = Me!lstTableNames.Column(0, SelectedTable)
Exit For
End If
Next SelectedTable

'Set the row source for the field combo box
Me!lstFieldNames.RowSource = TableName

End Sub

Obviously I did my form with list boxes, but the same logic can be used for combo boxes. Though with a combo box, you may want to use the on change event, then your code could be simpler:

Private Sub cboTableNames_OnChange()

cboFieldNames.RowSource = cboTableNames.Text

End Sub

I like the list boxes better because all of the table names are shown and most users find the double click an intuitive method for affecting changes.

hth
Jeff Bridgham

 

[rbowes]

on the AfterUpdate event of the first ComboBox place this code:

Private Sub MyFirstcbo_AfterUpdate()

WIth MySecondcbo
.RowSourcetype = "Field List"
.RowSource = MyFirstcbo.Value
end with

end Sub
Hope that helps

 

[gol4]

I do want to question your database design. You say
"given the list of magazines (the field names contained in 'Media"
I hope you have not named the fields in Media like so
SportsIllustrated,GQ,Enquirer, ETC..
and have 20 or so field names each one after a different magazine???
but instead have something like
media type, media name
magazine GQ
magazine Sports Illustrated
book Gone With the Wind

but to answer your question!!!
To get a combo to list the field names set the 2nd combos row source type to field list then under the combos rowsource set it to the table name select in selected in the first combo.
There are several postings and a FAQ on how this is done