Code for a simple loop with recordsets

[neillovell]

Hi all,
I'm having a problem looping through tables with a recordset - the Microsoft examples are not very clear to me. I'll explain what I want to do.

I have a table of players (PlayerTable) which has the ID number, name, and total points. I then have a table for each individual team - e.g. SomeTeamA, SomeTeamB etc.

I'm trying to create a recordset for PlayerTable and one for SomeTeamA, and perform some kind of loop where SomeTeamA's first player is looked up in PlayerTable and the total points added to SomeTeamA's totalpoints column. Then the second player is looked up, etc. etc.
I can then adapt the code for SomeTeamB, and the others.

Below I've just got an SQL string outputted to a messagebox (as a test) but the entire thing fails on the .OpenRecordset line. Can anyone help me out?

Private Sub UpdateTeamsButton_Click()

Dim DatabaseConnection As ADODB.Connection
Dim SQL As String
Set DatabaseConnection = Application.CurrentProject.Connection

' Error handling procedure.
If (Err) Then
Error_Handler:
MsgBox Err.Description, vbCritical
End If

Dim Counter As Integer
Dim CurrentPlayerTotalPoints As Integer
Dim TeamMemberRecordset As Recordset
Dim RecordLoop As Recordset

With DatabaseConnection

' Open the RecordSet for PlayerTable
Set TeamMemberRecordset = .OpenRecordset("PlayerTable&quot

For Each rstLoop In .Recordsets

With rstLoop
SQL = "SELECT TotalPoints FROM PlayerTable WHERE ID=123"
MsgBox SQL
End With
Next rstLoop
TeamMemberRecordset.Close
End With
End Sub

 

[Norris68]

Assuming you are using a recent version of Access (i.e. 2000 or above) you should be using ADO recordsets. You populate them by calling the Open method of the recordset object. OpenRecordset was a function of the DAO Database object.

You want ...

Set TeamMemberRecordset = New ADODB.Recordset
TeamMemberRecordset.Open "PlayerTable", CodeProject.Connection

After that though, I'm not sure exactly what you are trying to achieve. You are looping through a series of recordsets, but don't do anything with them. You could do what you want with a series of update queries

 

[neillovell]

Well this is a bit new to me.

I am planning to use the UPDATE query to update each row as I find it in the loop, does that sound good or is there a better way?

I'll try your method though, sounds good. I'm on Access 2002.

 

[Norris68]

An UPDATE query can update multiple rows at a time - and far quicker than VBA could.

 

[neillovell]

RecordLoop is set to -1 - any ideas why this is? I'm sure it's to do with
TeamMemberRecordset.Open "PlayerTable", CodeProject.Connection

If I put parenthesis around the parameters I get the help list appear but the code says syntax error. Without them, it compiles fine (which I wouldn't expect).

Private Sub UpdateTeamsButton_Click()

Dim DatabaseConnection As ADODB.Connection
Dim SQL As String
Set DatabaseConnection = Application.CurrentProject.Connection

' Error handling procedure.
If (Err) Then
Error_Handler:
MsgBox Err.Description, vbCritical
End If

Dim Counter As Integer
Dim CurrentPlayerTotalPoints As Integer
Dim TeamMemberRecordset As Recordset
Dim RecordLoop As Integer

' Open the RecordSet for PlayerTable
Set TeamMemberRecordset = New ADODB.Recordset
TeamMemberRecordset.Open "PlayerTable", CodeProject.Connection

RecordLoop = TeamMemberRecordset.RecordCount

Do While RecordLoop > 0

SQL = "SELECT TotalPoints FROM PlayerTable WHERE ID=123"
MsgBox SQL

RecordLoop = RecordLoop - 1
Loop

TeamMemberRecordset.Close

End Sub

 

[neillovell]

Got it!

I created CursorType and LockType, and put them in as parameters.


CursorType = adOpenStatic
LockType = adLockReadOnly

' Open the RecordSet for PlayerTable
Set TeamMemberRecordset = New ADODB.Recordset
TeamMemberRecordset.Open "PlayerTable", CodeProject.Connection, CursorType, LockType