Using IN clause in SQL via VBA

  • Thread starter Thread starter Steven B via AccessMonster.com
  • Start date Start date
S

Steven B via AccessMonster.com

I am trying to use some code that I had previously used in another DB that
worked, but now for some reason won't. Below is the code and after the code
is my question. Thanks in advance for you assistance.

Private Sub btnDeleteUser_Click()

Dim strUsers As String
Dim varUsers As Variant
Dim vSelectedItem As Variant
Dim i As Variant
Dim z As Integer

' Populate strUsers with a comma seperate list of values
For Each vSelectedItem In lstUsers.ItemsSelected
strUsers = strUsers & lstUsers.ItemData(vSelectedItem) & ","
Next vSelectedItem

' Checks to see if a User has been selected from the list box.
If Len(strUsers) = 0 Then
MsgBox "You must select at least one User from the list."
Exit Sub
End If

' Remove the trailing comma if it exists
If Len(strUsers) > 1 Then
strUsers = Left(strUsers, Len(strUsers) - 1)
End If

' Create the Array of Users
varUsers = Split(strUsers, ",", -1)
z = LBound(varUsers)

' Delete the Users from Array
For Each i In varUsers
DBEngine(0).Users.Delete varUsers(z)
Me.lstUsers.RemoveItem varUsers(z)
z = z + 1
Next i

' DELETE Users from stored table using IN list
DoCmd.RunSQL ("DELETE tblUser.Users, tblUser.Expiration_Date " _
& "FROM tblUser " _
& "WHERE tblUser.Users In (" & strUsers & ");")

End Sub


When I get to the SQL it asks for the parameters for all the items in the
list. For some reason it trys to execute the query as such:

DELETE tblUser.Users, tblUser.Expiration_Date
FROM tblUser
WHERE (((tblUser.Users) In ([sdsf123],[sdsf1234],[sdsf12345])));

Where my silly user names should be ("sdsf123","sdsf1234","sdsf12345")

Any thoughts as to why its adding this info in the query wrong?

-Steve
 
That would work if the users field was defined as numeric.
Since it is a string field, you have to add quotes to each
value.

(david)
 
In other words, you need to use

For Each vSelectedItem In lstUsers.ItemsSelected
strUsers = strUsers & Chr$(34) & lstUsers.ItemData(vSelectedItem) &
Chr$(34) & ","
Next vSelectedItem

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


david epsom dot com dot au said:
That would work if the users field was defined as numeric.
Since it is a string field, you have to add quotes to each
value.

(david)



Steven B via AccessMonster.com said:
I am trying to use some code that I had previously used in another DB that
worked, but now for some reason won't. Below is the code and after the
code
is my question. Thanks in advance for you assistance.

Private Sub btnDeleteUser_Click()

Dim strUsers As String
Dim varUsers As Variant
Dim vSelectedItem As Variant
Dim i As Variant
Dim z As Integer

' Populate strUsers with a comma seperate list of values
For Each vSelectedItem In lstUsers.ItemsSelected
strUsers = strUsers & lstUsers.ItemData(vSelectedItem) & ","
Next vSelectedItem

' Checks to see if a User has been selected from the list box.
If Len(strUsers) = 0 Then
MsgBox "You must select at least one User from the list."
Exit Sub
End If

' Remove the trailing comma if it exists
If Len(strUsers) > 1 Then
strUsers = Left(strUsers, Len(strUsers) - 1)
End If

' Create the Array of Users
varUsers = Split(strUsers, ",", -1)
z = LBound(varUsers)

' Delete the Users from Array
For Each i In varUsers
DBEngine(0).Users.Delete varUsers(z)
Me.lstUsers.RemoveItem varUsers(z)
z = z + 1
Next i

' DELETE Users from stored table using IN list
DoCmd.RunSQL ("DELETE tblUser.Users, tblUser.Expiration_Date " _
& "FROM tblUser " _
& "WHERE tblUser.Users In (" & strUsers & ");")

End Sub


When I get to the SQL it asks for the parameters for all the items in the
list. For some reason it trys to execute the query as such:

DELETE tblUser.Users, tblUser.Expiration_Date
FROM tblUser
WHERE (((tblUser.Users) In ([sdsf123],[sdsf1234],[sdsf12345])));

Where my silly user names should be ("sdsf123","sdsf1234","sdsf12345")

Any thoughts as to why its adding this info in the query wrong?

-Steve
Click to expand...
Click to expand...
 
Thanks Doug, I didn' realize I would have to force the quotes on each value.

In other words, you need to use

For Each vSelectedItem In lstUsers.ItemsSelected
strUsers = strUsers & Chr$(34) & lstUsers.ItemData(vSelectedItem) &
Chr$(34) & ","
Next vSelectedItem
That would work if the users field was defined as numeric.
Since it is a string field, you have to add quotes to each
Click to expand...
[quoted text clipped - 61 lines]
Click to expand...
 
Back
Top