Access Looping through Recordset And Incrementing 1

[jhabey]

Access Looping through Recordset And Incrementing
Greetings,
I currently have an access table with the following columns:
Table Name:tbl_Adjments
AcctKey GRPNO LOCNO PURGESTAT ADJAMT ADJNO
3 1000 500 0 500 0
4 1000 500 0 600 0
5 1000 500 0 700 0
6 1000 500 0 800 0

Data can be added to this table via a form, and can have many more records in this table.
I have another table with the following columns:
Table Name: Tbl_MedipremAdjs
GRPNO LOCNO PURGESTAT ADJAMT ADJNO
10327 0 1 12 2142
10037 0 4 750 2141
10037 0 4 288 2140
(I do not want to directly add the records here until tbl_Adjments
is completed by users).

Step 1
What I am trying to do is write code that will loop through and update the ADJNO in tbl_Adjments so that ADJNO takes the Max in ADJNO in tbl_MedipremAdjs and adds one.
So, tbl_Adjments.adjno would be 2143 the next record would be 2144 the next record 2145 and the last would be 2146.
(I need to perform this operation in bulk and can only be done after the user has entered the last record.

Step 2
Once tbl_Adjments is updated I will update the Tbl_MedipremAdjs with the data from tbl_Adjments.

Step 3
tbl_Adjments will be cleared so users can enter new records at a later time. Once the user has entered new records I should be able to run Step 1 again however the new Max Adjno will be 2146 and the first record will be 2147 etc.

I am most concerned about Step 1.
I have written code that runs through a table and prints out a report, but seem to be stuck in this.

I was able to write code that does update one record with the Max adjno from Tbl_MedipremAdjs but can't seem to then take and store that value to update the next record.

Thanks

 

[jhabey]

Sorry tables should read as follows:
Table Name:tbl_Adjments
AcctKey GRPNO LOCNO PURGESTAT ADJAMT ADJNO
3 1000 500 0 500 0
4 1000 500 0 600 0
5 1000 500 0 700 0
6 1000 500 0 800 0


GRPNO LOCNO PURGESTAT ADJAMT ADJNO
10327 0 1 12 2142
10037 0 4 750 2141
10037 0 4 288 2140

 

[jhabey]

Good grief...Ok lets try again. Just ignore ...thanks

Table Name:tbl_Adjments
AcctKey GRPNO LOCNO PURGESTAT ADJAMT ADJNO
3........ 1000.....500...... 0............. 500...... 0
4........ 1000..... 500...... 0............. 600...... 0
5........ 1000..... 500...... 0............. 700.......0
6........ 1000..... 500...... 0............. 800...... 0


Table Name: Tbl_MedipremAdjs
GRPNO LOCNO PURGESTAT ADJAMT ADJNO
10327....0........ 1................ 12....... 2142
10037....0........ 4................ 750...... 2141
10037....0........ 4................ 288...... 2140

 

[Andrzejek]

To get the next ADJNO from Tbl_MedipremAdjs table you may just do:
[tt]
SELECT MAX(ADJNO) + 1 As NextAdjNo from Tbl_MedipremAdjs
[/tt]
and store it in a variable iMyCounter As Integer (for example)

To create your recordset out of tbl_Adjments table you have something like:
[tt]
rst.Open "SELECT * FROM tbl_Adjments ORDER BY AcctKey"
[/tt]
so you may just:

For i = 1 to rst.RecordCount
  strSQL = "UPDATE tbl_Adjments SET ADJNO = " & iMyCounter 
  strSQL = strSQL & " WHERE AcctKey = " & rst!AcctKey [green]
  'Execute this Update SQL[/green]
  iMyCounter = iMyCounter + 1  [green]'Increment ADJNO[/green]
Next i

BTW, to align your data, use: [ignore][pre] some data [/pre][/ignore]

[pre]
AcctKey GRPNO LOCNO PURGESTAT ADJAMT ADJNO
3 1000 500 0 500 0
4 1000 500 0 600 0
5 1000 500 0 700 0
6 1000 500 0 800 0[/pre]

Have fun.

---- Andy

 

[jhabey]

Thanks, The following updates on one record but is not going to the next record in my table and updateing that record, etc. Any pointers?

Private Sub Command66_Click()
Set db = CurrentDb()
Dim rst As DAO.Recordset
Dim iMyCounter As Integer
Dim rs As DAO.Recordset

Nextadjno = DMax("ADJNO", "Adjstments_MEDIPremAdjs") + 1
iMyCounter = Nextadjno
Set rst = CurrentDb.OpenRecordset("tbl_adjments", dbOpenDynaset)

With rst
For i = 1 To rst.RecordCount

strsql = "UPDATE tbl_Adjments SET ADJNO = " & iMyCounter
strsql = strsql & " WHERE AcctKey = " & rst!AcctKey

db.Execute strsql 'Execute this Update SQL

iMyCounter = iMyCounter + 1 'Increment ADJNO
.MoveNext
Next i
End With
End Sub

 

[Andrzejek]

First, you need [tt]Option Explicit[/tt] at the top of your code.
That will take care of un-declared variables, like Nextadjno

Second, Command66 is really bad name for a button :-(

Try this:

Private Sub Command66_Click()
Set db = CurrentDb()
Dim strSQL as String
Dim rst As DAO.Recordset
Dim iMyCounter As Integer
Dim rs As DAO.Recordset

Nextadjno = DMax("ADJNO", "Adjstments_MEDIPremAdjs") + 1
iMyCounter = Nextadjno
Set rst = CurrentDb.OpenRecordset("tbl_adjments", dbOpenDynaset)[green]
'Your rst is not in any Order, does it matter to you?[/green]
With rst
  Do While Not .EOF

    strSQL = "UPDATE tbl_Adjments SET ADJNO = " & iMyCounter
    strSQL = strsql & " WHERE AcctKey = " & !AcctKey.Value

    Debug.Print strSQL
[red]
    db.Execute strSQL [/red]

    iMyCounter = iMyCounter + 1 
    .MoveNext
  Loop
End With

End Sub

Place a breake on the [red]RED[/red] line of code and see what you have in Immediate Window for your UPDATE sql.

Have fun.

---- Andy