Keep last entered date on form

I am curious to know if a TextBox on a form can be set to the last date that
was in the TextBox. I have this little piece of code that sets the TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Basically, you want to have the control default to today's date. However,
once you enter a different date during a data entry session, you want the
control to default to whatever date you have entered.

If you don't want that as a default value (automatic fill when you create a
new record), but only when you click on the control then you need to store the
date in a variable and use that. UNTESTED CODE follows

Option Compare Database
Option Explicit

Dim DefaultDate As Date

Private Sub TDate_AfterUpdate()
If IsNull(Me.TDate) = False Then
DefaultDate = Me.TDate
End If
End Sub

Private Sub TDATE_Click()
IF IsNull(DefaultDate) Then
Me.TDATE = Date
Else
Me.TDATE = DefaultDate
End IF
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ryguy7272 wrote:
I am curious to know if a TextBox on a form can be set to the last date that
was in the TextBox. I have this little piece of code that sets the TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---

On Sat, 6 Mar 2010 09:27:01 -0800, ryguy7272
wrote:

I am curious to know if a TextBox on a form can be set to the last date that
was in the TextBox. I have this little piece of code that sets the TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

I'd use the Doubleclick event rather than Click - it's much too easy to
unintentionally click a textbox. Then set the control's default value in its
own AfterUpdate event, to make the entry "sticky":

Private Sub TDATE_AfterUpdate()
Me!TDATE.DefaultValue = """" & Format(Me!TDATE, "yyyy-mm-dd") & """"
End Sub

The default property is a text string, hence the quotes; and the formatting
ensures that the default is a valid date/time value. You will need to change
the format if you include a time portion or have an input mask.
--

John W. Vinson [MVP]

You can easily carry the last entered date in the current session, i.e. while
the form remains open, forward to a new record by setting the control's
DefaultValue property in its AfterUpdate event procedu

Me.TDATE.DefaultValue = """" & Me.TDATE & """"

Note that the DefaultValue property is always a text expression regardless of
the underlying data type, so should be wrapped in quotes characters as above,
whatever the data type. This s particularly important with dates in fact as
otherwise a date in short date format would be interpreted as an arithmetical
expression and give the wrong result.

You should also set the control's DefaultValue property to Date() in its
properties sheet so that the current date is the default if you do not enter
another date. As the AfterUpdate event procedure does not execute if you
assign a value to the control in code you'll need to explicitly call it in
your code for the control's Click event procedu

Me.TDATE = VBA.Date
TDATE_AfterUpdate

To retain the last date entered as the default if the form, or even the
application, is closed, the easiest way is to have a table Defaults with
columns FormeName and ControlName and DefaultVal, all of text data type.
You can then insert rows for any field for which you wish to store a default
value dynamically. The code in the control's AdfterUpdate event procedure
would be extended as follows to update the Defaults table when the value is
changed:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & Me.ActiveControl.Name & """"

' is there an existing default for this control?
If Not IsNull(DLookup("FormName", "Defaults", strCriteria)) Then
' if so then update row in table
strSQL = "UPDATE Defaults " & _
"SET DefaultVal = """ & Me.ActiveControl & """ " & _
"WHERE " & strCriteria
Else
' insert new row
strSQL = "INSERT INTO Defaults(" & _
"FormName,ControlName,DefaultVal) " & _
"VALUES(""" & Me.Name & """,""" & _
Me.ActiveControl.Name & """,""" & _
Me.ActiveControl.Value & """)"
End If

cmd.CommandText = strSQL
cmd.Execute

' set control's default value to current value
Me.TDATE.DefaultValue = """" & Me.TDATE & """"

and the following would be put in the form's Open event procedure to set the
default value to the value in the table when the form opens:

Dim strCriteria As String

strCriteria = "FormName = """ & Me.Name & _
""" And ControlName = ""TDATE"""

Me.TDATE.DefaultValue = _
"""" & DLookup("DefaultVal", "Defaults", strCriteria) & """"

Note that this removes the need for your code in the control's Click event
procedure as everything is driven automatically by form or control events.

