AutoNumber - sequentially

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

The sequentially of the AutoNumber of one of the tables in the application
is of main importence for the calculation.
but somtimes the users have to delete records so the sequentially demage.
what can i do to protect the sequentially even when records are deleted
in the table or query ?

thank you
 
It sounds like this design violates the 7th commandment of Access.

If you really need a sequential number for your calculation, you can use a
method shown in one of Access MVP Roger Carlson's samples:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

And, instead of deleting a record, you can use a boolean (yes/no) data type
to mark it inactive, in the click event of a command button captioned
"Delete". The record is never actually deleted.

What type of calculation are you attempting where you believe that you must
have a sequential record number?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
thank you,

i have to calculate sum of periods between the fields 'from date' and 'to
date'
but i also have to reduce overlap between sequent periods.
so i bring (in query) two sequent periods to one row (by edding to the
sequent AutoNumber of one of the periods the number 1 ) and then i check if
there is overlap

yariv
 
the fields:

'ido' = AutoNumber
'i num' = Number
'from' = Date/Time
'until' = Date/Time
'place' = text


example:

IDo i num from until work
place

68 81713 01/10/1979 01/10/1982 oracle
69 81713 01/01/1995 01/01/1999 m - systems
72 81713 01/01/1998 01/01/2004 microsoft
73 81713 01/01/2005 01/01/2006 nice
 
Hello Tom,

I Adopt your statmant :
"It sounds like this design violates the 7th commandment of Access"
I am using ordinary number insted of the autonumber.
do you know if is there any kod wich will give as defult
the sequnt 'id' number of the 'id' number in the latest record ?

thank you
 
thank you tom
i found the answer from other qustion;
here,

Private Sub Text28_AfterUpdate()
Me.Text28.DefaultValue = Chr(34) & Text28 + 1 & Chr(34)
End Sub
 
???? ????? said:
thank you tom
i found the answer from other qustion;
here,

Private Sub Text28_AfterUpdate()
Me.Text28.DefaultValue = Chr(34) & Text28 + 1 & Chr(34)
End Sub
Click to expand...

Note that the above will not work in a continuous or datasheet form and that it
will not work in a multi-user environment.
 
Rick thanks,

It's work in a continuous form and in a multi-user environment.
The number are not autonumbers.
why should'nt it ?
yariv
 
???? ????? said:
Rick thanks,

It's work in a continuous form and in a multi-user environment.
The number are not autonumbers.
why should'nt it ?
Click to expand...

In a continuous form the Default value is "assigned" to the new record row as
soon as it is painted on the screen. When you start creating a new fifth record
the potential sixth record is painted on the screen on the first keystroke of
the fifth record, before you have made the entry that changes the DefaultValue
property. That sixth row will not be able to use the value from your fifth
record because you haven't entered that value yet and I don't think it will
update to the new value when you do make the entry.

In a Multiuser app a user can navigate to the new record position and even begin
filling the new record out and then an indefinite amount of time can pass before
that record is saved to disk. During that indefinite amount of time any other
user that tries to create a new record will get the SAME default value assigned
to their record. Whoever saves first "wins" and everyone else gets a duplicate
key error.
 
Rick said:
In a continuous form the Default value is "assigned" to the new
record row as soon as it is painted on the screen. When you start
creating a new fifth record the potential sixth record is painted on
the screen on the first keystroke of the fifth record, before you
have made the entry that changes the DefaultValue property. That
sixth row will not be able to use the value from your fifth record
because you haven't entered that value yet and I don't think it will
update to the new value when you do make the entry.
Click to expand...

After testing this one is actually not a problem. Where I had seen this
problem was with a default value expression that is non-changing, but where
the value resulting from that expression was. For example a default value
of DMax()+1 does not work in a continuous form. When you actually assign a
new DefaultValue property in code as you are, that appears to work fine.
 
Back
Top