Table headers to access listbox vba

[Rakesh935]

Hello,

It would be great if anyone can help me for the below mentioned requirement.

I have a table which has around 25 columns having unic headings (tble name - Worked_File). All I need to do is populate only the headings of all the coulumns through a button click to a list box.

Thank you,
Rakesh

 

[gemma-the-husky]

this is how to get at the column headers/ fields


dim fld as field
for each fld in currentdb.tabledefs("yourtable").fields
msgbox(fld.name)
next

 

[Rakesh935]

Many thanks for the reply...

just an additional requirement...

I tried ur code but afraid nothing seems to work....hnce looking for further more help...

Additional what i am looking at is after clicking a button on the form all the headings of the coulumns should get populated in the list box. (Can this be possible).

And lastly, the intend behind this tool is do concatenation of multiple column to a new column....

Thanks
Rakesh

 

[Rakesh935]

Just for the information while i execute the given code i get runtime error as 3420...

 

[JHB]

You have to create a variable that holds the object type for the current database.

  Dim dbs As Database
  Dim fld As Field
  Set dbs = CurrentDb
    For Each fld In dbs.TableDefs("[B]yourtable[/B]").Fields
    MsgBox (fld.Name)
  Next

 

[Rakesh935]

Hi JHB,

Feeling little embaresed to ask...

Well I am very new to access vba and dont have much idea with regards to variables.

Hence, requesting if possible could please give me a little more insight about the variables...

Thanks,
Rakesh

 

[gemma-the-husky]

rakesh

to be honest - what is the purpose of getting all these fields in a list box. this is quite advanced stuff, and its hard to see exactly what you would use this for.

 

[narang]

Hello,

To me it appears that on click of a button you wish to poulate a list box with the name of fields of particular table. In case, this is what you are looking for then you may provide the following code on the clisk event of the button object

Let the name of the ListBox be list1
name of table be table1

Click event of the button

me.controls("list1").rowsourcetype="field list"
me.controls("list1").rowsource="table1"

Rgds

 

[gemma-the-husky]

Hello,

To me it appears that on click of a button you wish to poulate a list box with the name of fields of particular table. In case, this is what you are looking for then you may provide the following code on the clisk event of the button object

Let the name of the ListBox be list1
name of table be table1

Click event of the button

me.controls("list1").rowsourcetype="field list"
me.controls("list1").rowsource="table1"

Rgds

narang

I had no idea you could do that. it has never occurred to me to ever use "field list".

not sure if I need it, but good to know

 

[Rakesh935]

Many thanks every body for the help....

@ Narang thank you very much for the help it finally got worked.....Ur a life saviour.....

Just for the knowledge want to ask one more question....

After having a multi select in the 1st listbox, can i move the selected feilds to a different list box for further process....

Thanks once again,
Rakesh

 

[narang]

Hello,


To transfer the contents of the selected rows of one list box to another list box on click of a button object we may use the following code

List1- The list containing items
list2- The list to whcih we wish to transfer these items to
command5- button on click of which selected items shall be added to list2 from list1

Private Sub Command5_Click()
Dim melement As Variant
Dim mint_counter As Integer
Dim mint_selected As Integer
Me.Controls("list2").RowSourceType = "value list"
mint_counter = 0

For Each melement In Me.Controls("list1").ItemsSelected
mint_selected = Me.Controls("list1").ItemsSelected.Item(mint_counter)
Me.Controls("list2").AddItem (Me.Controls("list1").ItemData(mint_selected))
mint_counter = mint_counter + 1
Next

End Sub

Rgds

 

[Rakesh935]

Hi Narang,

I tried the code which u have provided for item transfer with in list box and it works fantastic....

But what I was thinking is perhaps i can do a complete movement.

For e.g. lets say in listbox1 we have A,B,C,D (items) and after i select A from listbox1 it should get removed from listbox1 and get added into listbox2 and vice-versa.

Hope i am able to explain u about my requirment.

Thank you,
Rakesh