Graham
Many thanks for the prompt response. Your solution worked immediately, and
I can now get on with the job.
I could see that the variable was not being recognized when it was part of
the SQL statement, but finding out how what to do in such circumstances is
difficult for a VBA novice such as myself. That's why this site is so useful
to folk like me - being able to take advantage of the expertise of MVP's such
as yourself is invaluable.
Many thanks again
Jim Jones
Botswana
"Graham R Seach" wrote:
[Quoted Text] > Jim,
>
> The Jet Expression Service knows nothing about VBA variables, so you have to
> supply the *value* of the variable, not its name.
>
> StrSQL = "INSERT INTO MyTable_temp " & _
> "SELECT MyTable.* " & _
> "FROM MyTable " & _
> "WHERE [MyTable].[syllcode] = """ & Syllabus & """"
>
> Regards,
> Graham R Seach
> Microsoft Access MVP
> Sydney, Australia
> ---------------------------
>
> "Jim Jones" <JimJones[ at ]discussions.microsoft.com> wrote in message
> news:135B7B4B-A417-4DFB-87A3-85E73B8319C6[ at ]microsoft.com...
> >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
>
>
>
|