Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
256 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 500 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 31

Accepted Solution

by:
hnasr earned 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 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