Invalid use of property -- with vba openrecordset Please help

[Hugh N. Ross]

I am using MS Access 2000 and trying to create a dynaset recordset. I have a
query that works properly in interactive mode. I have tried to put the
corresponding SQL into the vba code routine to open a recordset with the
query results. I get a compile error: invalid use of property. Following is
the first part of the subroutine.
Referance to any example articles would be appreciated.
Hugh N. Ross


Private Sub OKEmailBut_Click()
On Error GoTo Err_OKEmailBut_Click

Dim Mydb As DAO.Database
Dim SQLdef As QueryDef
Dim QryDat As DAO.Recordset, OutFile As DAO.Recordset
Dim frm As Form
Dim DatStr As String, LocalQtr As String, SQLString As String

Set Mydb = CurrentDb()
Set frm = Forms![frmInputE]
LocalQtr = frm.[txtQtr] ' This line works

' Open a recordset on the Members table

SQLString = "SELECT ([First Name] & ' ' & [Last Name] & ', ' & ) AS
Name FROM Members "
SQLString = SQLString & "WHERE ((([email]) Is Not Null)) And
(([Expiration] = [LocalQtr]))) "
SQLString = SQLString & "ORDER BY [Last Name]);"

QryDat = Mydb.OpenRecordset("SQLString") ' This line gives the compile
error

QryDat.MoveFirst

' etc

 

[Brendan Reynolds]

Your first problem is the quotes around "SQLString". SQLString is the name
of a variable, not a literal, so you need to lose the quotes ...

QryDat = Mydb.OpenRecordset(SQLString)

After you do that, you'll get a different error message, because you need to
use the Set keyword when assigning to an object variable ...

Set QryDat = Mydb.OpenRecordset(SQLString)

 

[Dirk Goldgar]

I am using MS Access 2000 and trying to create a dynaset recordset. I
have a query that works properly in interactive mode. I have tried to
put the corresponding SQL into the vba code routine to open a
recordset with the query results. I get a compile error: invalid use
of property. Following is the first part of the subroutine.
Referance to any example articles would be appreciated.
Hugh N. Ross


Private Sub OKEmailBut_Click()
On Error GoTo Err_OKEmailBut_Click

Dim Mydb As DAO.Database
Dim SQLdef As QueryDef
Dim QryDat As DAO.Recordset, OutFile As DAO.Recordset
Dim frm As Form
Dim DatStr As String, LocalQtr As String, SQLString As String

Set Mydb = CurrentDb()
Set frm = Forms![frmInputE]
LocalQtr = frm.[txtQtr] ' This line works

' Open a recordset on the Members table

SQLString = "SELECT ([First Name] & ' ' & [Last Name] & ', ' &
) AS Name FROM Members "
SQLString = SQLString & "WHERE ((([email]) Is Not Null)) And
(([Expiration] = [LocalQtr]))) "
SQLString = SQLString & "ORDER BY [Last Name]);"

QryDat = Mydb.OpenRecordset("SQLString") ' This line gives the
compile error[/QUOTE]

You need to use the Set keyword (and also not put SQLString in quotes,
in the OpenRecordset call:

Set QryDat = Mydb.OpenRecordset(SQLString)

Also, you need to fix the lines that build the value of SQLString, to
get the value of LocalQtr into the string, rather than the name. If
Expiration is a number field ...

SQLString = SQLString & _
"WHERE ([email] Is Not Null) And ([Expiration] = " & _
LocalQtr & ") "

If Expiration is a text field ...

SQLString = SQLString & _
"WHERE ([email] Is Not Null) And ([Expiration] = '" & _
LocalQtr & "') "

If Expiration is a date field ...

SQLString = SQLString & _
"WHERE ([email] Is Not Null) And ([Expiration] = #" & _
Format(LocalQtr, "mm/dd/yyyy") & "#) "