Fill in fields on subform (2003)

Topic

New Reply

I’m attaching a stripped down db that I need help with. This is a database that tracks dance studio students and their tuitions, etc.

I have a form (Students&ClassesDE) that shows each student and the class(es) she is signed up for. The Class Name field is a combo box that picks from a table called Classes. Once the class is picked, I want the corresponding tuition and costume fees put into the fields in the form. I have read about cascading combo boxes and for the life of me I can’t get it to work. I struggled all weekend on this. Can you please help me? I need to be able to adjust the tuition (only necessary to adjust it for a few students) or costume fee once it’s filled in and have these fees show in the studentcharges table.

The end result I want after these fields are right is to be able to create a report for each parent showing their child and the classes and fees for those children. But, I can’t even get the cascading to work right so I’m stuck.

I really appreciate any help you can give me. Thank you!

Reply | Quote

Replies

After a cursory look, I think your table structure will have to be changed.

Are the Tuition and Costume fees completely determined by the class, or should the user be able to modify them for a particular student attending a particular class?

Reply | Quote

Thanks for looking at it Hans. The tuition and costume fees are determined by the class; but there are instances where they will be modified for a particular student attending a particular class. For instance, the basic tuition for the Sparkler class is $35; however if that student has a sibling in the class also, the sibling’s tuition is $25 (and there will be $10 deducted for each subsequent sibling so that the parents don’t pay full tuition for each student if they have more than one child in a class). So,in those cases, the tuition would be modified.

Reply | Quote

1) You should change the data type for the Tuition and Costume fields in the Classes table from Text to Currency.
2) You should change the record source of ‘Classes subform’ to Classes – no need to use a query involving drp_Classes here.
3) You should change the name of the text box bound to the Costume field on ‘Classes subform’ from Text16 to Costume.
4) Don’t use the On Exit event of the Class combo box on ‘Classes subform’. Use the After Update event instead – this will only occur if the user changes the value,
The code for this event looks like this:

Private Sub Class_AfterUpdate()
Me.Tuition = Me.Class.Column(2)
Me.Costume = Me.Class.Column(3)
End Sub

When the user changes the class, the 3rd and 4th column are copied to the Tuition and Costume text boxes (column numbering starts at 0, so Column(2) is the 3rd column).

See attached version. I removed duplicate indexes etc.

Reply | Quote

Yes! Thank you! Thank you!! I did what you suggested, and it works great!

Reply | Quote