Custom Message for Duplicate Entries

[Chipster]

I have tried the code below, but I am not getting what I need. I have a table [Student Info] with a field [Student ID] as the primary key and many other fields. I added the Student ID field to my form, [Student Add]. When I input an ID that already exists I get an error code as expected but then my form still loads. I currently have a second form for updating student info [Student Update] for which I have a parameter form that asks for the Student ID. I would like to use just one form to add and update students if possible. So, if you add a new Student ID you can enter all pertinent data. However, if you enter a duplicate Student ID the form should populate with that student's information.

If I must have two forms (1 to add and 1 to update) and a duplicate Student ID is entered into the Add form, the user should get a message stating that the record already exists. Then, (Option 1) force the Add form to close without saving or (Option 2) give an option for opening the Update form. Either option is acceptable at this point because I am stuck.

When attempting to use the code below, I get a data mismatch error on the "Answer = DLookUp......" line.

Any help is greatly appreciated.

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)
 
 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo

 

[arnelgp]

is it possible for you to upload your db?

 

[shoji]

Hi Chipster,

I don't understand why you cannot use the same code for Student ID. I would write:

Private Sub StudentID_BeforeUpdate(Cancel As Integer)
   
    If DCount("*", "[Student Info]", "[Student ID] = '" & Me!StudentID.Text & "'") > 0 Then
      MsgBox "Duplicate Student Record Found" & vbCrLf & "Please enter a new data.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

      Cancel = True
      Me.StudentID.Undo
    End if

End Sub

Shoji

 

[Chipster]

I'm going back through my db now and removing any spaces in my names for tables, queries, forms, fields, etc. It seems that my naming convention causes confusion when using the code that others have. I'll post back shortly and I'll also try Shoji's code.

 

[Chipster]

So, I went through and cleaned up my naming convention for all objects. I have tried the following two codes with similar failures. I have an OnLoad embedded macro to go to a new record when the form is opened. There are no other macros or codes associated with the form.

Option (1)
Private Sub StudentID_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[StudentID]", "StudentInfo", "[StudentID] = '" & Me.StudentID & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Student ID Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.StudentID.Undo

Else:
End If
End Sub

Option (2)
Private Sub StudentID_BeforeUpdate(Cancel As Integer)
If DCount("*", "[StudentInfo]", "[StudentID] = '" & Me!StudentID.Text & "'") > 0 Then
MsgBox "Duplicate Student Record Found" & vbCrLf & "Please enter a new data.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
Me.StudentID.Undo
End If
End Sub

Each of the options above give the following error:
Run-Time Error '3464'
Data type mismatch in criteria expression

When I try to debug the code, Option (1) shows the Answer line highlighted and Option (2) show the DLookUp line highlighted.

Also, I get this same failure even if I enter a student ID that does not currently exist in the db.

 

[GinaWhipp]

Try...

Private Sub StudentID_BeforeUpdate(Cancel As Integer)

 Dim lngAnswer As Long

 lngAnswer = DCount("[StudentID]", "StudentInfo", "[StudentID] = '" & Me.StudentID & "'")

 If lngAnswer > 0 Then
MsgBox "Duplicate Student ID Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.StudentID.Undo

Else:
End If
End Sub

 

[Chipster]

Try...

Private Sub StudentID_BeforeUpdate(Cancel As Integer)
 
 Dim lngAnswer As Long
 
 lngAnswer = DCount("[StudentID]", "StudentInfo", "[StudentID] = '" & Me.StudentID & "'")
 
 If lngAnswer > 0 Then
MsgBox "Duplicate Student ID Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
Cancel = True
Me.StudentID.Undo
 
Else:
End If
End Sub

Same failure error as before.

 

[GinaWhipp]

My bad, change line to below...

lngAnswer = DCount("[StudentID]", "StudentInfo", "[StudentID] = " & Me.StudentID)

 

[Chipster]

My bad, change line to below...

lngAnswer = DCount("[StudentID]", "StudentInfo", "[StudentID] = " & Me.StudentID)

Thanks, Gina. Your code worked for the pop up. Now for a follow-up:

After I click OK to close the message box, I get a second system error code: "This value violates the validation rule for the field or record" OK/Help

I would like to prohibit this system message if possible. Also, I would like the force the StudentID field to zero out automatically if a duplicate is entered. Is there additional code that can be added to yours to make this happen?

 

[Chipster]

I have attached a zip file of my stripped db for review. Please take a look and help me streamline it if possible. I have an additional question regarding filling in forms.

My db has a input form and an update form. The input form is based on the StudentInfo table, while the update form is based on a query of the same table with a criteria set for StudentID. This is working for the moment, but I'm wondering if one form can be used for all data entry. It would work like this:
Form opens to new record
StudentID is input
If StudentID does NOT exist, then you fill in the rest of the form
If StudentID DOES exist, then you are taken to that record to update.

If this is not a good way to do it, then I will stick with my current path and the code provided above.

 

[GinaWhipp]

Hmm, okay well a question (or two)...

Is StudentID the PK?
Have you set up a Validation Rule at the Table level?

 

