Loop to all records in a recordset.

[doulostheou]

I don't have very much experience dealing with recordsets. I need to run a loop that will run for each record in my table. Can anyone get me started?

 

[Nero]

What do you want to do with the loop?

 

[Nero]

I don't know if this will help.
basically it will loop through your records and add a value to the specified field.

On Error GoTo err
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Db Name")

With rst
.MoveFirst
Do Until rst.EOF
.Edit
.Fields("Field Name") = "Value"
.Update
.MoveNext
Loop

End With
Set rst = Nothing
Set db = Nothing

cls:
Exit Sub
err:
MsgBox err.Description
Resume cls
End Sub

 

[doulostheou]

I had a large macro that I had written when I was first learning access. I want to rework this into code. I have a logsheet that has up to 50 users at a time. Due to errors I was receiving having all the users use the logsheet at once, I created a logsheet for each person and a master database that pulls their records from the day into a master table one person at a time. The old macro ran an append query and a delete query for each linked table (named with the users first initial and last name).

I have created a table called "MaintenanceList". In it will be the names of each of the linked tables. I will have a form based off the maintenance table. On it will be a text box with the name of the table to be appended and then deleted (I have called this textbox "LogsheetID"). I then will need it to go to the next record append all records from whatever table is stored in the LogsheetID field into the Logsheet table and then delete all records from the LogsheetID table, go to next record append then delete, go to next record append then delete, etc., stopping when it reaches the last record.

 

[doulostheou]

Thanks for your post Nero. This would be even better than what I envisioned as I wouldn't have to base the form off of the table (which was a bit clunky). I have just a few questions about applying this to my situation.

When you say Set rst = db.OpenRecordset("Db Name"), do I specify my table here? If not, where do I tell it which table to get the records from.

I'm following the loop itself, and I'm guessing I would just place my code between "Do Until rst.EOF" and ".MoveNext" but I'm a little confused as to how to get the information from the Field.

My field name is LogsheetID and I'm guessing I could just store the information found in that field in a variable to use with my two SQL statements. Would this work?

Dim TableName as String
TableName = .Fields(LogsheetID)

Any help would be appreciated. This is a new direction for me.

 

[doulostheou]

I think I have been able to successfully adapt your code to my needs. However, I still have a few problems that I believe stem from me not having a firm grasp on dealing with recordsets. The code appears to run but it ends by saying it cannot run the SQL statement for "Auto". There is no Auto in my table. Secondly, the code takes awhile to run because it runs an Append and Delete query for every employee, regardless of whether or not they have any records. I tried to use what I learned tonight of recordsets to decide if the recordset isnull. However, it does not appear to be working. My full code follows:

On Error GoTo ErrHandler
DoCmd.SetWarnings False
DoCmd.Hourglass True

Dim DB As Database
Dim RST As Recordset
Set DB = CurrentDb
Set RST = DB.OpenRecordset("MaintenanceList")
With RST
.MoveFirst
Do Until RST.EOF
Dim TableName As String
TableName = .Fields("LogsheetID")
Dim CheckTable As Recordset
Set CheckTable = DB.OpenRecordset(TableName)
If Not IsNull(CheckTable) Then
DoCmd.RunSQL "INSERT INTO Logsheet SELECT " & TableName & ".* FROM " & TableName & ";"
DoCmd.RunSQL "DELETE " & TableName & ".* FROM " & TableName & ";"
End If
.MoveNext
Loop
End With
Set RST = Nothing
Set DB = Nothing

Beep
MsgBox "The Maintenance has been completed. Thank you!"
DoCmd.Close acForm, "Maintenance Dialog"
GetOut:
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub

ErrHandler:
Beep
MsgBox Err.Description
Resume GetOut
Again, any assistance would be greatly appreciated.

[This message has been edited by doulostheou (edited 04-22-2002).]

[This message has been edited by doulostheou (edited 04-22-2002).]

 

[Pete_Hill]

I am an amateur, but here is my attempt:

Dim rst as recordset
Dim strtablename as string

set rst = currentdb.openrecordset("MaintenanceList",dbopentable)

'Opens the table holding the names of the linked tables

rst.movefirst
'Start at the very beginning, a very good place to start

Do 'Start looping though the linked table names

strtablename = rst!LogsheetID

' Im guessing that is the field with the names of the linked tables?

DoCmd.RunSQL "INSERT INTO LogSheet SELECT * FROM " & strtablename & ";"
DoCmd.RunSQL "DELETE * FROM " & strtablename & ";"

'Okay, we've appended then deleted the records from the currently named table (LogSheetID)

rst.movenext
'go to the next table name in maintenancelist

Loop Until rst.EOF
'if there is no next record i.e. that was the last record, then exit the loop

rst.close

'and that should be it
rst.close

 

[Nero]

I have ammended my original code so that if a field value is empty it will move to the next record.Hope it helps with the second part of your problem.
On Error GoTo err
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table Name")

With rst
.MoveFirst
Do Until rst.EOF
Do While IsNull(rst.Fields("Field Name to test"))
.MoveNext
Loop

.Edit
.Fields("Field Name") = "Value"
.Update
.MoveNext
Loop
End With

Set rst = Nothing
Set db = Nothing

cls:
Exit Sub
err:
MsgBox err.Description
Resume cls
End Sub

 

[doulostheou]

I still must be doing something wrong.

Nero, I tried following what you posted, but it seemed that was trying to detect if a field was null in the MaintenanceList table (the table that holds the names of all the linked tables). I need to tell if the linked table is null.

Working with what I've learned about recordsets so far, I tried inserting the following code inside the loop:

Dim TRST As Recordset
Set TRST = DB.OpenRecordset(TableName)
If Not IsNull(TRST.Fields("EID")) Then ....
(Here I append and delete the records and proceed with the loop)

The loop is working perfectly, except that it does not stop if the table is null (the "auto" problem was a stupid mistake on my part). Every table is appended and deleted. The only thing I can think of that could be wrong is that TableName is a variable. But it seems like that shouldn't have an adverse affect.

By the way, thanks for all your help getting me this far!

[This message has been edited by doulostheou (edited 04-25-2002).]