Ken Sheridan
Stafford, England

ryguy7272 wrote:
I am curious to know if a TextBox on a form can be set to the last date that
was in the TextBox. I have this little piece of code that sets the TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

All great advice! John, i was thinking of something like that. I actually
went with Ken's suggestion:

Private Sub Form_AfterUpdate()
Me.TDATE.DefaultValue = """" & Me.TDATE & """"
End Sub

Thanks everyone!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"KenSheridan via AccessMonster.com" wrote:

You can easily carry the last entered date in the current session, i.e. while
the form remains open, forward to a new record by setting the control's
DefaultValue property in its AfterUpdate event procedu

Me.TDATE.DefaultValue = """" & Me.TDATE & """"

Note that the DefaultValue property is always a text expression regardless of
the underlying data type, so should be wrapped in quotes characters as above,
whatever the data type. This s particularly important with dates in fact as
otherwise a date in short date format would be interpreted as an arithmetical
expression and give the wrong result.

You should also set the control's DefaultValue property to Date() in its
properties sheet so that the current date is the default if you do not enter
another date. As the AfterUpdate event procedure does not execute if you
assign a value to the control in code you'll need to explicitly call it in
your code for the control's Click event procedu

Me.TDATE = VBA.Date
TDATE_AfterUpdate

To retain the last date entered as the default if the form, or even the
application, is closed, the easiest way is to have a table Defaults with
columns FormeName and ControlName and DefaultVal, all of text data type.
You can then insert rows for any field for which you wish to store a default
value dynamically. The code in the control's AdfterUpdate event procedure
would be extended as follows to update the Defaults table when the value is
changed:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "FormName = """ & Me.Name & """ " & _
"And ControlName = """ & Me.ActiveControl.Name & """"

' is there an existing default for this control?
If Not IsNull(DLookup("FormName", "Defaults", strCriteria)) Then
' if so then update row in table
strSQL = "UPDATE Defaults " & _
"SET DefaultVal = """ & Me.ActiveControl & """ " & _
"WHERE " & strCriteria
Else
' insert new row
strSQL = "INSERT INTO Defaults(" & _
"FormName,ControlName,DefaultVal) " & _
"VALUES(""" & Me.Name & """,""" & _
Me.ActiveControl.Name & """,""" & _
Me.ActiveControl.Value & """)"
End If

cmd.CommandText = strSQL
cmd.Execute

' set control's default value to current value
Me.TDATE.DefaultValue = """" & Me.TDATE & """"

and the following would be put in the form's Open event procedure to set the
default value to the value in the table when the form opens:

Dim strCriteria As String

strCriteria = "FormName = """ & Me.Name & _
""" And ControlName = ""TDATE"""

Me.TDATE.DefaultValue = _
"""" & DLookup("DefaultVal", "Defaults", strCriteria) & """"

Note that this removes the need for your code in the control's Click event
procedure as everything is driven automatically by form or control events.

Ken Sheridan
Stafford, England

ryguy7272 wrote:
I am curious to know if a TextBox on a form can be set to the last date that
was in the TextBox. I have this little piece of code that sets the TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

.

j;j,hv;,v

"ryguy7272" a écrit dans le message de
groupe de discussion : ...
I am curious to know if a TextBox on a form can be set to the last date
that
was in the TextBox. I have this little piece of code that sets the
TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as
yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

j;j;jhv;

"ryguy7272" a écrit dans le message de
groupe de discussion : ...
I am curious to know if a TextBox on a form can be set to the last date
that
was in the TextBox. I have this little piece of code that sets the
TextBox
to the current date.
Private Sub TDATE_Click()
TDATE.Value = Date
End Sub

However, as I think about it, I will enter dates using the form, but the
date, and related data, may not be today; I may enter data, and related
dates, for yesterday or two days ago, so I’d like to click the TextBox and
have the date be today’s date or if I choose another date, such as
yesterday,
keep this date in that TextBox, for the next record that I’ll enter, after
the current record is entered. Does that make sense?

Thanks!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.