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.
E.g.
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
rs.MoveNext
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)

eg.

table A
1,ian,smith
2,paul,jones
3,kevin,thomas

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

table B
1,john,evans
2,paul,davis
3,ringo,starr

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")
rs1.MoveFirst
Do Until rs1.EOF
rs2.AddNew
rs2![name] = rs1![name]
rs2.Update
rs1.MoveNext

Loop

rs2.Close
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")
rs.MoveNext
Loop

rs.Close
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')"
DoCmd.RunSQL
End If
rst2.MoveNext
Exit Do
rst1.MoveNext
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.Edit
MyRS![ID] = I
MyRS.Update
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.Edit
rstchildren![Hobbies] = "Football"
Do Until rstchildren.EOF
Loop

End Sub


How do you reference a field in a query from a recordset?
All-
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
chk1.MoveFirst
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
Else
Run Update
Endif
chk1.MoveNext
Loop
End If
chk1.Close

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!
Why?
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.

MyRecordset!Building_NO


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
MyRecordset.MoveNext
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.


Loop not working
Private Sub Text8_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.MoveFirst
Do While Not (rst.BOF Or rst.EOF)
rst.Edit
rst![Run] = IIf(Me.checkbox = True, "1", "2")
rst.Update
rst.MoveNext
Loop
Me.Requery
Set rst = Nothing
End Sub

It reads the selected checkbox and gives all the record values as 1 if TRUE - vice-versa

I want it to be displayed according the checkbox
(checked means 1, unchecked means 2)
but it is just giving me a number that is either all 1's or all 2's in all the records

am I doing this loop wrong?
Hero honda hunk specifications