Inserting Manipulated Text from Form into Table Field

[meeskelg]

I have four fields in a table:
First Name
Last Name
Nickname
Complete Name

I want to be able to enter in the First, Last & Nickname, but want the Complete Name to fill in automatically with "Last,First" if Nickname is blank, else "Last,Nickname"

How do I do this automatically?

I tried two areas:
1) Finding a way in the table design for Complete Name to default to this. Unsuccessful.
2) When this is information is being typed in on a form, I have a formula in the Control Source of Complete Name which displays the results I want. Now how do I get that result to "write" to the table.[Complete Name]?

Thanks,

Levi

 

[charityg]

Normal database design dictates that you not store a calculated field in a table. Any time you need to display this information (like for a report) you would use a query. BUT

if you really want to, you can put this code in the afterupdate events of the lastname,firstname, and nickname fields.

Me![Complete Name]=Iif(isnull(nickname),lastname & ", " & firstname, lastname & ", " & nickname)

Make sure you set the controlsource form the Complete Name txtbox control to the Complete Name field from the table.

Good luck!

 

[shacket]

The reason you are finding this so difficult is that you are duplicating information in a table, which is poor database design. Simply display the complete name on a report or form with:

=IIf(Nz([NickName]) = "",[Last Name]& ", " & [First Name],[Last Name]& ", " & [Nickname])

You do not need to store this information (the Complete Name) in the table.

 

[meeskelg]

I do need to have the Complete Name in the table because that field will be used as a drop down list of people. The other fields need to remain for the purpose of letters, etc. when addressing to First Name.

 

[pcs]

base your dropdown list on a query that has an expression that assembles the complete name.

al

 

[Steven Deetz]

Better than using a query is to use SQL in your drop down list box. I have done this in applications with a middle intial. Some people use their middle initials and some do not. The beauty of this is that when a name changes or is added to the table with people's names, the list box updates. Instead of adding names directly to a table you can create a form and have it based on an SQL statement or a saved query that lets you see the complete name being created while you type in the actual name parts.

SELECT [Last Name] & ", " & IIf(IsNull([Nickname]),[First Name],[Nickname]) AS [Complete Name]
FROM Table1;