Creating a Trigger

[kirtney]

I am designing a database that is going to be used to record student marks in a school. Due to the large number of students I was forced to create cascading combo boxes which would only show the corresponding students when a class is selected. My problem is that I need that selected Student Name and ID to be written to the Grades table as soon as the user clicks on a particular name. Therefore I am trying to create a trigger that would help me to do this. Can anyone outline how this can be done.

Thanks in advance

 

[gemma-the-husky]

I don't quite see why you need to write that data immediately. What if you change your mind? What is your data structure, and what forms do oyu have.

Personally, I would do this by selecting a class, tthen showing all the pupils for that class in a continuous form.

then you can enter grades against each pupil for whatever classes they take, although setting up linked continuous forms needs a bit of coding.

 

[jdraw]

Possibly.
There is NO Trigger in Access per se. But you can work from an Event (Click on a Name)
and cause specific code to be executed.

So in general your process is like this:

Select a Class from combo.
Display Students in Combo based on Class selected.

Click(Select) a Student from StudentCombo

In the Click Event of the Student combo have code that
Inserts a record in the Grades table with values for StudentId, Class

(at this point you could select a Mark/grade as well; or enter one etc) and include it in the record insert into Grades.

OR you could select a Grade from a combo/list and use it to UPDATE the record you just inserted into Grades.

Hope this helps.

 

[kirtney]

Gemma interesting idea I never looked at the continous form option. hmmm. The reason why i used the cascading combo boxes is because I am trying to minimize the number of errors they could possibly make by limiting them to dealing with seeing one record at a time.

 

[gemma-the-husky]

Here's an example. I modifed it from an existing example I had, so I had to change all the field/control names to make it look sensible for you, so some of the control names in the forms may be slightly off - but it works


there are some forms which are sub-forms. The main forms to try are


frmStudents - lets you use navigation buttons to step through students, and modify their classes

frmClasses - does this the other way - and lists students by class

you do not need any code for this - this is all automatic

you could add a combo box to select a student or class, and jump to that record - or you can use the binoculars
------


the other example frmContainer shows both students AND classes as continuous subforms, and this does need a couple of lines of code, as access cannot manage the links automatically, between continuous forms.


Hope you find these ideas useful

View attachment students.zip