ACCESS FEED: Re: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted

Jumat, 03 Januari 2014

Re: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted

Khalid-

One option is to "delete" a row by setting a Deleted flag, then filter out deleted rows in normal displays.

Why is it important that transactions remain in sequence? If you do want to allow delete, you will need to write some complex code to renumber after every delete. And if TransactionID is a linking field to other tables, you'll need to be sure that Cascade Update is turned on in the relationship. More trouble than it's worth, IMO.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 3, 2014, at 9:02 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,

Thanks Bob & John,
Replying to John's suggestion i did as you mentioned and it's OK, but what if i delete a record the sequence is broken. For e.g rows are 5 and i delete TransactionID 2, i am then left with
1
3
4
5
If there is a solution that after deleting TransactionID = 2, next ID's should change automatically like above to change as:
1
2
3
4
Or if not then i would remove Delete button on form and tell user that there is no option to delete, he may edit that particular record.

Khalid
On Thursday, January 2, 2014 5:50 PM, Robert Peterson <bob@alternatefinishing.com> wrote:
As you have found out a continuous sequence without gaps is not reliable with an auto number field.
You need to do it yourself in a field that is not auto number.
In the control source of the field you need something like =DMax()+1 then save the record so another user does not claim the same number.

See this topic at Rogers Access Library

http://www.rogersaccesslibrary.com/forum/topic142.html

Bob Peterson

"Never look back unless you are planning to go that way."
― Henry David Thoreau

Alternate Finishing will be closed for the holiday on December 24th & 25th, 2013 and Wednesday January 1, 2014. We hope you have a merry Christmas and a happy New Year!
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Thursday, January 02, 2014 7:08 AM
To: ms_access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted
Hi All,

I have a form on which i had a field "TransactionID" as AutoNumber, set as key. I was having problems if a record was deleted from button on form OR Undo from a button and then adding new record it was new TransactionID.
That is if TransactionID = 4 deleted or Undo, for new record i do not get TransactionID = 4, but get TransactionID = 5

I read on a forum the following, but i am not sure where to put this code, and also would it work or not. Could somebody help.

Khalid

Change your field from an autonumber to long integer and use the following code on your add record forms.
Code:
Dim varID As Variant
  
     If IsNothing(YourID) Then
        ' Get the previous high number and add 1
        VarID = DMax("YourFieldname", "YourTableName") + 1
        ' If this is first one, then value will be null
         If IsNull(YourID) Then YourID = 1
              FieldID = VarID
     End If