By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,475 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Entering the Record Number in a field?

P: n/a
So I've got an old MS Works database I imported into Access. I needed
a primary key so I created a record number field that was just the
record number I manually inserted when I entered it in the database so
I could sort the records by the date at which they were entered.

Well now I've deleted some of those records so its of course causing
gaps in the records. The record number in Access no longer matches my
record number that I entered when created. I was wondering if it would
be possible to somehow set the record so it would automatically assign
that primary key record number field the same number as the actual
record number. So say I deleted record 86, then record 87 would become
record 86, record 88 would become record 87, and so on down the line.

I was thinking AutoNumber format might do that, but for some reason it
doesn't assign the numbers successively down the list, so record 100 is
AutoNumber 100, but record 200 gets assigned AutoNumber 225, and record
1000 gets AutoNumber 200. I'm not sure what method its using to
determine which record to number next, but it doesn't seem to work.

Any functions in Access I could use for Access to just grab the current
record number, and put that into the Record Number field I created?
That would auto update the other record numbers when I deleted a record?

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Scott,

First of all, if the record number is your primary key or used in any link,
you don't want to be "rolling" the numbers each time you delete a record.
That primary key will be used to link to other tables and you will lose the
association between the records in this table and the other tables if that
key no longer matches. If you want a count of the records, then get a count
of the records (DCount() function or RecordCount property of the recordset),
don't rely on this number to be that count.

Consider Access tables as buckets to store your data. The data may or may
not be stored in the order that you want it stored. If you need it in a
certain order, you a query and set the Order By property on the desired
field(s). If you want the records in chronological order, then you will need
to store a date and time that the record was created and sort on that.

An autonumber is designed to give you a unique value for each record. It is
not designed to provide any meaning beyond that. If you want to create your
own record numbers, that can be done. In the BeforeUpdate event of a form,
check that you are at a NewRecord and if you are, get the DMax() of the
field that you want to increment and add 1 to it. Place this value in the
textbox bound to that field and Access will save that value in the record.
Set an index on that field to No Duplicates. You may need error handling
code in the form's Error event to handle a duplicate value error if you have
more than one person using the database at the same time. If two people were
creating a new record at the same time, it may be possible that each person
checked the DMax() prior to either record being saved and so they both are
attempting to save the same value. If this happens, your error code needs to
get the DMax() again, add one to it, and retry the save.

--
Wayne Morgan
MS Access MVP
"Scott269" <bu********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
So I've got an old MS Works database I imported into Access. I needed
a primary key so I created a record number field that was just the
record number I manually inserted when I entered it in the database so
I could sort the records by the date at which they were entered.

Well now I've deleted some of those records so its of course causing
gaps in the records. The record number in Access no longer matches my
record number that I entered when created. I was wondering if it would
be possible to somehow set the record so it would automatically assign
that primary key record number field the same number as the actual
record number. So say I deleted record 86, then record 87 would become
record 86, record 88 would become record 87, and so on down the line.

I was thinking AutoNumber format might do that, but for some reason it
doesn't assign the numbers successively down the list, so record 100 is
AutoNumber 100, but record 200 gets assigned AutoNumber 225, and record
1000 gets AutoNumber 200. I'm not sure what method its using to
determine which record to number next, but it doesn't seem to work.

Any functions in Access I could use for Access to just grab the current
record number, and put that into the Record Number field I created?
That would auto update the other record numbers when I deleted a record?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.