How to make constraint when putting records into Access table?

[vinh0105]

I am developing a simple database for students to register courses using Microsoft Access 2010. I have 4 tables: Student, Register, Course, and Section (1 Course can have many Sections).

Then, there is a criteria that a student can only take up to 6 courses. Hence, may I know that where and how can I include this constraint? I believe that it should be in the Macro (Before Change) of the table Register, but I have no idea about it.

My relationship is: Student -> Register <- Section <- Course

Here is my file: https://www.mediafire.com/?7oxbrhrrvhyh5bi

Please help me with that! Thanks in advance

[ranman256]

In the before update, you can check the count of records before.
Change the 'where' filter to add all your conditions.

dim vWhere

vWhere = "[student]=" & form!frmEntry!txtStudentID

If dCount("*", "Course",vWhere ) > 4 then
    msgbox "Student already has 4 courses"
else
    'allow addition of record
endif

[vinh0105]

In the before update, you can check the count of records before.
Change the 'where' filter to add all your conditions.

dim vWhere

vWhere = "[student]=" & form!frmEntry!txtStudentID

If dCount("*", "Course",vWhere ) > 4 then
    msgbox "Student already has 4 courses"
else
    'allow addition of record
endif

Hi, Thanks for your comment. However, I am using Microsoft Access 2010 and I cannot find anything called "Before update". Btw, I have posted my file in the link above, could you please have a look and give me an example with the file? Thanks in advance.

I am really sorry for any inconveniences caused cuz I am a newbie in this field. Please help me with that!

[ranman256]

The BEFORE UPDATE happens on the form. If you are entering records via form, using this action.

I personaly do not used it...instead I have an unconnected form, and a save button
the save button does the date validation (like you want)
If all is good, it writes the record, if not gives a message.

[ssanfu]

Your table structures leave a lot to be desired. Specifically, the PK/FK fields.
Text type PK fields can be used, but they are more difficult to use (IMO). I prefer autonumber type PK fields.

You should read these:
Use Autonumbers properly http://access.mvps.org/access/general/gen0025.htm

Autonumbers--What they are NOT and What They Are http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp

BTW, you do not need the "BirthMonth" field in table "Students". It can be derived at any time by using the Month() function.


Shouldn't table "Register" have a date field, "RegisterDate"? How will you know which year the student registered for??? And maybe a field for which semester?
Are the courses/sections the same every year? Maybe a date field for what year the course is/was offered?

[vinh0105]

Hi guys,

Thanks so much for your help. I will consider to include all these things in the database