adding sequential numbers to a group of records

[vsoeiro]

hi all, need your help on this,

I need to add a sequential number to all records in a given table, starting
at a given number. I think i should do something like this:

dim intStartingValue as Integer
'start at 21, for instance
intValue = 20

For Each element In group
intValue = intValue + 1
<field to be updated> = intValue
Next

My problem is to indicate element as being the records, and group being the
table I want to update. Can someone please help on this, or suggest a better
aproach?

Thanks,

Vasco

 

[dch3]

What is the specific reason why you want sequential numbers?

When working with a database, there is no guarantee that the database engine
will return records in any particular order unless you use sorting in your
query - and then you can always get mixed results based on the specific
sorting that you use and situations where a record has nearly the same values
for the fields on which you sort.

 

[vsoeiro]

thanks for your reply. I'll try to make the answer simple: the reason is I
have a local table in my database where I, provisionally, keep and edit some
records, before adding them to a sharepoint list, linked to my database.
before adding those records, I need one of their fields to start where the
last in the sharepoint list ended. for instance, the last is 20, I want my 4
records in the proviosional table to be 21, 22, 23 and 24 before adding them
to te list and deleting them from the provisional table. hope i have made
myself clear.

 

[vbasean]

a couple questions:
1) is the access table mimicking the sharepoint list? If so, then the table
should have the correct last seed. If that's the case, try using the 'DMax'
function to retreave the greatest seed value and add one to it.

2) if this is occuring in a form you can use the form's 'Before Update'
event to add the new seed number to each record as neccessary.

3) if you are looking to just take the 'unassigned' items in your table and
issue them the new seed numbers based off of the seed number you issue. i.e.
last number was 20 so you want 21, 22, 23, ... etcetera. Then iterate a
recordset:

Private Sub UpdateMyRecords(seed as integer)
Dim rs as Recordset
Dim db as Database
Set db = CurrentDb
set rs = db.openrecordset([name or recordset to update]) 'a filtered recordset
'so you only get records that need updating, maybe a query
rs.MoveFirst
do while not rs.Eof
With rs
..Edit
rs.[number you want to add] = seed
..Update
End with
seed = seed + 1
Loop
End Sub

you can issue this Subroutine anywhere you want
UpdateMyRecords(21) 'where '21' is the next number to start at

 

[vbasean]

rs.[number you want to add] = seed

this should be [field you want to update]

didn't mean to confuse anyone.

a couple questions:
1) is the access table mimicking the sharepoint list? If so, then the table
should have the correct last seed. If that's the case, try using the 'DMax'
function to retreave the greatest seed value and add one to it.

2) if this is occuring in a form you can use the form's 'Before Update'
event to add the new seed number to each record as neccessary.

3) if you are looking to just take the 'unassigned' items in your table and
issue them the new seed numbers based off of the seed number you issue. i.e.
last number was 20 so you want 21, 22, 23, ... etcetera. Then iterate a
recordset:

Private Sub UpdateMyRecords(seed as integer)
Dim rs as Recordset
Dim db as Database
Set db = CurrentDb
set rs = db.openrecordset([name or recordset to update]) 'a filtered recordset
'so you only get records that need updating, maybe a query
rs.MoveFirst
do while not rs.Eof
With rs
.Edit
rs.[number you want to add] = seed
.Update
End with
seed = seed + 1
Loop
End Sub

you can issue this Subroutine anywhere you want
UpdateMyRecords(21) 'where '21' is the next number to start at

hi all, need your help on this,

I need to add a sequential number to all records in a given table, starting
at a given number. I think i should do something like this:

dim intStartingValue as Integer
'start at 21, for instance
intValue = 20

For Each element In group
intValue = intValue + 1
<field to be updated> = intValue
Next

My problem is to indicate element as being the records, and group being the
table I want to update. Can someone please help on this, or suggest a better
aproach?

Thanks,

Vasco