how do I return date from previous record?

[Linda]

how do I return date from previous record?

What I want to do is to have a date filed on a data entry form default
to the date on the previous record.

 

[Guest]

Hi Linda

Have a look at the answer to the same question by Al Campagna.

http://groups.google.com/group/micr...ad4e00abc26?lnk=st&q=&rnum=1#20dc5ad4e00abc26

 

[Linda]

Thanks Wayne, unfortunately I am new at this and most of that
discussion went over my head. I entered the following:
=DLookUp("[DateWorked]","Time Entered","[ID]= " & Forms![Time Entry
Subform1]![ID]-1")

in a text box in my Time Entered Subform1. The underlying table is
Time Entered. My goal is to have the date in the previous record
automatically populate the DateWorked field of the form so an employee
only has to change it when he moves to a new day.

When I open the form with this in there it says #error in all the
DateWorked fields and seems to be in some kind of loop becuase the
words #error blinks sequentially.

Thanks,
Linda

 

[John W. Vinson]

how do I return date from previous record?

What I want to do is to have a date filed on a data entry form default
to the date on the previous record.

The simplest way is to use the textbox's AfterUpdate event to set its own
DefaultValue property. The syntax is a bit tricky since Default Values must be
strings, and you need a proper date value. If the date textbox is named
txtDate, click the ... icon by its AfterUpdate property and choose Code
Builder; put into it

Private Sub txtDate_AfterUpdate() <<< Access gives you this line for free
If IsDate(Me!txtDate) Then
Me!txtDate.DefaultValue = "#" & Format(Me!txtDate, "mm/dd/yyyy") & "#"
End If
End Sub


John W. Vinson [MVP]

 

[Linda]

Thanks so much John. It worked perfectly. Can you recommend a good
reference book for Access?
Linda

 

[John W. Vinson]

Thanks so much John. It worked perfectly. Can you recommend a good
reference book for Access?

There are many books on Access, and many of them are very good. Learning
styles differ, so what might be a great book for one person might be wrong for
someone else.

That said... "Microsoft Access <version> Step by Step" by my friend John
Viescas is terrific; for really advanced and in-depth develoment, the gold
standard is "Access 2002 Developer's Handbook" by Getz et al. It's two thick
volumes and costs a bundle, but the time I've saved from its examples is worth
ten times the cost.

John W. Vinson [MVP]

 

[Guest]

Linda,

Within a given data entry session, you can set the control's DefaultValue
property using its AfterUpdate event:

Me![YourTextBox].DefaultValue = Me![YourTextBox]

For a new session, if you want to pick up the value from the last entered
record, you can use the OnOpen event to move to the last record, pick up the
value, move to a new record, enter the value, and set the DefaultValue
property:

Dim strDocName as String
Dim dteLastDate as Date
strDocName = Me.Name

DoCmd.GoToRecord acDataForm, strDocName , acLast
dteLastDate = Me![YourDateControl]
DoCmd.GoToRecord acDataForm, strDocName , acNewRec
Me![YourDateControl] = dteLastDate
Me![YourDateControl].DefaultValue = dteLastDate
Me![YourFirstDataEntryField].SetFocus

 

[Guest]

Putting the following in the form's AfterInsert event procedure will set the
default value of the control bound to the date field to the last date entered
since the form was opened:

If Not IsNull([YourDateControl]) Then
Me.[YourDateControl].Defaultvalue = """" & [YourDateControl] & """"
End If

If you want to carry the date forward between different sessions of the form
being opened then, if the last date entered will always be the latest date in
the field, you can do the above, but also put the following in the form's
Open event procedure:

Dim varDate As Variant

varDate = DMax("[YourDateField]", "[YourTable]")

If Not IsNull(varDate) Then
Me.[YourDateControl].Defaultvalue = """" & varDate & """"
End Of

If the last date entered will not always be the latest date in the field
then you'd either need to determine which was the last row inserted into the
table by means of another field's value and use this as the criterion for
calling the DLookup function rather than using the DMax function as above, or
you'd need to store the date entered somewhere in the form's AfterInsert
event procedure. This could be a table of default values for instance, which
could be looked up, or you could create a custom property for the form and
assign the date value to this in the form's AfterInsert event procedure, and
then retrieve it in the form's Open event procedure.

Ken Sheridan
Stafford, England

 

[Guest]

Linda,

I posted an answer to your question earlier, but for some reason it's not
showing up. So here goes:

In a given session, you can use the previously entered record's value by
setting the control's DefaultValue property in the AfterUpdate event:

Me![YourControl].DefaultValue=Me![YourControl]

At the start of a new session, you can pick up the last entered value using
the form's OnOpen event:

Dim strDocName as String
Dim dteLastDate as Date

strDocName = Me.Name
DoCmd.GoToRecord acDataForm, strDocName, acLast
dteLastDate = Me![YourControl]
DoCmd.GoToRecord acDataForm, strDocName, acNewRec
Me![YourControl] = dteLastDate
Me![YourControl].DefaultValue = Me![YourControl]
Me![YourFirstDataEntryControl].SetFocus

Sprinks