I am trying to populate a new table (Tbl_MyTable_Temp) with records from an
existing table (Tbl_MyTable).  The records to be included are determined by
one of the fields in the existing table, the value of which is input by the
user.

First I create the new table using an SQL statement as follows:
Dim StrSQL As String
StrSQL = "CREATE TABLE Tbl_Mytable_temp ........
DoCmd.RUNSQL StrSQL

I then use the following code  to enable the user to input a value to a
string variable named Syllabus
Dim Syllabus As String
Syllabus = InputBox("Input Syllabus")

So far so good, but the I try to use the following code to populate
Tbl_Mytable_Temp with records from Tbl_Mytable for which one field [Syllcode]
has the same value as that inout by the user:

Dim StrSQL as String
StrSQL = "INSERT INTO MyTable_temp SELECT MyTable.* FROM MyTable WHERE
[MyTable].[syllcode] = Syllabus ;"
DoCmd.RunSQL StrSQL

However, the variable called Syllabus is not recognised in the SQL
statement.  What am I doing wrong?

Any help would be much appreciated.

Jim Jones
Botswana