How connect two fields in form

[Ardoos]

Hi!



I tried connect two fields (Name + Surname) from 'Coach' table in form. I created textbox and in Source formant I enter code

=[Coach.Name]+" "+[Coach.Surname]

But it doesn't work, in preview i see only "#Name?". I created query and enter this code and in query everything is fine! Why it's wrong in form?

[JoeM]

Is the "Coach" table the Control Source (Data Source) of this Form?
If it is, if you click on the "Show All Fields" box, it should list those fields as being available. If you do not see them there, then it probably won't work.

Also, I think the more proper syntax of your desired expression should be:

=[Coach].[Name] & " " & [Coach].[Surname]

[Ardoos]

Unfortunately Data Source of Form is another table. I can add fields "Name" and "Surname" separately to form, but still can't connect them. Your syntax also don't work :/

[JoeM]

Why would you be adding fields from a totally different table to this form?
Is there a relationship between that table and the one the form is based on?
If so, link those two tables in a query, and use the query as the Data Source of the form.
You can also then do your calculation in the query, and it will be available to you on the from (I prefer to do most of my calculations at the query level, whenever possible).

[Ardoos]

It's form about Club, but I want to enter Coach of this club which is in another table. So I must link this tables in query?

[Missinglinq]

You can use syntax like

[Coach].[Name]

in a Query, as you've found out, but not in VBA behind a Form nor in a Control's Control Source. The usual way to pull data from a Table that is not part of a Form's Record Source is to use the DLookup() function. Have a look in Help for the particulars for the function.

Linq ;0)>

[Ardoos]

Thanks for help. I think, I'll use query to solve this problem. At present DLookup() is 'slightly' hard for me ;P

[Missinglinq]

Just be aware that Forms based on Multi-Table Queries are frequently Read-Only! Allen Browne has an excellent article that explains why:

Why is my query read-only?

Linq ;0)>

[Ardoos]

Thanks for information. Happily this form is only for read, not enter new information. One problem less

[Missinglinq]

Then you should have no problem!

Good luck with your project!

Linq ;0)>

[Ardoos]

Thanks for help!

[JoeM]

It's form about Club, but I want to enter Coach of this club which is in another table. So I must link this tables in query?

That is how I would probably go around it. Not sure why you are reluctant to do that.
DLOOKUP will work in most cases, but note that there may be issues with using it (see the first four reasons here: http://allenbrowne.com/ser-42.html).