Duplicate Entries 1

[4072]

Hi

I have a form that rights to a table called NameApps, this table has two fields : Name and applications. The problem that i am having is that what code can i put in the form to tell the user that a user can not have two of the same applications. The d count will not work becasue other users are allowed the same applications, how do you do this ?

 

[KenReay]

Hi

Put a key (unique) of Name and Applications in your table, then users will not be able to add a duplicate row

By the way, I would not advise using name as a column name, Name is an access reserved word since it is a property of (among other things) tables and columns, using reserved words as object names will lead to tears

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[4072]

Hi

Yeah i am not sure what you mean by " putting a unique key" in the table, can you explain a little more, thanks

Amrit

 

[4072]

Hi

Also is there not an if or else statement i can assign to a button that say for example :

If Name has more than two of the same applications, bring back an error.

 

[KenReay]

Hi

Yes, you can do it the IF way, but why do that when Access will do it for you?

To define the (unique) key,

open your table in design view,
choose view\indexes,
in the leftmost cels of the first blank row, put a name (anything except primekey)
in the next cell put strname
on the next row, under strname put strApplications

(assuming your columns are called strname and strApplication)
move back onto the first row (ie the row with strName in it), and look at the lower part of the dialog, there is a dropdown box, choose NoDuplicates

you are in business

Note you could also make the two columns in question the prime key on this table, this is even easier, in design view select both columns and click Edit\PrimeKey a little key symbol will appear alongside the columns



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[4072]

Hi,

For some reason it would not let me save, Did you mean open up my existing table that i have already created. When i looked at the indexes the names were already filled in and their was no drop down menu for "NoDuplicates

 

[KenReay]

Hi

"open your table in design view", yes I meant your existing table

If you look at the botton of the dialog there is without doubt a series of three boxes
Primary
Unique
Ignore Nulls



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[4072]

Thanks very much mate it worked. By the way is their anyway of putting a nicer and simpler message up their explaining why it did not save ?

Thanks again that was a great help.

Amrit

 

[scottian]

try trapping the error and then use a custom form to popup instead

Be ALERT - Your country needs Lerts

 

[RoyVidar]

Forms error event. Not sure, but I think this is dataerr 3022. Something like this in the forms on error event:

[tt]if dataerr = 3022 then
msgbox "your custom message"
response = acdataerrcontinue
end if[/tt]

If it isn't 3022, put a msgbox dataerr within the form error event to find the correct number...

Roy-Vidar

 

[KenReay]

Hi

Error 3022 it is, as evryone has said, trp it with an on error and display whatever message you want

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[4072]

The message "the changes you requested tot he table were not successful because they would create duplicate values in the primary......" still appears even though i have added the code "if dataerr = 3022 then
msgbox "your custom message"
response = acdataerrcontinue
end if
" in the on error event. Is there a way around this ?

 

[scottian]

there are two error messages each assigned a different code. the first informs you that you cant add a duplicate record, the second informs you that the record cant be saved at this time
i think you are experiencing the second error message.
try using two IF's in the on error event :-

if dataerr = 3022 then
msgbox "your custom message"
response = acdataerrcontinue
end if
if dataerr = 2169 then
response = acdataerrcontinue
end if


Be ALERT - Your country needs Lerts

 

[4072]

Hi,

I got the first message saying that i could not have duplicates. There was no second message. My message that i put in only kicked in when i tried to change to design mode, not when the actual error happened. ?