Inserting Manipulated Text from Form into Table Field

  • Thread starter Thread starter meeskelg
  • Start date Start date
M

meeskelg

Guest
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
 
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!
 
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.
 
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.
 
base your dropdown list on a query that has an expression that assembles the complete name.

al
 
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;
 

Users who are viewing this thread

Back
Top Bottom