What is a recordset?

[dmbfan715]

Hi,

I fairly new to Access. I'm making a database that requires some complex calculations, along with some queries. My goal to make 1 report and use VBA to do all the calculations. I only know very basic VBA though. I'd like to run a query to find certain records from my table and then cycle through each record and do calculations for each record. How do I cycle through the query results? Do I use a recordset to make a "connection"? Thank you in advance!

Shivani

 

[DougP]

A recordset is a group of records returned from a particular query.

here is the code:
-----------------------------
Dim Conn2 As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Dim SQLCode As String

Set Conn2 = CurrentProject.Connection
Set Rs1 = New ADODB.Recordset

SQLCode = "SELECT * FROM yourtable WHERE somefield = '" & somecriteria & "';"
Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
' Rs1 is going to have the results of this query or Rs1 is the recordset.

' this little trick forces the recordset pointer to the end
' which gets a correct count of the number of records.
Rs1.MoveLast
Rs1.MoveFirst

' so to loop through the recordset one at a time we use a for next loop
For A = 1 To Rs1.RecordCount
Rs1!yourfield = somevalue
Rs1!yourfield2 = someOtherValue
'etc

Rs1.Update
Rs1.MoveNext
Next

' close it this way
Set Rs1 = Nothing
Set Conn2 = Nothing
------------- DougP, MCP

 

[dmbfan715]

Thank you! I'll try it out.

 

[dmbfan715]

DougP,

I thought of a few more questions...Would I be able to append to a temporary table in the For Next loop? Do I do this by running my query off of this temp. table?

Do I have to define these fields somewhere? And where do these fields exist?:

Rs1!yourfield = somevalue
Rs1!yourfield2 = someOtherValue
'etc

I'm having a hard time understanding recordsets conceptually...

Thanks,
Shivani

 

[cmmrfrds]

Your question "Would I be able to append to a temporary table in the For Next loop? Do I do this by running my query off of this temp. table? "

Yes, you can do this on an ADO Recordset. For the first part you will need to set the "recordset" activeconnection = nothing which will disconnect the recordset from the source. As long as, you don't set the connection object = nothing then you can reconnect later and update the results back to the database. It does not sound like you want to do this, but only add records to the disconnected recordset.

You can add records with the addnew.

Once you have finished updating the temporary recordset probably in the OnOpen Event of the Form (especially if you want then displayed on the Form), then you need to do something like the following. Assuming rs1 is the recordset you are working with.

Dim yy As Access.Form
Set yy = Forms!YourFormName.Form
'-Display Form yy
'--
Set rs1.ActiveConnection = CurrentProject.Connection
Set yy.Recordset = rs1
'-this moved your updated recordset into the Form recordset and will display on the Form.