Run-Time Error 424 Object Required

[Rafi]

I have the following code composed of two functions which results in the
above error. Note that if I run the second query independently I do not
encounter any problems. Your help is much appreciated.

Function IDD_Growth()
Dim stSQL As String
Dim stSQL2 As String
Dim stAE As String
Dim db As Database
Dim rs As Recordset
Dim CurrQtr As Double
Dim PrevQtr As Double
Dim Yr As Long
' Set values for Current Yr
Yr = 2006
Q = 1
stSQL = " Select Distinct AENAME" _
& " From QRY_VALID_ACCT_LIST"
MsgBox stSQL
Set db = CurrentDb()
Set rst = db.OpenRecordset(stSQL)
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
stAE = rst.Fields("AENAME")
MsgBox stAE
Call CurrYr(stAE, Q, Yr)
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
End Function

Function CurrYr(stAE, Q, Yr)
MsgBox (stAE & "/" & Q & "/" & Yr)
Dim stSQL2 As String
stSQL = "Select Comp_Rev" _
& " From Qry_IDD_Growth_By_AE" _
& " Where (((Qry_IDD_Growth_By_AE.AENAME) = """ & stAE & """)" _
& " and ((Qry_IDD_Growth_By_AE.Qtr) = " & Q & ")" _
& " and ((Qry_IDD_Growth_By_AE.Yr) = " & Yr & "));"
MsgBox stSQL
Set rst = db.OpenRecordset(stSQL)
With rst2
If Not (.EOF And .BOF) Then
Do Until .EOF
CurrQtr = rst2.Fields("Comp_Rev")
MsgBox CurrQtr
rst2.MoveNext
Loop
End If
End With
End Function

 

[Wayne Morgan]

Questions/Comments in line:

Function CurrYr(stAE, Q, Yr)
MsgBox (stAE & "/" & Q & "/" & Yr)

This is the only DIM statement in the function.

Dim stSQL2 As String

What is stSQL? stSQL2 has been DIMed

stSQL = "Select Comp_Rev" _
& " From Qry_IDD_Growth_By_AE" _
& " Where (((Qry_IDD_Growth_By_AE.AENAME) = """ & stAE & """)" _
& " and ((Qry_IDD_Growth_By_AE.Qtr) = " & Q & ")" _
& " and ((Qry_IDD_Growth_By_AE.Yr) = " & Yr & "));"
MsgBox stSQL

rst and db have not been DIMed. Also, db has not been set to CurrentDb.

Set rst = db.OpenRecordset(stSQL) Where did rst2 come from?
With rst2
If Not (.EOF And .BOF) Then
Do Until .EOF
CurrQtr = rst2.Fields("Comp_Rev")
MsgBox CurrQtr
rst2.MoveNext
Loop
End If
End With
End Function

Unlike your first procedure, you're not cleaning up after yourself by
Closing and setting to Nothing.

Part if this problem (actually, most of it) would be solved by adding Option
Explicit right below where the module says Option Compare Database in the
Declarations section of the module. I strongly recommend that you add this
statement to ALL modules you've already created. To have it added
automatically to any new modules as they are created go to Tools|Options in
the code editor. On the Editor tab, check the box next to Require Variable
Declaration.

When you first add Option Explicit to all of your modules, you may not be
able to compile the code. Any variables that you haven't explicitly declared
will generate a compile error. You'll need to go back and add DIM statements
for these. However, once you do that, compiling the code will catch spelling
errors such as these.