Repeat fields from last record

[Ardus Petus]

Access 2003

I have a form with many fields most of which are based on a table.
My client would like these fields to be defaulted from the last entered
record.
Should I use Defaultvalue property for each field? If yes, how to get last
record without disturbing current (new) record ?

TIA

 

[Klatuu]

You can set the Default Value property of each control you want to carry over
the value for by using the Form Before Update event. You make the default
value the current value:

With Me
.txtSomeControl.DefaultValue = .txtSomeControl
.txtAnotherControl.DefaultValue = .txtAnotherControl
etc....
End With

 

[Allen Browne]

See:
Assign default values from the last record
at:
http://allenbrowne.com/ser-24.html

The article explains how to use the form's BeforeInsert event procedure to
populate the fields of the current record with the values from the last one.
The code recognises the autonumber and any calculated controls, so doesn't
try to assign them. You can also specify other controls that should not be
copied over.

If you have only one or two fields you want to duplicate, you could use the
DefaultValue of those controls, as explained here:
Carry current value of a control to new records
at:
http://www.mvps.org/access/forms/frm0012.htm

 

[Ken Sheridan]

You'll hopefully have noticed that the code at the second link Allen set you
wraps the value in quotes characters. That's important. The DefaultValue
property is a string expression regardless of the data type in question.

In most cases it won't matter if you don't wrap the value in quotes, but in
some cases its crucial. Its particularly so with dates in short date format.
Say you have entered today's date in the format 07/01/2008 in a control and
you want it carried forward to the next record entered. If you use the
following in a form's AfterUpdate event procedure:

Me.txtMyDate.DefaultValue = Me.txtMyDate

then 07/01/2008 would be interpreted as an arithmetic expression evaluating
to 0.00348605577689243. In Access's date/time implementation that value in
fact represents 30 December 1899 00:05:01, which is not what you'd want
inserted. By using:

Me.txtMyDate.DefaultValue = """" & Me.txtMyDate & """"

its is interpreted correctly as a string expression and the correct value
will be assigned to the DefaultValue property.

The value will only be retained while the form is open of course, so if its
closed and reopened the value won't be carried forward. You can achieve that
if each record includes a unique value in a DateTimeStamp column, however,
whose DefaultValue property can be set to Now(), you can look up the last
entered record in the form's Current event procedure, and assign the values
from it to the DefaultValue properties of controls on the form:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim LastDateTime As Variant
Dim strSQL as String

If Me.NewRecord Then
LastDateTime = DMax("DateTimeStamp", "MyTable")

If Not IsNull(LastDateTime) Then
strSQL = "SELECT * FROM MyTable " & _
"WHERE DateTimeStamp = #" & _
FORMAT(LastDateTime,"mm/dd/yyyy hh:nn:ss") & "#"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Me.txtMyDate.DefaultValue = _
"""" & .Fields("MyDate") & """"
Me.txtSomeOtherField.DefaultValue = _
"""" & .Fields("SomeOtherField") & """"
' and so on
End With
End If
End If

One thing to be aware of when using the DefaultValue property is that this
does not initiate a new record and Dirty the form. That only happens when
the user begins to insert data, or values are assigned by code. This should
not be a problem as if no other values are inserted into other fields, or
none of the default values are changed, this would suggest that there is
something wrong with the design of the underlying table.

BTW don't assume that if the table includes an incrementing autonumber
column this can be used in place of a TimeDateStamp column to determine the
last entered record. Mostly it would, but you can't absolutely guarantee it
as an autonumber is only designed to ensure unique values not necessarily
sequential ones.

Ken Sheridan
Stafford, England

 

[Ardus Petus]

Thanks to all. I finally used the DefaultValue property.

Cheers