Solved

Using Access 2007  How to prevent a record account number from being reused after it has been deleted

Posted on 2012-03-21
4
249 Views
Last Modified: 2012-03-23
I need to prevent an account number that is created from being re-used and issued to another record after it has been deleted from a database.
0
Comment
Question by:Delores_C
  • 2
4 Comments
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 125 total points
ID: 37750161
If you simply delete it and the field type is number or text for example, anyone can type in a deleted number as it will not be there.
A simple solution would be to maintain the deleted account numbers in a separate table and create a lookup validation to that table that wont allow any number from that table to be reused as an account number.  Another simple way around this is not deleting the account number (as you will loose any other transactions associated with it and any history around it) is to include in your table where the Account number is the key an additional field, like a status and have values like Active, Inactive, Deleted etc. associated with it. If this field value is Deleted the account number should not appear in any list for a transaction.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37750381
Why would they be re-used?

The system you use to number the values in that field should be always unique.
(ex. an autonumber field, or a system that auto-increments: ab01, ab02, ab03, ...etc))

But, you have not given us any details on this table or this field, or the purpose of this table...

As always, can you First, take a step back and provide some background information...?

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37750393
The issue with Looking up the "used" values is that once the main list an the "deleted" lists get very long..., this will begin to take a longer and longer time (as the system will have to search more and more records for dupes)

Again, we need more info...


If this is the parent table, then your Account number should be auto-incremented in some way.

If this is in a child table, then you will have to provide more info on this system...

Jeff
0
 
LVL 30

Accepted Solution

by:
hnasr earned 125 total points
ID: 37751047
Before update of a new id, check if was deleted. If yes prompt message.
Deleting an existing record, record the id info in the accounts_deleted file.
Check this sample database.
checkDeletedIDs.accdb
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now