Update text box by command button 1

[seangefferson]

Hello:

I'm totally new to Access and so my question may be so basic it's embarassing but here goes.

I have created a form with 4 fields on it:

fname
mname
lname
display_name

I want the user to enter the customer name in the first 3 fields and then have a command button to fill the display_name field with the first three fields concatenated. But, if the middle name (mname) is blank, I don't want 2 spaces between fname and lname.

Here is my feeble attempt to make the command button do my bidding:

Private Sub cmdDisplayName_Click()

If Mname Is Null Then

    Display_Name.Value = Fname + " " + Lname

ElseIf Mname Is Not Null Then

    Display_Name.Value = Fname + " " + Mname + " " + Lname
    
End If

End Sub

Of course, it doesn't work. I get runtime error 424 Object required. I'm sure there's a huge chunk of code I don't have a clue about but I want to learn. Any help would be greatly appreciated.

seangefferson

 

[dhookom]

99% of the time you don't want to store the display name since it can be calculated any time it is needed.
You should be able to set the control source of a text box to:

=Fname & " " + Mname & " " & Lname

Do you have a good reason for storing the display name?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The function is "isNull(yourcontrolname)
or a better check
if trim(yourcontrolvalue & " ") = ""
also the NZ function can turn a null into a zero or ""

Private Sub cmdDisplayName_Click()
If trim(me.Mname & " ") = "" Then
me.Display_Name = me.Fname & " " & me.Lname
Else
me.Display_Name = Fname & " " & Mname & " " & Lname
End If
End Sub

With a little trick this can be done all in one line
me.display_name = Trim([fName] & " " & [mname]+" " & [lName])

The reason is if mname is null
then Null + " " = null, which turns into no space
null & " " = " " which is a space

In the above if you have
John L Smith = John L Smith
John Smith = John Smith
Smith = Smith

 

[MajP]

My post is basically the same as Ace Man's, we posted about the same time. I agree with what he is saying.

 

[seangefferson]

Wow! Great answers! I especially like the "little trick" of MajP. I tried that and it worked perfectly.

In response to dhookom, the reason I can't use a calculated field is because some of the customers (donors) prefer to be listed as "Anonymous" or even use their company name. Display_name may be a bit misleading, it is really how these customers want their names to appear in printed materials.

Thanks!

 

[MajP]

Sorry Duane, I said Ace Man. I knew it was one of the usual suspects.
Also I believe Duane was suggesting the same "trick".
Also I should have said it clearer
Null + a string = Null
Null & a string = a string