Help With Access Form - Duplicate Fields?

[JoeMee1]

Hi all,

Thanks for taking the time to read this. I am quite new to Access, and i was wondering whether you could help me with something?

I work at a School and have been given the task to devise a detention sheet.

I have a simple table and form created, which allows the user to submit the pupils first name, surname, date of detention, and type of detention.

I would like a way, to bring up an error box, whenever a pupil has been given the same detention on the same day.

For example, If Joe Bloggs was given a Curriculum detention on 1/1/15, then another one, of the same date and detention could not be added.

However, i want to make it so he could recieve a different type of detention on the same day.

I searched around online, and have came across code to prevent, for example, i can prevent the First Name being blocked via the VBA below, however this means any pupil with the same name cannot be entered.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StudentName As String
Dim TypeOfDetention As String
Dim stLinkCriteria As String
'Assign the entered customer name to a variable StudentName'
StudentName = Me.FirstName.Value
stLinkCriteria = "[First Name] = " & "'" & StudentName & "'"
If Me.FirstName = DLookup("[First Name]", "DetentionT", stLinkCriteria) Then
MsgBox "This customer, " & StudentName & ", has already been entered in database." _
& vbCr & vbCr & "Please check customer name again.", vbInformation, "Duplicate information"
Me.Undo 'undo the process and clear all fields
End If
End Sub


I hope this makes some sort of sense, and thanks beforehand for your time!

Joe

 

[plog]

This isn't something that should be solved with code, but by structuring your tables properly. At the table level you can tell it not to accept data on the same date for the same person with the same type of detention. It will then not let people enter data that meets that criteria.

First, you need a new table--Students. You would need it to contain these fields at a minimum:

Student_ID, autonumber, primary key
Student_FName, text, first name of student
Student_LName, text, last name of student

That table will ensure someone doesn't type Joe Bloggs, another types Joseph Bloggs and a third person enters Joe Blogs when they all mean the same person.

Then comes your Detention table:

ID_Student, number, foreign key to Students Table (https://en.wikipedia.org/wiki/Foreign_key)
Detention_Date, date, date of detention
Detention_Type, text, drop down of all valid types of detention


Then in Detenion table you set a composite primary key (http://stackoverflow.com/questions/6335094/how-to-define-composite-keys-in-ms-access) using all 3 of those fields. When you do that Detention table will not allow duplicates.

 

[spikepl]

I have to force myself to read that table name as Detention rather than Detonation

 

[plog]

With kids today, that may be a type of/reason for detention.

 

[JoeMee1]

This isn't something that should be solved with code, but by structuring your tables properly. At the table level you can tell it not to accept data on the same date for the same person with the same type of detention. It will then not let people enter data that meets that criteria.

First, you need a new table--Students. You would need it to contain these fields at a minimum:

Student_ID, autonumber, primary key
Student_FName, text, first name of student
Student_LName, text, last name of student

That table will ensure someone doesn't type Joe Bloggs, another types Joseph Bloggs and a third person enters Joe Blogs when they all mean the same person.

Then comes your Detention table:

ID_Student, number, foreign key to Students Table
Detention_Date, date, date of detention
Detention_Type, text, drop down of all valid types of detention


Then in Detenion table you set a composite primary key using all 3 of those fields. When you do that Detention table will not allow duplicates.

Thanks very much for your reply.
I have created a new sheet, with the two new tables (Students, Detentions).

I have created the fields as you suggested for both, and i believed linked the Foreign key on the Relationships. (Apologies, im a noobie!).

I've tried everything, and still i can add multiple values with exactly the same date,detention etc? Just wondering what i am most likely doing wrong?

The tables link, as i can add a Form, populate the fields and the data is inputted into both tables.

Many thanks for your help on this

 

[plog]

I don't think you did the composite primary key field thing I mentioned. Go into design view of Detentions, select all the fields, right click and click the 'Primary Key' option. that should put a key symbol to the left of all those fields. When that is done, you have enforced your rule on the table.

 

[JoeMee1]

I don't think you did the composite primary key field thing I mentioned. Go into design view of Detentions, select all the fields, right click and click the 'Primary Key' option. that should put a key symbol to the left of all those fields. When that is done, you have enforced your rule on the table.

Hi Plog,
Many thanks for your reply again, it really is appreciated.
I have just checked the composite primary key field, and can confirm i have the 'key' next to the Detention Fields (ID_Student, Detention_Date and Detention_Type). I then have made a form, and i can still submit duplicates with the same information for some reason.
I'm sure it is something i am doing wrong. I have gone into relationships and linked the Student_ID and ID_Student from the two tables.

I was wondering what else i may have done wrong? I apologise for maybe asking simple questions - as stated before, a newbie on access!

Many thanks for your time,

Joe

 

[plog]

You're fine, no need for aplogogies. Can you post your database? You will have to zip it then attach it.

 

[JoeMee1]

You're fine, no need for aplogogies. Can you post your database? You will have to zip it then attach it.

Hi Plog,
Thanks for your speedy reply! Database attached.
I think this is where i post an embarrassed face! There is not much on there at the minute, as started fresh with your guidance!

 

[plog]

It works like its suppose to for me. Do this: Open the Detention table and try and add a new record with the values already present in there.

I tried to add a record of {20, 10/15/2015, LATE} and it gave me an error message because a record like that already existed.

What exactly did you do that makes you think its not working?

 

[JoeMee1]

It works like its suppose to for me. Do this: Open the Detention table and try and add a new record with the values already present in there.

I tried to add a record of {20, 10/15/2015, LATE} and it gave me an error message because a record like that already existed.

What exactly did you do that makes you think its not working?

See that works for me too. Reason i am thinking that it's not working is that eventually i will need to put this in a nice form format, for staff to input data.

When i go to the Form Wizard, go through the process (adding the Student_FN, Student_LN, Detention_Date and Detention_Type) fields into the form. When i then put the information in, (after adding a save button etc), i am able to put duplicate fields in, and submit the same user multiple times for the same detention etc?

EDIT: Just to add - When i would then get the form sorted, i guess it gives a new Unique ID number each time? Ideally, the Staff will not put an ID in, and just put the First Name, Surname, Date and Type in. Would this be possible?

Hope that makes sense.

Thanks again,

Joe

 

[plog]

(adding the Student_FN, Student_LN, Detention_Date and Detention_Type)

One form shouldn't have those all fields on it. When you create a form to directly act on data (add, edit, delte) that form should be based on one table. Sounds like you were using your query in as your datasource for one form that attempted to do all that. That would explain why your name is in the Student table 3 times.

How do you envision data being added to your database? Walk me through the process--don't use any database jargon (I've got that part)--explain how you envision your staff will enter data. Will it be batched up on detention sheets and entered by someone at the beginning of detention? Will they log in to a computer the moment they assign detention to some student and log it that way? What happens in the real world that prompts this data to be populated?

 

[JoeMee1]

One form shouldn't have those all fields on it. When you create a form to directly act on data (add, edit, delte) that form should be based on one table. Sounds like you were using your query in as your datasource for one form that attempted to do all that. That would explain why your name is in the Student table 3 times.

How do you envision data being added to your database? Walk me through the process--don't use any database jargon (I've got that part)--explain how you envision your staff will enter data. Will it be batched up on detention sheets and entered by someone at the beginning of detention? Will they log in to a computer the moment they assign detention to some student and log it that way? What happens in the real world that prompts this data to be populated?

