keeping track of "Escaped" Autonumber records

[Paul James]

I'm building an accounting system that tracks Invoices, and I'm using an
Autonumber field to generate the Invoice ID.

If a user starts entering an invoice but presses "Esc" before the record is
saved, that record, including the Autonumber InvoiceID disappears from the
form. But then when the user starts entering another invoice in the form,
the Invoice ID will go to the next Autonumber, and the previous number will
be absent from the Invoice ID sequence.

For auditing purposes, I would prefer to have a record for all Invoice ID
numbers, even those that the user "escaped" from. In that case, I'd just
like to mark that Invoice record as Void.

I've got the Allow Deletions property of the form set to No.

I figured that the best way to overcome the disappearing Invoice records
would be to save the record as soon as the makes a change of any kind to any
control in the form, which is when the new Autonumber / InvoiceID is
created. So I tried the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

However, if the user presses "Esc" after entering several characters, the
record still disappears from the form, while the Autonumber notches up
another increment, but the "escaped" record never makes it to the table, and
that voided record is missing from the Invoice table.

Can anyone tell me how I can save these"escaped" invoices as records in the
Invoice table?

Thanks in advance,

Paul

 

[Jamie]

Hi Paul,

It isn't advisable to treat an AutoNumber as a meaningful piece of data (in
this case, as in Invoice ID requiring an audit trail). The autonumber field
should only be used as a means of uniquely identifying a row in a table and
is basically useless for any other purpose (for the reason you have
identified).

Can I suggest adding another field as an Invoice Number and controlling
incrementation of it with some kind of logic that wil suit your business
process. I have included a sample below. I wrote this code to get the next
primary key from a table, but you can use it to get any numeric value (it
doesn't have to be a key field).

'******************************************************************************
'This function only works on key values which can be interpreted as numeric
'Receives: Primary key column name, table name and an optional increment
amount
'Returns: The last records primary key value incremented by 1 or {increment}
'******************************************************************************
Public Function GetNextKey(KeyColumn As String, _
TableName As String, _
Optional Increment As Integer) As Long
On Error GoTo Err_GetNextKey
Dim varTmp As Variant

'Get the current max key value
'Make sure the key being used is numeric
varTmp = DMax(KeyColumn, TableName)
If IsNumeric(varTmp) Then
'Increment the key value by 1 or the increment amount
'then assign it to the function variable for passing back
If Increment = 0 Then
GetNextKey = CLng(varTmp) + 1
Else
GetNextKey = CLng(varTmp) + Increment
End If
End If

Exit_GetNextKey:
Exit Function

Err_GetNextKey:
LogError Err.Number, Err.Description, "basDataMods-GetNextKey", , -1
Exit Function

End Function

 

[Van T. Dinh]

You are much better off using a "Custom Number Sequence"
rather than an AutoNumber Field.

Search Google for "Custom Number Sequence" which should
have a number of hits.

If you want to use the AutoNumber, you will have to find
some way to convert from the user's undo action (of the
Form) to a save action with a flag "Cancelled". This is
messy at best, especially if some of the Fields
are "required Fields". You will also need to make sure
that the users cannot perform data entry directly into the
DatasheetView of the Table. There may be other
complications also (Put it this way, I certainly won't go
down this path).

The ONLY purpose of an AutoNumber Field is to provide
uniqueness to each Record in the Table. The values can
become negative or even random (in replicated database).

HTH
Van T. Dinh
MVP (Access)

 

[Paul James]

Good suggestion. Thanks for the function code, Jamie. It will be very
handy.

 

[Jamie]

Pleasure! Glad it's of some use.

 

[Jamie Richards]

Hi Folks,

Just in case anyone uses the code I posted here, I realised I did not
adequately handle nulls. The following code is the GetNextKey function with
a line added to fix the problem.

'******************************************************************************
'This function only works on key values which can be interpreted as numeric
'Receives: Primary key column name, table name and an optional increment
amount
'Returns: The last records primary key value incremented by 1 or {increment}
'Jamie Richards - August 2004
'******************************************************************************
Public Function GetNextKey(KeyColumn As String, _
TableName As String, _
Optional Increment As Integer) As Long
On Error GoTo Err_GetNextKey
Dim varTmp As Variant

'Get the current max key value
'Make sure the key being used is numeric
varTmp = DMax(KeyColumn, TableName)

If IsNull(varTmp) Then varTmp = 0 'This line added to handle nulls
If IsNumeric(varTmp) Then
'Increment the key value by 1 or the increment amount
'then assign it to the function variable for passing back
If Increment = 0 Then
GetNextKey = CLng(varTmp) + 1
Else
GetNextKey = CLng(varTmp) + Increment
End If
End If

Exit_GetNextKey:
Exit Function

Err_GetNextKey:
MsgBox Err.Number & ": " & Err.Description, vbExclamation
Exit Function

End Function

Jamie )

Server side anti spam rules are enforced and ALL unsolicited email is
deleted.

 

[Paul James]

Thanks for the update, Jamie.

FYI - one of my associates tested it when varTemp was null, and it just
returned a zero, as we would have hoped. However, it never hurts to check
for null values before running code.

Paul

 

[Jamie Richards]

Hi Paul,

Good news then. However, I did manage to create a situation where the code
failed on a null, which is why I added that bit. Thanks for you feedback,
much appreciated.

Jamie


Server side anti spam rules are enforced and ALL unsolicited email is
deleted.