Setting a recordset to an sql parameter, receiving an error 91.

**orangeCat** · Feb 24 '10, 03:22 PM

What is the purpose of your code?
If you are using DAO, you should use

Code:

Dim rst As [B]DAO.[/B]Recordset
Public gdbDatabase As [B]DAO.[/B]Database

**TheSmileyCoder** · Feb 24 '10, 03:47 PM

Code:

Option Compare Database
Dim rst As Recordset
Public gdbDatabase As Database
Public grstRecordset As DAO.Recordset
Public gstrSQL As String 

Private Sub Generate__PR_Status_Table_Click()
MsgBox "Entered the application", vbInformation
Set rst = CurrentDb.OpenRecordset("T_PRApprovalHistory")

getstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
"T_PRApprovalHistory.Workflow_Step_Name, " & _
"T_PRApprovalHistory.PR_Date, " & _
"Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
"FROM T_PRApprovalHistory " & _
"GROUP BY T_PRApprovalHistory.PR_ID," & _
" T_PRApprovalHistory.Workflow_Step_Name, " & _
"T_PRApprovalHistory.PR_Date " & _
"(T_PRApprovalHistory.PR_ID) = 14264637 Or " & _
"(T_PRApprovalHistory.PR_ID) = 14265338 Or " & _
"(T_PRApprovalHistory.PR_ID) = 11050390 Or " & _
"(T_PRApprovalHistory.PR_ID) = 11050254 Or " & _
"(T_PRApprovalHistory.PR_ID) = 14729068)) " & _
"ORDER BY T_PRApprovalHistory.PR ID"

Set grstRecordset = gdbDatabase.OpenRecordset(gstrSQL, dbOpenDynaset)
End Sub

Unless the value is set somewhere else (outside the code provided), it would seem you never set gdbDatabase to anything.

**TheSmileyCoder** · Feb 24 '10, 03:50 PM

Also you try to open a recordset with a string "gstrSQL" which doesnt appear to be set anywhere in your code. Above you are using "getstrSQL"

To avoid such issues always have in the top of your modules:

Code:

Option Compare Database 
Option Explicit

Then you wont make these silly name errors, which are a big waste of time.

**TheSmileyCoder** · Feb 24 '10, 03:55 PM

Seems I can't seem to restrict myself to just one post ....

I also noticed you writing:

Code:

"(T_PRApprovalHistory.PR_ID) = 14264637 Or " & _ 
"(T_PRApprovalHistory.PR_ID) = 14265338 Or " & _ 
"(T_PRApprovalHistory.PR_ID) = 11050390 Or " & _ 
"(T_PRApprovalHistory.PR_ID) = 11050254 Or " & _ 
"(T_PRApprovalHistory.PR_ID) = 14729068))

Which could simpler by written as:

Code:

"(T_PRApprovalHistory.PR_ID) In ( 14264637; 14265338;11050390; 11050254;14729068}))

**orangeCat** · Feb 24 '10, 04:03 PM

I think the semicolons in the IN should be commas ... , and the "}" removed

Code:

"(T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068[B]}[/B]))

**TheSmileyCoder** · Feb 24 '10, 04:05 PM

The "}" is a typo, thanks for spotting that. The , versus ; will depend on your regional settings I believe.I know that ";" worked for me.

**dowlingm815** · Feb 24 '10, 04:31 PM

thanks for the help. now there is a compiler error, variable not defined on:

Code:

getstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                    "T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date, " & _
            "Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
            "FROM T_PRApprovalHistory " & _
            "GROUP BY T_PRApprovalHistory.PR_ID," & _
                    " T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date " & _
                    "(T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068)))" & _
                    "ORDER BY T_PRApprovalHistory.PR ID"

**dowlingm815** · Feb 24 '10, 04:42 PM

k...don't know why the compile error has disappeared, i'm back to error 91. here is the entire code again:

Code:

Option Compare Database
Option Explicit

Dim rst As DAO.Recordset
Public gdbDatabase As DAO.Database
Public grstRecordset As DAO.Recordset
Public gstrSQL As String



Private Sub Generate__PR_Status_Table_Click()
    MsgBox "Entered the application", vbInformation
     Set rst = CurrentDb.OpenRecordset("T_PRApprovalHistory")

gstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                    "T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date, " & _
            "Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
            "FROM T_PRApprovalHistory " & _
            "GROUP BY T_PRApprovalHistory.PR_ID," & _
                    " T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date " & _
                    "(T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068)))" & _
                    "ORDER BY T_PRApprovalHistory.PR ID"

  
 Set grstRecordset = gdbDatabase.OpenRecordset(gstrSQL, dbOpenDynaset)

   
End Sub

**TheSmileyCoder** · Feb 24 '10, 06:32 PM

Please remember to use the [Code][/Code] tags around your text, it makes it alot easier to read.
The "Variable not defined" is because you didn't initialize a variable getstrSQL, but gstrSQL. Option explicit means that you cannot use a variable without first using a for example: "Dim gstrSQL as string" statement.

I think you forgot a where clause somewhere.

Code:

gstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
"T_PRApprovalHistory.Workflow_Step_Name, " & _
"T_PRApprovalHistory.PR_Date, " & _
"Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
"FROM T_PRApprovalHistory " & _
"GROUP BY T_PRApprovalHistory.PR_ID, " & _
" T_PRApprovalHistory.Workflow_Step_Name, " & _
"T_PRApprovalHistory.PR_Date " & _
"WHERE (((T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068))) " & _
"ORDER BY T_PRApprovalHistory.PR ID"

