The only way I can think of to limit the list to specific field names
would be to use some sort of naming convention for the fields whose
names you want to appear in the list. If you ended each field name
with a tilde character ~ for instance to indicate that it is to be
included in the list then you can fill the list box with code like
this in the form's open event procedure:
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ctrl As Control
Dim strField As String
Set ctrl = Me.YourListBox
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("YourTableName")
ctrl.RowSourceType = "Value List"
For Each fld In tdf.Fields
strField = fld.Name
If Right(strField, 1) = "~" Then
ctrl.AddItem Left(strField, Len(strField) - 1)
End If
Next fld
This would list the field names without the tilde in each case. You'd
have to remember to include the tilde when referencing the field in
code, queries etc of course.
While it will work I don't like this as a solution, however. If you
really need to do this then a better solution, which doesn't involve
messing up the column names, would be have a table, TableStructures
say, with three columns TableName, FieldName and ShowInList, the last
being a Boolean (Yes/No) column. Its primary key would be a composite
one of the TableName and FieldName columns. You can then edit the
table, setting the ShowInList column to True for those to be listed,
and use a query as the RowSource property of the list box:
SELECT FieldName
FROM TableStructures
WHERE TableName = "YourTableName"
AND ShowInList
ORDER BY FieldName;
It would be possible to fill the TableStructures table with all table
and field names from your database automatically with some VBA code
which loops through the Tabledefs collection, then through the Fields
collection of each Tabledef object, inserting a row into the
TableStructures table for each, leaving you to then edit the
ShowInList column manually.
Ken Sheridan
Stafford, England
