Multi-select List box with textPK

[Lynnannemarie]

I'm having problems with the following,
I originally had a PKnumber EmployeeID then had to change it to a text
field so it would accept both numbers and letters. Now it doesn't
work. Any suggestions?

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.[LName] & "",
"" & [FName] & "" "" & [MName] as Employees FROM tblEmployees "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployees.EmployeeID) = " &
Me.ActiveControl.ItemData(varItem) & ") Or "
Next

strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) &
");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployee"
Set qry = .CreateQueryDef("qryEmployee", strSQL)

End With

End Sub

Thanks.

[Lynnannemarie]

I get an error message of data type mismatch in criteria expression.
Thanks.

[Douglas J. Steele]

If EmployeeID is text, you need to enclose the values in quotes:

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployees.EmployeeID) = " & Chr$(34)
Me.ActiveControl.ItemData(varItem) & Chr$(34) & ") Or "
Next

(Chr$(34) is ")

However, I think it would probably be better to use:

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.[LName] & "",
"" & [FName] & "" "" & [MName] as Employees FROM tblEmployees "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & Chr$(34) & Me.ActiveControl.ItemData(varItem) &
Chr$(34) & ", "
Next

strSQL = strSQL & "WHERE tblEmployees.EmployeeID IN (" & Left(strTemp,
Len(strTemp) - 2) &
");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployee"
Set qry = .CreateQueryDef("qryEmployee", strSQL)

End With

End Sub

Otherwise, your SQL string may grow to be too large.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<Ly***********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I'm having problems with the following,
I originally had a PKnumber EmployeeID then had to change it to a text
field so it would accept both numbers and letters. Now it doesn't
work. Any suggestions?

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.[LName] & "",
"" & [FName] & "" "" & [MName] as Employees FROM tblEmployees "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployees.EmployeeID) = " &
Me.ActiveControl.ItemData(varItem) & ") Or "
Next

strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) &
");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployee"
Set qry = .CreateQueryDef("qryEmployee", strSQL)

End With

End Sub

Thanks.

[Lynnannemarie]

Thanks for your reply but I'm still getting Data type mismatch in
criteria expression error. I'm not an expert at writting this stuff
and with your assistance this is what I think you suggested?

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.[LName] & "",
"" & [FName] & "" "" & [MName] as Employees FROM tblEmployees "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployees.EmployeeID) = " & Chr$(34) &
Me.ActiveControl.ItemData(varItem) & Chr$(34) & ", "
Next
strSQL = strSQL & "WHERE tblEmployees.EmployeeID IN (" & Left(strTemp,
Len(strTemp) - 2) & ");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployee"
Set qry = .CreateQueryDef("qryEmployee", strSQL)
End With
End Sub
Thanks for your help.

[Douglas J. Steele]

No, it's not what I suggested. Rather than just copying the code I gave you,
you modified your existing code so that you've got a blend of the two
methods.

You originally were using

WHERE tblEmployees.EmployeeID = "A" OR tblEmployees.EmployeeID = "B"

and I suggested changing it to

WHERE tblEmployees.EmployeeID IN ("A", "B").

What you're getting instead is

WHERE tblEmployees.EmployeeID IN (tblEmployees.EmployeeID = "A",
tblEmployees.EmployeeID = "B")

The line of code

strTemp = strTemp & "((tblEmployees.EmployeeID) = " & Chr$(34) &
Me.ActiveControl.ItemData(varItem) & Chr$(34) & ", "

should be

strTemp = strTemp & Chr$(34) & Me.ActiveControl.ItemData(varItem) & Chr$(34)
& ", "
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<Ly***********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks for your reply but I'm still getting Data type mismatch in
criteria expression error. I'm not an expert at writting this stuff
and with your assistance this is what I think you suggested?

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.[LName] & "",
"" & [FName] & "" "" & [MName] as Employees FROM tblEmployees "

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblEmployees.EmployeeID) = " & Chr$(34) &
Me.ActiveControl.ItemData(varItem) & Chr$(34) & ", "
Next
strSQL = strSQL & "WHERE tblEmployees.EmployeeID IN (" & Left(strTemp,
Len(strTemp) - 2) & ");"

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "qryEmployee"
Set qry = .CreateQueryDef("qryEmployee", strSQL)
End With
End Sub
Thanks for your help.

[Lynnannemarie]

Works well, thanks for your help.

Lynn