Duplicate records , no error message

[NOL]

I've looked up all posts on this topic .
and tried almost everything.
but still no luck

Here's a thread which also describes teh very same problem i'm having.
The primary key violation error just can't be trapped.

http://www.access-programmers.co.uk/forums/showthread.php?threadid=1298&highlight=error+trapping


Please help!

Gina

 

[bradcccs]

I am guessing that the duplicate field is not the primary key field?? Primary keys are normally incremental autonumbers, and should look after themselves (unless appending etc).

Anyhow, to trap the duplicate error, you should use error trapping on error code 3022.

It must be noted, that a duplicate error is not generated until the record moves from "dirty" to saved. (on form close or record save etc).

If you wish to check for duplicate error on a particular field, then you will have to trigger a record save command after completing the relevant field. If you have other required fields etc you will need to ensure that they are completed prior than trying to save.

Otherwise, you can wait until the record is closed.

Error trap:

If Err.Number = 3022 Then
MsgBox "A duplicate record will be created. Please check data and re-enter.", vbOKOnly, "Data Duplicate Error"
' Any other actions required. eg:Me.Undo
End If

HTH

Brad.

 

[NOL]

Hi ,
Your response is so greatly appreciated !!!
I really needed sum ideas .Thanx

The duplicate field is the primary key . It is not an autonumber field but a user name. ( text field)

The "on error goto "doesn't seem to work .
Where should i be using the following code :

Quote :
************************
If Err.Number = 3022 Then
MsgBox "A duplicate record will be created. Please check data and re-enter.", vbOKOnly, "Data Duplicate Error"
' Any other actions required. eg:Me.Undo
End If
*************************

Also , I am inserting the record into a table which is not at all used by form . just that when the form loads i insert the record.
so how do i force the record in the table to be saved immediately after i insert ?

Quote:
*************************
then you will have to trigger a record save command after completing the relevant field
*************************

I'm almost there, if i could only trap this error!!!

Gina .

 

[bradcccs]

OK, now I am a little confused.

Also , I am inserting the record into a table which is not at all used by form . just that when the form loads i insert the record.

Could you explain the record addition process in a little more detail.

You say that you insert the record when the form loads? ? ? Where does the data come from. A form is empty upon loading if it is not based on a table?

I won't guess anymore, I will let you explain your situation to me

Brad.

Oh, and I won't go on about using user data to construct a primary key, I am sure you have heard all the "tut tut's" before.

 

[NOL]

) my head's swimming !!
I really wonder at the patience
people display on this forum .. hope i can be like that some day !


I have a shared access database with a login form.
when a user selects his name from the dropdown and cliks on MyButton :
1. I set a global variable to capture the username.
2. Myform loads
3. I insert username into table LOG
4. If sum1 logs in with same username from a diff PC at the exact same time , I want to capture the primary key duplication error and let him know that the user is already logged in.
I do not want to do this by checking the table using Dlookup or openrecordset as it takes too much time, and by then both users are in.

I have to use the username as primary key as i am using it to track who's logged into my application
And I know for sure it will always be unique

I hope i haven't scared every1 off with my long story !

And here is my other post , i originally intended to post two diff topics
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=144591#post144591

Thanks,
Gina.

 

[NOL]

I did a search in this forum for "3022"
and every1 seems to be able to trap the error just as u trap any other !!

i used the On error goto
as well as the Form_error event
neither seems to be trapping the error

what am i doing wrong ?
could it be coz I am inserting into a table on which the form is not based ?

I hope somebody has a brainwave , i've used up all of mine !

 

[bradcccs]

Which access version are you using?

 

[NOL]

MS Access 2000

 

[bradcccs]

Gina,

I am not a big fan of the process you are using, but then again, I haven't seen that complete process that you need to fulfill.

I have knocked up (an extremely rough) copy of what I think you are trying to achieve.

The two forms are only to emulate multiple user login forms on different PC's - Obviously only one form would be needed on a multiple user system.

You will still have to work around the possibily that a user will not log off correctly / PC or db crash etc.

I have used an action query simply to avoid complicating the VBA / logic behind the log in operation.

If you have any problems, let me know.

As mentioned, it is only rough. I apologise if I am on the wrong boat with what you want to achieve.

Brad.

 

[Pat Hartman]

Try this:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=41971&highlight=3022

Make sure you put it in the form's Error event.

 

[NOL]

Thanks Pat.

I did put a simple piece of code behind the error event of my form.
Msgbox "Here I am"

The event does not even get activated to show me this message.

Thanks,
Gina.

 

[NOL]

Brad,

Thanx so much for ur help.
I am looking at the file u sent and comparing it with my code.

Will let you know how i fare

Thanks,
Gina.

 

[NOL]

Brad ,

The application u created works great & captures error 3022
Is it because u are using DOcmd.runcommnad accmdsaverecord to update the record ?

I am using a SQL insert statement to update my table USER_LOG
The record source of my form is not table USER_LOG.
so acsaverecord does not work for me.

Thanks,
Gina.

 

[NOL]

Hi ..

Brad , I modified my login form to look like urs.
i am no longer using Insert SQL statement .
Now i can capture the 3022 error.
(Wish i knew why it doesn't work with Insert statement though)

Thanks to everyone for your help !
Gina .

 

[NOL]

Somebody , please help !

acmdsaverecord overwrites and does not create a new record in my table !

all i want to do is enter data into a table and catch the primary key violation error (

 

[bradcccs]

Gina,

When you say that it is "overwriting" the record, what exactly do you mean?

By your description, only one Username (PK) can exist in the User_Log table anyway.

 

[NOL]

Not one record but one record for every user !
I have a shared database.

Computer 1:
Bobb selects username "Bobb" from dropdown and cliks on "View"

Computer 2:
Bill selects username "Bill" from dropdown and cliks on "View"

USER_LOG table will now contain (No one has logged off):
Bobb
Bill

Computer 3:
Sam selects username "Bobb" from dropdown and cliks on "View"

Now, capture Primary key violation error 3022 , and display message to user " Bobb is already logged in"

USER_LOG will still contain (No one has logged off):
Bobb
Bill


Computer 4:
Annie selects username "Annie" from dropdown and cliks on "View"

USER_LOG will still contain (No one has logged off):
Bobb
Bill
Annie

Really hope I get get some ideas from you folks , I'm getting stumped every inch of the way

Thanks, really appreciate ur help.
Gina

 

[bradcccs]

Fantastic description Gina,

but, isn't that exactly what you wish to happen?

The list of Users in User_Log seems to be compiling perfectly. The users will be removed from the User_Log when they log off.

We will knuckle this problem in the end I'm sure.

Brad.

 

[NOL]

thanx

No that's not hapening.
Everytime i do a save , Bobb changes to Bill and so forth, there is always just one record in the table viz. the user who last logged in.


Thanx ,
Gina

 

[bradcccs]

Could you post your code that is now behind your "View" button.

(Or in fact, the entire code from the form if there are other buttons / after update events etc.)

Brad.