Corrupted Auto-number?

[Guest]

Hello everyone,

I have a ID field which is a Auto-number field.

Don't know what happened, but record number "655" now shows as
"4.2926103E+07". Somehow, this ID seems to have got corrupted.

Now all our work orders are numbered as per this/these ID numbers, so
obviously, I want to try to save these numbers... what are my options?

Thanks.

 

[David F Cox]

The first thing you should do is wish that you had read all of the ancient
posts saying autonumbers and users do not mix. The purpose of autonumber is
to create a unique identifier that can be used by the system to identify a
record. Its quirks are littered throughout the newsgroups. Users should not
see them.

You should be creating the next work order number with something like
[WO_ID] = MAX([WO_ID]) + 1

Some questions are "Is the database really corrupt,?". Do other tables link
with this one correctly? Is any other data not as expected?

After that it is a question of how much patch and how much rewrite is
needed.

 

[Guest]

Thanks David,

I tried entering the Max function into the Default Value property of the ID
field in the table design and can't seem to get it to work.

I suspect maybe it won't work from there and that I will have to enter this
code in the field on the entry form, whereupon your formula will then update
the calculated data into the ID field of the table.

Or can I do somehow get this to work this code to work from within the table
design?

The first thing you should do is wish that you had read all of the ancient
posts saying autonumbers and users do not mix. The purpose of autonumber is
to create a unique identifier that can be used by the system to identify a
record. Its quirks are littered throughout the newsgroups. Users should not
see them.

You should be creating the next work order number with something like
[WO_ID] = MAX([WO_ID]) + 1

Some questions are "Is the database really corrupt,?". Do other tables link
with this one correctly? Is any other data not as expected?

After that it is a question of how much patch and how much rewrite is
needed.

Hello everyone,

I have a ID field which is a Auto-number field.

Don't know what happened, but record number "655" now shows as
"4.2926103E+07". Somehow, this ID seems to have got corrupted.

Now all our work orders are numbered as per this/these ID numbers, so
obviously, I want to try to save these numbers... what are my options?

Thanks.

 

[John Vinson]

Hello everyone,

I have a ID field which is a Auto-number field.

Don't know what happened, but record number "655" now shows as
"4.2926103E+07". Somehow, this ID seems to have got corrupted.

Now all our work orders are numbered as per this/these ID numbers, so
obviously, I want to try to save these numbers... what are my options?

Thanks.

As David says... you should NOT be using Autonumbers for this purpose.
You just found out one reason why... <g>

Did you Replicate the database recently? If so, it will change all
Autonumber fields to Random. Each new autonumber will be somewhere in
the range -2^31 to +2^31-1. Or, somehow the table definition might
have been changed to make the autonumber field Random.

I would suggest creating a new table with a Long Integer primary key;
run an Append query to migrate your existing data into it. You'll be
able to edit this record in the new table. You will need to
reestablish all your relationships (dropping them from the old table
and creating new ones from the new) and follow David's advice about
programmatically assigning new ID numbers.

John W. Vinson[MVP]

 

[John Vinson]

I suspect maybe it won't work from there and that I will have to enter this
code in the field on the entry form, whereupon your formula will then update
the calculated data into the ID field of the table.

Or can I do somehow get this to work this code to work from within the table
design?

No. You must go through the Form - a Table default cannot reference
any user functions or any table field (in the same or another table).

John W. Vinson[MVP]

 

[Guest]

Thanks John/David,

I've managed to export all the data and import it into a new table.

I have all my ID numbers in a Long Integer number field now. What I'd like
to do is generate this so-called Auto-number (as per David's code) whenever I
click on the "New" command button on one of my forms. I'd like to have the
new number generated ONLY when we click on that button. If I were to add the
code in the On Open event, for example, a new number would be generated any
time I opened that form.

The following code gives me a "Sub or function not defined" error:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub


I know I'm close, almost there. Or would you guys suggest I do this some
other way?

 

[Guest]

To clarify, this code is in the frmRunningTasks form, and in this same form
is that "New" command button referred to in this code. As you'll immediately
resolve, this then opens the frmRunningTasksAddNewRec form from which I add
new records. I want this process to be the only way possible for us to add a
new record to the database.

Thanks again.

Thanks John/David,

I've managed to export all the data and import it into a new table.

I have all my ID numbers in a Long Integer number field now. What I'd like
to do is generate this so-called Auto-number (as per David's code) whenever I
click on the "New" command button on one of my forms. I'd like to have the
new number generated ONLY when we click on that button. If I were to add the
code in the On Open event, for example, a new number would be generated any
time I opened that form.

The following code gives me a "Sub or function not defined" error:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub


I know I'm close, almost there. Or would you guys suggest I do this some
other way?

As David says... you should NOT be using Autonumbers for this purpose.
You just found out one reason why... <g>

Did you Replicate the database recently? If so, it will change all
Autonumber fields to Random. Each new autonumber will be somewhere in
the range -2^31 to +2^31-1. Or, somehow the table definition might
have been changed to make the autonumber field Random.

I would suggest creating a new table with a Long Integer primary key;
run an Append query to migrate your existing data into it. You'll be
able to edit this record in the new table. You will need to
reestablish all your relationships (dropping them from the old table
and creating new ones from the new) and follow David's advice about
programmatically assigning new ID numbers.

John W. Vinson[MVP]

 

[David F Cox]

You have either to run a query (SELECT MAX(MAX([tblRunningTasks]![ID]) )
FROM .... )
which was what I had in mind, or use the DMAX function. Since I have never
used this I have to leave it to an expert to give you the syntax for that or
work it out for yourself .

Thanks John/David,

I've managed to export all the data and import it into a new table.

I have all my ID numbers in a Long Integer number field now. What I'd
like
to do is generate this so-called Auto-number (as per David's code)
whenever I
click on the "New" command button on one of my forms. I'd like to have
the
new number generated ONLY when we click on that button. If I were to add
the
code in the On Open event, for example, a new number would be generated
any
time I opened that form.

The following code gives me a "Sub or function not defined" error:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub


I know I'm close, almost there. Or would you guys suggest I do this some
other way?

As David says... you should NOT be using Autonumbers for this purpose.
You just found out one reason why... <g>

Did you Replicate the database recently? If so, it will change all
Autonumber fields to Random. Each new autonumber will be somewhere in
the range -2^31 to +2^31-1. Or, somehow the table definition might
have been changed to make the autonumber field Random.

I would suggest creating a new table with a Long Integer primary key;
run an Append query to migrate your existing data into it. You'll be
able to edit this record in the new table. You will need to
reestablish all your relationships (dropping them from the old table
and creating new ones from the new) and follow David's advice about
programmatically assigning new ID numbers.

John W. Vinson[MVP]

 

[John Vinson]

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub

Change the one line starting with txtID to

Me!txtID = DMax("[ID]", "[tblRunningTasks]") + 1


John W. Vinson[MVP]

 

[aaron.kempf]

David Cocksucker;

you're a liar and an idiot.

autonumbers and users don't mix because you use a SHITTY ASS DATABASE
FORMAT.

use Access Data Projects; you won't have any problems.

****ing dipshit

The first thing you should do is wish that you had read all of the ancient
posts saying autonumbers and users do not mix. The purpose of autonumber is
to create a unique identifier that can be used by the system to identify a
record. Its quirks are littered throughout the newsgroups. Users should not
see them.

You should be creating the next work order number with something like
[WO_ID] = MAX([WO_ID]) + 1

Some questions are "Is the database really corrupt,?". Do other tables link
with this one correctly? Is any other data not as expected?

After that it is a question of how much patch and how much rewrite is
needed.

Hello everyone,

I have a ID field which is a Auto-number field.

Don't know what happened, but record number "655" now shows as
"4.2926103E+07". Somehow, this ID seems to have got corrupted.

Now all our work orders are numbered as per this/these ID numbers, so
obviously, I want to try to save these numbers... what are my options?

Thanks.

 

[aaron.kempf]

a table default CAN reference a SQL Server User Defined Function..
RIGHT?

maybe you should lose the training wheels what a bunch of sorry ass old
fogies
lose the training wheels you dipshits

autonumber works GREAT using Access Data Projects.
Aren't you tired of all the workarounds??

MDB is obsolete. SPIT on anyone that uses it for anything.

-Aaron

 

[aaron.kempf]

John Vinson

lose the training wheels and use Access Data Projects you ****ing
retard

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub

Change the one line starting with txtID to

Me!txtID = DMax("[ID]", "[tblRunningTasks]") + 1


John W. Vinson[MVP]

 

[BruceM]

As another possibility, there is a method for generating a simulated
autonumber in the sample database here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

The Open event may be too early in any case to generate the number, but even
if it is not it runs only when the form is opened. In a single-user
environment the Current event could first test to see if it is a new record:

If Me.NewRecord Then
Me!txtID = DMax("[ID]", "[tblRunningTasks]") + 1
End If

In a multi-user environment this would lead to the possibility of "duplicate
number" errors, so either that needs to be addressed through error handling
or another event such as Before Update could be used to minimize the chance
of duplicate numbers. The command button you mentioned needs to start a new
record before the number can be assigned; you could make it the only way of
starting a new record, but you still need to get to the new record before
you can assign a value to a field in that record.

Thanks John/David,

I've managed to export all the data and import it into a new table.

I have all my ID numbers in a Long Integer number field now. What I'd
like
to do is generate this so-called Auto-number (as per David's code)
whenever I
click on the "New" command button on one of my forms. I'd like to have
the
new number generated ONLY when we click on that button. If I were to add
the
code in the On Open event, for example, a new number would be generated
any
time I opened that form.

The following code gives me a "Sub or function not defined" error:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub


I know I'm close, almost there. Or would you guys suggest I do this some
other way?

As David says... you should NOT be using Autonumbers for this purpose.
You just found out one reason why... <g>

Did you Replicate the database recently? If so, it will change all
Autonumber fields to Random. Each new autonumber will be somewhere in
the range -2^31 to +2^31-1. Or, somehow the table definition might
have been changed to make the autonumber field Random.

I would suggest creating a new table with a Long Integer primary key;
run an Append query to migrate your existing data into it. You'll be
able to edit this record in the new table. You will need to
reestablish all your relationships (dropping them from the old table
and creating new ones from the new) and follow David's advice about
programmatically assigning new ID numbers.

John W. Vinson[MVP]

 

[aaron.kempf]

you guys are a bunch of ****ing idiots

lose the training wheels and stop preaching UGLY workarounds.

I mean seriously here.. you actually write databases like this?

NON STOP BUG WORKAROUNDS

your life would be so much easier working with an Access Data
Projects... piece of shit you're making excuses about why the SIMPLEST
FUNCTIONALITY EVER isn't reliable in MDB.. maybe you should re-evaluate
the platform.

I mean seriously here.

MDB is for retards... stop writing CODE to handle MDB flakiness... it
just makes the whole problem more complex

-Aaron

As another possibility, there is a method for generating a simulated
autonumber in the sample database here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

The Open event may be too early in any case to generate the number, but even
if it is not it runs only when the form is opened. In a single-user
environment the Current event could first test to see if it is a new record:

If Me.NewRecord Then
Me!txtID = DMax("[ID]", "[tblRunningTasks]") + 1
End If

In a multi-user environment this would lead to the possibility of "duplicate
number" errors, so either that needs to be addressed through error handling
or another event such as Before Update could be used to minimize the chance
of duplicate numbers. The command button you mentioned needs to start a new
record before the number can be assigned; you could make it the only way of
starting a new record, but you still need to get to the new record before
you can assign a value to a field in that record.

Thanks John/David,

I've managed to export all the data and import it into a new table.

I have all my ID numbers in a Long Integer number field now. What I'd
like
to do is generate this so-called Auto-number (as per David's code)
whenever I
click on the "New" command button on one of my forms. I'd like to have
the
new number generated ONLY when we click on that button. If I were to add
the
code in the On Open event, for example, a new number would be generated
any
time I opened that form.

The following code gives me a "Sub or function not defined" error:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRunningTasksAddNewRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[txtID] = MAX([tblRunningTasks]![ID]) + 1

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub


I know I'm close, almost there. Or would you guys suggest I do this some
other way?

On Mon, 16 Oct 2006 05:39:01 -0700, Richard

Hello everyone,

I have a ID field which is a Auto-number field.

Don't know what happened, but record number "655" now shows as
"4.2926103E+07". Somehow, this ID seems to have got corrupted.

Now all our work orders are numbered as per this/these ID numbers, so
obviously, I want to try to save these numbers... what are my options?

Thanks.

As David says... you should NOT be using Autonumbers for this purpose.
You just found out one reason why... <g>

Did you Replicate the database recently? If so, it will change all
Autonumber fields to Random. Each new autonumber will be somewhere in
the range -2^31 to +2^31-1. Or, somehow the table definition might
have been changed to make the autonumber field Random.

I would suggest creating a new table with a Long Integer primary key;
run an Append query to migrate your existing data into it. You'll be
able to edit this record in the new table. You will need to
reestablish all your relationships (dropping them from the old table
and creating new ones from the new) and follow David's advice about
programmatically assigning new ID numbers.

John W. Vinson[MVP]

 

[m.cringle]

Aaron - you are a complete prat!

How about saving some of the space that your useless suggestions are
taking up for SANE people!!

 

[aaron.kempf]

what the **** are you talking about

these cornholers are sitting here; tell you-- to re-architect these
SIMPLE FEATURES like autonumber / identity.

it's just comical.

Lose the training wheels and your life would be a lot simpler.

Taking it up the ass; cold hard MDB jammed up your ass
MDB isn't practical for real world use anywhere.
I mean seriously here.

I mean-- if you're not pissed off then you're not paying attention.
Autonumber is one of those critical things; and if it's not working--
then change platforms you ****ing retards.

-Aaron

 

[BruceM]

I'm not Aaron, so your powers of perception and the validity of your
commentary are both suspect at best.

 

[m.cringle]

I'm not Aaron, so your powers of perception and the validity of your
commentary are both suspect at best.

Hmmmm... I think we may have crossed wires!

I was replying to Aaron (the guy who has posted 3/4 offensive and
pointless comments) Can you not see them??

I was just reading your comments to Richard

 

[BruceM]

Yes, I think there are some crossed wires, so to speak. I posted a
suggestion about how to increment a number in a new Access record. There
are several ways to do that, but my suggestion was certainly reasonable, and
within the realm of common practice.
I see a thread in a Microsoft Access newsgroup, in which your first message
suddenly appeared, with no apparent connection to anything else in the
thread, or in the group, for that matter. No Aaron anywhere in the thread
that I can see. I replied to Richard, who had asked a technical question
about a software product.

 

[aaron.kempf]

Here I am bitches...

If you love my ranting; I reccomend that you use groups.google.com
because Microsoft censors EMPLOYEES and CUSTOMERS from

and I quote:

SPEAKING THE TRUTH
MICROSOFT CENSORS EMPLOYEES AND CUSTOMERS FROM SPEAKING THE TRUTH

MICROSOFT CENSORS EMPLOYEES AND CUSTOMERS FROM SPEAKING THE TRUTH


MICROSOFT CENSORS EMPLOYEES AND CUSTOMERS FROM SPEAKING THE TRUTH



MICROSOFT CENSORS EMPLOYEES AND CUSTOMERS FROM SPEAKING THE TRUTH