So, the way I want it to be is that Staff get presented with a front screen which has the ability for them to add a students First Name, Surname, Date, and Detention on it. They then save this, and then at the end of the day, a member of staff from our admin team views an overview form of each type of detention, to see which students are on for the day.

A student can be added for multiple detentions, for example if he/she got a late, and curriculum detention in the same day, then he/she can be added twice. However, if student obtained two curriculum detentions in the same day, by two different teachers, then when the second teacher goes to add student in, it says 'duplicate'. Then student can get added to the next day.

Edit: just to add, all members of staff will be able to access this front screen the minute they grant a student a detention. So they will be able to just open it up, input the student name, type etc, and then save it.

Hope that makes sense mate

 

[plog]

Does the Students table really need to be populated by people? Do you not have a list of students already that you can use to populate your Students table?

Even if not. The Students table should be populated seperatly from the Detention table. I would have assigning detention like so:

Teacher opens database, it opens to Detention Form which is a continous form that is filtered to show Today's detention. It's a continous form where each row has 3 fields: A drop down to select Student name, a drop down to select Detention Type and input to enter Detention date, which by default is today's date. Teacher moves to a new record, if a duplicate would be made, Access will show an error message and teacher can change Detention Date.

 

[JoeMee1]

Does the Students table really need to be populated by people? Do you not have a list of students already that you can use to populate your Students table?

Even if not. The Students table should be populated seperatly from the Detention table. I would have assigning detention like so:

Teacher opens database, it opens to Detention Form which is a continous form that is filtered to show Today's detention. It's a continous form where each row has 3 fields: A drop down to select Student name, a drop down to select Detention Type and input to enter Detention date, which by default is today's date. Teacher moves to a new record, if a duplicate would be made, Access will show an error message and teacher can change Detention Date.

We have a list of students (700 odd) which we could put into the students table, yeah. I'm just thinking the drop down would then be huge, with all the names in?

However what you are saying would be fine, and exact what is needed

 

[Uncle Gizmo]

I'm developing this product for this exact problem!

https://youtu.be/sTPcEco-yoc

 

[JoeMee1]

Plog, you are a genius.
Got it working how it should this morning.
Thank you so much for your help!!

Joe