Showing last few records on a form

[rick roberts]

when i take the "last record" option for a form it does just that - shows the last record as the top line - as it does with a "New record"
i want the last or new record to be at the bottom of the forrm so that i see the last few entries - -the length of the form determining how many entries show
im surprised this is isnt a fundamental option for records

 

[missinglinq]

Don't have time to work out exact code now, but something like

DoCmd.GoToRecord acDataForm, "YourActualFormName", acGoTo, X

where X is the total number of records minus the number of records you want to see. Determine the total number of records then

DoCmd.GoToRecord acDataForm, "YourActualFormName", acGoTo, TotalRecords - 5

would show the final five records

Good Luck!

 

[rick roberts]

i tried the code you suggested but got an error

Runtime error 2505
An Expression in argument 4 has an invalid value

it seems not to recognise 'TotalRecords' was i meant to use it directly in that way or define it first in some way?

 

[missinglinq]

TotalRecords is just a variable I used as an example! You need to determine the number of records in the Record Source for your form. Depending on exactly how you have your form set up

TotalRecords = RecordsetClone.RecordCount

might work. So at the top of your sub where you're plaing the code try

Dim TotalRecords as Integer

TotalRecords = RecordsetClone.RecordCount

DoCmd.GoToRecord acDataForm, "YourActualFormName", acGoTo, TotalRecords - 5

An alternative would be

TotalRecords = DCount("*", "Name of your table or query form is based on")

 

[rick roberts]

i tried both ways and they work - although they seem to give 2 extre records in both cases i.e. if i put 7 in the code they produce 9 records -- but im sure ill live with that - any explanation for it?
thanks for your help

 

[missinglinq]

Close enough for government work! Actually, in thinking, the code to show last five records should have been TotalRecords - 4. If you have 200 records for example:

200 - 4 = 196

shows record
196
197
198
199
200

Can't explain the extra record! Just one of those Access things!

Glad it's working for you!

 

[rick roberts]

now i have a new problem - this doesnt work when i use the form as a subform??