Using Listbox selction to update a table

[Kenny Louden]

I am trying to figure how to do the following with a listbox
On my Form I have a list box that shows my users a distinct list of items from a "master" table that they can select and add to their own user defined table that is used as a filter for a number of reports and queries

As long as I have only 1 column in both - I can get the following code to work

DoCmd.SetWarnings False
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
' Build a list of the selections.
Set ctl = Me.lstProjectLists
For Each Itm In ctl.ItemsSelected
DoCmd.RunSQL "Insert into my_project values ('" & ctl.ItemData(Itm) & "')"
Next Itm
DoCmd.Requery
Me.Refresh
DoCmd.SetWarnings True

What I would like to do is expand my list box to contain more than 1 column and when the user selects from my master list to populate theirs - I want to copy the entire row over.

Example of what I am looking for
On a form I want to have a list box that contains Project No (column1) and Description (Column2) - when a user selects a row and clicks on a command button (code sample from above) to "populate" the selection into their list - I want both columns to be copied into thier table "my_project" .

Is this possible?

Thank you,
Kenny

 

[MStef]

Hello Kenny!

I suggest you to use a COMBO BOX instead of LIST BOX.
Look at "DemoComboBoxPopA2000.mdb".

 

[Kenny Louden]

MStef -
Thanks for the reply and the suggestion.
I will take a look at this and see if this will work for what I am trying to do.
kenny

 

[Kenny Louden]

Mstef -
not what I am really looking for - although it works really nice.
I should have mention in my post that I have to have my list box set to allow multi-select due to the number of distinct values - the master table is dynamically updated and the number of records could range from a couple to a few hundred.
By using the combo-box approach - that means the user would have to select one record at a time to add vs. using a multi-select list box.

Again thanks for the suggestion. It looks like I will have to dig around some more to find my answer.

There days when I just love my job and then there are days when I "love" my job