Combining fields in a form to create another field.

[Lkwdmntr]

Hello,

I need to populate my field "FullName" with Title + FirstName + MiddleInitial + LastName. The tricky part is that I am not making the Title or MiddleInitial a mandatory field. So, I need to run it through a few IF statements before populating the field. This is what I did so far and I got a "Run-time error 424 Object required". I thought the code was pretty straight forward, but it's not working. I made the event when the user enters the field following the LastName, which is the Address1 field.

Private Sub Address1_GotFocus()

If Me.Title.Value Is Null Then
If Me.MiddleInitial.Value Is Null Then
Me.FullName.Value = Me.FirstName.Value + " " + Me.LastName.Value
Else
Me.FullName.Value = Me.FirstName.Value + " " + Me.MiddleInitial.Value + " " + Me.LastName.Value
End If
Else
If Me.MiddleInitial.Value Is Null Then
Me.FullName.Value = Me.Title.Value + " " + Me.FirstName.Value + " " + Me.LastName.Value
Else
Me.FullName.Value = Me.Title.Value + " " + Me.FirstName.Value + " " + Me.MiddleInitial.Value + " " + Me.LastName.Value
End If
End If

End Sub​

 

[theDBguy]

Hi. If you're trying to "store" the combined data into a table field, may I suggest don't do it. There's no need since you can always just combine all the data together whenever you need the full combined data. If you store the combination and then later change one of the components (like add a missing Title), then you'll have to make sure you update the stored combined data, which would be just extra work. But if you don't update it, then the combined data is wrong.

 

[pbaldy]

You need to use the IsNull() function rather than Is Null, which is SQL.

 

[JHB]

You don't need the .Value it is default. Instead of + use &

 

[isladogs]

Agree with previous answers.
I would also recommend you just concatenate the fields as needed. Its simpler and avoids any risk of combined data being not kept up to date.
Instead use
[title] & " " & [firstname] & " " & [middleinitial] & " " & [LastName]
OR remove any leading/trailing spaces using
Trim([title] & " " & [firstname] & " " & [middleinitial] & " " & [LastName])
OR use the Nz function to handle nulls

 

[Lkwdmntr]

Thanks guys,

I do understand why I shouldn't do this, but the way I have things set up, I feel it will be best to stick with my original plan and create the combination when adding my user. I used the "IsNull and &" and everything worked great. Thanks again.

 

[theDBguy]

Thanks guys,

I do understand why I shouldn't do this, but the way I have things set up, I feel it will be best to stick with my original plan and create the combination when adding my user. I used the "IsNull and &" and everything worked great. Thanks again.

Hi. Glad to hear you got it sorted out. Good luck with your project.