Help with a Form, Boxes, Tables 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

 

[Guest]

Hi Joel,
Your list add field, must be a textbox bound to a table (like you said) in order to hold the different values for each record.
List boxes & comboboxes have static row sources that dictate their field values, blah, blah ,blah..(sorry).

So...if listbox "ListSource" has the criteria to be added to now textbox "...Destination", Here is a code I use to accomplish the same thing.

Me.txtListDestination.Value = Me.lstListSource.Value & Chr$(13) & Chr$(10) & Me.txtListDestination.Value

Compared to the code you were using, believe it or not, if I've understood your dilemma correctly, this will work.
The Chr$ functions simply puts each choice entered in ListDestination, one below each other, as opposed to side by side....so make your text box an appropriate height (it will actually look like a list box.

This code should go in the dblClick event of the "ListSource" listbox.

Good luck! hope it helps!

----- Joel wrote: -----

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

 

[Guest]

Dan
Firstly, my thanks for your efforts in helping with this frustrating problem. Most of this Access stuff is a bit over my head

Now, I tried your fix, and while it didn't work, I think we're closer; here's what's happening

I changed lstDestination to a text box, and added your code to the DblClick event. Now, when I double click an item in the lstSource list box, nothing appears to happen. So it seems..... However, something is happening. If I were to manually add text to the lstDestination box (say the word "sample"), then double click an item from the lstSource box, the previously entered text of "sample" does move down one row, as if something was entered above it. The more items I dbl click, the more rows down it moves. Also, I disabled the command copy feature previously set up for the list box destination, and changed a bunch of other stuff in trial and error, to no avail. Also, the selections are not being added to the record table, but manually entered words will. Any ideas?