The join is only created once, while dlookup, a function is evaluated every time you recurse to each record of the resulting query. If can get aeay without using func in query do so.
Doing a JOIN (of any kind) forms a single recordset, which you can then search (using a DLOOKUP).
Using a DLOOKUP on your tables in a loop means you call DLOOKUP once per entry in your main table. But DLOOKUP( field, source, criteria ) does the equivalent of this:
Code:
QryString = "SELECT " & field & " FROM " & source & " WHERE " & criteria & " ;"
SET recordset = CurrentDB.OpenRecordset( qrystring, dbOpenDynaSet )
recordset.MoveFirst
If NOT recordset.EOF Then
answer = recordset!field
Else
answer = Null
End If
Recordset.Close
SET Recordset = Nothing
(And I'm not saying this is exactly what is done, only that it is something LIKE this.)
So if you do this in a loop, you create and destroy as many recordsets as you have records in the main table. That's a lot of gyrations.
If you build the JOIN first, you only need one recordset (based on the JOIN) to find the values from your 2nd table. If you still need to loop through the findings, you do it by opening ONE recordset and stepping through the records of that set, which would stay open until you were done.
Will either work? Yes. Will there be a difference in performance? Oh, HECK yes! Will there be a difference in the results? ... maybe, if the database is shared and someone adds records to the main table or updates the dependent table. Which is why one of the options in opening a recordset is dbSeeChanges, which you can look up to see how it works.