Multiple Parameters for 1 field

[cbuono]

Hi everybody.

I am using a multiple record listbox to query a customer table and then show a new form based on those selected records in the list box. I am currently using the code below to create the where clause when the form opens. I would like it though instead of creating the where clause but to populate a temporary lookup table with each of the customer's name in it. Here's the code below:

Public Function list()

Dim myfrm As Form, myctl As Control
Dim varItem As Variant
Dim str As String
Set myfrm = Forms!frm_lookup_storerkey
Set myctl = Forms!frm_lookup_storerkey.Form![list_storerkey]
str = "storerkey in ("
'enumerate selected items and
'concatenate to strSQL
For Each varItem In myctl.ItemsSelected
str = str & "'" & myctl.ItemData(varItem) & "', "
Next varItem

'Trim the end of strSQL
str = Left$(str, Len(str) - 2)
str = str & &quot"
DoCmd.OpenForm "frm_list_test", acNormal, , str
End Function

For some reason though when I try to execute an insert statement using
Docmd.RunSQL "Insert into tbl_lookup_storer (storerkey) select str",-1
the value isn't being passed to that variable at all. I know that currently the code creates the where clause: storerkey in ('Value1','Value2',etc) and I've got it to where str will only = Value1 but again when I try to pass that str to the insert statement it gives me a popup window with str and a blank control to fill in.

Any suggestions.
Thanks in advance,
Chris Buono

 

[cbuono]

Well i was able to fix my problem. My insert statement was incorrect. Here's what i did:

DoCmd.RunSQL "Insert into tbl_lookup_storer (storerkey) select '" & myctl.ItemData(varData) & "'", -1