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

 

[Paul James]

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

 

[Jamie]

Pleasure! Glad it's of some use.

 

[Guest]

Jamie, this is a good answer to a problem I might
experience in a database I have set up. How will this
solution work in a multi-user set up? Would it not be
possible to end up with two new invoice numbers being the
same?

Thanks

Mossi

 

[Jamie Richards]

Hi Mossi,

It is possible that two users could create the same number, and as such it
would be necessary to index the Invoice Number field to prevent duplicates
(something I negliected to mention). That way, the first user to write the
number to the table, wins! The second one will receive an error, which
should be trapped and handled to ensure adequate flow for the user. For
example, if the constraint error comes up, advising the user and asking if
they would like to generate a new number would be one way to handle this.
You would simply recall the GetNextKey function (see the sample database)
again and write the new number.

That's the theory at least. I haven't had any problems with this so far,
but that could be good luck, not good planning ;o)

Jamie

 

[Paul James]

Jamei - if my objective is to notch up the number in that field by 1 every
time a new record is saved in the underlying table, what form event should I
use to call the GetNextKey function - Before or After Insert? Before or
After Update? (This db will be used in a multi-user environment).

Thanks again in advance.

Paul

 

[Jamie Richards]

Hi Paul,

As we know, Access doesn't require you to actually "save" a record in order
for it to be written to the underlying table. I have found this to be
troublesome for the users I have. To get around this, I create only unbound
forms and handle all edits, inserts and deletes in code (wrapped in
transactions). My advice to you if you are using bound forms with this
feature is to have some kind of "Generate Invoice Number" button that the
user can click. In the click event of that button, call the GetNextKey
function and assign the returned value to the invoice number field on your
form. You will need to make sure that the user cannot proceed to a new
record or exit the form without entering this number. The simplest way to
do that is to set the Invoice Number field to "required" in the table.

The truth is there are probably a thousand ways to do this. I have
suggested one for you. If it doesn't work, or you get stuck, post again or
drop me an email and we'll discuss it further.

Jamie

All Unsolicited email is deleted.

 

[Paul James]

Thanks, Jamie. At this time, I don't have a need to develop a more
sophisticated method of form handling with unbound forms and transaction
procedures. I realize the advantages of those methods when you have mission
critical data, but our situation doesn't require those capabilities, and it
would require additional development time and resources that aren't
available to us right now.

In this instance, I just need to make sure that your GetNextKey function
creates the next ID number every time a new record is created. What about
the After Insert event? Would there be any disadvantage to assigning the ID
field value in the After Insert event?

 

[Jamie Richards]

Hi Paul,

I know what you mean about "no development time". I don't think I would
call the function in the AfterInsert event as you may get problems if an
error occurs during the GetNextKey procedure.

I would be more inclined to use the BeforeInsert event of the form. That
way the function is called and the control populated before any attempt to
write the record is made.

Jamie

All Unsolicited email is deleted.

 

[Paul James]

Ok, I'll use the BeforeInsert event.

Thanks for all your help and guidance, as well as that great
"autonumber"-generating function, Jamie.

G'day.

Paul

 

[Jamie Richards]

No probs. I am not an expert on these matters, try both events and see what
works for you.

All the best!

Jamie

Unsolicited email is deleted, unread.

 

[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.