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.

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.


"Richard" <(E-Mail Removed)> wrote in message
news:5C2C75F6-05E6-4967-8DF8-(E-Mail Removed)...
> 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.
>

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?

"David F Cox" wrote:

> 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.
>
>
> "Richard" <(E-Mail Removed)> wrote in message
> news:5C2C75F6-05E6-4967-8DF8-(E-Mail Removed)...
> > 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.
> >
>
>
>

On Mon, 16 Oct 2006 05:39:01 -0700, Richard
<(E-Mail Removed)> wrote:

>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]

On Mon, 16 Oct 2006 09:06:02 -0700, Richard
<(E-Mail Removed)> wrote:

>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]

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?


"John Vinson" wrote:

> On Mon, 16 Oct 2006 05:39:01 -0700, Richard
> <(E-Mail Removed)> wrote:
>
> >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]
>

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.


"Richard" wrote:

> 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?
>
>
> "John Vinson" wrote:
>
> > On Mon, 16 Oct 2006 05:39:01 -0700, Richard
> > <(E-Mail Removed)> wrote:
> >
> > >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]
> >

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 .

"Richard" <(E-Mail Removed)> wrote in message
news:99C75E36-8654-4685-A674-(E-Mail Removed)...
> 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?
>
>
> "John Vinson" wrote:
>
>> On Mon, 16 Oct 2006 05:39:01 -0700, Richard
>> <(E-Mail Removed)> wrote:
>>
>> >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]
>>
>

On Mon, 16 Oct 2006 14:01:02 -0700, Richard
<(E-Mail Removed)> wrote:

>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]

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




David F Cox wrote:
> 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.
>
>
> "Richard" <(E-Mail Removed)> wrote in message
> news:5C2C75F6-05E6-4967-8DF8-(E-Mail Removed)...
> > 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.
> >