Append multiple records from a Listbox to a table - Reposted

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

Please help. I'm stuck, I've been doing this for the past two weeks and I
did not succeed.
 
I can't tell by this post exactly what your problem is. I've checked back a
few days and haven't seen the original post. Let me try to help. Basically,
you can append them one at a time as you click on them, or you can append a
bunch of them based on either a goup or a use a multi-select list.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Below is my original post. MVP Allen Brown commented that the codes is good
for criteria and that I need to execute the sql string for
each item, inside the loop. But my problem is I don't know how to do it.
Hope you can help me. Thanks in advance.



Thanks for the prompt response. Well, I'm not really good yet in VBA,
so I copied codes from the internet just like the one below. It works
when only single record is selected in the listbox. When multiple
records are selected it prompts "Syntax error (comma) in query
expressions '( "selectedvalue", "selectedvalue")'. Where selectedvalues
are the values selected in the listbox.


Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Dim strSQL As String


' Build a list of the selections.
Set ctl = Me![lstAssignee]


For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) &
Chr(34)
End If
Next Itm


If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If


strSQL = "INSERT INTO tblTest (DescriptionID,Assignee) VALUES ('" &
txtDescriptionID & "',(" & Criteria & "));"
CurrentDb.Execute strSQL
 
Jologs said:
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) &
Chr(34)
End If
Next Itm
Click to expand...

Instead of the above, try something like this (aircode adapted from working
code) in the click event of the listbox. The Chr(34) above is a double quote
" used to dilineate a string (text value). Numeric values will not need the
quotes. If the criteria is a string, you'll need to marry the logic above
into this line:

strList = strList & .Column(0, varItem) & ","

As in:

strList = strList & Chr(34) & .Column(0, varItem) & Chr(34) & ","


Dim strList As String
Dim varItem As Variant

With Me!lstWhatever ' Listbox name
If .MultiSelect = 0 Then ' Check if it is a multiselect
Me!txtSelected = .Value ' The textbox holding these
values
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The difference in the code is that my code is adding the comma at the end,
and taking it off if it's not needed.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top