Adding Records to a table (1 Viewer)

I'm having a problem adding some records to a table. I have pass a recordset to a subroutine which then finds which codes are included in a field of this recordset. I have if then statements that are checking this. Based on wether the code exists or not, I need to add some information from a tabel called zzz AutoMerge Letters to a table called zzz AutoMerge TEMP I am trying to do this with docmd.runsql, but it doesn't seem to be working. Here is the code:

DoCmd.RunSQL ("INSERT INTO [zzz AutoMerge TEMP] SELECT [zzz AutoMerge Letters].* FROM [zzz AutoMerge Letters] WHERE ((([zzz AutoMerge Letters].Related)='9M') AND (([zzz AutoMerge Letters].Language)="" & Lingua & ""));")

9M is the code that I am looking for. And the lingua is the language the letter is in. Lingua is a global that is assigned earlier in the code. As I have stepped through this code the if then statements work fine, the language variable is working fine also. I cannot find the problem. Any help would be greatly appreciated!

Thanks from Italy.

My understanding is that you cannot pass a global variable directly into SQL from VBA. Instead create a function which returns the value of your global variable.

I found that if I use three sets of quotation marks everything works fine.

i.e. )=""" & Lingua & """));")

Now that you've figured out the problem, here's a suggestion to help you with future ones - put the SQL into a string first. That way if it doesn't work, you can print the string in the debug window to examine it or copy it into the query window to test it.