I entered incorrect data into a record. How can I delete it?

[Guest]

I entered incrrect data into a record and nned to delete the data. Everything
I have tried has not worked. The Package ID number and the Customer ID number
are no longer corredt. Is there a way to straighten them out? I am taking a
class in Access and it is mostly "on your own" learning.

 

[John Vinson]

I entered incrrect data into a record and nned to delete the data. Everything
I have tried has not worked. The Package ID number and the Customer ID number
are no longer corredt. Is there a way to straighten them out? I am taking a
class in Access and it is mostly "on your own" learning.

If these ID numbers are Autonumbers, then there WILL be a gap in the
numbering if you delete a record. This isn't an error. Autonumbers
have one purpose, and one purpose only - to provide a unique key.
Deleted numbers will not be reused, and existing numbers cannot be
edited.

If you can't tolerate gaps in the numbering scheme, then you cannot
use Autonumbers, and must design your own numbering scheme.

One consideration: Suppose a few years down the road you have 31227
records in the Package table. You look in the table and realize that
records 1 and 2 were left over from testing and don't correspond to
any real package, and you want to delete them.

Do you REALLY want to renumber 31225 records, and all their associated
records? Do you want to renumber them on all the pieces of paper where
they've been printed or hand-written? Do you want to notify all your
customers that their PackageID's all need to be changed? No, of course
not! Just treat the ID's as they're intended to be treated:
meaningless unique values. It's best not to expose them to human view
at all, but just to use them as internal, behind-the-scenes linking
values.

John W. Vinson[MVP]

 

[Mike Lang]

I had a similar question for a Kitchen Installation Management application.
This probably isn't the answer you want at the moment but you might find it
helpful.

I needed a way to generate a customerID that was Alphanumeric and in a
particular format. Albert Kallal (another MVP)came up with some code that
allows me to define a starting number and prefix in a 'MyCompanyInformation'
file and then each time you create a record int eh main client data table,
it enters an increasing customerID for you - this is in ADDITION to the
autonum ID. Code is poosted below.

The other point is that you shouldn't really delete records unless you are
doing something like a 'month end' or 'year end' cleardown routine. Probably
the best way is to have a field in the table called 'deleted' and switch the
field on when you want to mark that record as deleted (& maybe then put data
into a field called something like 'reason for deletion'.

I do a similar thing for jobs which are completed. This allows me to run a
query and only have active jobs showing in my database but to be able to
look up historical jobs should I ever need to. Alternatively (or including )
you could have an 'archive flag' that would allow you to move the record to
a subsidiary table on a month end/year end cleardown. This keeps the main
table clean and with the smallest possible number of active records.


Albert Kallal's code

Put this in Module Global Code

Public Function NewCustNum() As String
Dim myrecs As DAO.Recordset
Set myrecs = CurrentDb.OpenRecordset("tblMyCompanyInformation")
NewCustNum = myrecs!PrefixforClientID & Format(myrecs!StartingClientID,
"0000")
myrecs.Edit
myrecs!StartingClientID = myrecs!StartingClientID + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


And have 2 fields in the MyCompanyInformation table (a single record table
with all YOUR company details). PrefixforClientID (in my case a 2 letter
alpha code representing the branch office) and StartingClientID (which is
where I put the starting number).

In the form you use for entering data you then put the following lines in
the BeforeInsertEvent procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CustomerID = NewCustNum()
End Sub

The moment you put any data into a new record, the CustomerID field on the
form gets populated with the next record in the series.