auto numbering primary key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a new form from a query. Thanks for the previous responses to
getting me to the new record, by the way.

At the blank (new) record, the bottom of the screen displays that this is
record 1 of 1. There are already 11,414 records that are in the table.
Before I added the new record command (DoCmd.GoToRecord,,acNewRec all of
the records appeared, so shouldn't the new record be record ? of 11,415?

Have I missed a step somewhere? Also, in relation to the subject, this is
an auto numbering field that is the primary key.

Thanks for your assistance.

Linda
 
Not necessarily. An autonumber field guarantees that a new number has never
been used before. It does not guarantee it will be sequential. A break can
occur if a record is created then cancelled. The number created then
cancelled will never be used again. It will go on to the next number.

You should never care what the autonumber value is. This is the Seventh
Commandment:
http://www.mvps.org/access/tencommandments.htm

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
That is how it works. If you want to see it displaying the number of
previous records just open the form then click on the arrow with the
asterisk. That will bring up a blank record.
 
Love the 10 commandments of Access:)

So, to help with my dilemma - I don't want people just willy-nilly entering
a number, or typing in the wrong "next" number. Is there a way to add 1 to
the previous record number and have it appear in the blank record? I alreay
have the tab set to skip that field because I don't want them to have access
to it.

Thanks,

Linda
 
Sure, but you don't want to use an autonumber. You want to create your own
number incrementing routine. The most common way is to use the DMax domain
aggregate function. It's easier to explain in a sample, so if you go to my
website (www.rogersaccesslibrary.com), you'll find a sample called
"AutonumberProblem.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Great site, Roger. I will put it in my favorites.
I entered this into the default value for RECID:
DMax("RECID","tblFARGASmain")+1
Recid being the primary field/number generator and the other the table.
I received this message when I tried to save: Unknown functin 'DMax' in
validation expression or default value on "tblFARGASmain.recid'


Any ideas?

Linda
 
I realize that probably wasn't much help. Meant to say there was no
"missing" next to the checked files. I am at square one!

Linda
 
PMFJI. Where are you defining the Default, Linda: in the table, or in the
form? It definitely won't work as a default in the table.

In actual fact, you should be using VBA in the form's BeforeInsert event,
and setting the value there.
 
I did take your sugggestion and put the DMax... in the forms BeforeInsert
event, but it didn't do anything (at least no error messages). Since it is
still morning and my brain is half working, let me restate what I would like
to do.

When the blank record appears, ready for data entry (that part is working),
I would like the RECID field to numerate itself, adding 1 to the last record.
So, if the last record was 11414, the RECID field would now say 11415, with
the rest of the form blank, ready for entry.

I hope I have clarified my intentions - I was confusing even myself!!!

Linda
 
Linda said:
I did take your sugggestion and put the DMax... in the forms
BeforeInsert event, but it didn't do anything (at least no error
messages). Since it is still morning and my brain is half working,
let me restate what I would like to do.\
Click to expand...

Tell us exactly what you entered and where you entered it. The BeforeInsert
event fires upon the first keystroke that dirties a new record. This would
notbe the event I would choose because it doesn;t support multiple users
very swel and in fact is not eeven guaranteed to fire when new records are
created in the form. I would use BeforeUpdate with an If-Then block to
ensure that it only applies the value once.
When the blank record appears, ready for data entry (that part is
working), I would like the RECID field to numerate itself, adding 1
to the last record. So, if the last record was 11414, the RECID field
would now say 11415, with the rest of the form blank, ready for entry.
Click to expand...

For you to see the value at the moment you navigate to a new record you
would have to use a DefaultValue or apply the value in the Current event
(testing for NewRecord). These would work if you never have more than one
person entering records. Otherwise you will have collissions where more
than one user grabs the same value.
 
Back
Top