Auto Number

[Sailormike]

I notice ur 10 commands say never expose the autonumber.
I have 2 tables tblContracts and Lots linked 1 to many enforced on
autonumber in tblContracts. That autonumber is also the paper file number for
that contract. The client wanted a consective number system (they were
keeping this data in an Excel SS and assigning numbers by hand) with NO
skips. I was a little leary of this but was assured that when the user set
down to add a record everything needed was in hand. Turns out that ain't so.
They sometimes start a record (open the frmaddNewContract) and have to stop.
And they may NEVER add this record. The deal fell thru for whatever good and
sufficent reason. BUT Access has already assigned a number.

So we have a missing file number. For one of the partners this a BIG DEAL.

I have created a set of tmp tables (tmptblContracts and tmpLots) that allow
then to save entries and go back and edit or delete them altogether, and only
after they are SURE this is going to be a deal do they append the record to
the permenant table. But this append process, all done with macros, is slow,
error prone, cumbersome, just not a very good solution.
I know just enough VBA to be dangerous. Any ideas on a better way?
SailorMike

 

[tina]

don't use an Autonumber for the file number. instead, assign the file number
programmatically in the data entry form, in the form's BeforeUpdate event,
as

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!FileNumberField = DMax("FileNumberField", "TableName") + 1

when you have multiple users entering data at the same time, there's always
the possibility that two records will be updated at virtually the same time,
resulting in a duplicate file number being generated. make sure the file
number field has a unique index at the table level, so Access will catch
this problem and stop the record(s) from being saved. for the user, you can
trap the error message and either tell the system to run the "assign file
number" code again, or stop the code and open a message box to tell the user
to try saving again.

hth

 

[mnature]

Have a query that checks for how many contracts exist. Have a button on your
form that the client clicks when they are sure they want to enter the
information as a new contract. The button uses the query to see how many
contracts currently exist, adds 1 to that number, and then assigns that
number as the ContractNumber, before exiting the form. It would no longer be
an autonumber, just an ordinary integer, but you would want to make it unique.

 

[Sailormike]

Thanx to u both.

I was thinking along those lines but I wasn't sure how to do it.

NOW how to be sure the next filenbr i assign is the next in sequence to the
current autonumber field. I'm thinking I should enter the next contract
myself, assign the correct number by hand and THEN attach ur code. The next
record entered after THAT will have the next number, right?

OR:
Can i can rename by current auto field to something else, make a new long
interger field, call it the same name as by current auto number field and set
it to my current biggest number, attach ur code, and let them add a new
contract. That work?

I'm kinda thinking out loud here.

 

[tina]

the question is, do you need an Autonumber field at all? if the current
"file number" field is functioning as the table's primary key, you need to
change it from an Autonumber data type so you can assign the values rather
than the system generating them. but the field can still function as the
primary key for the table.

since your Autonumber field is linked to a field in at least one other
table, you need to open the Relationships window and remove the link(s),
then save and close. now you can change the Autonumber data type to Number,
Long Integer. note that Access will set a default value of zero (0) on the
field, so be sure to delete that. then open the Relationships window and
recreate the link(s) you removed.

make sure you add the DMax() code to the data entry form's BeforeUpdate
event procedure, as discussed previously. now the sequence will
automatically continue as new contracts are entered, beginning with the
"highest number already in the table + 1".

hth

 

[Sailormike]

OK I can follow all that, might even be able to actually do it.
BUT:
This file number, also the paper file number, appears on just about every
form in this app. I have not protected it because Access wouldn’t let a user
change it. But after these changes, ???. Can a user just change an existing
number by typing over it? This has actually happened with a combo box (which
I since grayed out) involving some other tables and it took me many hours to
get all the records back in line.

Sure I can make a unique index so I won’t get two file 343’s but how file
44567?
I appreciate your time and didn’t intend this exchange to turn into War and
Peace but the more I think about this the antsier I get.

Do I need to protect this field wherever it appears?

 

[tina]

Sure I can make a unique index so I won't get two file 343's but how file

44567?

the size of the FileNumber value is immaterial - unique is unique.

Do I need to protect this field wherever it appears?

yes, in each form where you display the value to the user. it's as easy as
opening a form in design view, selecting the FileNumber control, and setting
its' Enabled property to No and its' Locked Property to Yes.

hth

 

[Sailormike]

No. i am sorry that won't do. What started this was "gaps" in the file number
sequence. This number must be unique, advance by 1, account for every number
by actually representing a paper file, and correctly match existing paper
file numbers. That was a given when I took this job and it remains a major
issue. And every time a file number gets “skipped†I get a memo reminding me,
rather bluntly, of that.

Thank you for you for taking the time to help me with this but I will have
to stay with my Rube Goldberg creation.

 

[Allen Browne]

Mike please re-read the advice Tina has given you. Following what she told
you will give you a unique number, without gaps (unless you actually delete
a record), and it cannot be changed by a user when you lock the controls.

Additionally, if you have related tables that use this number, and you
created relationships with referential integrity, that would also prevent
the user changing the value.

The requirement to account for every number is not unusual, particularly
where auditors are involved. Tina's advice is the standard way to achieve
this. If your data is important, you do not let users anywhere near the
tables or queries themselves, and so her suggestions are the way you need to
go.

 

[tina]

well, i'm confused as to how we segued from talking about protecting the PK
value to prevent user changes (to mimick the fact that an Autonumber can't
be changed by the user), to "no, this whole idea won't work". at any rate,
you made a major change in the previously stated requirements when you said
"This number must...correctly match existing paper file numbers." if the
paper file already has a number when the user begins entering the file data
into the form, and if that number must be the same as the file number of the
record created, then you can't use either an Autonumber or a
programmatically generated number - you'll have to have the user enter the
file number that appears on the paper file.

if you've miscommunicated, and the original specifications still hold, then
the solution i outlined previously is a standard solution, as Allen noted.
if it won't fit your needs because of a requirement that you haven't
explained so far, then you'll have to post those details before anyone can
suggest a solution that fits your needs better.

hth