Full Version: Open a saved query as a recordset
UtterAccess Forums > Microsoft® Access > Access Forms
doctor9
Must be too distracted by all the activity around my office today...
know how to set up a string with an SQL statement inside it, and open that as a recordset with VBA code.
However, I've got a pre-written query named "qryByesByWeek", and I'd like to just open that query as a recordset, just by using it's name, rather than grab it's SQL code. I've tried this:
CODE
  
Dim db As Database, rst As DAO.Recordset, strSQL As String, strTeamsOnBye As String
Dim rst2 As DAO.Recordset
'...A bunch of code using the rst recordset
Set rst2 = db.OpenRecordset("qryByesByWeek", dbOpenDynaset)

...but I get a "Run-time error '91': Object variable or With block variable not set" error.
HAs you may have guessed, I'm already using "rst" in my code for a different recordset. Originally, I closed "rst" and set it to Nothing, and tried re-using it. I next tried creating a new name, "rst2" in case the re-use was causing the error.
I'm sure this is something quite simple. Is it my syntax?
Dennis
RAZMaddaz
Are you looking for a DoCmd to open the query?
xample:
DoCmd.OpenQuery ("qryByesByWeek"), acViewNormal
doctor9
No, I want to open it as a recordset, cherry pick a few bits of data, and then close the recordset.
Dennis
doctor9
I solved the problem by creating a separate subroutine for examining the second recordset, and calling that, but I'd still like to know if anyone has any input on what I need to add/remove to just open two recordsets in a single sub.
Dennis
LPurvis
Yer gonna kick yerself soooo hard if this is the case...
I'm guessing your
'...A bunch of code using the rst recordset
doesn't include
Set db = CurrentDb
doctor9
Naw, that's there for the first recordset.
I'm thinking that my "rst.close" or "db.close" or "Set rst=Nothing" placement may be the culprit.
Honestly, the second recordset analysis really belonged in a separate routine anyway, since can be called from a spot where the work on the first recordset isn't needed.
Dennis
LPurvis
Do you want to include all your code rather than the
'...A bunch of code using the rst recordset
line then?
Hard to diagnose otherwise.
Just FYI - when you've not expicitly instantiated an object then setting it to nothing shouldn't create a problem with subsequent use.
e.g. in an ADO recordset - if you have
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM Table", CurrentProject.Connection
'...A bunch of code using the rst recordset :-p
rst.Close
Set rst = Nothing
rst.Open "SELECT * FROM OtherTable", CurrentProject.Connection

