What is wrong with my SQL Select statement?

[John2810]

Hey guys,

I keep getting into a "invalid operation" problem with the following code, but I can't see what's wrong!!

The part of the code that is highlighted is highlighted in bold.

I'm stuck at this for over 3 hours now...please advise!

Private Sub cmdMatchFields_Click()
Dim db As Database, rs As DAO.Recordset
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

MsgBox "test"

Set db = CurrentDb
Set tbl = db.TableDefs("tbl" & strSupplier & "temp")

For Each fld In tbl.Fields
strSQL = "SELECT [FieldName] FROM tblFieldDefinition INNER JOIN tblFieldThesaurus ON " & _
        "tblFieldDefinition.ID = tblFieldThesaurus.ID " & _
        "WHERE FieldNameAlt = '" & fld & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF Then
MsgBox "No match found for" & fld
Else
db.TableDefs("tbl" & strSupplier & "temp").Fields(1).Name = rs!FieldName
End If
Next fld

Set fld = Nothing
Set tbl = Nothing
Set rs = Nothing
Set db = Nothing
End Sub

[John2810]

Just managed to solve it...

There's nothing wrong with my SQL statement...my fld was stated correctly, Access doesn't know what I want to change.

[cowboy]

Mark the thread as solved please.