Updating Table data in Event Handler

[meq]

This is the first time I am working with MS Access. The database has 2 tables.
-Parent and Student and ParentID is the Primary key as a parent may have multiple Students
-There is a form that lets me add students for a particular parent

One of the fields in the Parent Table is FeeDue. I added a field in the Parent Table called NumOfStudents

What I want to do is as follows:

When a student is added on the Student Form, I want fee to be calculated automatically for display AND update the FeeDue field in Parent table

Event handler executed when a student is added (checkbox clicked)

OnClick()
{
Read NumOfStudents from Parent Table

if(student_added = true)
NumOfStudents++
else /* This is to cover student withdrawl*/
NumOfStudents--

if(NUmOfStudents = 1)
Fee= 400
else
Fee = 500
}

student_added check box is on the student form

NumOfStudents and FeeDue are fields in Parent

Can someone please help me with this. :banghead:

 

[StarGrabber]

Hi meq, welcome to the forum.

You're "looking forward to learn some cool stuff here"? Well, we will see if that stuff is really cool...

Assuming you use the ParentID as a foreign key in the student table you should delete the field NumOfStudents. For the count you better use a query like...

SELECT Count(*) AS NumOfStudents FROM StudentTable WHERE StudentTable.ParentKey = [Forms]![StudentForm]![txtParentKey];

And for updating the field FeeDue in Parent table use again a query. But this time an action query:

UPDATE ParentTable SET ParentTable.FeeDue = [Forms]![StudentForm]![txtFee] WHERE ParentTable.ParentID=[Forms]![StudentForm]![txtParentKey];

Please note you have to adapt the names in both SQL strings shown above, specially the ones of the textboxes (here with prefix txt and assuming you actually have these boxes).

Now you have various possibilities to run the queries:

If you save the first SQL string as a query, e.g. as qryStudentsPerParent, then the easiest way would be:

[COLOR=RoyalBlue]Dim[/COLOR] intStudentsCount [COLOR=RoyalBlue]As Integer[/COLOR]
intStudentsCount = DLookup("NumOfStudents", "qryStudentsPerParent")

And for updating the Parent table:

DoCmd.RunSQL("UPDATE ParentTable SET ParentTable.FeeDue = [Forms]![StudentForm]![txtFee] WHERE ParentTable.ParentID=[Forms]![StudentForm]![txtParentKey];")

May I ask, are you a C# programmer?