Stop the Auto Number!

[Mik3]

Hi,
In the Before Update event of a text box I put this procedure, such that the user would get a message if he tries to enter a duplicate entry.

Private Sub Tool_BeforeUpdate(Cancel As Integer)
If DCount("[Tool]", "Tools Table", "[Tool]='" & Me.Tool & "'") Then
MsgBox "This Tool already exists." & _
vbCrLf & "Sorry but this would create a duplicate Entry.", _
vbOKOnly, "Duplicate Entry"
Cancel = True
Me.Undo
Exit Sub
Else 'Do nothing
End If
End Sub

This works fine, hovever, the Auto Number (primary key) would still increment, even though nothing is written to the table. Is the problem something with the procedure or anything else?

Thanks for your help,

Mike.

 

[boblarson]

When using AutoNumber, the second that you populate a field in the table, a number is assigned. If you undo the record, the number has still been assigned and won't be used again.

If you are relying on AutoNumber to increment and keep the sequence intact ... forget it, it won't happen. You would need to come up with a manual process (code) to ensure that your numbers (not AutoNumber) increment with no skips.

 

[Mik3]

Coming up with a code won't happen as well
I guess what I need is to check for duplicate entries without trying to add the value in the table, such that the auto number would not increment.
Anyone thinks it's possible?

 

[Rich]

You could by using unbound controls, but then you'd have to use code to add the records anyway

 

[Mik3]

hmm..that's a BiG problem
I don't know how to write any coding.
I found that procedure from a previous thread, posted by Vassago if I'm not mistaken.

 

[Rich]

There have been numerous posts here for custom "autonumbers", search here for autonumber

 

[Mik3]

What if I compact the database each time a user tries to enter a duplicate value?

I know it's not such a good idea, however this shouldn't happen very frequently.

What is the command to compact a db as if the user had clicked Tools > Database Utilities > Compact Databse..such that he won't be prompted to select a db from which to compact of into which file, etc. ?

 

[Graham T]

An autonumber is used to provide a unique identifier for a table.

There are occasions when a table will have a naturally occurring unique identifier.

When there is a naturally occurring key, it should be used. But when it would require too many columns to define a natural key or there simply isn’t one, you should use an Autonumber.

Very common uses are CustomerID, OrderID, InvoiceNum, ProjectNum, etc.

Don’t worry about the fact that there may be occasional gaps in the Autonumber sequence. The primary key of a table does not need to be sequential. It only needs to be unique.

Graham

 

[Mik3]

I need the primary key (the auto number) to be the same as the record number since I would then need to go back any modify a record from a form by going to record = [Tool ID]
That is why I need it to be sequential, without any gaps.

 

[Waltang]

I personally hate domain functions, but If it is imperative that you keep the key sequential, and dont feel comfortable doing it in code, dont use an autonumber. Set the datatype to be a long, and make sure that it is Indexed as Yes (No Duplicates).

In your form(s) set the default value of the field to be:

dmax("Tool","Tools Table") + 1


Though, have you considered what happens if you delete a record in the middle somewhere?

 

[Mik3]

Many thanks for your replies.
There won't be any records deleted, only modified.
I will try the DMax, but only next Monday since it's closing time at work.
I will keep you informed.
Thanks again.

 

[Pat Hartman]

I need the primary key (the auto number) to be the same as the record number

- the concept of record number does not exist in a RDBMS. You never actually work with a table, you only work with recordsets and recordsets are by their nature unordered sets. The ONLY way to produce a recordset with a predictable order is to use a query with an order by clause.

 

[Mik3]

The dmax("Tool","Tools Table") + 1 posted by Waltang should solve the problem.

Many thanks for your help guys.

 

[Mik3]

I'm gonna shoot myself
I set the default value - now I need to set the ID as a number instead of autonumber.
Problem is - it won't let me..it says I must delete any relationships from the relationship table. And there are no relationships in the relationships table - I tried Show All, but nothing.
Will I have to remove all links in the queries?

 

[Mik3]

it's not the links in the queries either.
I tried to delete them all and still got the same error.
Any ideas what relationships is it talking about? Can they be hidden or something?

 

[Waltang]

Goto Tools-> Relationships

then tell it to Show all