Jun 25 2009, 03:06 AM
hi, is there a way of getting the last record in subform(datasheet view) and display it on report.
thanks a lot
Jun 25 2009, 03:41 AM
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?
Jun 25 2009, 03:43 AM
because of the ID number
Jun 25 2009, 04:03 AM
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
ORDER BY ID DESC;
Then make this query as the Record Source for your report.
Jun 25 2009, 04:14 AM
yes i already did but the problem is displaying in a report, is this code right?
Jun 25 2009, 04:23 AM
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.
Jun 25 2009, 05:09 AM
yes im putting the expression in the control source.. but the data in the form is actually from the table, could it be possible?
im just new in accesss :(
Jun 25 2009, 03:34 PM
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).
Jun 25 2009, 07:53 PM
thanks steve, but is there a way of getting ONLY the last record based on employeeID as reference?
Jun 25 2009, 08:30 PM
HI STEVE THANKS FOR THE HELP, I ALREADY GOT THE IDEA OF SELECT TOP1
Jun 25 2009, 08:34 PM
Very good, Jah.
Good luck with the rest of the project!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here