New record using a form

[ck11]

I am trying to use a form to allow a person to generate a new record. There is a field that is an autonumber. The forms works fine except if someone enters anydata and then decides not to save it then the autonumber still progress to the next number. How do I reset the autonumber? I am using an if dirty statement to see if data has been changed and then if the user does not want to save changes the code converts everything back to the origional, but this does not work for autonumbers for some reason.

Chris

 

[PHV]

This is standard behaviour for autonumbers.
What does matter the autonumber is not sequentially numbered ?
An autonumber should has no other meaning than be unique.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ck11]

I am trying to use the autonumber feature to generate work order numbers. And a sequential order would be better

Ck11

 

[willir]

Hi Chris

M$ Access generates the autonumber on the OnInsert event (as soon as data entry commences). So you are quite right in being concerned about the autonumber sequence being lost. (Other database engines generate the serial number just before the update event which would suite your needs more).

You need to create your own process for generating the Invoice Number.

If you are using a shared database, you have another concern...
When one user is in the process of creating an Invoice, within the same time span another user is creating an invoice, you run the risk of both end users grabbing the same Invoice Number! (This is because both records have not have been committed so both data entry tasks are ignorant of the other in the 2 or 3 min it takes to generate the invoice.)

There are various solutions, but in a mutiple user environment, it is best to use a function, and store the Invoice Number in a control table. Here is something to consider...

Function GetCtlNum (strSysName as String) as Long
'Control table format
'SysName - text, primary key, INVOICE, PAYROLL, WORKORD, etc 
'SysNum - long
'Function 
'- called with system name
'- gets current control number from control table
'- updates control number + 1
'- returns control number

Dim strSQL as String, strQ as String, lngSysNum as Long

lngSysNum = DLookUp ("[SysNum]", "tblControl", "[SysName] = " & strQ & strSysName & strQ

strSQL = "UPDATE tblControl SET SysNum = SysNum +1 " _
& "Where SysName = " & strQ & strSysName & strQ

DoCmd.SetWarnings (False)
DoCmd.Run strSQL
DoCmd.SetWarnings (True)

GetCtlNum = SysNum

End Function

Then call the function, snippet of code BEFORE UPDATE event, or run when the end user clicks on a command button such as "Commit"...

Me.InvoiceNumber = GetCtlNum ("INVOICE")

No system is perfect, but this approach grabs the next control number just before updating the record, thus maintaining a serial / sequential apporach, and greatly minimizing the risk of two end users grabbing the same control number.

You will of course have to prevent end users from accidentally deleting records, or modifying the control table.

Richard