**dowlingm815** · Feb 24 '10, 06:52 PM

thanks for you response but doesn't it get covered by using

Code:

Public gstrSQL As String

for

Code:

 Dim gstrSQL as string

as shown in the code below. sql was grabbed from the SQL view from an Access query that didn't have a where clause. i believe it is okay?

Code:

Option Compare Database


Dim rst As DAO.Recordset
Public gdbDatabase As DAO.Database
Public grstRecordset As DAO.Recordset
Public gstrSQL As String

Option Explicit



Private Sub Generate__PR_Status_Table_Click()
    MsgBox "Entered the application", vbInformation
     Set rst = CurrentDb.OpenRecordset("T_PRApprovalHistory")

gstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                    "T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date, " & _
            "Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
            "FROM T_PRApprovalHistory " & _
            "GROUP BY T_PRApprovalHistory.PR_ID," & _
                    " T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date " & _
                    "(T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068)))" & _
                    "ORDER BY T_PRApprovalHistory.PR ID"

  
 Set grstRecordset = gdbDatabase.OpenRecordset(gstrSQL, dbOpenDynaset)

   
End Sub

**dowlingm815** · Feb 24 '10, 06:56 PM

fyi...i commented out the 'public statement and added the "Dim" statement. it is generating the same error code.

Code:

Option Compare Database


Dim rst As DAO.Recordset
Public gdbDatabase As DAO.Database
Public grstRecordset As DAO.Recordset
'Public gstrSQL As String
Dim gstrSQL As String
Option Explicit


Private Sub Generate__PR_Status_Table_Click()
    MsgBox "Entered the application", vbInformation
     Set rst = CurrentDb.OpenRecordset("T_PRApprovalHistory")




gstrSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                    "T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date, " & _
            "Count(T_PRApprovalHistory.PR_Date) AS CountOfPR_Date " & _
            "FROM T_PRApprovalHistory " & _
            "GROUP BY T_PRApprovalHistory.PR_ID," & _
                    " T_PRApprovalHistory.Workflow_Step_Name, " & _
                    "T_PRApprovalHistory.PR_Date " & _
                    "(T_PRApprovalHistory.PR_ID) In ( 14264637, 14265338,11050390,11050254,14729068)))" & _
                    "ORDER BY T_PRApprovalHistory.PR ID"

  
 Set grstRecordset = gdbDatabase.OpenRecordset(gstrSQL, dbOpenDynaset)

   
End Sub

**Stewart Ross** · Feb 24 '10, 09:45 PM

Hi. Error code 19 (I'm assuming that it is 19 as in post 1, not 91 as in your post title) is NOT an empty string; it is described as 'Application-defined or object-defined error'. Error code 91 is Object Variable or With block not set. If it is error 91 then this indicates that your recordset variable is not set - in otherwords, your OpenRecordset statement has failed.

Your string, in its correctly-named form, is self-evidently not empty, as you would find by using the VB editor to set a breakpoint in your code and checking the value of gstrSQL before your OpenRecordset statement.

If the SQL is otherwise OK (and without access to your database I can only go by what you have posted, which appears OK) there must be some other reason for the error. One thing I see which may or may not be significant: why are you trying to open the recordset as a dynaset? Group-by queries are non-editable. As in your first OpenRecordset statement, I'd try not specifying the recordset type and see what happens.

By the way, with Option Explicit on the compiler error was indeed because you had misnamed the gstrSQL variable as getstrSQL in your earlier attempts, as TheSmileyOne indicated.

Finally, the Public statement is a form of Dim. It is used to define the scope of a variable definition. Although Dim is the correct form within a sub or function, changing Public to Dim will make no difference within your sub itself.

-Stewart

**dowlingm815** · Feb 25 '10, 04:34 PM

Error 91 was caused by the rst statement. the code is now working properly, thank you all for you guidance.

Code:

Option Compare Database

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String

Private Sub Generate__PR_Status_Table_Click()
    MsgBox "Entered the application", vbInformation


strSQL = "SELECT T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date, Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
            "FROM T_PRApprovalHistory GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
            "HAVING (((T_PRApprovalHistory.PR_ID)=13728252 Or (T_PRApprovalHistory.PR_ID)=14264637 Or (T_PRApprovalHistory.PR_ID)=14265338 Or (T_PRApprovalHistory.PR_ID)=11050390 Or (T_PRApprovalHistory.PR_ID)=11050254 Or (T_PRApprovalHistory.PR_ID)=14729068)) " & _
            "ORDER BY Count(T_PRApprovalHistory.Workflow_Step_Date) DESC"

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
    Debug.Print rst!PR_ID & " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!Workflow_Step_Date; " "; rst!CountOfWorkflow_Step_Date
    rst.MoveNext
Loop

End Sub

**TheSmileyCoder** · Feb 25 '10, 04:45 PM

Unless your also using your recordset in other pieces of code, you should always remember to unset anything you have set.

Code:

Set rst=Nothing

Setting a recordset to an sql parameter, receiving an error 91.

Setting a recordset to an sql parameter, receiving an error 91.

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment