automatic value for a column

[Asi]

Hi

I have a table and a form that helps me enter data into this table (data entry form). In this table I have 3 columns (col1,col2,col3) and I want to concatenate their vales into the 4th column (col4). Basically If the values are:
col1 col2 col3
Q 1 2
I want col4 to have Q-1-2

The questions are:


Do I need to do it on the table level or the form level. I tried using the "Default Value" property of the col4 column but couldn't handle the expression builder to achieve this. i would prefer the table level.
If I use code, on which event to relate it to ?
Do I need to create a button on the form that says "Save" and pressing the button will assign this concatenation into col4 ?Thanks a lot
Asi

[Imdabaum]

Are all three columns required for the fourth one? Or if you omit a value for col3, would you just want the concatenation of col1&col2?

If col3 is required then simply use the AfterUpdate event for the col3 value.
If none of the columns are required, you would want an afterupdate event for each of them so that you could append as you entered data.

Sub col3_AfterUpdate()
col4 = col1 & "-" &col2 & "-" & col3
End Sub

I'm not sure you would be able to have it handled at the table level unless you ran an UpdateQuery in which case you still are going outside the table to populate and might as well use the afterupdate event.

[Asi]

Thanks for your reply.

1 - Just to make sure I understand about the AfterUpdate event. Do you mean the AfterUpdate event of the controls in the form that are bound to col1, col2 col3 ?

2 - I am still thinking about having the form or not. I think I prefer entering data directly into the table. Is it possible to use the "Default Value", "validation Rule" or some other property of col4 in the table for that purpose ?

Thanks
Asi

[hansup]

In this table I have 3 columns (col1,col2,col3) and I want to concatenate their vales into the 4th column (col4). Basically If the values are:
col1 col2 col3
Q 1 2
I want col4 to have Q-1-2
Why do you want your table to include col4?

Based on your description, col4 is a value derived from col1, col2, and col3. In that case, you can use a query to derive the value for col4 whenever you need it.

SELECT col1, col2, col3, col1 & "-" & col2 & "-" & col3 AS col4
FROM YourTable;
You can avoid the issues about how to keep col4 updated when values change in any of the other three columns by not storing a col4 value in the first place.

[Imdabaum]

Well it depends on your process. If you want col4 to concatenate after a value is entered into col3, then just the AfterUpdate for the col3 control.
If you want it concatenated regardless of null values, then you would want to use the AfterUpdate on all column controls. Mind you, if you are taking the latter route, it might be an idea to just write one sub or function that the afterupdate events call. That way you don't have to repeat the same code.

ConcatVal() is a lot easier to write 3 times than

_AfterUpdate()
col4= col1 & "-" & col2 & "-" & col3
End Sub

As for your question about the default value of a field.

I tested that out yesterday and it doesn't appear to work as a default value.

Ideally if anyone else is going to use your application you want to hide the tables and provide a presentation layer. If this is just for your personal use, then I suppose you get to make the final decision.

[hansup]

You don't need a col4 field in the table in order to display its derived value on a form.

The Control Source for a col4 text box can be a field expression which concatenates the values from text boxes bound to col1, col2, and col3.

=[txtCol1] & "-" & [txtCol2] & "-" & [txtCol3]

If you use that approach, you don't need to add any code to manage updates of the col4 text box. Access will update txtCol4 for you automatically whenever a value in any of the other three text boxes changes.

See asi.mdb in the attached Zip file for a simple working example.

[Imdabaum]

You don't need a col4 field in the table in order to display its derived value on a form.

The Control Source for a col4 text box can be a field expression which concatenates the values from text boxes bound to col1, col2, and col3.

=[txtCol1] & "-" & [txtCol2] & "-" & [txtCol3]

If you use that approach, you don't need to add any code to manage updates of the col4 text box. Access will update txtCol4 for you automatically whenever a value in any of the other three text boxes changes.

See asi.mdb in the attached Zip file for a simple working example.

Always digging at the problems below the surface. Good call.

[Asi]

Thanks a lot to you both. I think that's what I'll do...
I thought about col4 as a one column PK but I can change it to 3 columns PK (col1, col2, col3) which is the natural key.
I need col4 but I will select it whenever I need it.

Thanks Asi