hi, is there a way of getting the last record in subform(datasheet view) and display it on report.

thanks a lot

Jah,

Rather than thinking in terms of the "last" record in a subform, which is a very loose concept (because of the way the records on the subform happen to be ordered), it is better to try and identify the record you want by referring to the data it includes.

So what makes the "last" record last? Is there a date/time field, or an ID number of some sort, or some other way you can determine the last record?

because of the ID number

Jah,

Ok, so I will suppose that your subform is based on a table or a query, maybe it's named YourTableOrQuery.

Make a query based on YourTableOrQuery and sort it descending on the ID field. And then set the query's Top Values property to 1. The SQL view of such a query will look something like:
SELECT TOP 1 ID, OneField, AnotherField, SomethingElse
FROM YourTableOrQuery
ORDER BY ID DESC;

Then make this query as the Record Source for your report.

yes i already did but the problem is displaying in a report, is this code right?

=forms!frm_mainform!frm_subform.field

Jah,

I don't understand. Do you mean you are putting this expression in the Control Source of a textbox on the report? If you try to refer like this to the value of a control on a form, it will use the whatever happens to be the form's Current record. This will be unreliable to give you what you want. If you instead base the report back to the actual data, via a query based on your tables, and forget the form altogether, you will get a reliable result.

If I'm missing your meaning here, can you please give some more details and examples of what you are trying to achieve.

yes im putting the expression in the control source.. but the data in the form is actually from the table, could it be possible?

thanks steve

im just new in accesss :(

Jah,

Best wishes with your forth-coming adventure with the wonderful world of Access!

Yes, the data is always in the tables. Forms only provide a convenient way to view and interact with the data, but the data is not "in" the form. Same with a report... they are for the presentation of data, but the data stays in the table.

It is good to think of it like this.

Whereas it is possible to use an expression to reference a control on a form, and the value of the data (sourced from the table) by that control, and sometimes this is a useful approach. But it is usually simpler and more efficient in a case such as yours, to create a query that returns the data you want, and then base your form or report on that query, and forget about trying to cross-reference data from controls on another object (form or report).

Make sense?

thanks steve, but is there a way of getting ONLY the last record based on employeeID as reference?

HI STEVE THANKS FOR THE HELP, I ALREADY GOT THE IDEA OF SELECT TOP1

ALREADY WORKING

Very good, Jah.

Good luck with the rest of the project!