Clearing ID numbers

[Tony]

I am in the process of creating a new DB.
I have inserted some records into a test form then deleted
them and even when i try to create a new form the Call_ID
(Auto Number & Primary)start from ID 35 for example even
though i have deleted these records.
Is there anyway to reset the call ID Auto number back to 0.

Thanks to those who take time to reply

 

[Matthew DeAngelis]

I am in the process of creating a new DB.
I have inserted some records into a test form then deleted
them and even when i try to create a new form the Call_ID
(Auto Number & Primary)start from ID 35 for example even
though i have deleted these records.
Is there anyway to reset the call ID Auto number back to 0.

Thanks to those who take time to reply

Tony,

There is really no need to reset those numbers. I'm assuming this
Autonumber field is your primary key. These numbers are sequentially
allocated because of the way they are set; they could just as easily be
random and work the same way.
However, if you feel the need, I've heard that if you clear the table
data and then compact and repair the database, it will reset the
Autonumber.


Matt

 

[Hugh O'Neill]

I am in the process of creating a new DB.
I have inserted some records into a test form then deleted
them and even when i try to create a new form the Call_ID
(Auto Number & Primary)start from ID 35 for example even
though i have deleted these records.
Is there anyway to reset the call ID Auto number back to 0.

Thanks to those who take time to reply

Tony is absolutely right and beware of trying to use Autonumber for
anything visible to users.

Autonumber is designed only to provide a unique identifier for records
in a table. This is used internally by Access in building relationships
between tables according to your design. The actual values that
Autonumber delivers are unimportant and should never be exposed to
users or have any external meaning at all. Depending on the design and
use of the database, Autonumber will develop gaps, become
random and go negative.

For any number series that you want to control or have available
externally, you must design your own. There are several schemes
available depending on what you want to do.

hth

Hugh

 

[Hugh O'Neill]

This is exactly what I want to do! Please can you tell me how?

The Primary Key autonumber is not suitable, for all the reasons you
give, but I need to have a visible record numbering system which is
logical and prevents users from entering duplicates and which
constrains them to a particular format and which, ideally, forces
them into using the next number in the sequence..... and absolutely
ideally without them having to actually enter it themselves.

Fflei

Here is some code to do this. It works for both single and multi-user
scenarii.

You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later version,
you will need to use DAO.Recordset and DAO.Database and make sure you
have the appropriate reference set for the DAO Library.

The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you will
need to store the number value in your Table holding your records.

If, in your system, you need other unrelated number series, just add
another Field to tblSeries and manage it in a similar manner.

hth

Hugh

 

[fflei]

Here is some code to do this. It works for both single and multi-user
scenarii.

You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later version,
you will need to use DAO.Recordset and DAO.Database and make sure you
have the appropriate reference set for the DAO Library.

I'm not quite sure what this means..... I have Access 2002 and, as I'm sure
you've guessed by now, was a total innocent when I blindly volunteered for
this.....

I rooted around in the menus and found Tools/References and found Microsoft
DAO 3.51 object library and checked it. DAO is now listed in the Object
Browser - was that the right thing to do?

I started copying the above code, and got as far as Dim rst As ..... and
then the helpful dropdown thing appeared and Recordset is listed twice - as
are lots of other items on the list which has confused me somewhat!

The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you will
need to store the number value in your Table holding your records.

This is exactly what I want to do - thank you so much. The users are
volunteers, highly intelligent academics but short on computer (and
keyboard) skills, so gaps are bound to appear, but if I can minimize the
chaos by preventing them from inventing their own ways of expressing data,
it will be so much easier to find!

Thank you so much,

Fflei

 

[Hugh O'Neill]

Here is some code to do this. It works for both single and
multi-user scenarii.

You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other
Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and
unlock. Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later
version, you will need to use DAO.Recordset and DAO.Database and
make sure you have the appropriate reference set for the DAO
Library.

I'm not quite sure what this means..... I have Access 2002 and, as
I'm sure you've guessed by now, was a total innocent when I blindly
volunteered for this.....

I rooted around in the menus and found Tools/References and found
Microsoft DAO 3.51 object library and checked it. DAO is now listed
in the Object Browser - was that the right thing to do?

I started copying the above code, and got as far as Dim rst As .....
and then the helpful dropdown thing appeared and Recordset is listed
twice - as are lots of other items on the list which has confused me
somewhat!

The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you
will need to store the number value in your Table holding your
records.

This is exactly what I want to do - thank you so much. The users are
volunteers, highly intelligent academics but short on computer (and
keyboard) skills, so gaps are bound to appear, but if I can minimize
the chaos by preventing them from inventing their own ways of
expressing data, it will be so much easier to find!

Thank you so much,

Fflei

You can do either of two things here, in the References you can uncheck
the ADO library (Idoubt if you will be using ADO) or you can choose the
DAORecordset in the Intellisense drop-down box.

However, rather than typing the code in manually, you could just
copy/paste the whole code from my message! In which case check through
afterwards and make sure there are no line-wraps. You should create
the Table first, however.

hth

Hugh

 

[Hugh O'Neill]

Here is some code to do this. It works for both single and
multi-user scenarii.

You will need a new separate Table (tblSeries) having just one
Field (NextNumber) of type Long Integer to store the
incrementing number. The Table will have no index and will not
be related to any other Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and
unlock. Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later
version, you will need to use DAO.Recordset and DAO.Database and
make sure you have the appropriate reference set for the DAO
Library.

I'm not quite sure what this means..... I have Access 2002 and, as
I'm sure you've guessed by now, was a total innocent when I
blindly volunteered for this.....

I rooted around in the menus and found Tools/References and found
Microsoft DAO 3.51 object library and checked it. DAO is now
listed in the Object Browser - was that the right thing to do?

I started copying the above code, and got as far as Dim rst As
..... and then the helpful dropdown thing appeared and Recordset
is listed twice - as are lots of other items on the list which
has confused me somewhat!


The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button
you use finally to commit your numbered record. This must
happen after all opportunities to abort your record, otherwise
you could have gaps in your numbering to explain to your
auditors! After this code, you will need to store the number
value in your Table holding your records.

This is exactly what I want to do - thank you so much. The users
are volunteers, highly intelligent academics but short on
computer (and keyboard) skills, so gaps are bound to appear, but
if I can minimize the chaos by preventing them from inventing
their own ways of expressing data, it will be so much easier to
find!

Thank you so much,

Fflei

You can do either of two things here, in the References you can
uncheck the ADO library (Idoubt if you will be using ADO) or you
can choose the DAORecordset in the Intellisense drop-down box.

Did that! (Thanks for telling me its proper name)

However, rather than typing the code in manually, you could just
copy/paste the whole code from my message!

My madness did have a smidgeon of method - working on two unconnected
computers at the time - rectified for doing the real thing...

In which case check through
afterwards and make sure there are no line-wraps. You should create
the Table first, however.

I did all this, Hugh, and now have a button which when I chick it
changes the record in the table so that it is one number higher than
before, but, biting off far more than I can chew I know, I still
can't work out how to get that number into a text box on the form,
and from that text box into my main table.

fflei

To get the number into a Field in your main Table in this way:

As your Form will probably be bound to your main Table,

Me.MyDocumentNumber = lngNextNumber

where MyDocumentNumber is the Field in your main Table where you want
it stored.

Putting this in the click event of the button you mention and after the
number generation as I suggested, should do it.

You won't normally be seeing the new number on your entry Form because
the number generation and allocation is done as you store the record
and not before - that's how you avoid gaps caused by aborting an entry.

To show the number when you subsequently look at a record or when you
print the record, simply have a text box bound to the Field containing
it [MyDocumentNumber] above.

hth

Hugh

 

[fflei]

To get the number into a Field in your main Table in this way:

As your Form will probably be bound to your main Table,

Me.MyDocumentNumber = lngNextNumber

where MyDocumentNumber is the Field in your main Table where you want
it stored.

Putting this in the click event of the button you mention and after the
number generation as I suggested, should do it.

Yes, it does!

You won't normally be seeing the new number on your entry Form because
the number generation and allocation is done as you store the record
and not before - that's how you avoid gaps caused by aborting an entry.

Yes, I see what you mean. The problem that would most likely arise here is
if the user just didn't click the button.

To show the number when you subsequently look at a record or when you
print the record, simply have a text box bound to the Field containing
it [MyDocumentNumber] above.

Hmm. I thought about perhaps adding this code to the Save Record button I
already have, rather than a special one - but I can't guarantee they'd click
that either - because if I label a button something like Create Inventory
Number and they can't see an immediate result, then my users would click
again, and again...... do you think there is a way
1) to make them click a button before the record can be added?
and
2) to lock the record the form creates in the main table once this procedure
has been done so that the number is not changed - it would be helpful to be
able to add this field to reports and queries and if the users can change
the number on a subsequent visit to the form they will get confused. Either
that or disable the button Create Inventory Number once it has been used so
that it won't work again on a particular record?

BTW - I am learning a great deal from you: thanks!

fflei

 

[fflei]

What I try to do in my applications is to make buttons available
(enabled) only when it is appropriate for them to be used. Your 'Save
Record' button, for instance should only be enabled when it is OK for
them to use it. Once pressed, it should be disabled until the next
time it's OK to use it. You may also have an 'Undo' button, in which
case I can think of no circumstance where they should both be enabled
at the same time - either one, or the other but never both. Remember
that you have to move the focus off the button in order to disable it.

In Your final sentence, you have hit on this same possibility!

You may care to consider that the 'Save' button should be disabled in
the Form's Open event and in it's Current event. Also that the 'Undo'
button is not enabled until the record starts to be created. To stop
users using the 'X' close button in the Form's title bar, make the
Form's border style Dialog. The rule is to stop them doing what you
don't want them to do and only to allow them to do what you do want to
do!

Following on from your excellent advice re making a sequential reference
number by clicking a button, I have been giving this considerable thought
since you posted and have nearly cracked the problem..... but not quite!

What I've done:
Made a data entry form (Form1) which writes directly to the main table. It
opens only in Add mode (i.e. as a blank form) and most of the data is
available only as combo boxes, so there's little room for user
creativity.... I have taken off the control box on the title bar and done as
you suggested so that the save and undo controls are disabled when the form
opens.

The Undo button is enabled as soon as the first field is filled - I suppose
I should add the enable code to all the fields in case they start filling it
in backwards or something? Is this something that could be on Form's
AfterUpdate event?

The other buttons are not enabled until they click on a button which
confirms that the form is finished and correct....

but this has presented a difficulty.....

The other buttons are:
1. Save Current and Add another record.... this uses the code you gave me to
give the record a unique number (which it doesn't display on this form)
which has been pasted in to the wizard's Add Record button. This works: it
updates the serial number in the table, and then clears the form for a new
entry
2. Save Current and Exit. This also works: it updates the serial number and
exits.
3. Undo current - this seems to work.
4. Exit without saving - Is there a neat way of doing this? I ended up
making a wizard's delete button, copying the OnClick event code and pasting
it into a wizard's exit button. This works, but it was actually making a
record and then deleting it so the AutoNumbers on the primary key had gaps
in the sequence (solved that by changing it to ReplicationID, so no-one will
ever know!) I have not added the number-generating code to this, so it
doesn't interfere with the sequence.

But....These four buttons all remain enabled if either the Undo or Save &
Add buttons are used, so it is possible to save an incomplete or incorrect
record ... a couple of the fields can't be 'required' because there isn't
always data to go in them, and I also have a checkbox which enables two
other fields if it is ticked, and if it is ticked in one form, those fields,
like the buttons remain available in the next.

I've tried adding the disabling code to other form events, but so far
haven't found an appropriate one. I am tempted to force a Save & Exit every
time, and have the user click on the switchboard button to add another
record, but it seems very clumsy to me.

In order to view/edit records, I've made a query and then bound another
form, Form2 to that. This has no save button at all, and most of the fields
which were completed in Form1, (including displaying the number Form1 made)
but there are a couple which might have to be changed and space to add notes
at a later date. This seems to work in that it saves these alterations in
the original record and cannot change the reference number or add a new
record - or delete one either.

fflei

 

[Hugh O'Neill]

Following on from your excellent advice re making a sequential
reference number by clicking a button, I have been giving this
considerable thought since you posted and have nearly cracked the
problem..... but not quite!

What I've done:
Made a data entry form (Form1) which writes directly to the main
table. It opens only in Add mode (i.e. as a blank form) and most of
the data is available only as combo boxes, so there's little room for
user creativity.... I have taken off the control box on the title bar
and done as you suggested so that the save and undo controls are
disabled when the form opens.

The Undo button is enabled as soon as the first field is filled - I
suppose I should add the enable code to all the fields in case they
start filling it in backwards or something? Is this something that
could be on Form's AfterUpdate event?

The other buttons are not enabled until they click on a button which
confirms that the form is finished and correct....

but this has presented a difficulty.....

The other buttons are:
1. Save Current and Add another record.... this uses the code you
gave me to give the record a unique number (which it doesn't display
on this form) which has been pasted in to the wizard's Add Record
button. This works: it updates the serial number in the table, and
then clears the form for a new entry
2. Save Current and Exit. This also works: it updates the serial
number and exits.
3. Undo current - this seems to work.
4. Exit without saving - Is there a neat way of doing this? I ended up
making a wizard's delete button, copying the OnClick event code and
pasting it into a wizard's exit button. This works, but it was
actually making a record and then deleting it so the AutoNumbers on
the primary key had gaps in the sequence (solved that by changing it
to ReplicationID, so no-one will ever know!) I have not added the
number-generating code to this, so it doesn't interfere with the
sequence.

But....These four buttons all remain enabled if either the Undo or
Save & Add buttons are used, so it is possible to save an incomplete
or incorrect record ... a couple of the fields can't be 'required'
because there isn't always data to go in them, and I also have a
checkbox which enables two other fields if it is ticked, and if it is
ticked in one form, those fields, like the buttons remain available
in the next.

I've tried adding the disabling code to other form events, but so far
haven't found an appropriate one. I am tempted to force a Save & Exit
every time, and have the user click on the switchboard button to add
another record, but it seems very clumsy to me.

In order to view/edit records, I've made a query and then bound
another form, Form2 to that. This has no save button at all, and most
of the fields which were completed in Form1, (including displaying
the number Form1 made) but there are a couple which might have to be
changed and space to add notes at a later date. This seems to work in
that it saves these alterations in the original record and cannot
change the reference number or add a new record - or delete one
either.

fflei

Exit, without saving. In the button click event code for this you need
two things. First, to abort the record so it does not get saved and so
no number is generated, use Me.Undo. Then, if you want to close the
Form, use DoCmd.Close.

For the Enabling and Disabling of buttons, you will just have to write
down a list of the different states you want for each possible
condition. To code this, I use functions such as the following, in a
Standard Module:

Function SetMyButtons(frm As Form, DirtyState As Boolean)

'Enables and disables non-navigation buttons.
frm.cmdNew.Enabled = Not (DirtyState)
frm.cmdUndo.Enabled = DirtyState
frm.cmdDelete.Enabled = Not (DirtyState)
frm.cmdAccept.Enabled = DirtyState

End Function

and I call it in the Current event of the form in this fashion:

SetMyButtons Form, False

Once you see how this is working, you will be able to apply your own
variations to it.

hth

Hugh

 

[fflei]

fflei wrote:


Exit, without saving. In the button click event code for this you need
two things. First, to abort the record so it does not get saved and so
no number is generated, use Me.Undo. Then, if you want to close the
Form, use DoCmd.Close.

I struggled with this one! In fact the Me.Undo only works if there actually
is something to undo - it won't close a blank form. I solved it in the end
by tacking the Do.Cmd.Close onto the end of the OnClick event procedure of a
wizard's Delete button. It works because if there is no record to delete it
just closes, and if the user has entered stuff then it is deleted before
closing. Where I had gone wrong before was to paste all the code from the
wizards Close button onto the the Delete button.

For the Enabling and Disabling of buttons, you will just have to write
down a list of the different states you want for each possible
condition. To code this, I use functions such as the following, in a
Standard Module:

Function SetMyButtons(frm As Form, DirtyState As Boolean)

'Enables and disables non-navigation buttons.
frm.cmdNew.Enabled = Not (DirtyState)
frm.cmdUndo.Enabled = DirtyState
frm.cmdDelete.Enabled = Not (DirtyState)
frm.cmdAccept.Enabled = DirtyState

End Function

and I call it in the Current event of the form in this fashion:

SetMyButtons Form, False

Once you see how this is working, you will be able to apply your own
variations to it.

This was a very useful exercise! All badly-behaved buttons now knocked into
shape.

Thank you very much indeed for all your help. The end result is much more
sophisticated than I had thought I would be able to manage. Apart from
solving specific problems, this exercise has been a valuable learning curve.
I'm off to tackle filtering and filtered reports..... so no doubt will be
back when I get stuck!

Cheers,
fflei