Multiselect listbox code using IN operator

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

Guest

Hi, Problem with code in form after selection is made.
code:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the CompanyID column for each selected item
strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","

Next varItem

' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)

' Open the companies form filtered on the selected companies
strWhere = "[ShipperID] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmCustShipper", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub

had gotten code from MS Access 2003 inside/out.
code works find if ShipperID is autonumber field, even for mutliple
selection. My ID fields are text, when next form opens all fields are
blank,like new record.

I did find out that if I change the strWhere statement to:
'strWhere = "[ShipperID]= '" & strWhere & "' "
this works, but can only select one record at time to be in next form when
it opens.
The secord form is a edit form, would like to select all records which need
editing before going to next form.

How do you code ( IN ) for mutliselect for ID fields which are text not
Interger?
 
The elements in your In clause need to be enclosed in quotes, i.e.
In('text1','text2', 'etc')
 
this is not any part of a query. This is a on_click event to a button on a
form, single quotes will not sovle the problem, ie
this part of the code:

strWhere = "[ShipperID] IN (" & strWhere & ")"

which is the where condition for the form to be open.

In VBA6 if you put single quote's in this code. it gives error message,
I have tried :
strWhere = "[ShipperID] IN (' " & strWhere & " ')"

strWhere = "[ShipperID] IN (" ' " & strWhere & " ' ")"

each time after first single quote, rest of statement is treated as remarks,
error message expects end of statement.

thanks for response

Paul Overway said:
The elements in your In clause need to be enclosed in quotes, i.e.
In('text1','text2', 'etc')

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Jim Greene said:
Hi, Problem with code in form after selection is made.
code:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the CompanyID column for each selected item
strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","

Next varItem

' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)

' Open the companies form filtered on the selected companies
strWhere = "[ShipperID] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmCustShipper", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub

had gotten code from MS Access 2003 inside/out.
code works find if ShipperID is autonumber field, even for mutliple
selection. My ID fields are text, when next form opens all fields are
blank,like new record.

I did find out that if I change the strWhere statement to:
'strWhere = "[ShipperID]= '" & strWhere & "' "
this works, but can only select one record at time to be in next form when
it opens.
The secord form is a edit form, would like to select all records which
need
editing before going to next form.

How do you code ( IN ) for mutliselect for ID fields which are text not
Interger?
Click to expand...
Click to expand...
 
This is second reply:
At first the brain was to foggy. I figure out how to get the single quotes in.

I was working on the wrong part; Ichanged this part from:

strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","

to:

strWhere = strWhere & " ' " & Me!lstCName.Column(0, varItem) & " ' " & ","

this allow the single quotes in strWhere to show up.

thank for jogging the foggy out
 
Back
Top