DLookup function in form

[jopi]

DLookup function in form (see attached file)

[June7]

Why do you need to lookup Prefixo_PS? Already have Prefixo in the [Espécie] combobox.


DLookup in queries and forms/reports can be slow performers. Try this instead:

Espécie combobox properties:

RowSource: SELECT Espécie.ID_Esp, Espécie.Espécie, Espécie.Prefixo, Espécie.Sufixo, Esp_Pref_Suf.Espécie_PS
FROM Espécie LEFT JOIN Esp_Pref_Suf ON Espécie.Prefixo = Esp_Pref_Suf.Prefixo_PS;

ColumnCount: 5

ColumnWidths: 0";6.3";0";0";0"


MOE_final expression: =[Espécie].[Column](2) & [MOE] & [Espécie].[Column](3)


Saving calculated data is usually a bad idea. There is no reason to save the concatenated value. Calculate it whenever needed. Saving it to field will require code.

[jopi]

Thanks for the reply. However I think it is only partially working (please see attached file). What am I doing wrong..?

[June7]

Sorry, I should have taken closer look at those two tables. What is each of them for? Why do they have the same data? Don't need query as RowSource after all. Can use table reference as you had but show ColumnCount as 4 and ColumnWidths as appropriate.

Then the Prefix and Suffix should both be available for the concatenation expression. Except you are not saving the Prefix and Suffix text in Espécie table, you are saving their ID values. This was not apparent to me before because you have lookups set in tables and I saw text not ID. Setting Lookups in tables is ill-advised, especially if the lookup has alias. Review: http://access.mvps.org/access/lookupfields.htm

I suggest you eliminate the ID fields from Prefixo and Sufixo tables and save the actual text values into Espécie table.

[jopi]

Good idea..Problem solved. Thanks a lot.....!

[jopi]

Sorry, I should have taken closer look at those two tables. What is each of them for? Why do they have the same data? Don't need query as RowSource after all. Can use table reference as you had but show ColumnCount as 4 and ColumnWidths as appropriate.

Then the Prefix and Suffix should both be available for the concatenation expression. Except you are not saving the Prefix and Suffix text in Espécie table, you are saving their ID values. This was not apparent to me before because you have lookups set in tables and I saw text not ID. Setting Lookups in tables is ill-advised, especially if the lookup has alias. Review: http://access.mvps.org/access/lookupfields.htm

I suggest you eliminate the ID fields from Prefixo and Sufixo tables and save the actual text values into Espécie table.

The form field is not saving "concatenated" values. How can I do a query based on these values, afterwards (please see attatched file)? Thank you.

[June7]

Saving calculated data requires code and should be avoided whenever possible. Calculate the concatenated value when needed.

But if you must save, the real trick is figuring out what event to put the code in. Try the BeforeUpdate event of the subform.

Rename the MOE_final textbox different from the field name.

Me!MOE_final = Me.tbxMOE_final

or

Me!MOE_final = [Espécie].[Column](2) & [MOE] & [Espécie].[Column](3)

[jopi]

Saving calculated data requires code and should be avoided whenever possible. Calculate the concatenated value when needed.

But if you must save, the real trick is figuring out what event to put the code in. Try the BeforeUpdate event of the subform.

Rename the MOE_final textbox different from the field name.

Me!MOE_final = Me.tbxMOE_final

or

Me!MOE_final = [Espécie].[Column](2) & [MOE] & [Espécie].[Column](3)

If I choose to always calculate (and not to save to a table), in this particular case, what should I write in the query field to get something similar to what I got in the form with : “= [Species]. [Column] (2) & [MOE] & [Species]. [Column] (3)” '? Many thanks for the help.

[June7]

By joining tables in query.

Must change Espécie field to Number type (Long Integer).

SELECT Esp_Pref_Suf.Espécie_PS, [Prefixo_PS] & [MOE] & [Sufixo_PS] AS MOEFinal
FROM RelEspApt LEFT JOIN Esp_Pref_Suf ON RelEspApt.Espécie = Esp_Pref_Suf.ID_PS;