After Update Questions

[Guest]

I have the following code in a form. I have two questions:
1) The bulk of the code is to populate an unbound field with the day of the
week. This piece of code works just find as long as the values are entered
from the keyboard. When the Default Value is set (to the current date), this
code does not produce any results--the day of week field is blank. If I type
over the default with any date, the code works again.

2) The last line is intended to reset the Default Value to the last entered
value to simplify data entry. I have used similar code in other applications
with no problems, but it is not working here. In this case, the default
appears as 12/30/1899 on the next record.

I have the same problem with a similar line of code for another field on the
same form. In that case, the next record does not show the default at all.

Can someone help in explaining why these problems are occurring and suggest
changes?

Thanks.

Private Sub SessionDate_AfterUpdate()
DOWHold = Weekday(Me.SessionDate.Value)
Select Case DOWHold
Case 1
Me.DayOfWeek = "Sunday"
Case 2
Me.DayOfWeek = "Monday"
Case 3
Me.DayOfWeek = "Tuesday"
Case 4
Me.DayOfWeek = "Wednesday"
Case 5
Me.DayOfWeek = "Thursday"
Case 6
Me.DayOfWeek = "Friday"
Case 7
Me.DayOfWeek = "Saturday"
End Select

Me.SessionDate.DefaultValue = Me.SessionDate
End Sub

 

[Steve Schapel]

Martin,

The first problem is caused by the fact that your code runs on the
control's After Update event, and this event only occurs after you
update the data in the control. This event does not fire in the case of
the control's value being set via it's Default Value.

In any case, the general approach here is probably more complex than it
needs to be. You can just remove this code entirely, and set the
Control Source property of the DayOfWeek control like this:
=Format([SessionDate],"dddd")

The second question, relating to setting the default value in code,
depends on whether this is a single view or continuous view form. You
should also note that setting a property in this way will not be
retained when the form is closed, so you will start from scratch the
next session.

 

[Marshall Barton]

I have the following code in a form. I have two questions:
1) The bulk of the code is to populate an unbound field with the day of the
week. This piece of code works just find as long as the values are entered
from the keyboard. When the Default Value is set (to the current date), this
code does not produce any results--the day of week field is blank. If I type
over the default with any date, the code works again.

2) The last line is intended to reset the Default Value to the last entered
value to simplify data entry. I have used similar code in other applications
with no problems, but it is not working here. In this case, the default
appears as 12/30/1899 on the next record.

I have the same problem with a similar line of code for another field on the
same form. In that case, the next record does not show the default at all.

Can someone help in explaining why these problems are occurring and suggest
changes?

Thanks.

Private Sub SessionDate_AfterUpdate()
DOWHold = Weekday(Me.SessionDate.Value)
Select Case DOWHold
Case 1
Me.DayOfWeek = "Sunday"
Case 2
Me.DayOfWeek = "Monday"
Case 3
Me.DayOfWeek = "Tuesday"
Case 4
Me.DayOfWeek = "Wednesday"
Case 5
Me.DayOfWeek = "Thursday"
Case 6
Me.DayOfWeek = "Friday"
Case 7
Me.DayOfWeek = "Saturday"
End Select

Me.SessionDate.DefaultValue = Me.SessionDate
End Sub

Because the day of week code is in the AfterUpdate event, it
won't be executed until you edit the text box's value.

Instead of using all that code, use an expression in the say
of week text box:
=Format(SessionDate, "dddd")

and if you really need it, the expression for the DOWHold
text box would be
=Weekday(Me.SessionDate)
or
=DatePart("w", SessionDate)

 

[Guest]

Because AfterUpdate event is not firing. You can verify by inserting a Msgbox
in your procedure (a standard debugging technique).
Put code in the OnCurrent event instead.

-Dorian

 

[John W. Vinson]

I have the following code in a form. I have two questions:
1) The bulk of the code is to populate an unbound field with the day of the
week. This piece of code works just find as long as the values are entered
from the keyboard. When the Default Value is set (to the current date), this
code does not produce any results--the day of week field is blank. If I type
over the default with any date, the code works again.

The default value is pretty useless for an unbound field. The Default controls
what's stored if you don't enter anything - and an unbound field stores
nothing, so what's the point?

Simply set the Format property to "dddd". No code is needed at all.


John W. Vinson [MVP]

 

[Guest]

Thanks to all for the feedback.

I have changed the unbound field for the day of week to the suggested Format
statement and that is working fine.

In answer to Steve, this is a single view form.

I am still having problems with setting the Default Value on the SessionDate
field. As Dorian suggested, I tried moving the statement to the On Current
event, but the form will not open and produces an "Invalid use of Null"
message.

With the statement in either the After Update or in the Lost Focus event
(but not both), when data entry moves to another record, the default value
appears as 12/30/1899, not the value entered from the previous record of
10/22/2007. All of this is with no Default setting on the control on the
form.

