VBA to increment all records in a table by 1

[rblaustein]

I am trying to write a VBA procedure to increment an entire table by a
value of 1 when a user clicks on a button of a form (or selects from a
custom menu). The table, called tblStudents contains student ID's,
names, etc. and their grade level (e.g.- 10). The user has requested
for this functionality to be built so that he/she can click the button
at the end of each school year, and thus update the entire table with
the new grade levels. Any ideas on the best way to do this???

This is what I tried thus far, but to no avail...
Private Sub btnUpdateGradeLevel_Click()
Dim RecordQty As Integer
Dim RecordCounter As Integer
DoCmd.OpenTable "tblStudents", acViewNormal, acEdit
RecordQty = DCount("Grade", "tblStudents")
DoCmd.GoToRecord acDataTable, "tblStudents", acFirst

For RecordCounter = 1 To RecordQty
DoCmd.GoToRecord acDataTable, "tblStudents", acGoTo,
RecordCounter
Grade = Grade + 1
Next

DoCmd.Close acTable, "tblStudents", acSaveYes
End Sub

This code only updates the first record - but by the total number of
records in the table. In other words, if John Smith is the first
record of 1000 total records, and he is in grade 10, it updates his
record to be in grade 1010, and none of the other records are changed
at all.

Any help is greatly appreciated!!! Thanks!

 

[Roger Carlson]

There are several solutions, but I'll give you two just to illustrate some
techniques:

1. Using ADO (ActiveX Data Objects)

Sub btnUpdateGradeLevel_Click()
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
'Open a recordset on the table.
Set conn = CurrentProject.Connection
rs.Open "tblStudents", conn, adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
rs!Grade= rs!Grade+ 1
rs.MoveNext
Loop
End Sub

2. Using Embedded SQL Update Query:

Sub btnUpdateGradeLevel_Click()
Dim conn As ADODB.Connection
Dim strSQL As String
Set conn = CurrentProject.Connection
strSQL = "UPDATE tblStudents SET Grade= [Grade]+1;"
conn.Execute strSQL
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Roger's response is very good; however, it assumes you are using a ADP. If
you are using an mdb, the easiest method would be:

CurrentDb.Execute("UPDATE tblStudents SET Grade= [Grade]+1;"),
dbFailOnError

 

[rblaustein]

Thank you both so much!!! I tried all three options (definetely helps
w/ the learning process to understand the different ways to attack the
problem), and each one worked perfectly.

Thanks again - very much appreciated.

 

[Roger Carlson]

While the example you gave is undoubted easier, both of mine will work in
MDBs in Access 2000 and higher.

Just don't want anyone to misunderstand.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger's response is very good; however, it assumes you are using a ADP. If
you are using an mdb, the easiest method would be:

CurrentDb.Execute("UPDATE tblStudents SET Grade= [Grade]+1;"),
dbFailOnError

I am trying to write a VBA procedure to increment an entire table by a
value of 1 when a user clicks on a button of a form (or selects from a
custom menu). The table, called tblStudents contains student ID's,
names, etc. and their grade level (e.g.- 10). The user has requested
for this functionality to be built so that he/she can click the button
at the end of each school year, and thus update the entire table with
the new grade levels. Any ideas on the best way to do this???

This is what I tried thus far, but to no avail...
Private Sub btnUpdateGradeLevel_Click()
Dim RecordQty As Integer
Dim RecordCounter As Integer
DoCmd.OpenTable "tblStudents", acViewNormal, acEdit
RecordQty = DCount("Grade", "tblStudents")
DoCmd.GoToRecord acDataTable, "tblStudents", acFirst

For RecordCounter = 1 To RecordQty
DoCmd.GoToRecord acDataTable, "tblStudents", acGoTo,
RecordCounter
Grade = Grade + 1
Next

DoCmd.Close acTable, "tblStudents", acSaveYes
End Sub

This code only updates the first record - but by the total number of
records in the table. In other words, if John Smith is the first
record of 1000 total records, and he is in grade 10, it updates his
record to be in grade 1010, and none of the other records are changed
at all.

Any help is greatly appreciated!!! Thanks!