[Access VBA] Subform.Form.Recordset problem

[Daniel Santos]

Greetings, everyone.

I'm relatively new to developing VBA Access code, and I'm facing this
problem: I have a form, in which an employee has to type in his ID,
and based on this criteria, a SQL query will be run, and its results
should be displayed in a subform. I'm trying to perform this using the
code that follows:

Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Dim vSQL As String

'Creates SQL statement
vSQL = " SELECT data_hext, dsc_ativ, qtd_h_solic " & _
" FROM HE_Ativid " & _
" WHERE (status='Preliminary') AND (ID=" & txtID.Value & ") "

'Activates recordset
Set loRst = loDb.OpenRecordset(vSQL)
'Tries to link the recordset to the subform
subAtivEmitidas.Form.Recordset = loRst

At the last line, here, I get the following runtime error 2467: The
expression you entered refers to an object that is closed or doesn't
exist. What am I doing wrong? Please, help me.

Thanks in advance,

Daniel.

[peter walker]

Firstly when playing with objects you must use the word SET.

Set subAtivEmitidas.Form.Recordset = loRst

Secondly make sure of the target object.
Set Me!subAtivEmitidas.Form.Recordset = loRst

BTW
Get into the habit of always specifying the recordset type

Set loRst = loDb.OpenRecordset(vSQL, dbOpenDynaSet)

...because if you just specify ...

Set loRst = loDb.OpenRecordset("Table2")

...then the recordset type is dbOpenTable (type 1, the default, and forms
only like type 2) so an error (7965) will result from using an incorrect
recordset type (dbOpenDynaset = type 2) and you will wonder what the heck is
going on when it seems to work for a SQL based rst<g>

Set loRst = loDb.OpenRecordset("Select * From Table2")

...here Access is smart enough to realise that this is probably not a table
name, but you should not leave anything to chance .

hth
--

peter walker

Please post replies to the news group so everyone can benefit.
papw...@ozemail.com.au.nospam
X close button problems? see...
www.papwalker.com/links.html

"Daniel Santos" <dlsa...@terra.com.br> wrote in message
news:64945bb.02090...@posting.google.com...