Read query records

[redrover]

I hope someone can tell me how to open a query when I load a form. I need
to read the query records and combine information from several records to
create the information displayed in the form. I can't combine the
information properly with queries alone so I want to execute the VBA code to
do that. I'm having difficulty getting the recordset open so I can read it.
My code:

Dim cnn as ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSQL As string
Dim rst As ADODB.Recordset
strSQL = "Select * FROM qryCompTrScore order by [Combcde]"
set rst = CurrentDb.OpenRecordset (strSQL, dbOpenDynaset, dbReadOnly,
dbPessimistic)

The code executes until it gets to the 'set rst' statement. How do I get
the recordset open?

 

[Ken Snell MVP]

You're trying to open an ADO recordset using DAO methods. You must use ADO
method to open:

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

 

[redrover]

Thanks you! I changed the code and I'm now getting the following message:

Object variable or with block nariable not set.

I'm guessing that I need a set statement before the open but I haven't been
able to get it coded correctly. Can you provide assistance on the coding I
need?

You're trying to open an ADO recordset using DAO methods. You must use ADO
method to open:

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I hope someone can tell me how to open a query when I load a form. I need
to read the query records and combine information from several records to
create the information displayed in the form. I can't combine the
information properly with queries alone so I want to execute the VBA code
to
do that. I'm having difficulty getting the recordset open so I can read
it.
My code:

Dim cnn as ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSQL As string
Dim rst As ADODB.Recordset
strSQL = "Select * FROM qryCompTrScore order by [Combcde]"
set rst = CurrentDb.OpenRecordset (strSQL, dbOpenDynaset, dbReadOnly,
dbPessimistic)

The code executes until it gets to the 'set rst' statement. How do I get
the recordset open?

 

[Ken Snell MVP]

Yes:

Set rst = New ADODB.Recordset

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks you! I changed the code and I'm now getting the following message:

Object variable or with block nariable not set.

I'm guessing that I need a set statement before the open but I haven't
been
able to get it coded correctly. Can you provide assistance on the coding
I
need?

You're trying to open an ADO recordset using DAO methods. You must use
ADO
method to open:

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I hope someone can tell me how to open a query when I load a form. I
need
to read the query records and combine information from several records
to
create the information displayed in the form. I can't combine the
information properly with queries alone so I want to execute the VBA
code
to
do that. I'm having difficulty getting the recordset open so I can
read
it.
My code:

Dim cnn as ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSQL As string
Dim rst As ADODB.Recordset
strSQL = "Select * FROM qryCompTrScore order by [Combcde]"
set rst = CurrentDb.OpenRecordset (strSQL, dbOpenDynaset, dbReadOnly,
dbPessimistic)

The code executes until it gets to the 'set rst' statement. How do I
get
the recordset open?