I have written an Access VB module that, when I try to run it, reports
"Run-time error '2482': Microsoft Office Access can't find the name
'LSAYCollVars_ACIS' you entered in the expression." and highlights the line:
par.Value = Eval(par.Name), which I realize means that the problem is
somewhere before that line. But, the table =does= exist.

The code in question is
---------
Sub CreateCharVars()

Dim strVarType As String, strCllgVar As String, strCllgFice As String
Dim strCllgEnrollYr As String, StrFiceYr As String, strSQL As String
Dim iLoop As Integer, jLoop As Integer, kLoop As Integer
Dim dbs As Database
Dim rsVarType As Recordset, rsCllgFice As Recordset, rsCllgVar As Recordset
Dim rsCllgEnrollYr As Recordset, rsFiceYr As Recordset
Dim qdf As QueryDef         'add these two
Dim par As Parameter

 Set dbs = CurrentDb
   With CurrentDb
     Set qdf = dbs.QueryDefs("MyQuery")    'add this
   
       For iLoop = 1 To 5
         For jLoop = 1 To 18
           For kLoop = 1 To 21
             Set rsVarType = dbs.OpenRecordset("Select VarType From
tblCharacter Where ID = " & iLoop)
             strVarType = rsVarType!VarType ' Set the first Variable Type
string
             Set rsCllgVar = dbs.OpenRecordset("Select CllgVar From
tblCharacter Where ID = " & jLoop)
             strCllgVar = rsCllgVar!CllgVar ' Set the first College Var
string
             Set rsCllgFice = dbs.OpenRecordset("Select CllgFice From
tblCharacter Where ID = " & jLoop)
             strCllgFice = rsCllgFice!CllgFice ' Set the first College Fice
string
             Set rsCllgEnrollYr = dbs.OpenRecordset("Select CllgEnrollYr
From tblCharacter Where ID = " & jLoop)
             strCllgEnrollYr = rsCllgEnrollYr!CllgEnrollYr ' Set the first
College Enrollment Year string
             Set rsFiceYr = dbs.OpenRecordset("Select FiceYr From
tblCharacter Where ID = " & kLoop)
             StrFiceYr = rsFiceYr!FiceYr ' Set the first Fice Year string
             strSQL = "UPDATE LSAYCollVars_ACIS LEFT JOIN [LSAYFice-" &
strVarType & "-02May-MLM]"
             strSQL = strSQL & " ON LSAYCollVars_ACIS." & strCllgFice & " =
[LSAYFice-" & strVarType
             strSQL = strSQL & "-02May-MLM].UNITID SET LSAYCollVars_ACIS."
& strVarType & strCllgVar
             strSQL = strSQL & " = [LSAYFice-" & strVarType &
"-02May-MLM].[" & strVarType & StrFiceYr & "]"
             strSQL = strSQL & " WHERE (((LSAYCollVars_ACIS." & strVarType
& strCllgVar & ") Is Null)"
             strSQL = strSQL & " AND ((LSAYCollVars_ACIS." &
strCllgEnrollYr & ")=" & kLoop & "));"
     qdf.SQL = strSQL      'this stuffs your SQL string into the query
definition
                           'the following resolves the VB parameter so that
Access can understand it
             For Each par In qdf.Parameters
               par.Value = Eval(par.Name)
             Next par

     qdf.Execute   'this should run the query
           Next kLoop
         Next jLoop
       Next iLoop

 End With
End Sub
---------

When iLoop, jLoop & kLoop all = 1,
VarType    =    affil
CllgVar    =    31B
CllgFice =    R30A1
CllEnrollYr =    R31B
FiceYr =    06

and the VB module should return the query
----------
UPDATE LSAYCollVars_ACIS LEFT JOIN [LSAYFice-affil-02May-MLM] ON
LSAYCollVars_ACIS.R30A1=[LSAYFice-affil-02May-MLM].UNITID SET
LSAYCollVars_ACIS.affil31b = [LSAYFice-affil-02May-MLM].[affil06]
WHERE (((LSAYCollVars_ACIS.affil31b) Is Null) AND
((LSAYCollVars_ACIS.R31B)=1));
----------

When I run this query as a query in Access, I have no problem.

Why would the VB module not recognize a table that exists?

Have I missed a syntax issue? When I hit F1 the most recent time, Access
Help came up with a screen about With Statements. Should I have nested With
statements? How would I fit them in? (Have I done something to change my
object, CurrentDb?)

Is there a way to set this up so that the module spits out the query, so
that I can check what the module is actually producing? (something like a DOS
echo function? - as you can tell, I'm fairly new at this. I've gotten as far
as I have with this script with the help of people in the Access listserve &
MS discussion groups)

Thank you in advance.

Marian Mitchell