Help with loop

[mok44]

I am creating a rotation of numbers from 1 to 20. With the code below, I trying to the update the field RANK of table tblRanks with value from 1 to 20, everytime a user click on cmdRank button. With each click, each field value is incremented with 1, unless the current value is 20, then reset it to 1.
Field rank is Integer data type. I keep getting Type Mismatch error (runtime 13) on line :"Set Rs1 = CurrentDb.openrecordset("tblRanks&quot"
Any ideas? Thanks.

Here is my code:

Private Sub CmdRank_Click()
Dim rank As Integer
Dim Rs1 As Recordset

Set Rs1 = CurrentDb.openrecordset("tblRanks&quot

Rs1.MoveFirst 'Go to first record
Do Until Not Rs1.EOF 'Read until end of file
If rank = 20 Then rank = 1 'if 20 reset to 1
rank = rank + 1 ' else add 1
Rs1.MoveNext 'go to next record
Loop
Rs1.Close
Set Rs1 = Nothing
End Sub

 

[NorthNone]

You may need to specify a DAO recordset. It may think it is an ADO recordset.
Dim Rs1 As DAO.Recordset
HTH
JeanS

 

[CajunCenturion]

In addition to the Recordset issue explained by NorthNone, you also have a problem in your rank statements.

If rank = 20 Then rank = 1   'if 20 reset to 1
rank = rank + 1   ' else add 1

That is not an If-Then-Else statement. Both statements will always be executed meaning that rank with go from 20 to 2, because it will always be incremented. To make it an If-Then-Else statement, try the following:

If (rank = 20) then
   rank = 1
Else
   rank = rank + 1
End If

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mok44]

Thank you both for the help, but still not working. I am not getting the error message but still not getting the values in Rank to change.
Thanks

Here is my code again (edited):

Private Sub CmdRank_Click()
Dim rank As Integer
Dim Rs1 As DAO.Recordset

Set Rs1 = CurrentDb.openrecordset("tblRanks&quot

Rs1.MoveFirst 'Go to first record
Do Until Not Rs1.EOF 'Read until end of file
If rank = 20
Then rank = 1 'if 20 reset to 1
Else
rank = rank + 1 ' else add 1
End If
Rs1.MoveNext 'go to next record
Loop
Rs1.Close
Set Rs1 = Nothing
End Sub

 

[RoyVidar]

Hi!

You are changing the integer variable Rank. To update the field in the recordset, try;

[tt]Rs1.MoveFirst 'Go to first record
Do Until Not Rs1.EOF 'Read until end of file
If rs1!Rank = 20 Then
rs1!Rank = 1 'if 20 reset to 1
Else
rs1!Rank = rs1!Rank+ 1 ' else add 1
End If
rs1.update
Rs1.MoveNext 'go to next record
Loop[/tt]

(BTW - this doesn't require a variable called Rank)

HTH Roy-Vidar

 

[dhookom]

I would be concerned about the order of the records in the recordset. I never assume records will be in a particular order unless I use "ORDER BY" in the recordset.

Also, if you want to update a DAO recordset, you must use:
rs1.Edit
'
'
rs1.Update

Also, I question why a rank is being saved in a table when I assume it can be calculated. If you need to save a rank, I might first try an update query rather than creating a recordset. These opinions are based solely on my speculations.

Duane
MS Access MVP

 

[CajunCenturion]

One problem may be the Loop control statement.

Do Until Not Rs1.EOF

EOF will be False until you reach the end of the recordset. As stated, you will process the loop until you are not longer at the End of the Recordset. What I think you mean to enter the loop until you reach the EOF.

Do Until Rs1.EOF

(do this until I reach EOF)
or

Do While Not Rs1.EOF

(Do this while I'm not as end of file)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mok44]

You're absolutely right dhookom, an update query works fine in this case. Thanks to all for your help and suggestions, I am still working on a subroutine solution. I will post it when I get it to work.