If I use =Date() as the Default Value on the control, all records entered
are fine. When the default is typed over, the same strange date of
12/30/1899 appears.

Can someone help explain this?

Thanks.

 

[John W. Vinson]

If I use =Date() as the Default Value on the control, all records entered
are fine. When the default is typed over, the same strange date of
12/30/1899 appears.

Can someone help explain this?

12/30/1899 is the base of the date/time numbering system, and corresponds to a
value of 0.0 in the date/time field. Might the field somehow be getting set to
zero?

Perhaps you could post the current version of your code. Again... if it's an
unbound control, then there is no point in having a DefaultValue for it AT
ALL; just set its value.

John W. Vinson [MVP]

 

[Guest]

The Default Value is not on the unbound field. I am trying to set a Default
Value on the SessionDate field which is bound. The code that remains is:
Me.SessionDate.DefaultValue = Me.SessionDate

I agree with your idea of the value being set to zero, but how? I added
some MsgBox statements to the AfterUpdate event of the SessionDate field to
display the values before and after the above statement and they appear fine.
But the next record still displays the 12/30/1899.

I put the same MsgBox statements in the Form_AfterUpdate event and the
values appear fine. But by the time it is displayed the 12/30/1899 shows up.
(The format of SessionDate is ShortDate.)

Thanks.

 

[John W. Vinson]

The Default Value is not on the unbound field. I am trying to set a Default
Value on the SessionDate field which is bound. The code that remains is:
Me.SessionDate.DefaultValue = Me.SessionDate

I agree with your idea of the value being set to zero, but how? I added
some MsgBox statements to the AfterUpdate event of the SessionDate field to
display the values before and after the above statement and they appear fine.
But the next record still displays the 12/30/1899.

I put the same MsgBox statements in the Form_AfterUpdate event and the
values appear fine. But by the time it is displayed the 12/30/1899 shows up.
(The format of SessionDate is ShortDate.)

ah... perhaps it's setting the default value to a number, 11 divided by 7
divided by 2007; this will be a Very Small Number, corresponding to a time in
the dark after midnight on the fifth day of Christmas 1899.

Try

Me.SessionDate.DefaultValue = """" & Format(Me.SessionDate, "\#mm/dd/yyyy\#")
& """"

The DefaultValue property of a form control must be a String, regardless of
the datatype of the field bound to that control. The # is a date delimiter.

John W. Vinson [MVP]

 

[Marshall Barton]

The Default Value is not on the unbound field. I am trying to set a Default
Value on the SessionDate field which is bound. The code that remains is:
Me.SessionDate.DefaultValue = Me.SessionDate

I agree with your idea of the value being set to zero, but how? I added
some MsgBox statements to the AfterUpdate event of the SessionDate field to
display the values before and after the above statement and they appear fine.
But the next record still displays the 12/30/1899.

I put the same MsgBox statements in the Form_AfterUpdate event and the
values appear fine. But by the time it is displayed the 12/30/1899 shows up.
(The format of SessionDate is ShortDate.)

Seems like I answered this question a little while ago.

You need to use:

Me.SessionDate.DefaultValue = _
Format(Me.SessionDate,"\#yyyy-m-d\#")

 

[Guest]

That gets the solution one step closer. Your suggestion works as long as I
do not type over the default value. When I do that, the next record produces
"#Error" in the date field. It seems that when I type a date in mm/dd/yyyy
format, it apparantly is dividing out as you suggest. (I double-checked the
field in the table and it's datatype is Date/Time.)

Any other ideas?

Thanks.

 

[John W. Vinson]

That gets the solution one step closer. Your suggestion works as long as I
do not type over the default value. When I do that, the next record produces
"#Error" in the date field. It seems that when I type a date in mm/dd/yyyy
format, it apparantly is dividing out as you suggest. (I double-checked the
field in the table and it's datatype is Date/Time.)

You may need to use CDate:

Me.SessionDate.DefaultValue = """" & Format(CDate(Me.SessionDate),
"\#mm/dd/yyyy\#")
& """"


John W. Vinson [MVP]

 

[Guest]

I'm sorry to say that your CDate suggestion does not work either. Typing an
entry still produces the "#Error" on the next record.

Any other thoughts?

Thanks.

 

[Guest]

I think I have a solution for this date problem. I modified John's
suggestion by taking out the double-quotes as follows:
Me.SessionDate.DefaultValue =
Format(CDate (Me.SessionDate), "\#mm/dd/yyyy\#")

Here is another statement I wound up with. As always, I guess there is more
than one way to do things.
Me.SessionDate.DefaultValue = Chr(35) & FormatDateTime(Me.SessionDate,
vbShortDate) & Chr(35)

Thanks to all for the help.