will raise an error - because the rst object isn't a valid object any more.
DAO (which doesn't instantiate objects as such) shouldn't suffer from this.
Similarly - ADO code where you don't explicitly instantiate the object
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM Table", CurrentProject.Connection
'...A bunch of code using the rst recordset :-p
rst.Close
Set rst = Nothing
rst.Open "SELECT * FROM OtherTable", CurrentProject.Connection

Anyway - back to your point.
You say that Set db = CurrentDb isn't required - as it's handled in the first recordset.
But you mention that you think the problem might lie with "rst.close" or "db.close" or "Set rst=Nothing".
If it's actually present - then yes I'd say No. 2 is a big winner.
It's inclusion would absolutely once again require the presence of the object creation.
Although it in itself wouldn't give the error you mention - if you have along with it Set db = Nothing
then naturally you're back to square one and see the error you report.
Is that just a long way of saying - hard to say without actually seeing the code? :-p
Cheers.
doctor9
No prob. I'll post what I have right now.
It's two separate subs; "FilterToPlayerAndWeek" and "DisplayTeamsOnBye". Originally, I was trying to use the "DisplayTeamsOnBye" code as the last half of "FilterToPlayerAndWeek", just above the "Set db = Nothing" line near the end. Then I realized that I had to be able to call "DisplayTeamsOnBye" without calling "FilterToPlayerAndWeek", so the separation into two subs became the final decision.
CODE
Private Sub FilterToPlayerAndWeek()
    Dim db As Database, rst As DAO.Recordset, strSQL As String
'   If there are no picks for this player/week, create empty slots to be filled in
    If DCount("[GameID]", "qryPicks", "[intPlayerId] = " & Me.cmbSelectedPlayer & _
               " AND [WeekNum] = " & Me.cmbSelectedWeek) = 0 Then
        
        Set db = CurrentDb
'       Get a list of the selected week's games
        strSQL = "SELECT tblGames.GameID, Val(Format([GameDateTime]+3,""ww""))-36 AS WeekNum " & _
                 "FROM tblGames " & _
                 "WHERE (((Val(Format([GameDateTime]+3,""ww""))-36)=" & Me.cmbSelectedWeek & "));"
        Set rst = db.OpenRecordset(strSQL)
        rst.MoveFirst
        While rst.EOF = False
'           Add a record for this player to the picks list for each game
            strSQL = "INSERT INTO tblPicks (intPlayerID, intGameID) values (" & _
                     Me.cmbSelectedPlayer & ", " & rst.Fields("GameID") & ");"
            CurrentDb.Execute strSQL, dbFailOnError
            
            rst.MoveNext
        Wend
        rst.Close
    End If
    Me.Filter = "WeekNum = " & Me.cmbSelectedWeek & " And intPlayerID = " & Me.cmbSelectedPlayer
    Me.Requery
'   Display the number of games this week in the footer
    Me.txtNumOfGames = Me.RecordsetClone.RecordCount
    
    Set db = Nothing
End Sub
Private Sub DisplayTeamsOnBye()
'   Show a list of teams on bye in the footer, if applicable
    Dim db As Database, rst As DAO.Recordset, strSQL As String, strTeamsOnBye As String
    Set db = CurrentDb
'   Get a list of teams on bye
    strTeamsOnBye = ""
    
    strSQL = "SELECT qryAllTeamsAllWeeks.WeekNum, qryAllTeamsAllWeeks.TeamID, [strTeamCity] & "" "" & [strTeamName] AS ByeTeam " & _
            "FROM (qryAllTeamsAllWeeks LEFT JOIN qryTeamsPlayingByWeek ON " & _
            "qryAllTeamsAllWeeks.WeekTeam = qryTeamsPlayingByWeek.WeekTeamID) " & _
            "INNER JOIN tblTeams ON qryAllTeamsAllWeeks.TeamID = tblTeams.TeamID " & _
            "WHERE (((qryTeamsPlayingByWeek.WeekTeamID) Is Null)) " & _
            "ORDER BY qryAllTeamsAllWeeks.WeekNum, qryAllTeamsAllWeeks.TeamID;"
    
    Set rst = db.OpenRecordset(strSQL)
'   Look through the list.  If the week number matches, add the team name to the list
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        While rst.EOF = False
            If rst.Fields("WeekNum") = Val(Me.cmbSelectedWeek) Then
                strTeamsOnBye = strTeamsOnBye & rst.Fields("ByeTeam") & ", "
            End If
            rst.MoveNext
        Wend
        rst.Close
        Set rst = Nothing
'       If any teams are listed, add the list preamble to the list
        If Len(strTeamsOnBye) > 0 Then
            strTeamsOnBye = "Teams on Bye: " & Left(strTeamsOnBye, Len(strTeamsOnBye) - 2)
        End If
'       Display the list (an empty string or a list of teams) in the footer
        Me.lblTeamsOnBye.Caption = strTeamsOnBye
    End If
    
    Set db = Nothing
    
End Sub

You'll notice that I'm just hard-coding the SQL code in the second sub. This code is copy/pasted from the saved query, so if you can tell me the syntax I need to remove the SQL and just create the recordset from the query name, that'd be sweet.
It was mostly an exercise to see if I could store minimal data (only the home/away teams and the game date), and write a query to determine which teams were on bye each week (among other things).
In case you haven't deduced it yet, this is an office football pool manager that I'm throwing together before the NFL season starts in earnest next Thursday. It's pretty slick so far, if I must say so myself. I'll probably post it to General Chat sometime next week when the forms are a little more polished.
Dennis
ace
If there was a problem using the saved query you would get a
can't find the query error so the problem is with an unset object
variable somewhere.
LPurvis
Are you saying that, even in the separated procedures, if you substitute the query name into the recordset source parameter instead of the SQL it fails with the original message?? But it works otherwise - as is?? :-s
If not - then how did it look previously - when it *was* failing?
doctor9
No, the separate procedures work just fine. I'd like to know the proper syntax for using the name of a query, rather than re-writing the SQL statement in the second sub.
Sorry this has been so confusing. When I couldn't get them to work as one sub, I tried removing the "use the query's name" bit and just put in the SQL. Then, I moved the second half of the code into it's own sub (still using the SQL code), which worked, so I stopped tinkering for the time being. (I'm still in that "get the code to work" mode, and haven't yet gotten to the "streamline and clean up the code" mode.
Dennis
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.