Data Type Mismatch error 2

[cdgeer]

I'm trying to take criteria from a Form textbox(txtEEID) and use it to run Sql that queries a recordset of tablenames(y_FindUIDsList) and returns corresponding values from each table into a temp table(tmpCollectorUID). I keep getting a datatype mismatch error and can't figure out why. All fields from all tables used are text. Here's the VB code:

Private Sub cmdFindUIDs_Click()
On Error GoTo Err_cmdFindUIDs_Click

Dim db As Database
Dim rs As Recordset
Dim SQL As String
Dim TableName As String
Dim strEEID As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From [y_FindUIDsList]")

DoCmd.OpenQuery "tmpCollectorUIDDelete"

strEEID = Forms![Find User IDs].txtEEID

Do Until rs.EOF
TableName = rs![TableName]

SQL = "INSERT INTO tmpCollectorUID( AccountName, EEID, DataSource ) SELECT AccountName, EEID, DataSource FROM " & TableName & " WHERE " & TableName & ".EEID = " & strEEID & ";"

db.Execute SQL
rs.MoveNext
Loop

Exit_cmdFindUIDs_Click:
MsgBox "Find UID Operation Complete."
Exit Sub

Err_cmdFindUIDs_Click:
MsgBox Err.Description

End Sub

Ideas anyone? Thanks!

 

[randy700]

Try using delimitors...

SQL = "INSERT INTO tmpCollectorUID( AccountName, EEID, DataSource ) SELECT AccountName, EEID, DataSource FROM [COLOR=red][b]'[/b][/color]" & TableName & "[COLOR=red][b]'[/b][/color] WHERE [COLOR=red][b]'[/b][/color]" & TableName & "[COLOR=red][b]'[/b][/color].EEID = [COLOR=red][b]'[/b][/color]" & strEEID & "[COLOR=red][b]'[/b][/color]"

Randy

 

[Golom]

I think you want brackets around the tablename and quotes around the string

SQL = "INSERT INTO tmpCollectorUID( AccountName, EEID, DataSource ) SELECT AccountName, EEID, DataSource FROM [red][[/red]" & TableName & "[red]][/red] WHERE EEID = [red]'[/red]" & strEEID [red]& "'"[/red]

Assuming that EEID is a text field.

You don't need the [blue]TableName.[/blue] qualifier for EEID because there is only one table in the select.

 

[cdgeer]

Thanks to both of you! It works great now!