I have a form called ProductionReportLandscape that has a table called ProductMasterData set as its record source.
There is text box control called CartonDateFormat (which gets its value from the ProductMasterData table) as well as an unbound text box control on ProductionReportLandscape.
Also in the database is a second table called DateCodes. Every record in the DateCodes table has a field called OutputDateCode. CartonDateFormat is a numeric value and can be any number 1-36.
I am trying to get the unbound text box control to return the value from OutputDateCode that corresponds to CartonDateFormat for each record.
For example, if CartonDateFormat is 13, then I would like the text box control to display the OutputDateCode from record 13 of the DateCodes table. If CartonDateCode is 14, then I want the text box to display OutputDateCode from record 14, etc for every number 1-36.
I have built a DLookup expression the control source property for the unbound text box, but it always returns the OutputDateCode from the first record regardless of what record is selected.
I cannot figure out how to change the expression in order to achieve the desired result. Any ideas?
=DLookUp("OutputDateCode","DateCodes","[CartonDateFormat] =" & [CartonDateFormat])
You reference names CartonDateFormat and CartonDateCode. You indicate CartonDateFormat is a textbox but not what CartonDateCode is.
Unless DateCodes has field CartonDateFormat with carton codes, DLookup cannot find match.
The DLookup needs to use name of field in DateCodes table that has carton codes. If that is CartonDateCode, then change DLookup to:
=DLookUp("OutputDateCode","DateCodes","[CartonDateCode] =" & [CartonDateFormat])