Record set access/reference

M

Monty

Having run a DoCmd.RunSQL('select statement') in VBA, what commands are
available to me for programmatically accessing the resulting record
set? The SQL will return more than one record and I need to step
through them one at a time to act on various values in certain fields.

Thanks,
Dale
 
R

Rick Brandt

Monty said:
Having run a DoCmd.RunSQL('select statement') in VBA, what commands
are available to me for programmatically accessing the resulting
record set? The SQL will return more than one record and I need to
step through them one at a time to act on various values in certain
fields.

Thanks,
Dale
Click to expand...

None. RunSQL is for "action queries" (update, insert, delete). You need to
open a Recordset object...

Dim db as Database
Dim rs as Recordset

Set db = CurrentDB
Set rs = db.OpneRecordSet("select statement")

....then you have lots of things you can do with the recordset depending on the
kind it is and what your select statement is.

Do Until rs.EOF
MsgBox("The second field value is " & rs.Fields(1))
rs.Edit
rs.Fields(1) = "some New Value"
rs.Update
rs.MoveNext
Loop

It would be difficult to list ALL of the methods available in a Recordset.
Consult the help file for more details or post specific questions for what you
want to do.
 
M

Monty

Rick said:
None. RunSQL is for "action queries" (update, insert, delete). You need to
open a Recordset object...

Dim db as Database
Dim rs as Recordset

Set db = CurrentDB
Set rs = db.OpneRecordSet("select statement")

...then you have lots of things you can do with the recordset depending on the
kind it is and what your select statement is.

Do Until rs.EOF
MsgBox("The second field value is " & rs.Fields(1))
rs.Edit
rs.Fields(1) = "some New Value"
rs.Update
rs.MoveNext
Loop

It would be difficult to list ALL of the methods available in a Recordset.
Consult the help file for more details or post specific questions for what you
want to do.
Click to expand...

Thanks Rick, what you say makes sense, but then I'm so new to this
aspect of Access you could be telling me the moon was made of cheese.

I've coded like you suggested. In fact the code I've written I'll post
here:

Dim Licenses As Database
Dim HostRecords As Recordset
Set Licenses = CurrentDb
Set HostRecords = Licenses.OpenRecordset("SELECT
HostSystems.hostname FROM HostSystems WHERE
(((HostSystems.hostname)=Forms!DeleteHostForm!hostname))")

<the select statement comes from a regular select query I built and
then copied the resultant SQL>

....and my error message has changed. It now reads "Too few parameters,
expected: 1". Can you interpret this? I've looked at the help page for
the OpenRecordset method and have tried a few things, only to get
further, more complicated errors messages referring to Jet. Do I need
to set up any preparatory statememnts for this SQL to run?
 
R

Rick Brandt

Monty said:
Thanks Rick, what you say makes sense, but then I'm so new to this
aspect of Access you could be telling me the moon was made of cheese.

I've coded like you suggested. In fact the code I've written I'll post
here:

Dim Licenses As Database
Dim HostRecords As Recordset
Set Licenses = CurrentDb
Set HostRecords = Licenses.OpenRecordset("SELECT
HostSystems.hostname FROM HostSystems WHERE
(((HostSystems.hostname)=Forms!DeleteHostForm!hostname))")

<the select statement comes from a regular select query I built and
then copied the resultant SQL>

...and my error message has changed. It now reads "Too few parameters,
expected: 1". Can you interpret this? I've looked at the help page for
the OpenRecordset method and have tried a few things, only to get
further, more complicated errors messages referring to Jet. Do I need
to set up any preparatory statememnts for this SQL to run?
Click to expand...

When you run a query in the Access GUI it can take advantage of the Expression
Service which is what allows it to understand form references like
Forms!DeleteHostForm!hostname. When executing a query in code you don't get
that so form references don't work when they are contained in the SQL string.
However; the vba code can easily translate the form reference so you just need
to place it outside the quotes.

Set HostRecords = Licenses.OpenRecordset("SELECT HostSystems.hostname FROM
HostSystems WHERE (((HostSystems.hostname)='" & Forms!DeleteHostForm!hostname &
"'))")

Notice I also added some single quotes because I assume hostname is text and not
a number.
 
M

Monty

Rick said:
When you run a query in the Access GUI it can take advantage of the Expression
Service which is what allows it to understand form references like
Forms!DeleteHostForm!hostname. When executing a query in code you don't get
that so form references don't work when they are contained in the SQL string.
However; the vba code can easily translate the form reference so you just need
to place it outside the quotes.

Set HostRecords = Licenses.OpenRecordset("SELECT HostSystems.hostname FROM
HostSystems WHERE (((HostSystems.hostname)='" & Forms!DeleteHostForm!hostname &
"'))")

Notice I also added some single quotes because I assume hostname is text and not
a number.
Click to expand...

That did it, thank you so much.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy record into same table 1
"New Record" methods (not Access "methods") 9
mailmerg current record from a Access form to Word 1
Accessing current record 4
How to reference a field of a recordset with a variable 4
VBA Open Record Set multiple tables 8
Add recordset data to local linked table. 1
Problem running an Action Query with VBA 4

Top