Update Textbox when other textboxes are updated.

[Lori]

I have 5 text boxes with the data type of Date/Time. Text1, Text2, Text3,
Text4 have either nulls or date values. I want to use VB Code to update
Text5 with the most current date found in Text1 through Text4 after any of
Text1 through Text4 is updated. I’m very rusty with my VB code and I need
help.

 

[Allen Browne]

Grab the MaxOfList() function from here, and paste it into a standard
module:
http://allenbrowne.com/func-09.html

You can then set the AfterUpdate event procedure of each text box to:
Me.Text5 = MaxOfList([Text1], [Text2], [Text3], [Text4])

Having said that, I'm not sure that it's a good idea having Text5 in your
table. Unless there is some reason why Text5 might not be the same as the
latest date, it would be better to create a query where you use the
MaxOfList() function to give you the latest date, so it can never go wrong.
More info in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

Ultimately, it might be better if these 4 fields were 4 records in a related
table, so one record (whatever it is here) can have multiple dates
associated with it.

 

[Lori]

Thank you so much Allen, it works wonderfully.

Yes, I know that the Text5 is redundant and should not be a field in the
table, nor should any of the other date fields be there but should be records
in a related table. However I inherited this db from another developer who
is expremely reluctant to make such a change at this time so I'm trying to
work with the current structures of the tables. In time I hope to convince
him to make the change you suggest.

Again, thank you!

Grab the MaxOfList() function from here, and paste it into a standard
module:
http://allenbrowne.com/func-09.html

You can then set the AfterUpdate event procedure of each text box to:
Me.Text5 = MaxOfList([Text1], [Text2], [Text3], [Text4])

Having said that, I'm not sure that it's a good idea having Text5 in your
table. Unless there is some reason why Text5 might not be the same as the
latest date, it would be better to create a query where you use the
MaxOfList() function to give you the latest date, so it can never go wrong.
More info in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

Ultimately, it might be better if these 4 fields were 4 records in a related
table, so one record (whatever it is here) can have multiple dates
associated with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 5 text boxes with the data type of Date/Time. Text1, Text2, Text3,
Text4 have either nulls or date values. I want to use VB Code to update
Text5 with the most current date found in Text1 through Text4 after any of
Text1 through Text4 is updated. I’m very rusty with my VB code and I need
help.