Autonmuber stopped incrementing

[Guest]

Greetings,

I have a table with an autonubered index. I enter data to this table via a form. In the form I have a textbox referring to the index. I have protected the textbox to avoid changes to the index.

Previously, when I entered a value to any other field in the form, a new record was added to the table and the index automatically got a new number.

I then moved the data to another table, emptying my table. Now the autnumber stopped incrementing. Every time I enter a new record, the index remains 0.

What did I do wrong? How can I solve the roblem?

Thanks
Kamil

 

[Joseph Meehan]

Greetings,

I am not at all sure what you are doing but it sure sounds like you need
to re-think you database design.

I have a table with an autonubered index. I enter data to this table
via a form. In the form I have a textbox referring to the index.

Normally it is not a good idea to have users view an autonumber. They
only get confused by them. They expect them to be consecutive and that is
not the way autonumber works.

I
have protected the textbox to avoid changes to the index.

How have you protected it?

Previously, when I entered a value to any other field in the form, a
new record was added to the table and the index automatically got a
new number.

I then moved the data to another table, emptying my table. Now the
autnumber stopped incrementing. Every time I enter a new record, the
index remains 0.

This is where I am getting confused. Creating a record with an
autonumber then moving it and deleting the original .... What is going on
here.

What did I do wrong? How can I solve the problem?

What exactly is the problem? Is it that you can't create a new record
in the first table using the form? Can you add a record without the form?
Are you trying to copy records from one table to another and maybe the
unique number in the first table is already present in the second?

 

[Guest]

Joseph, thanks for the reply. I will explain it in more detail:

I have one table (tableA) with an autonumbered index. I have another table (tableB) with exactly the same fields but the index is not autonumbered.

Regularly I transfer data from tableA to tableB (Append query + delete query). Then TableA becomes empty. I want to continue entering data to TableA for later transfer to TableB.

If the autonumber procedure worked as expected, I would have no problems here because the index would simply continue incrementing. There would not be any double numbers neither in TableA nor TableB. When I enter data directly in the table, the autonmuber works as expected. However as I wrote, it stopped working when I enter the data via the form. Now the index of a new record is 0 whenever I enter data.

The text box in the form is protected as Enabled=YES, Locked=YES. This was also the case previously, when the autonmuber was working well.

 

[Nick Coe \(UK\)]

If you want a field to increment automatically then you must
set that field to be an Autonumber field in design view for
that table. It doesn't matter what you do to the form
control properties, unless you write your own code to
increment the number, Autonumber is a type/property of the
underlying field in the table.

--
Nick Coe (UK)
www.alphacos.co.uk

---

in message

Joseph, thanks for the reply. I will explain it in more detail:

I have one table (tableA) with an autonumbered index. I

have another table (tableB) with exactly the same fields but
the index is not autonumbered.

Regularly I transfer data from tableA to tableB (Append

query + delete query). Then TableA becomes empty. I want to
continue entering data to TableA for later transfer to
TableB.

If the autonumber procedure worked as expected, I would

have no problems here because the index would simply
continue incrementing. There would not be any double numbers
neither in TableA nor TableB. When I enter data directly in
the table, the autonmuber works as expected. However as I
wrote, it stopped working when I enter the data via the
form. Now the index of a new record is 0 whenever I enter
data.

The text box in the form is protected as Enabled=YES,

Locked=YES. This was also the case previously, when the
autonmuber was working well.

 

[Guest]

Thanks Nick,

this is what I am going to do. Not only to solve my problem but also to have more freedom and control in numbering of my records.

Kamil

 

[Joseph Meehan]

Joseph, thanks for the reply. I will explain it in more detail:

I have one table (tableA) with an autonumbered index. I have another
table (tableB) with exactly the same fields but the index is not
autonumbered.

Regularly I transfer data from tableA to tableB (Append query +
delete query). Then TableA becomes empty. I want to continue entering
data to TableA for later transfer to TableB.

If the autonumber procedure worked as expected,

You can't expect autonumber to work as you expect.

I am still not sure about what the autonumber is doing for you. If it
is going to be an ID for a set of data, which for some reason you will want
to move from one table to another while maintaining that specific ID then
you don't want autonumber. You should design you own system. I am going to
guess someone will post the procedure for providing that kind of ID number
using code along with some sample code. Autonumber is just the wrong tool.

Think of Autonumber as a tool to provide an unique number for each
record in a table. It is not a tool to provide a unique number for the data
in that record if it is to be copied or moved to a second table.

Don't feel bad, we get questions about autonumber every day all based on
the same type of assumptions about autonumber. Maybe MS should add an ID
number function.

Maybe if I could understand how that ID is used and why records are
moved from one table to another, I might be able to make some other
suggestions.

I suspect there is more to this issue and there may be a better database
design possible.

 

[Tony Toews]

I have one table (tableA) with an autonumbered index. I have another table (tableB) with exactly the same fields but the index is not autonumbered.

Regularly I transfer data from tableA to tableB (Append query + delete query). Then TableA becomes empty. I want to continue entering data to TableA for later transfer to TableB.

Why move data from table to table? Why not just leave the records in
one table and have a status flag? Although I've done this
occasionally myself when having a transaction data entry table where
data is entered in batches such as POs, receipts, adjustments and
such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm