Yes (No Duplicates)

[partana]

Hello, (Access 2007)
I have one form that has the follow data types
Patient ID (primary key)
Patient Research number. This is a text field with a unique alphanumeric code.
I do not want this code to be duplicated when i enter a new subject. I have set the Table view Indexed condition to Yes (No Duplicates), thinking that a popup box would stop me from entering a duplicate value. Well, it worked but not how I envisaged . When I tested it, it did give me an error message when I put in duplicate values. Great! However, for some reason it is giving me the same error message when I enter new (unique) data into this field.
Is this happening because the Primary Key is indexed the same way? If so, what can I do?

If I am not asking too much, could someone also tell we how (step by step) I can create a custom error message box when this event happens?
Thanks

 

[Galaxiom]

Generally it is better to anticipate the error and code to avoid it rather then wait for it to happen and try to deal with it.

One way is to use the DLookup function to see if the proposed value already exists.

If the new unique value is also causing the same error then there is definitely something you have not understood.

 

[jdraw]

What data type is PatientId?

http://office.microsoft.com/en-us/access-help/prevent-duplicate-values-in-a-field-HA010283317.aspx

 

[RainLover]

First of all this has nothing to do with the PK or any other Field in the Table.

The error message is most likely correct. Error messages sometimes mislead.

How and when do you enter this new value. Is it the same record or a new one. Please do a test on each of these methods to narrow down exactly when the error occurs.

 

[partana]

To all that responded so quickly, I thank you.
Re: DLook up. The database is one i am trying to develop. As such there are only 6 'play' subjects in the database with no chance of the data being duplicated. I was hoping to build in this precaution before i start to migrate data.
Re: PatientID. This is an auto number generated by Access. My ResearchID data looks like this A 001. How would i make this unique? I tried making both the PatientID and the ResearchID primary keys, but when I did this the database changed the ResearchID to an auto number.
Re Correct Error message: I have tested this with my 'play' data. As I stated, I get the error message even when I enter an entirely different code.
Help

 

[partana]

Rainlover- I love you!
I re-read your answer a few times until I Got It!. I was not going to a new record to test the duplicte or the new value- I was just changing the value in the same field. It is working just fine now!!great!
Now, can you guide me through the steps to create a custom pop-up that will display my error message for this event.

 

[RainLover]

If what I believe is happening is correct then yes I can help.

First of all we need to find the Error Number. This can be done by a simple error trap.

If you don't know how to write one then use access to create a command like go to next record. Then look at the code that Access wrote for you. Adopt that code and use it behind the form.

Do that then post the code and the result.

 

[partana]

Hi RL,
Sorry, you have lost me.
I created a command button using Wizard for a Go To Record button. I clicked on the ... which brought up the command code sheet.
This is the code:
Private Sub cmdNextRecord_Click()

End Sub
No indication of an Error Number. Obvioulsy I am not following you. Sorry to be so thick.

 

[RainLover]

I decided to write the error for you but I am having problems working out which event is throwing the Message.

I will keep looking as I have done this before but can't remember where.

I'll find it.

 

[RainLover]

I finally remembered what I did.

Try this by simply doing a Copy Paste into the Module behind the FORM.

Please let me know if this worked.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
If Err.Number = 0 Then
    Response = False
        MsgBox "This has already been recorded." _
            & vbCrLf & "Please adjust", vbExclamation, "Duplicate Values"
 
     Me.Undo
Exit Sub
 
End If
    MsgBox Err.Description

End Sub

 

[RainLover]

PARTANA

The code I wrote will not work for Collingwood supporters.

 

[partana]

Rain, my new best friend!
After much head-banging and near tears of frustration I finally found the 'Module behind the Form" ie VBA screen. Your message worked. However , (isn't there always I have several problems.
1. I still get the original Access error message for the duplicate. I click okay, then your message pops up. I would like only your message to appear. If this will require extensive work, please don’t bother, I can live with two messages.
2. The error message(s) do not occur until I shut down the Form. I have also discovered that when i hit a command button to go to a different Form, i am not prompted that an error has occurred (only when I exit the form).

I would like, if possible for the error message to occur right after I leave the text box (Focus?) and go to the next field.
Thanks again

PS I will barrack (AKA ‘root’ since I am an American) for any team EXCEPT Collingwood… the ferals!

 

[RainLover]

Rain, my new best friend!

I don't know what you have missed. I have posted a sample Database in A 2003 for you to look at.

I have not been able to duplicate the error that you get. You may be running a later version which is causing the problem. I really don't know about that.

Just try entering data in my test DB to see if it works for you. If it does work then compare my DB against yours.

If all else fails you will need to post your DB for me to look at.

I don't want the whole DB. Create a new DB and import the Table and the Form. Test it to see if it demonstrates the problem. You should also delete your data and then add a few records of dummy information via the Form.

PLEASE MAKE SURE that the DB you send me is in A 2003 or earlier.

Hope you are enjoying Melbourne. It is a great City especially if you like sport, entertainment and fine restaurants. I have worked down there for about 4 years. I traveled down on Monday and home on Friday.

Nice to have met you.

 

[partana]

Hi Rain,
Thanks for the info. I am running Access 2007 so i guess it won't help if i send you a copy, but i will still look at your database.

Yes, Melbourne is nice.I worked in Brisbane for a year and a half before returning to Melbourne and I loved the weather up there.
Thanks again for your help. I am sure you will be hearing from me again....soon!
partana

 

[RainLover]

Hi Rain,
Thanks for the info. I am running Access 2007 so i guess it won't help if i send you a copy, but i will still look at your database.

Yes, Melbourne is nice.I worked in Brisbane for a year and a half before returning to Melbourne and I loved the weather up there.
Thanks again for your help. I am sure you will be hearing from me again....soon!
partana

You can always convert your DB to 2003, or just send it and I will do the conversion from an old machine that is set up as a Test Bed for 2007.

Just get rid of all the forms etc that I don't need to see.

Make sure you have a look at my DB first and try to solve your own problem.

 

[partana]

RainLover.
1. Your db came as a read only. To enable content I had to make a copy.
2. In design view I was able to verify that I have put the code you had sent me in the right location on the Form i.e. On Error.
3. When I open the your db I was able to enter the duplicate data, but I only got the Access error message (The changes you requested to the table were not successful because they would....). Your custom message never popped up.
4. I am attaching the db with the Form I am working with (no identifiable data)
[FONT=&quot]I noticed something really strange this morning. I take my database home on a portable hard drive. When I entered your code on my home computer (Windows 2000 I think) it worked, albeit two consecutive error messages. Today when I opened the same database on my work computer (XP Pro), I did not get your pop up message at all- just the standard Access error message for duplicates :banghead:. What the heck is going on?
Thanks again

[/FONT]

 

[boblarson]

RainLover.
1. Your db came as a read only. To enable content I had to make a copy.
2. In design view I was able to verify that I have put the code you had sent me in the right location on the Form i.e. On Error.
3. When I open the your db I was able to enter the duplicate data, but I only got the Access error message (The changes you requested to the table were not successful because they would....). Your custom message never popped up.
4. I am attaching the db with the Form I am working with (no identifiable data)
[FONT=&quot]I noticed something really strange this morning. I take my database home on a portable hard drive. When I entered your code on my home computer (Windows 2000 I think) it worked, albeit two consecutive error messages. Today when I opened the same database on my work computer (XP Pro), I did not get your pop up message at all- just the standard Access error message for duplicates :banghead:. What the heck is going on?
Thanks again

[/FONT]

I just downloaded it from your post and tried it out and it works for me. Are you sure you enabled code? It won't show that the code is disabled if you have, in the FILE > ACCESS OPTIONS > TRUST CENTER > TRUST CENTER SETTINGS > MESSAGE BAR have the option NEVER SHOW INFORMATION ABOUT BLOCKED CONTENT selected. It should have the option selected which reads - "Show the message bar for all applications..."

EDIT: Oh, and RainLover's database isn't read only for me and it may be that you either had the same problem due to the trust center settings, or you needed to right click on the file and uncheck the Security checkbox which states (on some operating systems) that this came from an unknown source.

 

[RainLover]

Thanks Bob.

I was thinking it was a Access setup problem but have not been able to solve it.

Your knowledge of 2007 is helpful.

 

[partana]

Hi Bob,
I have done as you suggested The message bar setting for all office application has two options:
1. Show the Message Bar in all application when content has been block. This has been ticked
2. Never show information about blocked content. Un-ticked.
I just tried ticking the other option (#2)- no difference.
Any other ideas of what i might be doing wrong?

 

[Galaxiom]

As I said earlier I don't believe waiting for the error is the best approach to the problem.

Better to check for the duplicate before you attempt to add the record. This is very easily done with a DLookUp.