Ошибка 2482 access

Consider:

strFunction = «MarkReadyToProcess(» & intCount & «, » & Me.txtSubjectID & «,True}»

Must be a Function not a Sub procedure.

Works for me but my Function is in general module.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thank you for your help. I got it to work by doing the following:

1. I moved the subroutines into their own module (out of the report’s processes) and converted them into functions.
2. I removed the function variables from the all-inclusive string and put them in the suggested concatenated form.
3. I do not need to run the function on the right side of an equation.

So I guess the answers for me are that Eval() needs to call a function and not to put functions in a report process. I cannot say that other than Eval() needing a function that I have the definitive answer on why it didn’t work when that function was in the report’s modules rather than as a stand-alone module.

Sonnydl said:

I’d love to learn why the latter code won’t execute as I’d expected.

Eval evaluates an expression that returns a value. It can evaluate a function if it can see the function. Your Post #8, step 1, made the function visible (put it in Public scope) so that Eval could find it using the «standard» expression evaluator. What Eval does, in brief, is takes the right-hand side of an assignment statement and evaluates it. But the acid test of whether it would work or not is whether the following works:

Dim A as Variant
...
    A = {expression to be evaluated)

If the above works, then an Eval call for that expression that follows the «=» should also work providing you get any required quotes properly managed. However, Eval is concerned about where it is executing, and it is NOT where you might have thought. That is why you had to move things around to be visible.

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

What’s actually in strSQL when the error occurs?

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marian M. said:

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

I have no idea. At one point, I had a different error message that quoted
this much of strSQL: «[LSAYFice-affil-02May-MLM].[affil06] WHERE
(((LSAYCollVars_ACIS.affil31b) Is Null) AND ((LSAYCollVars_ACIS.R31B)=1))»

How would I get VB to tell me what is in strSQL?

Marian

Douglas J. Steele said:

What’s actually in strSQL when the error occurs?

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marian M. said:

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

Before the line of code

qdf.SQL = strSQL

put

Debug.Print strSQL

After the code runs, the SQL string will appear in the Immediate Window
(which you can get to using Ctrl-G)

From a quick glance at the SQL, I don’t see where you have any parameters in
it, therefore I don’t understand why you’re trying to resolve parameters.

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marian M. said:

I have no idea. At one point, I had a different error message that quoted
this much of strSQL: «[LSAYFice-affil-02May-MLM].[affil06] WHERE
(((LSAYCollVars_ACIS.affil31b) Is Null) AND ((LSAYCollVars_ACIS.R31B)=1))»

How would I get VB to tell me what is in strSQL?

Marian

Douglas J. Steele said:

What’s actually in strSQL when the error occurs?

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marian M. said:

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

I put Debug.Print strSQL into the code where you said & found one incorrect
entry in tblCharacter. After fixing that, the code worked. Thank you. Why VB
comes up with error 2482 on a data entry error is another question.

On why the parameters, I started out trying to get the innermost loop to
work. At one point I got the error message: «Run-time error ‘3063’: Too few
parameters. Expected 1.». When I asked for help, someone told me to put in
both the qdf & the par bits. The resulting code worked. When I comment out
the three lines about parameters in the 3 loop code, error 3063 comes back.

Douglas J. Steele said:

Before the line of code

qdf.SQL = strSQL

put

Debug.Print strSQL

After the code runs, the SQL string will appear in the Immediate Window
(which you can get to using Ctrl-G)

From a quick glance at the SQL, I don’t see where you have any parameters in
it, therefore I don’t understand why you’re trying to resolve parameters.

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marian M. said:

I have no idea. At one point, I had a different error message that quoted
this much of strSQL: «[LSAYFice-affil-02May-MLM].[affil06] WHERE
(((LSAYCollVars_ACIS.affil31b) Is Null) AND ((LSAYCollVars_ACIS.R31B)=1))»

How would I get VB to tell me what is in strSQL?

Marian

Douglas J. Steele said:

What’s actually in strSQL when the error occurs?

—
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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