Open a blank query in SQL view. Type in:
Code:
SELECT Team.TeamID, Team.TeamName, Consultants.ConsultantID
FROM Team INNER JOIN Consultants ON Team.TeamID = Consultants.TeamID
Save this query as qryVwConsultantTeams (Note: I have a habit of starting all my query names with "qry", following them with the type, "Vw" (meaning View) for SELECT queries, followed by a description. You'll notice that many follow this form)
Go back to your DLookup and replace:
=DLookUp("[TeamID]","Consultants","[ConsultantID]=" & [Forms]![Adjustments]![ConsultantID])
With:
=DLookUp("[TeamName]","qryVwConsultantTeams","[ConsultantID]=" & [Forms]![Adjustments]![ConsultantID])