Displaying Field Names in a Combo Box

Displaying Field Names in a Combo Box

If you wish to allow the user to be able to browse the Tables present in your Database, and then select a Field from within that Table, then you can do this in the following way.

Create a new Form in your Database, not based on a Table or Query. Add two Combo Boxes to this Form, and call them cboTable and cboField. The quick and easy way to do this is to use a system object, MSysObjects, rather than looping the TableDefs collection. Set the RowSource of cboTable to:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((Left([Name],4)<>"MSys") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

This will select all native Access tables (this is due to them having a Type of 1) that aren't System Objects (which are prefixed with 'MSys'). Then, in the AfterUpdate event for cboTable, we can set the RowSource for cboField, which needs to have a RowSourceType of Field List:

Private Sub cboTable_AfterUpdate()
Me!cboField.RowSource = Me!cboTable
End Sub

Top

Last modified at 06/06/2006 14:54:06