setting form's recordset property

  • Thread starter Thread starter DoDev
  • Start date Start date
D

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
 
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.
 
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
 
Back
Top