[I posted this about 36 hours ago, but it never appeared in the newsgroup.
I'll try again...]
Hi Margy
They may very well have their reasons, but my advice to you is to get this
in writing and signed in blood, because I promise it will end in tears! It
won't be very long before two people are looking at the same report printed
on different days, and great confusion will come from the fact that the same
person has two different ID numbers.
Anyway, you should, under the covers, use a unique, non-changing ID number
as your primary key. An autonumber would be well suited to this. The user
need never see this number - it would be used only for establishing links
between related records in your one-to-many relationships.
For the "volatile" ID numbers, you have two different options:
1. If you only ever want to see these numbers printed on a report, use Ted's
idea of a "running sum" textbox with its Control Source set to =1. Note
that this is only a textbox on your report, not a field in your table. You
must be aware though, that the numbers will change depending on the
filtering and sorting of the report. If you sort by first name, or filter
only those members with expired subscriptions, the numbers will be different
from a full list sorted by last name.
2. You could add a new field to your table, besides the primary key, named
"Useless". Actually, you might want to choose a more diplomatic name
After you add or delete records from the table, you can run some code to
renumber ALL the records. Something like this:
Public Function RenumberMembers()
Dim db as DAO.Database, rs as DAO.Recordset, i as Integer
Set db = CurrentDb
db.Execute "Update MembersTable set Useless=Null"
Set rs = db.OpenRecordset("Select Useless from MembersTable " _
& "order by [Lastname], [Firstname]")
With rs
Do Until .EOF
i = i + 1
.Edit
!Useless = i
.Update
.MoveNext
Loop
.Close
End With
End Function
The "order by" fields will specify the order that you want the numbers
assigned.
You will also need to run this renumbering code any time you change the
value of one of your "order by" fields. For example, if Jenny Aardvark gets
married and changes her name to Jenny Zymax, you will presumably want her to
move from the start of the list to the end, and for her number to change
accordingly.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Marg said:
Hi Graham
My sentiments exactly but this is what the club wants. They want the
record
ID Number to re number if we add or delete a member from the list and I
agree
its meaningless. I would have prefered to assign a unique ID number to
each
record. They have their reasons.....
--
Margy
Graham Mandeno said:
Hi Margy
I don't quite understand... What is the point of a record number if it
changes all the time?
You print a membership list one day and John Doe is member number 56.
Then
the next day Abraham Abalone joins the club. Does John Doe then become
number 57? If so then it's meaningless.
Can you please explain a little more?
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Marg said:
I have created a Member's database for our Golf Club using Access 2007.
I
do
not want to assign an Autonumber to records as this becomes a static
number.
How do I insert a column that will display a line number for my
records.
I
want this number to automatically renumber each record when records are
added
or deleted.
I am using Access 2007 to create the database, but this will be saved
to
an
earlier version for installing on Club's Computer.
