MS Access populate a field based on 2 other form fields

MS Access newbie created a simple Access database used to assign horses to student riders. Fields are Student's FirstName, LastName, HorseName, LessonType, Instructor's FirstName, LastName and LessonFee. When the admin creates a record she will fill in the Student's Name, HorseName, LessonType, Instructor's Name which are combo boxes that look up the drop down data in their respective tables. The Fee field is manually filled in by the admin based on whatever agreement was made between the student and instructor. All the fields are saved so reports can be run.

 Now my client wants to add some "matrix logic" so that the Fee field is auto populated based on the LessonType and Instructor Name fields. For example the Fee field logic would go something like:
 If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00
 If Instructor = "Doe" and LessonType="Hunter" populate Fee with $60.00

 and the 'If Instructor and LessonType' logic would be quite long as they have many Instructors and many LessonTypes. Could be as much as 30 IF lines.

 How would I go about making this happen? I created a mock database trying to use some suggestions an expert on a prior ticket gave me but I could not figure it out so he told me to post a new ticket with a database attached, and here it is. I put some of the IF matrix on the form for reference in a label field.
 Thx Experts...BobR
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
I suggest that you complete first the entries in your table "tblInstructorRates"
when it is done, upload a copy of the db.
Leigh PurvisDatabase DeveloperCommented:

Well, I think before you do that, you need to have a think about what you want to achieve.

Do you want a single rate for a given instructor and a given lesson type?  No possibility for more than one cost for a particular combination?
Your current indexes aren't going to allow you to achieve that.  (They're too restrictive, preventing any instructor or lesson type from appearing more than once).
A unique index across both would allow the above scenario.
You then just need to simply lookup the value matching both in the form.
For example attached.

If you wanted more than one possibility, then you'd remove the unique index, or extend it to include the Charge field too.
And provide a combo as you originally had for the charge, to filter the list of options.

bobrossi56Author Commented:
Leigh....only one possibility for instructor and lesson type. I will take a look at your example and see how it works and then get back with you. Thanks SO much...
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

bobrossi56Author Commented:
OK, this works GREAT Leigh...but I cannot "see" how you are doing this. When I get the properties for the LESSON FEE field I do not see how this field is being populated.....AND...

I added a new row to the tblInstructor rates which should have populated the LESSON FEE box with $500.00 when I select SMITH and JUMPER but it does not populate it with anything...don't know why but I attached the database. Once I understand how this InstructorID and LessonID matrix works I can add all the other ones myself.
thx much
Leigh PurvisDatabase DeveloperCommented:

You'll see code in your form's module.

Private Sub cboInstructor_AfterUpdate()
End Sub

Private Sub cboLessonType_AfterUpdate()
End Sub

Sub CalcCharge()
    If Not IsNull(Me.cboInstructor) And Not IsNull(Me.cboLessonType) Then
        Me.txtCost = DLookup("LessonRate", "tblInstructorRates", "LessonTypeID = " & Me.cboLessonType & " AND InstructorID = " & Me.cboInstructor)
    End If
End Sub

Open in new window

There was a small typo in it, that's working now.
If you didn't need to ever alter the lesson rate, then there's no need to use code to insert it, you could have the value looked up directly in an expression.  But the code is simple enough and offers flexibility.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobrossi56Author Commented:
Sweet Leigh...thank you so much!! One more detail/question. I just heard back from my barn manager and she informed me that the LessonFee field was to calculate the lesson fee, but the instructor does not get all that money. For example:

If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00, (which is the Instructor's earnings), the lesson fee to the student is $60.00

Is there a way using the same logic that you got working to populate another field with the other amount? Would I need another table? Looking at the tblInstructorRates it seems like I would need to maybe add another column there?
thank you
Leigh PurvisDatabase DeveloperCommented:

It depends entirely on how you want to work it.
If it's a fixed percentage, then you could just calculate that at insert time (store the percentage in a table and use it in a calculation in the code).  If it's a variable proportion of the amount then, yes, I see no definitive reason not to just add another field to your table.  You can look up both fields from tblInstructorRates then, the fee and the proportion they receive.

bobrossi56Author Commented:
It's a fixed amount of money.
Leigh PurvisDatabase DeveloperCommented:
I'm not sure what you mean there.  Fixed amount, for each scenario, for every instructor?
All instructors earn the exact same amount?  (If not, it would then be stored against the instructor would it not?)

You know this business best of all.  You're going to have to make decisions on how everything is calculated.  And to whom it relates.
And details are pivotal.  Brevity doesn't achieve a whole lot.

bobrossi56Author Commented:'s clear in my head so I should have been clearer for others....the rate would be fixed based on the same criteria as the lesson fee to the student. For example:

 If Instructor = "Hanny" and LessonType="Western" populate LessonFee with $65.00 and paid to the instructor $15.00
  If Instructor = "Doe" and LessonType="Hunter" populate LessonFee with $75.00 and paid to instructor $20.00

So I was thinking if I just made another field in my tblInstructorRates called StudentOwes, then on the form made another field called txtStudentOwes, all I would have to do it repeat the code you put into the form module and change out the first field  name and LessonRate for StudentOwes, right?

Me.txtStudentOwes = DLookup("StudentFee", "tblInstructorRates", "LessonTypeID = " & Me.cboLessonType & " AND InstructorID = " & Me.cboInstructor)

So I tried it and IT WORKS !!! You have been SO MUCH help to me I cannot thank you enough. Thank you for sticking with the question and seeing it through. So many others just hit the question with a quick answer and never look at it again.
Leigh PurvisDatabase DeveloperCommented:

>> So I tried it and IT WORKS !!!
I'm very pleased to hear that.  Not just that you got it working, but hat you took what you'd had working and applied it elsewhere.  And that you attempted it.  The very essence of learning.
Just as a by the way, it seems you've accepted your own, earlier, post as the answer.

bobrossi56Author Commented:
Closed by accident, need to aware points to Leigh and not myself.
bobrossi56Author Commented:
All set now....many thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.