On Error Statement (1 Viewer)

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
i have a field call "studentid" with no duplicates allowed
im using (DAO) "with rst" "addnew"
now it could be that the "studentid" is already in the system and when the code well run it well give run time error
so i need to put error handler

Code:
On Error GoTo ErrorHandler
if "studentid" excit move record to [sometable] and insert the new record as the code else nothing
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:19
Joined
Jul 9, 2003
Messages
16,243
I think your question is:- When I try and add a duplicate value, I get an error how do I stop this error?

What's puzzling me is when I've used code to add a duplicate, it doesn't add a duplicate but it doesn't throw an error... So either I've got the wrong end of the Stick or you're doing something different.

In any event, the solution is to look in the table first and see if the value already exists. If it does, then do not try and add it...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 28, 2001
Messages
26,996
I guess you are asking the best way to do this?

I MIGHT try something like this, where I am assuming only for this limited example that the student ID is in a text box:

Code:
If DCount( "[StudentID]", "yourtable", "[StudentID]=" & txtStudentID ) > 0 Then
    move the record
Else
    insert the record
End If

That is, since you say the ID is no-dups, you will get either 0 or 1 on the DCount. So that is a simple test for exists / does not exist.

OK having said that, if you are moving the record to another table, that says something about the structure you have chosen. I know you are touchy on this subject, but it seems to me that what is going on here is that you have a record for student 1234, and you get ANOTHER record for student 1234. Since you can't have duplicates you have to get rid of the record in the first table. But you want to KEEP it because you are moving it to another table. By implication, this other table is the same as the one that is losing the record. Which means you have a LOT of data movement.

An alternative is to add a code to the record so that you can categorize this record and make the primary key a COMPOUND key. So that if you want to make room for the new record, you just change that code and then insert the new record with the appropriate code. Then you move nothing except for inserting the new record with the correct code so that the compound key uniqueness remains intact.

Moving data from table to table is clearly a sign of Excel-think, flat-file thinking. Look at some of the videos posted by Uncle Gizmo relating to Excel users moving to Access.

Database utilities really don't like to do the sort of thing you are doing. If I am to be a good helper, I would be failing in my duty if I didn't mention that you are working too hard and putting data at risk by excessive handling.
 

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
tnx
It is an automatic registration system for students.
A student comes and presses a button and the system takes a picture of him.
After that, a form opens for him and he enters his details.
At the end, he presses a confirm button and its prints an interview form with his picture
Now I have a problem. Let's say a student has not been accepted. And he has not updated in the system yet
Which sometimes happens. The student changes a haircut. Adds a beard and comes for another interview
 

vba_php

Forum Troll
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
2,884
Now I have a problem. Let's say a student has not been accepted. And he has not updated in the system yet
Which sometimes happens. The student changes a haircut. Adds a beard and comes for another interview
The_Doc_Man gave you exactly what you need, in order to technically solve your problem, however what I would add is....what kinds of students come to your school who attempt to disguise themselves as someone else and be accepted a 2nd time when they've already been rejected? Maybe you should try an alternative method of registration and qualification review of the students before even letting them fill out the form in access? That's just yet another way to give you a suggestion....
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 28, 2001
Messages
26,996
Adam has pointed out correctly that part of your problem isn't technical, it is personal. If you have student candidates who don't follow the rules, the problem is actually simple but the solution is NOT technical first. It is "policy-level" first - and THEN You implement your technical solution to follow the policy. Therefore, this is a "design" question.

Once you decide what the POLICY rules tell you to do, then it will be easier to do it.
 

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
The_Doc_Man gave you exactly what you need, in order to technically solve your problem, however what I would add is....what kinds of students come to your school who attempt to disguise themselves as someone else and be accepted a 2nd time when they've already been rejected? Maybe you should try an alternative method of registration and qualification review of the students before even letting them fill out the form in access? That's just yet another way to give you a suggestion....

