I have a control on a form that needs to auto-update/lookup the "rate" after a selection has been made in two previous combo boxes, "location" and "specialty", on the same form.
If I were to use a query for data already stored in the Assignments table, then the following works great.
SELECT A.AssignmentID, R.Rate
FROM (tblMalpracticeLocations L
INNER JOIN tblAssignments A ON L.LocationID = A.MPLocation)
INNER JOIN tblMalpracticeRates R
ON (L.TerritoryID = R.TerritoryID) AND (A.MPSpecialty = R.SpecialtyClassID)
ORDER BY A.AssignmentID;
However, the above only works when the data is already stored in the table. I'm working on a form used to enter data. I have 2 combo boxes to select Location and Specialty (the IDs of which are stored in the table fields once the record is saved), and I have a text box that I would like to auto-update with the Rate based on the selections in the combo boxes (this will not be stored in the table but simply auto-updated anytime the data in the combo boxes changes.)
So, in my text box, I'm trying the following code --- and of course, I'm getting an error. I'm not sure if the code is wrong or if it's that I'm not capturing the data selected in the combo boxes so the code knows what to use.
"(SpecialtyClassID = " & Forms![frmAssignments]!MPSpecialty)
AND (TerritoryID = DLookup("TerritoryID", "tblMalpracticeLocations",
"LocationID = & Forms![frmAssignments]!MPLocation")))
Does that make sense? Can anyone send me in the right direction with this? Sometimes it seems like writing the question is almost as hard as making the code work. :-/