Multi Select List box

[FrankC]

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList", dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

 

[Dirk Goldgar]

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not
when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList",
dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

That seems strange. What event are you using to run this code?

 

[Dale_Fye via AccessMonster.com]

Do you have an error handler configured for this code segment? If not, add
one. It sounds like you might be generating an error. Also, you might want
to add a line right after the For loop begins, something like:

debug.print varItm

Might even want to add one before the loop:
debug.print ctl.name, ctl.ItemsSelected.count

HTH
Dale

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList", dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

 

[FrankC]

Dale,
I have the standard error handling in the code.
Put in the Debug.print statements and with 'extended' it prints only the one
before the For loop.

Frank

Do you have an error handler configured for this code segment? If not, add
one. It sounds like you might be generating an error. Also, you might want
to add a line right after the For loop begins, something like:

debug.print varItm

Might even want to add one before the loop:
debug.print ctl.name, ctl.ItemsSelected.count

HTH
Dale

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList", dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

 

[FrankC]

Dirk,
The code runs on a OnClick event for a command button on the form. I also
tried it on the Lost Focus event for the list box. Same result.
Frank

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not
when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList",
dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

That seems strange. What event are you using to run this code?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dale_Fye via AccessMonster.com]

Frank,

I've never encountered a difference between Simple and Extended in this type
of loop. What version of Access are you using?

You could try changing the loop from looping through the selected items to
looping through all items in the list, and checking their Selected status.

For intLoop = 0 to lst.listcount - 1
if lst.selected(intLoop) = true then
'add your append stuff here
endif
next

You might also want to add a dbFailOnError in the options section of the
OpenRecordset action. You will probably need to add it to the dbAppendOnly,
but you could also remove the AppendOnly as it is not technically necessary.
This would force an error WRT the recordset, if one is encountered.

HTH
Dale

Dale,
I have the standard error handling in the code.
Put in the Debug.print statements and with 'extended' it prints only the one
before the For loop.

Frank

Do you have an error handler configured for this code segment? If not, add
one. It sounds like you might be generating an error. Also, you might want

[quoted text clipped - 30 lines]

 

[Dirk Goldgar]

Dale,
I have the standard error handling in the code.
Put in the Debug.print statements and with 'extended' it prints only the
one
before the For loop.

What did that Debug.Print statement say was the value of
ctl.ItemsSelected.Count? I can't reproduce your problem here, using Access
2003. Have you said yet what version of Access you're using?

 

[JimBurke via AccessMonster.com]

This probably has nothing to do with it, but who knows. Is there any reason
why you are setting the form and control names to variables and then using
those variables in the loop? Why aren't you just using

For Each varItm In SelectPC.ItemsSelected
rs.AddNew
rs![ProspectID] = SelectPC.ItemData(varItm)
rs.Update
Next varItm

I don't think that would cause a problem, but can't say for certain. I've
used many multi-select listboxes and never had a probem, but I always refer
to them directly.

I have a list box using multi select that appends the selected items to a
table ‘HoldProsList’.
The form is ‘fSelectPCPlan’ and the control is ‘SelectPC’ and the bound
column is an integer.
It works fine when the Multi Select property is set to ‘Simple’ but not when
it is ‘Extended’.
The flow jumps to the end after the ‘For each…. ‘ statement. (No errors)

The code is:
' Copy the ProspectID's into the HoldProsList table
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Set rs = DBEngine(0)(0).OpenRecordset("HoldProsList", dbOpenDynaset,
dbAppendOnly)
Set frm = Forms![fSelectPCPlan]
Set ctl = frm!SelectPC
For Each varItm In ctl.ItemsSelected
rs.AddNew
rs![ProspectID] = Me.SelectPC.ItemData(varItm)
rs.Update
Next varItm

 

[FrankC]

Dirk and Dale,

Problem solved!

In the sub, before the section we have been dealing with, there was a
Me.Refresh line. In Extended mode the refresh resets the list box and set
the selections to null. It doesn't do this in Simple mode. I moved the
refresh to later in the procedure and everything works fine. I tried it
with both the every-line and the only-selected lines versions and it works
now in both cases. The help item for Multi Select says this should happen
when you requery the list box but doesn't say anything about refreshing the
form.

Thanks for you quick responses and help.
Frank