Macro to update table from textbox

[SueM]

Hi

I have a form on which 2 textboxes give me the date
(textbox1)and the day of the week (textbox2)dependent on
the date in the first textbox.

So far textbox2 can only give me the day of the week if
textbox1 is it's control source in the properties.

This means that I can't link the table behind the form to
the textbox and the table field for the day of the week
ends up with no values in it.

Is their either an after update event for textbox1 which
would insert the day of the week value into textbox2 for
me or an after update event for textbox2 which would
insert the value from the textbox into the field in the
table?

Cheers

Sue

 

[Ken Snell]

Yes, but why do you want to store the "day of the week" if you're already
storing the date? You can always "calculate" the day of the week from the
date; there is no need to store redundant data.

To put a value in a second textbox based on the value entered in a first
textbox, delete the control source expression from the second textbox, and
use code similar to this on the first textbox's AfterUpdate event:

Private Sub textbox1_AfterUpdate()
Me.textbox2.Value = Format(Me.textbox1.Value, "dddd")
End Sub

 

[Ken Snell]

And, if you want to use a macro instead of VBA code:

Action: SetValue
Control: Forms!FormName!textbox2
Expression: Format(Forms!FormName!textbox1, "dddd")

Also note that you'd need to bind textbox2 to the desired field in your
form's recordsource.

 

[SueM]

Thanks Ken,

I've used the after update version successfully.

Just needed this for a food product ordering sales
database.

I want to measure whether some items sell better on
certain days of the week ie welfare benefit pay days

Thanks again

Sue

 

[Ken Snell]

Just to reiterate my earlier point....you can always calculate the day of
the week in a calculated field in a query and then sort on it and filter on
it and use it for reports and statistics. No need to store it in the table.

Good luck.