Related Topics

Loop Through Dao Recordset

DAO.Recordset Vs ADODB.Recordset Vs Recordset
So I know that there are two types of libraries for retrieving recordsets, either DAO or ADO.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

but if the default is DAO anyways (I think), would it really cause any problems. (I'm using Access 2003)

So now, which is better DAO or ADO if I'm only using a recordset to update a single field of a record from a table that may contain hundreds of records.

Which is better in general? What exceptions are there? What do other people use

simple recordset loop?
here? I have this simple code-

Dim db As DAO.Database Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReturnList", dbOpenDynaset)
j = rs.RecordCount
For I = 1 To j
'##collect data from table or query##
strGetData = rs!fldFirstName
MsgBox strGetData
Next I

it only ever returns the value of 1. If I add rs.MoveLast above the rs.RecordCount, I get the record count but then the loop fails on the msgbox as it is at the end of the recordset.

All im trying to do is loop through the records in the recordset and display a messagebox with the name. Hardly a complicated thing and ive done it many times but feel I might be a little rusty under the arches lol.

Any quick help available

rs.findfirst with another dao recordset
What would be the most optimum way to .findfirst on a dao recordset with the criteria from another dao recordset? I'll try to explain more with code that may not be right but you should get the idea.

Dim rs as dao.recordset, rs2 as dao.recordset, db as database, db2 as database set db = currentdb set db2 = opendatabase("Path to DB", False, False, ";PWD=###")
set rs = db.openrecordset("Table Name", dbopendynaset) set rs2 = db2.openrecordset("Table Name", dbopendynaset)
'// Finds records marked for deletion. Deletion equals 20 rs.findfirst "[Record Status] = 20" do while rs.nomatch = false
rs2.findfirst rs
rs2.delete rs.movenext loop rs.close rs2.close set rs = nothing set rs2 = nothing set db = nothing set db2 = nothing

Loop through recordset and run query
I am using the following code based on Bob Larson's sample on his website

Dim db As DAO.Database Dim rst As DAO.Recordset
Set db = CurrentDb Set rst = db.OpenRecordset("qry_RecNumberCollect")
Do Until rst.EOF
DoCmd.OpenQuery "qry_RecNumberUpdate"
rst.MoveNext Loop
rst.Close Set rst = Nothing

When I run it I get an error that says error 3601 too few parameters expected 1

When I click the debug button the followin line of code is highlighted.

Set rst = db.OpenRecordset("qry_RecNumberCollect

Update table with values from another table
I am trying to update values in a table with the correct values from another table. The first column is an autonumber field and is linked to another table and so I cannot just delete the records and add new ones.
I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)


table A

I want to loop through and amend the last two fields to;

table B

This is my code;

Dim db As DAO.database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("t_A")
Set rs2 = db.OpenRecordset("t_B")
Do Until rs1.EOF
rs2![name] = rs1![name]


Set rs2 = Nothing
Set db = Nothing

End Sub

This just adds three more names and doesn't amend the current ones

Correct code for opening a recordset from another database?
Can anyone write a quick sub that basically opens a recordset from another database, iterates through them all, then closes correctly? My code isn't working properly:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = OpenDatabase("\\path\otherdb.mdb")
Set rst = db.OpenRecordset("SELECT * FROM Table_Name")

Do While Not rs.EOF
MsgBox rs("Col_name")

Set rs = Nothing

I'd also like to use ADODB

Recordset loop + display all?
I've never tackled a recordset loop before in vba and have no idea what I'm doing - I tend to work best with a very basic example and rip it apart from there to make it do what I want through trial and error!

To that end, assuming I have a table called tNames and in it are the following fields:

NameID - Autonumber
Name - Text

If I wanted to just loop through that recordset and echo out the results from both fields for all records, how would I go about doing it

Do while ... Loop
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Set rst1 = CurrentDb.OpenRecordset("HtoROrignial")
Set rst2 = CurrentDb.OpenRecordset("subfolder")
Do While Not rst1.EOF
Do While Not rst2.EOF
If rst2("Fname") <> 0 Then
strSQL = "INSERT INTO subfolder (notes) VALUES ('this works')"
End If
Exit Do
Exit Do
End Sub

Hi, above is my code, it isn't running the SQL, and there is a problems with the loop (is this because you can't have a loop within a loop?)

I basically want the code to search through all of the values in table 1, against all of the values in table 2 to see if the field Fname matches the field Fname in table 1 (which is what the if will eventually do, just want the loop to work first)

Modify recordset
The following code is giving some problem.Please can anybody help to modify the code.

Private Sub Command0_Click() Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Set MyDB = CurrentDb() Set MyRS = MyDB.OpenRecordset("tbldata", dbOpenForwardOnly)
I = 51372911 Do While Not MyRS.EOF
MyRS![ID] = I
I = I + 1 Loop End Sub

rs.PercentPosition Bug
I have a VB code block that takes several minutes to run. To prevent the user from getting impatient and cancelling execution (which could potentially mess up 2-3 Tables in the DB), I've been trying to set up a rudimentary progress bar using the .PercentPosition Property of DAO Recordsets.

