stop auto number

[blake7]

Hi I have a database that is running fine and well, it is recording quality
issue for the team i work in, each one of us insert any new quality issue we
find during the day, the issue is given a unique number by the use of the
auto number function.
I have designed a form for each team member to review and update information
when required, the problem is when we view our issues we use the record
selector at the bottom of the form to scroll through each entry, this is ok
unless you go one record too far, ie you have 10 records but you click beyond
the 10th, a new auto number is generated which cannot be used again, is there
any way to stop the auto number being generated when you go beyond the number
of records assigned to a person. Thanks All

 

[Tom Wickerath]

Hi Blake,

You should not be seeing this behavior. Simply scrolling past the 10th
record, landing on a new record, should not automatically assign a new
autonumber until you do something to "dirty" the record. It sounds to me like
something in your code is automatically causing the new record to be dirtied,
thus causing the autonumber value to be "used up". Are you perhaps recording
the date (or date & time) using the Date() or Now() functions, but using the
wrong event procedure, such that each record displays a pencil symbol in the
record selector as you scroll through the records?

Note: You must have the record selector visible to see this effect.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[blake7]

Hi Tom, Thank you very much for your help on this issue, i did in fact have a
hidden date field that I forgot was there, I put it there some time ago when
experimenting with a query, my own fault really, i removed this field and it
worked a treat. Thank you once again. Regards Tony

 

[Tom Wickerath]

Hi Tony,

You're welcome. Glad I could help.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Linq,

Agreed, however, the OP never stated that the autonumbers were available for
the users to see. I think Blake was simply concerned with why this was
happening.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

1. You don't need the If….Else….EndIf construct. You can simplify it to a
single line with:

Me.[IDNum] = Nz(DMax("[IDNum]", "[YourTableName]"),0) + 1

2. The statement:

"Number" fields that aren't used for math really should be defined as Text

doesn't really have any sound rationale. An attribute whose values are of
an ordinal or cardinal nature would be appropriately modelled by a column of
number data type without being involved in any mathematical operations.
Values of number data type will sort correctly of course, whereas text values
won't, e.g. "12" sorts before "2". I think what you have in mind are
'numbers' which are really coding systems; telephone numbers or zip codes are
a case in point. For these a text data type is appropriate.

3. Its worth mentioning that if the DMax function is used to compute the
next number in a sequence conflicts can arise in a multi-user environment if
two or more users are inserting a record simultaneously. The first user to
save their record will succeed, the other(s) will experience a data error in
view of the key violation. This should either be handled in the form's Error
event procedure, or the error can be avoided in the first place by ensuring
user's cannot simultaneously obtain the same number. One way of doing this,
which also includes a means of setting the next number at which a sequence
will continue, can be found at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

I'm glad you have your problem of an unwanted record being generated solved,
but as far as an AutoNumber being "wasted" and not being available to be
"used again," it really shouldn't matter! AutoNumbers should never be
seen/used by the end users! AutoNumbers are intended to be used for one
purpose and only one purpose, to provide a unique identifier for each record.
Here's a post I've archived from a gentleman named John Vinson, MVP,
explaining how AutoNumbers work:

******************************************
"When using Autonumber, do be aware that there will be gaps in the numbering -
any record that's deleted will leave a gap; hitting <Esc> after starting a
record will leave a gap; adding records using an Append query may leave a
gap, often a huge one; replicating the database will make your invoice
numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous."
*****************************************
Yo use an ID type number, you need to use an auto-incrementing number hack
such as one of these.

The first code here would be for an IDNumber that is defined in the table as
Text datatype. "Number" fields that aren't used for math really should be
defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
End If
End If
End Sub

If you insist on using a Numerical field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
End If
End If
End Sub

 

[Ken Sheridan]

You may be right, but nevertheless I wouldn't rule out a number data type.
In my department, for planning application numbers we used a long integer and
formatted it to pad it out with leading zeros.

Ken Sheridan
Stafford, England

 

[blake7]

Hi all, thanks for all your comments on the issue, I understand about the
missing blank spaces etc and was not really concerned about having gaps in
the table, I just wanted to understand how I could stop it happening, because
I was also getting a "jet database" error message because I hade fields
linked to a table of unique part numbers, and it just was being a pain
everytime a team member click beyond his / her last record. So thanks all
again, it's good to talk or so they say. Regards Tony

 

[Ken Sheridan]

Tony:

If you ever do need to automatically insert a value into a new record, but
don't want to 'dirty' it and inadvertently create a new record unless a user
specifically adds other data, set the DefaultValue property of a control
rather than its Value. If you do this in code one thing you need to remember
is that the DefaultValue property is always a string expression, regardless
of the data type of the field to which the control is bound. This is
particularly important when setting it to a date value as if the date is
expressed in short date format it will be interpreted as an arithmetical
expression not a date. This won't cause an error, but will put in the wrong
date, usually one at the end of the 19th century as 'day zero' in Access is
30 December 1899.

Say for instance you wanted to carry forward a date from one new record to
the next. You'd set the DefaultValue of the control to the value of the same
control in the form's AfterInsert event procedure. If you put:

Me.MyDateControl.DefaultValue = Me.MyDateControl

it would put in the wrong date because it would interpret it as an
arithmetical expression. You might think you could use the # date delimiter
character:

Me.MyDateControl.DefaultValue = "#" & Me.MyDateControl & "#"

That would work if the US short date format mm/dd/yyyy is used, but if I did
it here, where the short date format is dd/mm/yyyy it would change 4 July to
7 April! So what should be done is:

Me.MyDateControl.DefaultValue = """" & Me.MyDateControl & """"

The pair of quotes within quotes are interpreted as a literal quotes
character. Whatever the data type you'd do exactly the same in fact. With
other data types often its not actually necessary to wrap it in quotes, but
there can be circumstances where its crucial when you might not think so; if
a control contained the string "2E4" for instance this could be interpreted
as an exponential operation 2 * 10^4, i.e. 20000. So always include the
quotes to play safe.

Ken Sheridan
Stafford, England