Update TextField

[SBBmaster09]

I have 4 text fields, Last name, first name, middle initial and full name. I need to update full name text field every time last name, first name and middle initial is being populated.

When first name is populated , update appears in fullname then when last name is populated it will be populated with fullname and lastname then same with middle initial.

 

[Minty]

Assuming you have Access 2010 onwards make a calculated field , there is no need to store full name.
FullName = [First Name] & " " & [Middle Initial] & " " [Last Name]

I would personally remove all the spaces from your field names - they make writing code and queries very difficult.

 

[SBBmaster09]

I just need it to show how should it look like when in completed base. That's why I need to reflect it everytime I change one of the fields.

 

[Minty]

That's exactly what a calculated field will do. No need for code or anything else.
It is updated dynamically / automatically when the underlying data is changed.

 

[SBBmaster09]

Where should I put that function, is it after update, on change or anywhere else?

 

[Eljefegeneo]

Just in case there is no Middle Initial, I would suggest using:

 =[FirstName] & (" "+[MiddleInitial]) & (" "+[LastName])

That way there will not be an extra space between the first name and the LastName. Just makes it look nicer.

 

[Minty]

Where should I put that function, is it after update, on change or anywhere else?

In your table go to design view, change the Field type of FullName to Calculated, then use the code provided by Eljefegeneo to avoid weird spacing appearing.

 

[SBBmaster09]

There is no Field Type to Calculated Field.

 

[Minty]

What version of access are you using?

Even if you can't use 2010 and a calculated field, it makes little sense to store the full name if you are already storing all the other parts. Simply create a function that returns it from the unique employee ID or build it on the forms, in exactly the same way

 

[SBBmaster09]

Using 2013. How will I put it?

 

[Eljefegeneo]

All I can add is that when I was developing the DB for work, everyone insisted that I have what you call the "Full Name" field. Took some convincing, but they finally realized that if they wanted the "Full Name" to use in reports, queries, exports, etc. all they had to do was to use the concatenation formula.

In my case it was:

=([Title]) & (" "+[FirstName]) & (" "+[MiddleName]) & (" "+[Surname]) & (" "+[NameSuffix])

You don't want or need the "Full Name" field in the table. Just my two cents worth of advice, which is probably not worth even that.

When I use it on a form, that is, an unbound "Full Name" field, I have a hidden label that appears when the field has focus (and disappears when it doesn't) that says "You cannot edit this field, for reference only". After a few months I was able to delete this little notice.

 

[Minty]

In the table design this picture is from 2010 it can't be that different ;

 

[The_Doc_Man]

The ultimate way to avoid this is never use a calculated field in a table. ALWAYS use a query that contains what you want, after which you can pick it up from the query. Forms, reports, VBA recordset code, and even other queries simply want a recordset as input. Tables can provide this, but so can SELECT queries including JOIN and UNION variants thereof.

It might not be the right time for your retrofit, but consider this... if you actually store a concatenated name in a table that already has the parts, you double the size of the table. If you have a calculated field in the table, you slow down table functions that do not need to use the FullName field. If you have a SELECT query that contains FullName as a computation - and you use a different query when that is not required - then you save space AND save speed. For tables with short records and few records, the difference is negligible. For tables with a lot of records, it mounts up.

I actually recommend the idea of using a function that trims & concatenates the name fields, then trims the results one more time to remove embedded multiple blanks. Although with the U.S. Navy, they decided that the correct approach was that if the middle name was blank, they wanted to see "<nmn>" in that position. Of course, with the Navy, you have that wonderful case where documents that have blank pages have to have disclaimers at the top of those pages that say "This Page Intentionally Left Blank" - which of course it isn't any more if you add the disclaimer.... but I digress.

Functions are usable in queries run through DoCmd with no special requirements, and you can even use "EVAL(function(...arguments.))" if you use the DAO.Execute method. So perhaps as a future design goal you should remember to always avoid trying to store something in a table that could be computed in a query on-the-fly. That would certainly meet your requirement that the FullName has to change if any name part changes.

 

[Minty]

FWIW I completely agree with The Doc, but just for simplicity as a starting point the calculated field seemed a reasonable suggestion. As a beginner I think a function may be difficult to get his/her head round.

 

[SBBmaster09]

Hi, I thank you all for the suggestions, but I really need this requirement on how to show it visibly in that disabled field.

Every time the fields are updated, the fullname is also updated.

 

[missinglinq]

Every time the fields are updated, the fullname is also updated.

On the Form, simply use your expression in the Control Source of the FullName unbound Control:

=[First Name] & " " & [Middle Initial] & " " & [Last Name]

or one of the alternative expressions given.

Linq ;0)>

 

[SBBmaster09]

Hi Linq,

Thank you.

But just need another thing coz I need to view it as:
= [txtLastName] & ", " & [txtFirstName] & " " & [txtMiddleInitial] & "."

LastName, First Name M.I.


So since last name and MI is still blank it will display:
, FirstName .

is it possible to update as without punctuations.

 

[SBBmaster09]

HELP!!! I really need it badly!

 

[Minty]

You have been shown how to format this in previous posts;

=([Title]) & (" "+[FirstName]) & (" "+[MiddleName]) & (" "+[Surname]) & (" "+[NameSuffix])

Just move the fields around and spaces as you require.

 

[SBBmaster09]

But just need another thing coz I need to view it as:
= [txtLastName] & ", " & [txtFirstName] & " " & [txtMiddleInitial] & "."

LastName, First Name M.I.


So since last name and MI is still blank it will display:
, FirstName .

it should be
FirstName only without any punctuation,

it will looks like,
while i am typing on the fields, the fullname field will display the same