Set up filter for listbox now ItemsSelected isn't read

To make my form a little more dynamic for each entry the user inputs, I
decided to add cboVehicleType to my form which, when updated, requeries
lbDamagedParts to only include items related to the cbo selection. Before
adding this filter, everything worked perfectly.

On the BeforeUpdate event of the form, I have code to take the listbox
selections and concatenate them with some text into a sentence w/ commas
separating them. Now that I've set up the filter on the list box, the
resulting concatenation reads, ",,,, ." whereas there's supposed to be text
in between those commas.

To filter the box, I set up a many-to-many relationship between ltblParts &
ltblVehType w/ a junction table jtblVehTypePartsID as each Part will have
many Veh's it goes with and each VehType has many Parts. I set the
AfterUpdate event of the cbo to Requery my listbox and set the RecordSource
of my listbox to:
SELECT qryVehTypeParts.Expr1 FROM qryVehTypeParts WHERE
(((qryVehTypeParts.VehTypeID)=VehicleType));
(Expr1 has DLookup to display the PartName vs. just the ID which is
AutoNumber)

The code for concatenating the ItemsSelected is:
If Me.lbDamagedParts.ItemsSelected.Count 0 Then
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(1, varRow) & ", "
Next varRow
Me.Text39 = "The vehicle sustained damage to the " & _
Left(strText, Len(strText) - 2) & " and additional damage may be found
once the repairs begin."
Else
Me.Text39 = "The vehicle did not sustain any visible damage."
End If
Thanks so much for any help you can give!

Pamela

On Thu, 14 Jan 2010 09:36:02 -0800, Pamela
wrote:

Your code looks good from a distance, but you need a closeup: set a
breakpoint at the top of this procedure and carefully step through.
Select View Locals Window and also use the Watch window to inspect
variables. Pretty soon you'll say "Eureka!"

-Tom.
Microsoft Access MVP


To make my form a little more dynamic for each entry the user inputs, I
decided to add cboVehicleType to my form which, when updated, requeries
lbDamagedParts to only include items related to the cbo selection. Before
adding this filter, everything worked perfectly.

On the BeforeUpdate event of the form, I have code to take the listbox
selections and concatenate them with some text into a sentence w/ commas
separating them. Now that I've set up the filter on the list box, the
resulting concatenation reads, ",,,, ." whereas there's supposed to be text
in between those commas.

To filter the box, I set up a many-to-many relationship between ltblParts &
ltblVehType w/ a junction table jtblVehTypePartsID as each Part will have
many Veh's it goes with and each VehType has many Parts. I set the
AfterUpdate event of the cbo to Requery my listbox and set the RecordSource
of my listbox to:
SELECT qryVehTypeParts.Expr1 FROM qryVehTypeParts WHERE
(((qryVehTypeParts.VehTypeID)=VehicleType));
(Expr1 has DLookup to display the PartName vs. just the ID which is
AutoNumber)

The code for concatenating the ItemsSelected is:
If Me.lbDamagedParts.ItemsSelected.Count 0 Then
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(1, varRow) & ", "
Next varRow
Me.Text39 = "The vehicle sustained damage to the " & _
Left(strText, Len(strText) - 2) & " and additional damage may be found
once the repairs begin."
Else
Me.Text39 = "The vehicle did not sustain any visible damage."
End If
Thanks so much for any help you can give!

Pamela