Access having 2 VBA connections to current db

[Brendan Ganning]

I am trying to select all the chapters from the chapter table, and
then for each chapter instert a count of how many members are in that
chapter into another table.. Here is the code so far and I have marked
where the error occurs:

Dim Chapter, Month, Year, Count, EndMonth, EndYearAs Integer
Dim strSql, subStrSql As String
Dim rst, subrst, rsnew As New ADODB.Recordset

' This is just saying that the count should be over 2 months
Month = cbo_Month.Value
Year = txt_Year.Value

If Month < 11 Then
EndMonth = Month + 2
EndYear = Year
Else
EndMonth = Month - 10
EndYear = Year + 1
End If

'Setup the RegPrez2 Table
'----------------------------------------------------------

strSql = "Delete * from RegPrez2;"
CurrentDb.Execute strSql

strSql = "INSERT INTO RegPrez2 ( Chapter, Region, State, Category,
Descr )"
strSql = strSql & " SELECT DISTINCT Chapter.Chapter, Chapter.Region,
Chapter.State, Chapter.Category, Chapter.Descr FROM Chapter;"
CurrentDb.Execute strSql

'----------------------------------------------------------

strSql = "Select Chapter from RegPrez2;"

rsnew.Open strSql, CurrentProject.Connection

With rsnew
..MoveFirst
While Not .EOF

strSql = "SELECT Count(Member.MembNo) AS CurrentCount From
Member WHERE Member.Chapter = """
strSql = strSql & rsnew("Chapter") & """ AND Member.JoinDate <
Datevalue('" & EndMonth & "/01/" & EndYear & "');"

'It Bombs here with object expected I believe
rst.Open strSql, CurrentProject.Connection

subStrSql = "Update RegPrez2 Set CurrentCount = " &
rst("CurrentCount")
CurrentDb.Execute subStrSql
rst.Close
Set rst = Nothing

..MoveNext
Wend

End With
'---------------------------------------

Any help would be greatly appriciated. Thank you

[Bob Quintal]

br*****@vt.edu (Brendan Ganning) wrote in
news:f7**************************@posting.google.c om:

I am trying to select all the chapters from the chapter table,
and then for each chapter instert a count of how many members
are in that chapter into another table.. Here is the code so
far and I have marked where the error occurs:

Dim Chapter, Month, Year, Count, EndMonth, EndYearAs Integer
Dim strSql, subStrSql As String
Dim rst, subrst, rsnew As New ADODB.Recordset
Any help would be greatly appriciated. Thank you

You have dim'ed rst and subrst as type variant.

You need to specify AS <TYPE> individually for each variable on a
line.

Dim Chapter as integer, Month as integer, , Year as integer, Count
as integer, EndMonth as integer, EndYear As Integer
Dim strSql as string, subStrSql As String
Dim rst as new adodb.revcordset, subrst as new adodb.recordset,
rsnew As New ADODB.Recordset

Bob Q.