autosequencing numbers in access 2000

[BobBrooks]

I have a field in a Form that is used in the Report for the Label number. This needs to automatically increment with each additional record and be a field that can have the starting number resetable if necessary. Autonumber does not qualify for the obvious reasons.
I may be trying to make this too hard but I can't seem to make it work to my satisfaction. If any one has any ideas on this subject, please share them with me. I am trying to create a Numbered label for approval of manufacturing ingredients and I need to start at 49001 and increment from there. The ingredients will vary but the sequence of numbers does not.
Example:
_____________________________________________________
Number[ 49001 ]

Approved for Use

Date: [ 00/00/0000 ] Code: [ 000000 ]
Approved Initials: [ AA ] Lot No: [ 12345678 ]
______________________________________________________
The Field > "Number" must increment and allow deletions in case of input error but retain the last record used.

Thanks in advance to all suggestions
Bob Brooks

 

[Trendsetter]

I have taken this code extract from an old application of mine that needed to generate the next (highest + 1 ) workshop number for a new set of records to be set against.
This may give you sufficient help to get what you want.

Let me know

Private Sub cboWorkshopQS_AfterUpdate()
Me!lstMaxNum.Requery
Me!txtCount = Forms!frm6MakeNewNum!lstMaxNum.ListCount + 1
If IsNull(Me!txtCount) Or Me!txtCount = 0 Then
Me!txtNewNum = Me!cboWorkshopQS & "/" & "001"
Else
Me!txtNewNum = Me!cboWorkshopQS & "/" & Format(Me!txtCount, "000&quot
End If
Me!txtAgain = Me!cboWorkshopQS
Me!txtNewNum.SetFocus
End Sub

 

[BobBrooks]

To: Trendsetter

This doesn't appear to do what I need or I may not be implementing it correctly.
______________________________________________________
Number[ 49001 ]
("The above field ("Number&quot is the field I need to start at 49001 and increment from there. the remaining 4 fields are for user input.)
Approved for Use

Date: [ 00/00/0000 ] Code: [ 000000 ]
Approved Initials: [ AA ] Lot No: [ 12345678 ]
______________________________________________________

 

[Trendsetter]

Hi Bob
Can you send me your application so that I can do some hands on for you.

Frank@fhsservices.co.uk

Do you want the system to generate the other 4 fields or do these exist in the database???

 

[hymn]

Possibly something like this

Set Db = CurrentDb()
Set rds = Db.OpenRecordset("SELECT DISTINCTROW Max(Table.Number) AS MaxOfNumber FROM Table WHERE((Table.DocIdent = '" & [Forms]![FormName]![Number] & "') ;", DB_OPEN_DYNASET)

Me![Number] = IIf(IsNull(rds![MaxOfNumber]), 1, rds![MaxOfNumber] + 1)
Me![Number].Requery

I have a Shortened version but where? Hope this helps
Hymn