Auto increment a number in code

[L1sta]

Hi there
I've got a subform which needs an auto-incremented number as one of its fields.
I was using the following code previously, which works fine - but i recently put in code to allow the user to 'archive' a record (effectivly this stops the record being selected by the subform, but keeps it in the table). This means that if there are 4 records (1,2,3,4) and number 2 is archived, i need the numbers 3 and 4 to become 2 and 3.
Is this possible?

here is my current code:

If IsNull(DLookup("[Querynumber]", "tblQuery", "[tblsubjectID] = text37")) Then
MsgBox "no query records"
Me.Querynumber.Value = "1"
End If

If IsNull(Me.Querynumber.Value) Then
Me.Querynumber.Value = Nz(DMax("[Querynumber]", "tblquery", "( [tblSubjectID] = Text37 AND Active = yes)") + 1)
End If

Thanks in advance for any help,
Lista

 

[TheAceMan1]

how are ya L1sta . . .

[blue] . . . i recently put in code to allow the user to 'archive' a record . . . This means that if there are 4 records (1,2,3,4) and number 2 is archived, [purple]i need the numbers 3 and 4 to become 2 and 3[/purple].
Is this possible?[/blue]

Too many seem to be stuck on sequential numbers. Unless this numbering has some significance to you, whats the difference as long as there all unique?

Besides you'd now have two records with an index of 2. [blue]What will you do when you want to bring the archived record back into the form? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[L1sta]

Hi - thanks for replying
Unfortunatly the sequential number is necessary on the form. I don't mind if the archived record has no index number and when it was restored it could just be given the next in the list - the only important bit is that the form never has any gaps or any duplicates, and always starts at 1.
What I think I need is something that will perform the function of Dmax but looking up a value on the subform instead of in a table.
Thanks, Lista

 

[ProgramError]

SO the principle is...

Copy selected record to archive table.
Delete selected record from master table.
sort the records in numerical order (just in case the table has been filtered or sorted by another field at some other time).
Run a vba routine from 1 to EOF putting the number in the sequential field and increasing it by one each time.

dim counter as integer
dim rs as recordset
counter = 1
rs.movefirst
do while not rs.EOF
.edit
!Querynumber = counter
.movenext
.update
counter = counter +1
loop

or something along these lines.

DAO reference needs adding if not already added


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[TheAceMan1]

L1sta . . .

One a record is archived you'll need to serialize the entire table as you can archive any record. [blue]ProgramError[/blue] has laid out the foundation to do this.

You'll also need DMax for adding new records.

If you have any problems let us know . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[SeanWcisel]

How about this:

Store the max record number used (assign new ones as max+1)

use a while loop like this:

Dim currentRecord (VB uses Dim, right?)
Dim previousRecord
Dim nextRecord
If (currentRecord.Index != previousRecord.Index + 1)
{
currentRecord.Index--;
}
Else
{
previousRecord = currentRecord;
currentRecord = nextRecord;
nextRecord = FindNext(currentRecord); (cop out, I dunno how you want to go about this)
}

anyway, I think I broke into a crappy dialect of c++ there, but you'll need some sort of hashing algorithm, or something like that.

anyway, doubt that was helpful, but good luck anyway

Sean

 

[TheAceMan1]

How are ya SeanWcisel . . .

Access uses [blue]VBA![/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: