By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Access having 2 VBA connections to current db

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.