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 Variant
Dim a As Long

' Return Control object variable pointing to list box.

Set ctlList = Me.lstSource

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

Next varItem
'Debug.Print a
ListAdd.Value = a

End Sub

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then strItems = strItems & ctlSource.Column(1, intCurrentRow) & ";"
'End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Dim var As Variant
Dim ctl As ListBox
Dim str As String
Set ctl = Me!lstSource
For Each var In ctl.ItemsSelected
str = str & ";" & ctl.ItemData(var)
Next var
str = Mid(str, 2)
Me!lstDestination = str
Set ctl = Nothing

End Function

 

[Rod Scoullar]

Joel,

What you are trying to do requires quite a bit of code. You haven't given
enough information for us to help you with all your problems.

I suspect the key to success is the relationship between the recordsource of
the form you are using and the other table you mention.

Without knowing this it is impossible to offer a definite solution.

Basically what has to be done is that when the record changes, the code has
to look up the matching selections you have saved to that other table and
fill in the lstDestination rowsource. It can probably do that fairly
easily. This code would go in the Form_Current event.

By the way the CopySelected function appears to duplicate code. You need to
set the lstDestination.RowSource to a string in the form item1; item2; ...
itemN as you do in the first part of the code. The second part does the
same thing but doesn't set the RowSource but rather trys to set the listbox
itself to the string. Try taking the second part out. Eventually you might
have to do something different anyway to make it fit in with the ability to
have it change as the form's record changes.

If you post more details of your form and table structure we will be able to
offer more concrete suggestions.

Rod Scoullar.