validation msgbox

[eugz]

Hi All.
I would like to create validation if user forgot enter data in a form. How to create message if user forgot to enter data on some of the field(s) and try to go to other record by click Tab button or use navigation button. In that case will pop up message with a list of empty field(s) and highlighted box(es) of those field(s), for instance, by Red color. When user click OK button on message box he(she) will have ability to correct according record.
And other question. I combine first letter of two text fields of table A. I used TextBox. How to keep result in a other field of the same table?
Thanks.

 

[Moniker]

For your first question, look at the "Validation Rule" property, followed by the "Required" property and the "Allow Zero Length" property, all in the table design window.

For your second question, create a third field in your table and set its Control Source to be:

Field3.ControlSource = Left(Field1, 1) & Left(Field2,1)

~Moniker

 

[eugz]

Hi Moniker.
I solved first problem.
When I use Field3.ControlSource = Left(Field1, 1) & Left(Field2,1). In a form looks fine. But cannot see value of Field3 in a table.
My other question. How to cut last character from Field4?

 

[Moniker]

To remove the last character from a field, do this:

Field4 = left(Field4,len(Field4)-1)

~Moniker

 

[eugz]

Hi Moniker.
Thanks for help.
Can you show me how to save values of Field3 and Field4 in the table A? They look OK on the form but table fields are empty.
Thanks

 

[boblarson]

When you use
Field3.ControlSource = Left(Field1, 1) & Left(Field2,1)
You are setting it to the formula and not binding it to a field in a table. That's why it isn't writing to the table - there's no bound field.

 

[eugz]

But those fields we took from a table. Do we have ability to bind their and writing to the table? How to do bound fields in our case? Is it posible?
Thanks.

 

[Bat17]

you cant bind a calculated field, and it is best not to store it, as you can always calculate it on the fly when needed. have a search for normalisation to find out more about how to store data.

HTH

Peter

 

[Moniker]

Correct. Calculated fields shouldn't ever be stored. You would want to bind the fields where you want pieces of them (field1 and field2, for example), but you would want to calculate the concatenation on the fly (as in field3 = left(field1,1) & left(field2,1)). That way, when field1 or field2 changes, field3 is still accurate.

This is a big issue with normalization. Do not store a calculated value, as in the sum of a row of expenses, the number of employees in a department, etc. Any time those expenses or departments change, the calculated data is wrong if it's stored, and normalization is broken.

~Moniker

 

[boblarson]

Moniker wrote:
Calculated fields shouldn't ever be stored.

Actually there is one instance when a calculated field should be stored. If it is a calculation based on a value that may change over time and you need to maintain history of what the value was at a particular time.

Now, that being said, there are ways to not store that value, but it requires a lot more work to implement than may be worth (example - keeping a table of effective dates) depending on the resources available.