Populate a field on a form from another field & update table

[beacon]

Hi everybody,

I'm putting a database together at work for a committee, but one of their requirements is giving me trouble.

They want a table and a form that will track trips made by patients at a hospital. Two of the fields on the table, TripDate and DateValidation, also need to be on a form. I've got that part taken care of.

On the form, when the TripDate is entered, they want the DateValidation to be populated from the TripDate with the day of the week. For instance, if TripDate is 11/21/2008 they want Friday to appear in the DateValidation textbox and on the underlying table. Then they want me to disable the textbox so they can see what the day of the week is, but not allow users to change it.

Initially, I created the two textboxes and put

=Format([TripDate],"dddd")

in the control source property of the DateValidation textbox, but it isn't placing the value on the table.

I also tried using Before & After Update events for the TripDate and DateValidation textboxes (separately) with the following code:

dim myDate, weekDay
 
myDate = TripDate.Value
 
weekDay = Format([myDate], "dddd")
 
DateValidation.Value = weekDay
 

I used the code above, or some small variation of it, but to no avail.

Does anyone have any ideas how I can implement this?

Thanks...
~ beacon

[RuralGuy]

Since the DateValidation field seems to always be derived from another field in the record, there is absolutely *no* reason to waste disk space with the field. Simply calculate it in the query of the table and lock the control that displays it on the form.

[beacon]

I took your advice, but I may be missing something.

I moved my =format expression to the query that was used to create the form, but that still isn't populating the textbox on the form for DateValidation.

This may show my lack of knowledge with Access, but does a textbox have to have a control source to a field on a table/query for it to show up on the table/query?

Can I have a field on a form that calculates the day based on the TripDate field and then set the value of another field that has a control source on the table/query to the field that calculated the day? Perhaps using hidden textboxes?

[beacon]

How do I calculate it in the query of the table?

[RuralGuy]

Format([TripDate],"dddd") As TripDay in the query
and then bind TripDay to your TextBox control

[beacon]

Ok...I understand now. Thanks for your patience!