Hello,
I have been using one table to store all information for appointments. This works, but is really a bad way to do it, since these are the fields I'm using as an example.
Date, StartTime, StudentName, TutorFName, TutorLName, MathDetails, CompDetails, ScienceDetails
So, I'll be duplicating each of the fields staring at TutorFName many times, not ideal. So, I'm now trying to have it reference another table, and use an Initial as an identifier.
AppointmentTable:
Day, StartTime, StudentName, TutorInitials
StaffListTable:
TutorInitials, TutorFName, TutorLName, MathDetails, CompDetails, ScienceDetails
This worked fine, if my form was working with one tutor at a time, since the Staff list values will not change. I used DLookup to make this work. The problem is with my continuous form, to show all tutor appointments who do a certain subject.
So, for example, I have a form to filter it by those who do science, so it will show all tutors who do not have Null for their [ScienceDetails], which works fine, but my form displays this way:
SubjectForm: Uses AppointmentTable as record source.
(Only StudentName can be entered in)
Day, StartTime, StudentName, TutorFName, TutorLName, ScienceDetails
I need TutorFName, TutorLName, ScienceDetails to be pulled from the StaffListTable based on the initial attached to each appointment. I tried this:
The text box where I want the ScienceDetails is called Details.
Me.Details.ControlSource = DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")
This will make the Textbox Details show "#Name?".
When I display the DLookup with a Msgbox like this:
MsgBox DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")
It correctly shows the ScienceDetails based on the TutorInitials in the first record displayed, so all the table names, and field names are not mispelled, and the DLookup does work.
If I try:
Me.Details = DLookup("[ScienceDetails]", "StaffListTable", "TutorInitials = '" & Me.[TutorInitials] & "'")
It displays the correct details dor the first record, and duplicates it throughout all the records.
How can I get it to show the correct value for each field?
Thank you so much in advance.