Your code is written to create a new record in the table tblTest for each
item selected in the list box. It will insert the value in the field SECOND.
If SECOND is a numeric field, then it would do the subtraction. If you want
the 50000-2, it will need to be a text field.
Based on your post, I get the feeling you have a multi column list box. If
this is the case, you are getting only the bound column of the list box
columns. If you have multiple columns, you need to identify each column in
the SQL statement. Sort of like:
Private Sub cmdTest_Click()
Dim ctl As Control
Dim varItem As Variant
Dim lngNdx As Long
Set ctl = Forms!frm!lstOracleLinesForROEs
For Each varItem In ctl.ItemsSelected
lngNdx = varItem
Debug.Print ctl.Column(0, lngNdx)
db.Execute "INSERT INTO [tblTest] ([SECOND], [THIRD],[FIFTH])" &
"VALUES(" & ctl.Column(0, lngNdx) &
, " & ctl.Column(1, lngNdx) & ", " & ctl.Column(1, lngNdx) & ")",
dbFailOnError
Next varItem
Set ctl = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP
worksfire1 said:
I think my problem is very similar only I need to insert the data from
multiple list box lines to a table. Here is what I have so far. I can't
seem to get but one field from each of the list box lines to go into the new
table. And on top of that the field that does go over (the first
field/column) in the list box, subtracts the two values on the sides of
hypens in that field!! (ex "50000-2" turns into "49998" in the table.)
Is there any way you can help tweak mine to do multiple fields in my
listbox???
Thank you for your time in reading this.
Private Sub cmdTest_Click()
Dim frm As Form, ctl As Control
Dim db As DAO.Database
Dim varItem As Variant
Set db = CurrentDb
Set frm = Forms!frmROEsList
Set ctl = frm!lstOracleLinesForROEs
For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO [tblTest] ([SECOND])" & "VALUES(" &
ctl.ItemData(varItem) & ")"
Next varItem
End Sub
:
This function will do what you want:
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control
Set ctl = Me.Controls(strControl)
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
BuildWhereCondition = strWhere
End Function
I built it this way because I have forms that use multiple list boxes for
selecting. Here is an example of how to use it:
strWhere = BuildWhereCondition("lstPool")
If Len(strWhere) > 0 Then
strWhere = " AND Pool " & strWhere
End If
The result is a WHERE string without the word WHERE. This allows you to use
it for filtering functions in Access as well as in SQL by adding the word
WHERE:
strWhere = "WHERE " & strWhere
--
Dave Hargis, Microsoft Access MVP
:
In Access2002, I have a form with a listbox, where a user can select multiple
line items. I'd like to pass all of the selections into a query, so that the
associated report will contain data for all the items selected, rather than
generating individual reports for each line item. How can I do that please?
Any information would be greatly appreciated.
Thanks!
I've been using this syntax up to now:
For Each valCount In ctl.ItemsSelected
---- Open the report and print it
Next valCount
where "ctl" is the listbox control.