The code block has three loops in it where it steps through several Recordsets (each containing thousands of Records). The Property works fine through the first loop, but once it gets to the second loop (which uses a different Recordset) it stops working and always returns 0%.

The third loop uses yet another Recordset and .PercentPosition always returns 0% on it too. In the code, I never to anything with .PercentPosition except read the value into a Form field. I never attempt to modify it in any way.

Also, as far as I can tell, the Recordsets continue function properly in all aspects other than their .PercentPosition Property. Has anyone else run into .PercentPosition just breaking like that?

How to: Work With Attachments In DAO
In DAO, Attachment fields function just like other multi-valued fields. The field that contains the attachment contains a recordset that is a child to the table's recordset. Learn how to load and save attachments in a recordset.

Update recordset - 'Not responding'
I am trying to run the following code which updates a recordset, but Access just crashes with 'Not responding'

Sub updatechildren()

Dim dbsSchool As DAO.Database
Dim rstChidren As DAO.Recordset
Dim strSQL As String
Set dbsSchool = CurrentDb
strSQL = "select * from Children where Gender = 2 "
Set rstchildren = dbsSchool.OpenRecordset(strSQL, dbOpenDynaset)
If rstchildren.EOF Then Exit Sub
rstchildren![Hobbies] = "Football"
Do Until rstchildren.EOF

End Sub

The DAO Recordset in Action - An Example Lending Library Database
Last week I wrote a post on How to Access Data with VBA Code. Instrumental in this was the use of the DAO Recordset Object. Through using this object we were able to add, find, edit and delete records from an Access Database table or query. This week I am going to provide a working example of how the DAO Recordset is used in an actual database application. In order to do this, I have created part of a Lending Library Database for demonstration purposes.

How do you reference a field in a query from a recordset?
I am attempting to run through a recordset and use some select queries to find out if a particular record exists, and then if it does, to update it. I was wondering how I could pass a value within the recordset to one of the select queries. I have it loosely set up as:
Dim chk1 As DAO.Recordset
Set chk1 = CurrentDb.OpenRecordset("_Check1")
If chk1.RecordCount > 0 Then
Do Until chk1.EOF

'--> This is where I am having trouble - there is a field in the recordset, chk1!OID, that I need to link to the query _Check2 - how do I do that?
If DCount("*", "_Check2") > 1 then
Run Insert
Run Update
End If

How can I use a value from the chk1 recordset in the _Check2 query? What do I put into the criteria? Help is appreciated,

Recordset string concatenation
I'm trying to concatenate together all the ID fields from a table. However for some reason, my recordests are only picking up a single record meaning the loop cannot concatenate.

I might be missing something simple for all I know

my code :

Dim dbs As Database Dim rst As DAO.Recordset Dim Counter As Integer Dim getID As String Dim ConcatID As String
' set up a updateable recordset of your table Set dbs = CurrentDb Set rst = dbs.OpenRecordset("TmpTblQA", dbOpenDynaset) ' find number of records in your recordset Counter = rst.RecordCount ' loop until run out of records While Counter & gt With rst ConcatID = ![ContactID].Value
getID = ConcatID & ", " & getID
Debug.Print getID
Debug.Print ConcatID End With ' moves to next record rst.MoveNext ' one less record to go Counter = Counter - 1 ' start loop again Wend

order recordset
I use this query to filter an ordering a recordset. After I use the getrow statement. During the loop in the records the ordering is lost!
In effect to speed up the looping in the recordset, to store the recordset in a variant array and lopp in, or not?

Cant open recordset from a query
I'm trying to open a recordset from a query using following code.

Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim param As DAO.Parameter
Set qdf = db.QueryDefs("FilteredClientQueryForAddFamily")

For Each param In qdf.Parameters
param.value = Eval(param.Name)
Next param

Set rst = qdf.OpenRecordset

It doesn't work. There are no errors and it doesn't fill the recordset either.

Error In RecordSet Loop . .
I keep getting an "item not found in collection error"! Can anyone please spot my mistake? I do have a textbox name "Building NO". When I step through, it seems that this line is causing the error. Thank you for any help.


Private Sub Form_Current() Dim MyRecordset As DAO.Recordset Set MyRecordset = Me.RecordsetClone Dim I As Long, iCounter As Long MyRecordset.MoveFirst
Do Until MyRecordset.EOF
If MyRecordset!Building_NO = Me.Building_NO Then iCounter = iCounter + 1
Loop MyBldLabel.Caption = "NO. Of Records for Bldg" & " " & Me.Building_NO.Value Me.BldCount = iCounter End Sub

How to: Delete a Record From a DAO Recordset
Microsoft Access Tutorial: Learn how to delete a record from a DAO recordset.

How to: Extract Data from a Record in a DAO Recordset
After you have located a particular record or records, you may want to extract data to use in your application instead of modifying the underlying source table. Learn several techniques for extracting data from a DAO Recordset.