Auto increment field on multi-user

[Michelle Mulo]

Hi everyone

I am writing a multi-user database in Access 97 (on a peer-to-peer
setup), and I need to create a number that is in the format of
"1234/01". The 1234 part of the field should increment in the way that
an autoincrement field would, but the number generated will be able to
be amended by the user for a totally different number (if necessary).

If that '1234' number wasn't used, I want to preserve it, to use it for
the next record.
BTW, the "/01" part refers to the current year.

Any suggestions on how I can accurately do this in code?

TIA
Michelle

[Pete B]

There is a MSKB article on how to do this, complete with code. Forget
the title, but it is something like how to build a custom multiuser
autocount system. It really consists of nothing more than a "private"
table which stores the next available number to use, and code in the
form's event procedures to fetch the number when adding a record and
update the table for the next number to use.

--
Pete B

"Chuck Grimsby" <cdotg...@worldnetdotattdot.net> wrote in message
news:in51btcb5dgghsn9m...@4ax.com...
>
> Basically, there's no =good= way to do this with an autonumber
field.
> The smartest way to do it is to use 2 fields, neither of which is an
> autonumber, and do your own incrementing.
>
> Joe Foster has posted some good code to do the incrementing a number
> of times. Search http://groups.google.com/ for "GetNextID". (I
think
> that's what he called the function....)

> ---
> If Thine Enemy Offendth Thee Giveth His Son A Drum

[Pete B]

Microsoft Knowledge Base. Got to the Support section of the MS
website. It is arguably the single most important source of info for
all things Microsoft. Thousands of articles on every aspect of MS
development.

http://search.support.microsoft.com/kb/c.asp

--
Pete B

"Michelle Mulo" <mm...@yahoo.com> wrote in message
news:3AB12687...@yahoo.com...
> Thank you everyone who helped out, I will look into those
suggestions.
>
> Pete, pardon my ignorance, but what is MSKB?
>
> Regards
> Michelle

[Brendan Reynolds]

This is work in progress and probably not really ready for prime time yet,
but as this question seems to be coming up a lot lately, I'll throw it into
the fire and see if it survives! :-)

Option Compare Database
Option Explicit

Private mdb As DAO.Database
Private mrst As DAO.Recordset

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrorHandler
If Me.NewRecord Then
Set mdb = CurrentDb
Set mrst = mdb.OpenRecordset("qryCompanyNumber", dbOpenDynaset,
dbDenyWrite, dbPessimistic)
With mrst
If (.BOF And .EOF) Then
.AddNew
!CompanyNumber = 1
Me!txtCompanyNumber = 1
Else
.MoveFirst
.Edit
!CompanyNumber = !CompanyNumber + 1
Me!txtCompanyNumber = !CompanyNumber
End If
End With
End If

ExitProcedure:
Exit Sub

ErrorHandler:
Select Case Err
'
Case Else
Cancel = True
If Not mrst Is Nothing Then
With mrst
If .EditMode Then
.CancelUpdate
End If
.Close
End With
Set mrst = Nothing
End If
If Not mdb Is Nothing Then
Set mdb = Nothing
End If
Beep
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbInformation, _
"Unexpected Error"
Resume ExitProcedure
End Select

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo ErrorHandler
Select Case DataErr
'
Case Else
If Not mrst Is Nothing Then
With mrst
If .EditMode Then
.CancelUpdate
End If
.Close
End With
Set mrst = Nothing
End If
If Not mdb Is Nothing Then
Set mdb = Nothing
End If
Beep
Response = acDataErrDisplay
End Select

ExitProcedure:
Exit Sub

ErrorHandler:
Select Case Err
'
Case Else
Beep
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbInformation, _
"Unexpected Error"
Resume ExitProcedure
End Select

End Sub

Private Sub Form_AfterUpdate()

On Error GoTo ErrorHandler
If Not mrst Is Nothing Then
With mrst
If .EditMode Then
.Update
End If
.Close
End With
Set mrst = Nothing
End If
If Not mdb Is Nothing Then
Set mdb = Nothing
End If
mboolCodeFromNumber = False
Me!cboFind.Requery

ExitProcedure:
Exit Sub

ErrorHandler:
Select Case Err
'
Case Else
Beep
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbInformation, _
"Unexpected Error"
Resume ExitProcedure
End Select

End Sub

--
Brendan Reynolds
bren...@indigo.ie
http://www11.ewebcity.com/brenreyn

"Pete B" <Pete....@Home.com> wrote in message
news:tb2528b...@news.supernews.com...

[Michelle Mulo]

Thank you everyone who helped out, I will look into those suggestions.

Pete, pardon my ignorance, but what is MSKB?

Regards
Michelle