How to reference in a text box 2 fields from another table?

[Alex E.]

Hallo

I have a Form “Call-Log” where I want to display the Contact name for the current record prominently in the upper right hand corner of the Form. This Form is based on the table Call_Log. The table Call_Log is linked to the table Contacts where the contact names can be found.

I created an unbound text box on the Call_Log Form and typed into it:

(a) =[Last]&”, “&[First]
(b) =[tbl_Contacts].[Last]&”, “&[tbl_Contacts].[First]

Neither (a) nor (b) works. In both cases I get an error: #Name?

What am I doing wrong?

 

[ajetrumpet]

'A' should work. Make sure of these:

1) type the expression in the control source property
2) make sure the form is bound to the call-log table.
3) make sure the field names are correct.

 

[DCrake]

Another and more important issue is the fact that you are using words like First and Last as field names. Have you read up on naming conventions and reserved words?

David

 

[Galaxiom]

Another and more important issue is the fact that you are using words like First and Last as field names. Have you read up on naming conventions and reserved words?

David

While using reserved words as field names can lead to confusion for some, in most cases it does not cause problems for Access provided the names are enclosed in square brackets when refered to in a query or control. It may cause other databases problems but it does not cause Access any trouble.

In this case it is certainly not "a more important issue".

Sometimes the reserved words are just too suited to the task not to be used. Particularly words like "Date" and "Time". I have used "First" and "Last" myself before today without any problems.

The only words I have come across so far that Access really does reject as field names are those referring to forms or sections of forms. I know that Access will not handle "Detail" and I presume the same would be the case for "Header", "Footer" and "Form". There may be others but using function names is not a problem.

 

[DCrake]

I beg to differ on your past experience and simply put that down to good luck and bad naming conventions, however, going back to the first post the correct syntax for the control source of the unbound control would be

[Field1] & ", " & [Field2]

However what the poster neglected to tell us that they applied this to the ControlSource property of the textbox. What they should have done was to place this in the DefaultValue property of the control. When using bound forms the control source property is expecting a single value which in normal circumstances can be updated by the user. The fact that they attempted to combine more than one field made that particular control un-editable. Which would defeat the objectives of the form in general.

By placing it in the default value means that access will display it. However if the textbox was left enabled and unlocked and the user attempted to change the contents of the control this would not be cascaded back to the original fields. Conversely changing the source fields would reflect back to the calculated textbox.


David

Hope that clears the matter up.

 

[Alex E.]

Hallo,
thank you for trying to help me.

(1)Originally, I typed the expression directly into the text box and when I check the property sheet I find that it ended up in the Control Source property.

(2)As suggested, I removed the expression from the Control Source property and entered it into the Default property. The same error message still shows.

(3)Re: different opinions about field names. I did something silly and copied part of the data base into a test1 database file and renamed First => shrubs and Last => trees. The words shrubs and trees should be beyond reproach. The error is still there.

(4)I also made sure that the potential trouble spots Adam has mentioned are not the cause of the error message.

(5)Please note, a few lines up in this forum there is another posting called error “#Name?” One of the responses to that posting provides a link that talks about reference problems. Unfortunately, the link is completely out of my league. But I am left with an uneasy feeling that I too may have some sort of reference problem ???

What really leaves me flabbergasted is that I successfully installed such a text box in the Contacts Form. I did there exactly what will not work with the Call Log Form. The big difference between the two Forms is:

a.The Contacts Form is solely based on the contacts table – just one Form and one Table

b.The Call Log form is based on TWO Tables: Contacts Table and Call Log Table. Both Tables are linked with ContactID as PK/FK I am afraid that when TWO tables are involved something different needs to be done. That’s why I surmise that the sticking point could be indeed a reference problem – although I have no idea what reference problem implies….

 

[Galaxiom]

Your Record Source for the form needs to be the join query not the call_log table.
The field names you are using in the control are not in the call_log table so they won't be found.

If your form must use the table as a Record Source you will have to lookup the first and last names from the Contacts table.

=DLookUp("[Last]", "[tbl_Contacts]", "[tbl_Contacts].[ID]=[ID]") & ", " & DLookUp("[First]", "[tbl_Contacts]", "[tbl_Contacts].[ID]=[ID]")

 

[Alex E.]

Thank you everyone for the advice given.

I created a join query and based the form on the query. Then I typed the expression into the unbound textbox and now it works fine - each time a new record is selected to unbound textbox is updated.

 

[Alex E.]

Thank you everyone for the advice given.

Creating a join query and basing my form on that query did the trick. I entered the expression into the unbound text box. And, the text box is now updated each time a new record is selected.