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
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

717 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