Combining fields on a form

[GeoByte]

Hay Folks,
I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form.
The problem.
I cant get the expression right to show real values instead of key-values.
Here's the sample expression for the control-field:
=[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]

Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]...
SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;

Can anybody help me on track?
Thanks a lot.

 

[KeithG]

Are these fields from the recordsource?

 

[RuralGuy]

Here's an article you might want to read. The Evils of Lookup Fields in Tables

 

[GeoByte]

Hay Keith,
Yes, the source is the query [qryBPSverhardebaansoort]. This query is put on the subform (as a form).
So I'm constructing an expression in an unbound textbox. I want to construct a string from multiple query-fields, not showing the key-values but the lookup-values as with the bound-textboxes.
I Hope you understand.
Thanks

 

[GeoByte]

lookup-values

Are these fields from the recordsource?

Hay Keith,
Yes, the source is the query [qryBPSverhardebaansoort]. This query is put on the subform (as a form).
So I'm constructing an expression in an unbound textbox. I want to construct a string from multiple query-fields, not showing the key-values but the lookup-values as with the bound-textboxes.
I Hope you understand.
Thanks

 

[KeithG]

Is your unbound textbox on the subform or Mainform?

 

[GeoByte]

Is your unbound textbox on the subform or Mainform?

It's on the subform

I think this also applies to reports.

Still struggeling..

 

[KeithG]

Can you post an example db and I can have a look?

 

[GeoByte]

Can you post an example db and I can have a look?

Here is a sample.

In the testform I want to combine the 2 fields in the third, (now showing the key-value.


Many Thanks

 

[KeithG]

is this what you want?

 

[GeoByte]

is this what you want?

This is exactly what I want.

Thanks a lot. Now I can get on with it.
The key is to use a query as the recordsource, which reflect the relations.

Now i'll try it out on my projects form.

Thanks again!