Help with a Form, Tables, Boxes and Code

[Guest]

OK - I have a table with 2 columns. The second column is text items (called Activities). The first column is numbers(called risk severity). The number represents a value for the text in column 2.

Now, I have a form. One item is a list box (list source), which lists all the text choices (activities)from column 2. Multiple selections can be made, which are automatically tallied in a separate textbox (list add), and displayed as a numerical total from column one (risk severity

There is a command copy button, that copies only the selected items into another list box (called list destination). This info is recorded in another table

Here is my incredibly frustrating problem

When moving from one record to another, the text displayed in the "list destination" does not change, as all other items on the form do. All other items are either combo boxes or text boxes. So I change "list destination" to a combo box. Instead of the text being displayed, the numerical values are displayed. But they do change with record changes. It's half dozen of one, 6 of the other. If I change it to a text box, I get errors in the VB code. I really have no understanding of the code, I got most of it from these discussion boards.

Any help??

Here's the code I'm using

Private Sub lstSource_AfterUpdate(
Dim ctlList As Control, varItem As Varian
Dim a As Lon

' Return Control object variable pointing to list box

Set ctlList = Me.lstSourc

' Enumerate through selected items
For Each varItem In ctlList.ItemsSelecte
' Print value of bound column
Debug.Print ctlList.ItemData(varItem
a = a + ctlList.ItemData(varItem

Next varIte
'Debug.Print
ListAdd.Value =

End Su

Function CopySelected(frm As Form) As Intege
Dim ctlSource As Contro
Dim ctlDest As Contro
Dim strItems As Strin
Dim intCurrentRow As Intege
Set ctlSource = frm!lstSourc
Set ctlDest = frm!lstDestinatio
For intCurrentRow = 0 To ctlSource.ListCount -
If ctlSource.Selected(intCurrentRow) Then strItems = strItems & ctlSource.Column(1, intCurrentRow) & ";
'End I
Next intCurrentRo
' Reset destination control's RowSource property
ctlDest.RowSource = "
ctlDest.RowSource = strItem

Dim var As Varian
Dim ctl As ListBo
Dim str As Strin
Set ctl = Me!lstSourc
For Each var In ctl.ItemsSelecte
str = str & ";" & ctl.ItemData(var
Next va
str = Mid(str, 2
Me!lstDestination = st
Set ctl = Nothin

End Functio

 

[Rolls]

You could make it simpler by setting up a combo box in your destination
field (where you want to save the code representing the descriptive field.)
The source for the choices to be displayed is your two column table.
Control source = col 1, the codes. Set columns = 2. Widths = 0, 1 (size
the second column to display your text). If you use a bound control you
won't need to write any VBA code.

 

[Guest]

I'm not sure that'll work in this case. There has to be a List box with choices available, so that a user may select one item, 5 items or all 63. When a choice is made, that selection or selections has to go to another box for display, and that second box contains the data that needs to be recorded in a table. (There is a 3rd box which contains the numerical tally of said choices). I have a feeling that this whole form is morphing into something that's more complicated than necessary, and perhaps needs to be re-worked from scratch.