setting form's recordset property

[DoDev]

I am having problems with setting form's recordset property.

My subform's form_load event sub includes the following code:

Private WithEvents rstA As ADODB.Recordset
Dim SQLStmt As String
Set rstA = New ADODB.Recordset
SQLStmt = "SELECT * FROM tblA"
rstA.Open SQLStmt, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
Set Me.Recordset = rstA


The last causes the following VB error: "the object you entered is not a
valid recordset propertyâ€

I use Access 2007. Reference is set to : Microsoft ActiveX Data Objects 2.8
Library.

I will very much appreciate your help.
Thanks.
DoDev

 

[Douglas J. Steele]

Why are you declaring the recordset as Private WithEvents in the form's Load
event? WithEvents won't do anything useful for you there, as the
instantiation is deleted as soon as the subroutine completes.

Try putting the declaration at the very top of the module, before any of the
VBA code.

 

[Beetle]

A form has a RecordSource property, not a RecordSet
property. The RecordSource property is a string, so you
just use your SQL statement directly, rather than creating
a record set and trying to use that, which would result
in a Type Mismatch error.

Dim SQLstmt As String
SQLstmt = "Select * From TableA"
Me.RecordSource = SQLstmt

 

[RoyVidar]

A form has a RecordSource property, not a RecordSet
property.

Starting with the 2000 version, the Access forms have a recordset
property which can be set to a DAO or ADO recordset.

Initially with some difficulties/challenges, such as not being
updateable (Access 2000 based on Jet data), but it does exist, and
work

See for instance
http://support.microsoft.com/kb/227053/EN-US/
http://support.microsoft.com/kb/281998