Forms and Subforms

[MadCat]

Data between Forms and Subforms

Hi people

I have a form which will take details from user input and relates to types of training courses which get organised. What i'd like to do is have the last field on the form (Nuber of trainees on the course) populate automatically depending on the result of a query.

What i have got at the moment is the main form and then on that an invisible subform which runs the query and outputs the correct answer. I want to take the value from the sub form and populate the field on the main form that should store the number of trainees on a specific course. This is not working at the moment.

I have also tried to set the default value of the field (number of trainees on course) to the query output but this didn't work either.

I'm relatively new to access so this may or may not make sense.

Any help would be greatly appreciated.

Thanks

 

[Fizzio]

Do you have a main form/subform currently set up with the course details on the main form and the participants on the subform?

I suspect from your question (and this is a very big assumption) that your data is not structured correctly - shoot me if I'm wrong.

If your data is correctly structured and you just choose not to display the participants, than you can use a Dcount to show the number enrolled.

ie in an unbound Textbox

=Dcount("[ParticipantID]", "tblCourseParticipants", "[CourseID] = " & Forms!MainForm!CourseID
(you will have to replace the values with your equivalent field/table/control names)

 

[MadCat]

I probably didn't make much sense. The DB consists of three main tables: course info, trainee, training record. a trainee can have a number of training records, and a registered course can relate to a number of training records. the query i wrote counts how many entries there are in the training record where the course ID matches the ID on the course history form. when i assign an unbound text box to the result of the query, the correct value is displayed. However this text box isn't bound to the "Number of Trainees on Course" field in the course information table and is therefore not updated. Does this help any?

Thanks For the speedy response

Cheers

 

[Fizzio]

You do not need to store this value as you have already demonstrated that you can get the value via a calculation. As this value may be volatile, do not store it, merely calculate it and display it when and where you need to.

 

[MadCat]

I tried doing that by setting the default value of the "Number of trainees on course" field to th query result. The value 0 keeps on appearing in the bound text box. Is there something simple i seem to be missing? It seems to work with an unbound but not a bound text box.

Cheers

 

[Fizzio]

As I have just said, do not store this value in the table as you can produce it 'on the fly' whenever you want to. You do not need a "Number of Trainees on Course" field in the table.
The reason you should not store this as it will quickly become redundant as more students enrol for this course.

Is this any clearer?

 

[MadCat]

Apologies Fizzio

I now understand what your getting at.

Thanks for your help

 

[Fizzio]

No Problem - nice when it clicks innit?

 

[MadCat]

Indeed