473,383 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

Entering the Record Number in a field?

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
1 5372
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
2
by: Rose | last post by:
I have a transactions table with a field named Transaction. I want Transaction to be positive for input and negative for outflow. How do I set up the form for entering outflow so for data entry,...
9
by: DS | last post by:
Whenever you use a continous form each row represents a record. Does this record have a number? If so how do you access it. Right now I have a field using auto number. But if access generates...
0
by: Scott269 | last post by:
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...
3
by: domcatanzarite | last post by:
How would one create a button that on click advances the form to the next "non recurring record" as opposed to the next record. The field the button needs to que from has groups of duplicate...
3
by: cyber0ne | last post by:
I'm designing a basic form for data entry into one main table. There are two fields in the table that I would like to be automatically populated, not user-entered, when the record is posted. ...
3
by: CourtGuy | last post by:
Hi Folks, I've got a problem that's been confounding me for months. I work for a criminal court, and I'm trying to set up an Access database to track petitions filed by criminal defendants. ...
3
prn
by: prn | last post by:
Hi folks, I've got something that's driving me crazy here. If you don't want to read a long explanation, this is not the post for you. My problematic Access app is a DB for keeping track of...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.