control source for text box

[newbieone]

I have a form based on one table and need to have a text box based of a different table. I have tried =[Forms]![Personal Data]![FName] & " " & [Forms]![Personal Data]![MI] & " " & [Forms]![Personal Data]![LName] as the control source but it's not working I get the #Name?. how can I get this text box to look at another text box for information so the user doesn't have to select a name twice.

 

[MajP]

You need to describe this better. What is your form name, what are the two tables, what relevant values are in each table? Which table is bound?
I can not tell if you have two forms open, if the name information is contained in the bound recordset, or if all the text boxes are on the same form.

 

[newbieone]

Two tables are tblDeployment and Personal data
Form is FrmDeployment with the control source of tblDeployment. Have a main form, when clicking on deployment tab goto FrmDeployment. Two text boxes on frmDeployment are Employee Name and SSN. Need both text boxes to show Full employee name from Personal data table on the FrmDeployment. Employee name is a cancatenation of FName, MI and LName all based off of Personal Data Table.

Sorry about confusion

 

[MajP]

How are these tables related? You will probably want to use this format in other forms and reports, so I find it much easier to build a query from your Personal Data table and reuse a calculated field. Lets call it qryPersonalData. Include in this query the information that you need and this calculated field.

strFullName: [datTblPersonnel]![strFirstName] & " "+[datTblPersonnel]![strMiddleInitial] & " " & [datTblPersonnel]![strLastName]

The plus signs will fix the case where you have no middle name. Now you can use the field "strFullName" like any other field. Just use qryPersonalData in place of the table Personal Data for your record source.

 

[MajP]

Oh yeah, just replace your table and field names with mine.

 

[newbieone]

please explain what you mean by calculated field.

and just so I get this right, you want me to put this code behind my textbox so that when the textbox is activated this code comes into play right?

 

[MajP]

In a query you can make calculated fields. If you make a query from your table Personal Data, select the fields you need from your table, but make a new field. To make a calculated field in the query builder (read the help file), first give it a label followed by a colon.
example: FullName:
Next write your function
FullName:[Personal Data]![FName] & " " + [Personal Data]![MI] & " " & [Personal Data]![LName]

I still not sure how these tables are related,and how they work on your form. But if you can get the correct LName to show up on the form in the Name text boxes then you can get "FullName" to show up.

 

[MajP]

Once you make a calculted field in a query, and your form is bound to a query with a calculated field. You use the calculated field like any field. The control source would just be "FullName" for any text box that needs the full name.