I am another alternative to high school
With ease of study and special scholarships
The bylaw states that every student who comes even 10 times must submit an interview form(can't fix it)
 

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
I'll try The_Doc_Man code And update later
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 28, 2001
Messages
26,996
Not asking you to fix anything. What you told me IS a policy statement. Now you have to decide the rules that go with that for purposes of implementation in your code.

By the way, there is an error in what I posted. I will post a corrected version.

Code:
If DCount( "[StudentID]", "yourtable", "[StudentID]=" & txtStudentID ) > 0 Then
    move the record
End If

insert the record

If the student is there, you need to get the record out of the way. But once that is done, you were saying you needed to insert the new record anyway. My apologies for being a bit loose in that.
 

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
By the way, there is an error in what I posted. I will post a corrected version.

I tried a different method

I entered this code in the form field

Code:
Private Sub StudentID_AfterUpdate()
If DCount("StudentID", "Studentsregister", "=[StudentID]" & Me.StudentID) > 0 Then
Me.StudentID = Me.StudentID & "dup"
End If
End Sub

so if the student id is already there it will add the "dup" to the id then i'll know it's a duplicate record and delete one of them

but i'm gating a syntax error "=studentid"
 
Last edited:

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
Code:
Private Sub StudentID_AfterUpdate()
If DCount("StudentID", "Studentsregister", "=[StudentID]" & Me.StudentID > 0) Then
Me.StudentID = Me.StudentID & "dup"
End If
End Sub

pls help instead of add the "dup" for the duplicate "123456789dup"
it insert the "dup" to every value i insert to the form field
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:19
Joined
Aug 30, 2003
Messages
36,118
Did you see Doc's corrected version? Try

If DCount("StudentID", "Studentsregister", "[StudentID] = " & Me.StudentID) > 0 Then
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:19
Joined
Aug 30, 2003
Messages
36,118
What exact error? If the ID is text:

If DCount("StudentID", "Studentsregister", "[StudentID] = '" & Me.StudentID "'") > 0 Then
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 28, 2001
Messages
26,996
OK, this problem is a simple issue of understanding what is in the three parts of a Domain function like that. Here is a link.

https://support.office.com/en-us/article/dcount-function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3

Your error is simple. Normally, when you do this, you are counting how many members of a particular field have a particular value. But... because you can have multiple filtering elements in that third part, the "criteria" segment, each relationship must be a complete clause - and you used an incomplete and malformed clause, so the count gets confused. But you must have error reporting disabled, because that should not have compiled correctly either.

You used this statement, errors highlighted in red:

Code:
If DCount("StudentID", "Studentsregister", "[COLOR="Red"]=[/COLOR][StudentID]" & Me.StudentID [COLOR="red"]> 0[/COLOR]) Then

You might have used something like:

Code:
If DCount("StudentID", "Studentsregister", "[StudentID]=" & Me.StudentID ) > 0 Then

You put the ">0" part inside the DCount's criteria clause. It needs to be outside because it applies to the DCount result, WHATEVER the criteria. And the equals sign was on the wrong side as well. Does that explain it?

Note, however, that adding DUP only works once if you have a NODUPS field here. What are you going to do if the person tries this again (for a third time) when 123456789 AND 1234567789DUP both exist in your table? You need to perhaps consider how you would handle that.

I think you need a separate field for the DUP count because this is getting more complex as you drill down. My original suggestion was a code but that code COULD have just been the duplicate count instead. You see, once you have the first DUP, your count will be based on, not "colliding" with 123456789 but rather on "colliding" with 123456789DUP.
 

eshai

Registered User.
Local time
Today, 12:19
Joined
Jul 14, 2015
Messages
193
tnx all

that one work
Code:
Private Sub StudentID_AfterUpdate()
If DCount("StudentID", "Studentsregister", "[StudentID] = '" & Me.StudentID & "'") > 0 Then
Me.StudentID = Me.StudentID & "dup"

End If
End Sub

The_Doc_Man tnx
for
What are you going to do if the person tries this again (for a third time)
the record Will be dealt with within a week
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 28, 2001
Messages
26,996
OK, good luck with your project. Come back if something else pops up. I'm not always here but SOMEONE is pretty much here most of the time.
 

Users who are viewing this thread

Top Bottom