When I select sex and level I want the Rate appear automatic

[a.phoenix]

Hi Everyone,





In the form I use table data_Student is the master and data_uniform is subform. When I select sex and level I want the Rate appear automatic.

ex. when Student name is Sarah and gender : F level is 11 when I input the data I want the Uniform subform auto appear her rate uniform (in the picture is 1000)

I'm not sure with this can do it but I want to ask first if anyone have idea to do it please help me.

I use combo box for select it for now.

Thank you
A. phoenix

[isladogs]

You either need to write some very complex code or redesign your tables
You should also rename some fields which have identical names but different meanings in your tables
i.e. Rate / Level

TBH its hard to give correct advice as its unclear how the tables are related or the data actually works
What happens when you add Bill, male student at level 8
Or more confusingly Jane, female student at level 6

The following may be helpful but it depends on the answers to the previous 2 new students

data_Uniform
Split the rate column into two OR just use 1,2,3 etc (RECOMMENDED)

code U-rate
Add 2 extra fields MinLevel & MaxLevel

Then use the StudentLevel and Gender from the students table with a DLookup on the Code table?

[a.phoenix]

Hello Ridders52,

Thank you for your answer. The master table is data_student and data_uniform is the transaction of student in data_student then I created form for user to input the data.
we gave money for student who attend the school and we have rate for uniform.


if the student gender M and Level 1-12 they will get 700 bht.
if the student gender F and Level 1-6 they will get 500 bht.
if the student gender F and Level 7-12 they will get 1000 bht.

ex. Sarah Gender F andLevel 11 so her gender :F and Level :7-12 she will get 1000 bht. for heruniform this year (she can reimburse any uniform she has been paying until 1000bht.) .

when Sarah come to reimburse I have to open her record and update transection for uniform so every time I have to choose combo box rate.

It doesn’t so difficult to do that but some time data entry will mistake because the data not show like this I have many tab it also about school so he can see gender but can’t see level of student in same time so I think it will be helpful if I can do some macro or VBA to fix it. Like the price rate will appear automatic with criteria gender and level.

[ArviLaanemets]

Redesign the table "code U-Rate" (very bad choice for table name IMHO, I'll use tblRates instead) like
tblRates: RateID, Level, Gender, Rate
where level has values like 1, 2, etc. and Gender has values "F" or "M".

It is unclear for me, how are levels for students estimated. Is student stucked with same level from start to end (as having the level in Students table as in your post), or is the level changing over time. When 2nd option, then instead of one student table you need student table like tblStudents: StudentID, ForeName, LastName, Gender, ..., and student levels table, like tblStudentLevels, DtudentLevelID, StudentID, Level, ...

It looks like for one level, student is paid for uniform once.

When the level never changes, you don't need the table data_Uniform at all (and the subform too) - a field Paid in students table will do. No need for fields Rate and Balance, as the rate can always be calculated from tblRates, and Balance from rate and Paid.

When the level for student changes, then you need a field Paid in table tblStudentChanges. Again, no need for fields for Rate and Balance there, as both can be calculated. The subform will be based on query from tblStudentChanges and there will be a record for every registered level for student, and a field for paid sum. And the query has calculated field for level rate for student, which is displayed only. The balance will be calculated over all levels, and is calculated in unbound control on main form.