Populate List Box with variable table field names (1 Viewer)

[sammylou]

I have a form called frmHotspots and dependant on which mainframe system table the user selects from a combo box above called ComboTable, I want the list box below called MyList to be populated with the field names of that system table. Each mainframe system table has its own table in my DB with 1 record in it just so that the field names can be picked up in the DB. For example if the user selects the table called TWA001_Transaction from the ComboTable field above i, I want the code to look within the DB find an Access table called TWA001_Transaction and populate Mylist listbox with the Access field names from that table:

i.e.
DBSERIAL_NUMBER
NO_OF_NARR_LINES (this is 2 of the field names in the table there are actually loads of fields in TWA001_transaction

I have attached the code which is a module called AddItemToEnd Currently the "select from" bit is hardcoded with TWA001_Transaction but this should be variable

In the OnCLick properties of Mylist listbox I have put the following code:

=AddItemToEnd([«ctrlListBox»],[«strItem»])

I'm obviously doing something majorly wrong as nothing happens when I click on Mylist


I then tried putting

=AddItemToEnd([«ctrlListBox»],[«strItem»])

into the Rowsource of the ListBox (with RowSourceType as Value List) but all this did is display =AddItemToEnd([«ctrlListBox»],[«strItem»]) as text in the ListBox
Any help from you clever people would be much appreciated

Thanks

Sam

 

[sammylou]

Can anyone please advise me on my post

any help would be really appreciated

Thanks

Sam

 

[Pat Hartman]

If I understand your question, the code is way overkill. RowSourceType should be set to "Field List". Then all you need to do is to place the table name in the RowSource property. You may need to requery th combo but try without the requery first. You don't need to use the AddItem method. The list will populate automatically. At MOST you need two lines of code.

Me.SomeCombo.RowSource = "table name"
Me.SomeCombo.Requery (may not be necessary)

 

[jdlc]

can i do this in a query? because I want both field name and field description, I can use this to my hide/unhide fields at run time.

 

[CJ_London]

no - but you might be able to do something in a form. The caption from the table field properties (I presume that is what you mean by description) can be referred to in the associated label which is referenced as follows

so if you have a field where
the name is: myField
the caption is: myDescription

then to refer to it from VBA you would uses something like

myField.Controls(0).Caption

 

[jdlc]

no - but you might be able to do something in a form. The caption from the table field properties (I presume that is what you mean by description) can be referred to in the associated label which is referenced as follows

so if you have a field where
the name is: myField
the caption is: myDescription

then to refer to it from VBA you would uses something like

myField.Controls(0).Caption

thanks a lot cj, i manage to play with it and here is what i come:

Dim dbs As Database, tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!<DataBase Name>
For Each fld In tdf.Fields
    'don't include those fields in the list
    If fld.Name = "Field1" Or fld.Name = "Field2" Or fld.Name = "Field3" Or fld.Name = "Field4" Then
    Else
        ' and that's include those fields with reserve as description
        If fld.Properties("description") = "Reserve" Then
        Else
            Me![ListBox].AddItem fld.Name & ";" & fld.Properties("description")
        End If
    End If
Next fld
Set dbs = Nothing
Set tdf = Nothing

column bound = 1 (because I need the Field Name to use)
column count = 2
column widths = 0";10"
row source type = value list