Copy data from last record.

[rhonda11]

I have a form with a subform. The subform is the updates of my projects which has a delivery date. However, when I add a new updates record on the subform, I like it to copy the previous updates delivery date field to my new update record, instead of having to retype it again. Any suggestion?

Thanks in advance.

 

[Lightning]

Straight from the Access Help file:

DFirst, DLast Functions
See Also Specifics
You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro, module, query expression, or calculated control on a form or report.

DFirst(expr, domain, [criteria])
DLast(expr, domain, [criteria])

The DFirst and DLast functions have the following arguments.

Argument Description
expr An expression that identifies the field from which you want to find the first or last value. It can be either a string expression identifying a field in a table or query, or an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain.
criteria An optional string expression used to restrict the range of data on which the DFirst or DLast function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DFirst and DLast functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DFirst and DLast functions return a Null.


Remarks
Note If you want to return the first or last record in a set of records (a domain), you should create a query sorted as either ascending or descending and set the TopValues property to 1. For more information, see the TopValues property topic. From Visual Basic, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.



HTH
Lightning

 

[rhonda11]

Thank you for your response. However, I only want one field (deliverydate) from the last record to copy to the new one when I select the new button. If your saying is true, can you please show me how to implement that expression into my form record resource.
My table is call "ProjectUpdate"
The field that need to be copy from the last record "Deliverydate"

Thanks

 

[bubba100]

This is what I use in the "On Key Down" even procedure of a form. It was written in 97.

Dim strKey As String
Dim EndOdom As String
Dim oldUnit As String
Dim Message
strKey = Chr(KeyCode)
If KeyCode = 27 Then
KeyCode = 0
End If

'If the F2 Key has been Pressed, the Field from the previous invoice is duplicated

Select Case KeyCode

Case vbKeyF2 'Duplicate The Field From The Previous Record
SendKeys "^(')"
Case vbKeyF5 'Duplicate The Unit and Insert The End Odometer Reading from Previous
' Record into Bgn Odometer, Set Focus on Date Field
On Error GoTo errhandler:
Docmd.GoToRecord Record:=acPrevious
oldUnit = Me.Unit.Value
EndOdom = Me.EndOdom.Value
Docmd.GoToRecord Record:=acNext
Me.Unit = oldUnit
Me.BegOdom = EndOdom
Me.Refresh
BegDate.SetFocus
End Select

Exit Sub
errhandler:
Message = MsgBox(" Be Sure There Is A Unit Number And Ending Odometer In The Previous Record", vbOKOnly)
Me.EndOdom.SetFocus

 

[rhonda11]

When I click the newrecord button, a new blank form appear for entry. However, there's one particular field "DeliveryDate" that I need to duplicate from the previous record(not the whole record)automatically when click on newrecord button.I should also be able to change it or overwrite. I think the DFirst or DLast work but I don't know how to use that expression. Please help.

Thanks in advance.

 

[Lightning]

The DFIRST and DLAST functions don't return a record - they return a field value from an existing record.

Using the DLAST function (to get the value from the LAST record):

DLast(expr, domain, [criteria])

your string would be

=DLAST("DeliveryDate","ProjectUpdate&quot

Put this in the Default Value property of the DeliveryDate Field on your ProjectUpdate form.

HTH
Lightning

 

[rhonda11]

Thanks for your help,it works! However, I want it to duplicate the deliverydate field depending on the Project No being selected. For instance, I have a ProjectNo that have many updates (projectUpdate). When a ProjectNo is retreive to update, I would like it to copy the last update delivery date from the last project update in that Project No.
I hope this is not confusing.

Thanks again

 

[ALEKSJAY]

If you need to retrieve a value depending on another, then use then method that "Lightning" is suggesting, just add the "criteria" parameter:
something like "[project] = " & your_variable

 

[rhonda11]

I'm sorry. But, I'm new at this. How do I do that if the DeliveryDate from the ProjectUpdate table depends on the ProjectNo from the Project table. This is what got so far:
=DLast("DeliveryDate","ProjectUpdat&quot
The above is not giving me the updated deliverdate depending on the projectNo is selected. It only giving the last update in general.


Thanks