Update duplicate checking

[bigderon88]

I want to be informed when a unique identifier is a duplicate before saving. When i try to update I would like to be told that ID is used and to open the file to update. I have tried a few things from the web that I copied and updated, but not really a programmer and I struggle to fix syntax errors. Thanks.

[bigderon88]

Private Sub Twist_Case___BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[Twist Case Number]", "records", "[Twist Case Number] = '" & Me.Twist_Case__ & "'")) Then
MsgBox "Duplicate Twist Case! Will retrieve record."
Me.Filter = "[Twist Case Number] = '" & Me.Twist_Case__ & "'"
DoCmd.RunCommand acCmdApplyFilterSort
End If
End Sub


Tried this, but get error in DoCmd line

[orange]

Investigate DCount
http://www.techonthenet.com/access/f...ain/dcount.php

[bigderon88]

THx, I used DCount to identify a duplicate case after update. Now I would like the duplicate file to auto open so it can be edited. Any ideas?

[orange]

Why exactly do you want duplicates?

In most databases, especially normalized structures, you design so there will be no duplicates.
That's what primary keys and unique records is all about.

[bigderon88]

No No. I am sorry if I have misled you. I dont want duplicates, I want to prevent them. When I enter a new file into the database, the DCount function tells me if the case number already exists, I would like for it to then open the existing case so I can edit it instead of adding the new file. Make sense?

[orange]

Do you already have a table? Does it have a primary key?
If so, and you try to add a duplicate, you will get an error.
I forget the number , I think 3022?? and you could write code to trap that error.

Here's a link re duplicate prevention. http://www.databasedev.co.uk/duplicates.html

[bigderon88]

Thats exactly what I want to do, but ignorance prevents me from knowing what areas to change to customize for my situation. I appreciate you trying, but you are working with a user, not a programmer.

[orange]

So in a few lines what's the situation.

The old what, why,where, when, who, how much, and how often (so to speak)

[bigderon88]

OK, have db, 1 table named 'records'. The form is simple. One source is a unique identifier, its called 'Twist Case Number'. I used this to check for duplicates. When I enter the Twist number, if it is duplicate, I want to know, and I want the original value to open, if not dupe, then continue entering. The DCount you recommended was enough to inform me of duplicates, but give me an inch....

So, the table is 'records', the control source is 'Twist Case Number' and the form field is named 'Twist'.

That help?

[bigderon88]

Apparently I need to invest in some VBA classes.

[orange]

So what is the application and what fields are in the table?
How many tables do you have?

[bigderon88]

I have 2 tables, but one is just used for combo box in form. The main table is 'records'. It consists of Last Name, First Name, Middle Initial, Twist Case Number, Day, Month, Year, Person Requesting Case, Date Case Pulled, New 115, Reopen, Other, Other Text, On Shelf and On Loan. The ones in red are check boxes.

I use the db to store information on paper files and their whereabouts. When a file is here, it simply shows on teh shelf, When it is checked out borrower info is appended and it will show on loan. I have just started entering files and I run across alot of duplicate files that may have already been entered. The files have unique ID through the Twist # and I want to be notified if I begin to add a twist # that has already been added, then taken to the original file so I can edit it if necessary. Frequently files cannot be located and workers will create duplicate files. Most of the time the file was just out to someone else. Then I get back both files and one may have already been entered. So the two files are just attached to each other, but they will always have the same case number(Twist number).

[orange]

Sounds a little like a library. How close is this?

Customers borrow Files/Books
Files are stored on shelves
Files exist in paper format only
A File is stored on one shelf(location)
A shelf can store many Files
Customers borrow 1 or more files for a period of time
Customers return borrowed files
Borrowed files are checked in and returned to shelf location
A File can be identified as In Storage or Borrowed
A Borrowed file can be associated with a Customer

How many copies of a File exist?

There are data models, maybe even a template database for such systems, available for free.

[bigderon88]

Yeah thats pretty much it. There is only one file, or case number, but each case number can contain multiple 'files'. That make sense? A user borrows a case file. The files are tracked by case number. On case could have just one file, but another could have 5 files, depending on the length of time and incidents.

I have looked at those templates. They are great, I really don't need anything that elaborate. What I have is great, just trying to add some bells and whistles. I can actually live without the duplicate identification, I just want it, kinda like navigation in a car.