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_AfterUpda te()

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

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

For Each varItem In Me.ActiveContro l.ItemsSelected
strTemp = strTemp & "((tblEmployees .EmployeeID) = " &
Me.ActiveContro l.ItemData(varI tem) & ") Or "
Next

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

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Dele te "qryEmploye e"
Set qry = .CreateQueryDef ("qryEmploye e", 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.ActiveContro l.ItemsSelected
strTemp = strTemp & "((tblEmployees .EmployeeID) = " & Chr$(34)
Me.ActiveContro l.ItemData(varI tem) & Chr$(34) & ") Or "
Next

(Chr$(34) is ")

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

Private Sub List2_AfterUpda te()

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

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

For Each varItem In Me.ActiveContro l.ItemsSelected
strTemp = strTemp & Chr$(34) & Me.ActiveContro l.ItemData(varI tem) &
Chr$(34) & ", "
Next

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

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Dele te "qryEmploye e"
Set qry = .CreateQueryDef ("qryEmploye e", 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.goo glegroups.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_AfterUpda te()

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

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

For Each varItem In Me.ActiveContro l.ItemsSelected
strTemp = strTemp & "((tblEmployees .EmployeeID) = " &
Me.ActiveContro l.ItemData(varI tem) & ") Or "
Next

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

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Dele te "qryEmploye e"
Set qry = .CreateQueryDef ("qryEmploye e", 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_AfterUpda te()

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

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

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

' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Dele te "qryEmploye e"
Set qry = .CreateQueryDef ("qryEmploye e", 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.Em ployeeID = "A" OR tblEmployees.Em ployeeID = "B"

and I suggested changing it to

WHERE tblEmployees.Em ployeeID IN ("A", "B").

What you're getting instead is

WHERE tblEmployees.Em ployeeID IN (tblEmployees.E mployeeID = "A",
tblEmployees.Em ployeeID = "B")

The line of code

strTemp = strTemp & "((tblEmployees .EmployeeID) = " & Chr$(34) &
Me.ActiveContro l.ItemData(varI tem) & Chr$(34) & ", "

should be

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

<Ly***********@ yahoo.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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_AfterUpda te()

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

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

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

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

[Lynnannemarie]

Works well, thanks for your help.

Lynn