If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
keeping track of "Escaped" Autonumber records
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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) -----Original Message----- 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 . |
#4
|
|||
|
|||
Good suggestion. Thanks for the function code, Jamie. It will be very
handy. |
#5
|
|||
|
|||
Pleasure! Glad it's of some use.
-- Jamie ) "Paul James" wrote in message ... Good suggestion. Thanks for the function code, Jamie. It will be very handy. |
#6
|
|||
|
|||
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. "Jamie" wrote in message ... 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" wrote in message ... 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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. "Paul James" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
New records can't be seen | rleblanc | Using Forms | 6 | August 14th, 2004 02:43 PM |
Keeping Records | Frank Kabel | Worksheet Functions | 1 | February 9th, 2004 01:33 AM |