Go to the last record minus 5 records on a continuous form (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 04:42
Joined
Aug 20, 2010
Messages
357
I have a continuous form and when it opens, I would like it to go to the last record, (or a new record), minus 5 records. I'm trying to make it easy for a user to add a new record yet when you use Go To New when a continuous form opens, you see at the top of the form a blank row to start inputting. The user might think all of the records are gone, yet they are simply hidden. You can see them if you scroll up. The Offset function doesn't work with New or Last records.

Any ideas?

Chuck
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:42
Joined
Oct 29, 2018
Messages
21,357
Hi Chuck. I usually just walk through each record one row at a time, backwards. So,

Goto new record
Goto previous record
Goto previous record
... five times
 

vba_php

Forum Troll
Local time
Today, 04:42
Joined
Oct 6, 2019
Messages
2,884
I would attempt to help you, but maybe you don't realize that your post is a bit confusing. for instance, this:
I have a continuous form and when it opens, I would like it to go to the last record, (or a new record), minus 5 records.
doesn't jive with this:
I'm trying to make it easy for a user to add a new record yet when you use Go To New when a continuous form opens, you see at the top of the form a blank row to start inputting.
it sounds like ur asking 2 different questions. which is it? furthermore, the OFFSET function is from excel. it doesn't exist in access because it is related to a grid-like matrix system. access doesn't use that architecture or layout.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:42
Joined
May 21, 2018
Messages
8,463
Code:
Private Sub Form_Load()
  If Me.Recordset.RecordCount > 5 Then
    Me.Recordset.MoveLast
    Me.Recordset.AbsolutePosition = Me.Recordset.RecordCount - 5
  End If
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:42
Joined
Aug 30, 2003
Messages
36,118
The question isn't confusing at all, and dbGuy gave a working solution. If you use

DoCmd.GoToRecord , , acNewRec

by itself in the load event of a continuous form, you will typically see only the new record at the top of the form. The existing records will be scrolled out of view, which is what Chuck is describing. That can be confusing to users, so you open it so a few existing records are visible along with the new record.

Another option is putting the new record at the top. That can't be done natively, but can be faked either by putting a data entry subform on top of an existing records subform, or unbound controls with code to add the record. Either way you can get it to look like the new record is at the top of the list instead of the bottom.
 

tonez90

Registered User.
Local time
Today, 20:12
Joined
Sep 18, 2008
Messages
41
Hi I have used something simple on the form when I start up and is based on the number of records being displayed. You can easily modify it to form a module etc. In my code I also resize the form depending on the number of records I want to display. It then sets to top of the form the actual records and then moves the record selector to last row but you could not have it in if you want.

RunCommand acCmdRecordsGoToLast 'goto last record to set it up
'resize form to show last few records
Select Case Me.RecordsetClone.RecordCount
Case 0 To 5: 'up to 5 records to show
DoCmd.MoveSize , , 18500, 8500
Case 6 To 10: ' between 6 and 10 records to show
DoCmd.MoveSize , , 18500, 8500
Case 11 To 50:
DoCmd.MoveSize , 1000, 18500, 11000
Me.SelTop = Me.RecordsetClone.RecordCount - medium_top
RunCommand acCmdRecordsGoToLast
Case 51 To 80:
DoCmd.MoveSize , 1000, 18500, 11000
Me.SelTop = Me.RecordsetClone.RecordCount - Large_top
RunCommand acCmdRecordsGoToLast
Case Is > 80:
DoCmd.MoveSize , 1000, 18500, 11000
Me.SelTop = Me.RecordsetClone.RecordCount - extra_large_top
RunCommand acCmdRecordsGoToLast
Case Else
DoCmd.MoveSize , 500, 18500, 13500
Me.SelTop = Me.RecordsetClone.RecordCount - medium_top
RunCommand acCmdRecordsGoToLast
End Select
 

tonez90

Registered User.
Local time
Today, 20:12
Joined
Sep 18, 2008
Messages
41
Sorry forgot to say that medium top is a number that I can set to move the record selector back a number (so Medium_Top is set to 10 which is 10 records and fits on my screen)

Regards
Tonez
 

onur_can

Active member
Local time
Today, 02:42
Joined
Oct 4, 2015
Messages
180
Hello,
First, create 1 query, edit this query to bring 5 of your records (top 5), then save it. Name it QRY_END5.
Create a normal query based on your table in the 2nd place, name it QRY_RECORD. Then set your form's registration source to QRY_RECORD. Place one command button on your form and write the code below.
This will allow you to go to the new recording and see only 5 records on the screen.

Code:
Private Sub cmdNewRecord_Click()
Me.Form.RecordSource = "QRY_END5"
DoCmd.GoToRecord acDataForm, "FORM_RECORD", acNewRec ' Record form name.
End Sub
 

Users who are viewing this thread

Top Bottom