Using a field value as default for next field

[KenInMA]

Very simple customer database with the usual fields, including First Name, Last Name, and File As.  I'd like the default for the File As field to be Last Name + First Name. 

Thought it would be simple as =[Last Name] & [First Name] in expression builder but get the error, "The database engine does not recognize either the field 'Last Name' in a validation expression, or the default value in the table 'Customer'"

I think the problem is that the default values are assigned when a blank record is first presented for data entry, not dynamically as each field is entered. IOW I think Access wants to set the default value before any real data has been entered, and since I want the default value for File As to be based on what the user enters in the previous fields, Access doesn't know what to do.

Example -

If, when adding a new record, I enter "Josephine" for First Name and "Donuts" for Last Name, I want Access to set the default value for File As to "DonutsJosephine".  I don't want the user to be required to enter this data for every record.

Is there a way?

[#2]

https://learn.microsoft.com/en-us/office/vba/api/Access.TextBox.DefaultValue

[#3]

Thanks@peiyezhu 

Yup.  Default Values are easy, except when they don't work. 

[#4]

@KenInMA 

This appears to be a case where a calculated value would be appropriate.

[#5]

@George_Hepworth 

Yup, but the problem is that I want the user to be able to change File As if desired.

I could just index the table on Last Name, then First Name, and eliminate File As altogether, but then the user doesn't have control of the sort order of the table.

... and in the big picture, giving the user this control has its own issues.  A user might be a bad typist and set File As for George Hepworth to " HepworthGeorge" (note the leading space), sorting you in with spaces where they'd never find you. 

What it really boils down to is an academic question:

Does Access create default values when a new record is presented for data entry, or does it create default values during the data entry process?

I think the answer is the former, and if true, that means it's not possible to generate default values for a field based on data in other fields in the same record that have already been entered.

Here's a more realistic example...

Suppose a handle was assigned to folks in a database, and suppose the usual handle was last name plus  first name plus last two digits of phone number. 

So, first we'd want to build that default value, maybe something like [Last] & [First] & right([Phone],2)

then check for duplicates. 

... but it appears that using data that's already been entered for a record (in this case Last, First, and Phone) isn't available to build a default value for Handle on the fly.

Not trying to beat a dead horse here.  Just trying make data entry a little easier for users.

[#6]

@KenInMA 

Apologies for the confusion. If you want the `FileAs` field to be filled with the values from the previous record's `FirstName` and `LastName` fields, you can use the `Form_Current` event. Here's an example of how you can achieve this:

1. In the form's module, add the following code:

```vba
Private Sub Form_Current()
Dim rs As Recordset
If not Me.NewRecord Then ' Code to handle editing a new record

'MsgBox "Editing a new record"

exit sub

End If
Set rs = Me.RecordsetClone
rs.movelast


Me.FileAs.Value = rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value


rs.Close
Set rs = Nothing
End Sub
```

[#7 · Accepted solution]

Hi,

1. In a table you cannot reference a field name in an expression for a default value. That's why you get the error message.

2. A default value has to be there before or at the latest in the moment when a record is created. That's the point of a default value not only in Access.

3. If I understand your intention correctly then you should use a form for data entry where you can use VBA code to populate the value (not default value) property of a field in the same record to set a standard value or filling aid for the users.

To do this you can use sth like this e.g. in the AfterUpdate event of the Last Name and the First Name control:

Me!FileAs = Me![Last Name] & Me![First Name]

Servus
Karl
****************
Access DevCon - online conference April 18+19
Access Forever
Access News
Access-Entwickler-Konferenz AEK

[#8]

Thanks Karl!