[Chipster]

Hmm, okay well a question (or two)...

Is StudentID the PK?
Have you set up a Validation Rule at the Table level?

Yes the StudentID is the PK, but I do not have any validation rule set on the table.

 

[GinaWhipp]

Oh, I see you uploaded your database... give me a minute and I'll look at it.

 

[GinaWhipp]

Which Form am I looking at?

Side note... do you want me to talk about the Tables or not because you have a few issues I think we should address.

 

[Chipster]

Which Form am I looking at?

Side note... do you want me to talk about the Tables or not because you have a few issues I think we should address.

Please discuss anything you see that needs to be addressed. The form in question is the StudentAdd form

 

[wiklendt]

I would like to use just one form to add and update students if possible. So, if you add a new Student ID you can enter all pertinent data. However, if you enter a duplicate Student ID the form should populate with that student's information.

How about something like this (see attached). Not pretty, just did it quickly, but shows functionality of one form to view update or delete student info. have no idea how your data groups together, so the tab separations in the pages control are kind of random.

Edit: and i agree with GinaWhipp, your table structures need some attention. Let us know if you would like some guidance there too. Basically, if you find yourself creating fields like "Pref1" "Pref2" "Pref3" etc, then these should really be normalised out to a separate table, eg tblPreferences with StudentFK in it.

 

[GinaWhipp]

What is it you are trying to track? Your Tables do not appear to be properly normalized and to do that would need to know what you are trying to accomplish. Also your field names... you are using some Reserved Words, have a look here...

http://allenbrowne.com/AppIssueBadWord.html
http://www.utteraccess.com/forum/Access-Reserved-Words-t539419.html

You should develop a Naming Convention to avoid situations like this. Have a look here...

For Naming Conventions see...
http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.access-diva.com/d1.html

Also, why are you adding a new Student from where you also enter data for a Student. I would have your Users select existing Students from a Combo Box and then use the Not_In-List event to add a new Student.

 

[Chipster]

How about something like this (see attached). Not pretty, just did it quickly, but shows functionality of one form to view update or delete student info. have no idea how your data groups together, so the tab separations in the pages control are kind of random.

Edit: and i agree with GinaWhipp, your table structures need some attention. Let us know if you would like some guidance there too. Basically, if you find yourself creating fields like "Pref1" "Pref2" "Pref3" etc, then these should really be normalised out to a separate table, eg tblPreferences with StudentFK in it.

This could work for my purposes, I'll mill it over and see what I can do with it. It appears that the tabs in your form simply hold the fields on each page vice having to use a subform for no reason as in mine. This was a db design that I inherited and I'm trying to make the db work, so I'm not "in love" with how it is designed now.

It sounds like you are both suggesting one table to hold the student's information (i.e. Student ID, Name, Rank) and another table for the selections data I need and another table for the winging data that I need. All of these would need a relationship on the Student ID.

What is it you are trying to track? Your Tables do not appear to be properly normalized and to do that would need to know what you are trying to accomplish. Also your field names... you are using some Reserved Words, have a look here...

http://allenbrowne.com/AppIssueBadWord.html
http://www.utteraccess.com/forum/Access-Reserved-Words-t539419.html

You should develop a Naming Convention to avoid situations like this. Have a look here...

For Naming Conventions see...
http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.access-diva.com/d1.html

Also, why are you adding a new Student from where you also enter data for a Student. I would have your Users select existing Students from a Combo Box and then use the Not_In-List event to add a new Student.

I will review these links, but first let me answer your questions on tracking. Background: I track student production from start to finish within our course of instruction. This course has several phases: (1) Primary - Once complete with Primary each student is assigned a pipeline (2) Advanced - This is the specific pipeline that each student will follow. Once complete with Advanced each student is "winged". (3) FRS - This is the specific aircraft that each student will fly during their career. Once complete with FRS each student is assigned to an operational squadron and this is where I stop tracking them.

I didn't realize that I had deleted my Production Report when I stripped the db down to zip it. I will upload it again with that report and this may clear up what I'm tracking to some extent. I can elaborate after you see that if necessary. There are also some sub-courses that some students attend during their total course of instruction. This data is held in the TADData table and is tracked/updated by the TADTracker form.

 

[GinaWhipp]

Thanks... I kind of get it now!

Going forward, I would first go backwards and fix those tables. Have a look here...
http://www.access-diva.com/dm7.html

This is just an example to show more of what we should be seeing for Table structure.

I realized you inherited this database but what you inherited was a database that thinks its an Excel Spreadsheet on steroids. Fixing that will make a lot of things easier, including adding a new Student.

 

[Chipster]

Also, why are you adding a new Student from where you also enter data for a Student. I would have your Users select existing Students from a Combo Box and then use the Not_In-List event to add a new Student.

I have uploaded my db again with all objects. Take a look at the ProductionCharts report to see what I am tracking. As far as your question here, my assumption is that the fewer tables you have to link with relationships, the easier it is to pull queries and reports, etc. Also, I always seem to run into issues trying to update records when say a form is based on a query that is based on two tables. Your guidance is appreciated.