How to have a field autofill with values from previous fields.

[Famfeld]

I am new to this forum and not very experienced with Access, so please be patient with me. I would like to have 4 fields populate the 5th one. Here is what I mean:



Field 1: Name (text)
Field 2: City (text)
Field 3: Number 1 to 10 (number)
Field 4: Car color (text)

so these four fields are entered, but a fifth field will then be created automatically and formatted a certain way. I can have the number be the primary key if that is important.

Here is an example:

I enter, Bob
Brooklyn
20
Red into the four fields in one record, then the fifth field autopopulates to Bob_Brooklyn_20_Red


I can think of how to do this with a Python script relatively easily, and I imagine it can be done.


Any help greatly appreciated.

Famfeld

[kd2017]

Does it have to be a field? Can it just be generated at runtime using a query using string concatenation?

SELECT [Name] & "_" & [City] & "_" & [Number] & "_" & [Car color] FROM [MyTable];

*Note: Name and Number are reserved words, don't use them as field names: http://allenbrowne.com/AppIssueBadWord.html

[Bulzie]

Or if you want it on the screen, you can put it in the AfterUpdate event on each of the first 4 fields, that way if they go back and change any values it will update Field5.

Me.Field5 = me.Field1 & "_" & me.Field2 & "_" & me.Field3 & "_" & me.Field4

[Famfeld]

This is what I want to do.

Where do I write this string?

Thank you

[Minty]

I agree with kd2017's post - Generally speaking, what you want for field 5 is the result of a calculation.

As a rule in a database you don't store anything that can be calculated, simply because if anything changes in the underlying data, you have to capture that change somehow and update you stored value.
There are some exceptions, such as maybe invoice line tax values that have to be stored due to legal & auditing considerations, but most of the time in a database it's best avoided.

[Bulzie]

Consider what those guys are suggesting on if you need to store it or not. But if you do want to have it save in Field5, then go to design on form, click properties on each field and go Events and AfterUpdate and put that code(again need to do that for each field). Other way is on the BeforeUpdate event on the form itself, you can add that code and it should update Field5 also when you save the record.

[Famfeld]

I understand that. How can I get it displayed? Using a report? I think I understand this but may just be too inexperienced to make this happen.

Thanks for the help.

I agree with kd2017's post - Generally speaking, what you want for field 5 is the result of a calculation.

As a rule in a database you don't store anything that can be calculated, simply because if anything changes in the underlying data, you have to capture that change somehow and update you stored value.
There are some exceptions, such as maybe invoice line tax values that have to be stored due to legal & auditing considerations, but most of the time in a database it's best avoided.

[June7]

Why not simple expression in query or textbox:

Field1 & "_" & Field2 & "_" & Field3 & "_" & Field4

[Blings]

I understand that. How can I get it displayed? Using a report? I think I understand this but may just be too inexperienced to make this happen.

Thanks for the help.

Give field 5 an alias in your query for that specific report which will concatenate your data i.e.

Field5: [Field1] & "_" & [Field2] & "_" & [Field3] & "_" & [Field4]

See what I use on my form where I use DDCP as an ALIAS which stands for Delivery Detail Contact Person. The [DCP] is the Delivery Contact Person field and the [DCN] is Delivery Contact Number where I concatenate as one field known as DDCP. So on the form you can call it by using an unbound text box by using: =([Field5])

DDCP: "Contact:" & " " & [DCP] & ", " & [DCN]