jhabey
Programmer
- Dec 26, 2012
- 6
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
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