Requery Pop Up Form and scroll to bottom

Snowflake68

Snowflake68

Registered User.
Local time
Today, 20:11
Joined
May 28, 2014
Messages
464
I have a pop up form (Continuous view) that is bound to a query. The detail section contains only two fields of information, Item Name and Item Cost. As actions are taken on the main form I need the pop up form to update to show the costs of additional items that have been added. Basically the pop up displays an overall picture of the items and their related cost. I am querying the pop without a problem however each time it is re-queried it displays the top of the form again.

Is there a way of scrolling to the bottom each time it is re-queried.

I have tried to use the Send Keys Page Down functionality when a control on the pop up form has the focus but all this does is scroll down on the main form and not the pop up.

SendKeys "{PGDN 2}"

Thanks in advance
 
Snowflake68 said:
I have a pop up form (Continuous view) that is bound to a query. The detail section contains only two fields of information, Item Name and Item Cost. As actions are taken on the main form I need the pop up form to update to show the costs of additional items that have been added. Basically the pop up displays an overall picture of the items and their related cost. I am querying the pop without a problem however each time it is re-queried it displays the top of the form again.

Is there a way of scrolling to the bottom each time it is re-queried.

I have tried to use the Send Keys Page Down functionality when a control on the pop up form has the focus but all this does is scroll down on the main form and not the pop up.

SendKeys "{PGDN 2}"

Thanks in advance
Click to expand...

Most developers avoid SendKeys as that approach isn't guaranteed to work reliably in different Access versions

Requery always moves to the first record
So one of the easiest ways is to just to sort your popup form in descending order

Otherwise if you are reopening the popup form after each record is added, you could use
Code: 
DoCmd GoToRecord , , acLast
in the Form_Load or Form_Activate event
 
Last edited:
ridders said:
Most developers avoid SendKeys as that approach isn't guaranteed to work reliably in different Access versions
Click to expand...
Thanks for that I will avoid this then
Requery always moves to the first record
So one of the easiest ways is to just to sort your popup form in descending order
Click to expand...
I have a specific order they are presented in no matter which order they added them so unfortunately I cannot just sort them in descending order.
Otherwise if you are reopening the popup form after each record is added, you could use
Code: 
DoCmd GoToRecord , , ac_Last
in the Form_Load or Form_Activate event
Click to expand...
I have tried the DoCmd.GoToRecord , , ac_Last on the OnActivate event but this isnt firing when the form is Requeried (I know this because I placed a Message box to confirm if the code was running. However when I go to the design of the popup form it does fire but I then get a message saying that it cannot go to the specified record. I also tried this on the forms OnLoad event and I get the same error.

Any ideas?
 
The On Activate event won't be triggered from another VBA event. Other than Got focus events, no Control events are fired by other VBA code acting on those controls, this is deliberate to avoid infinite loops being created.

What you would need to do is probably requery the form, then got the last record. This all needs to be achieved from the other form.
 
I added a stray hyphen by mistake. It should read acLast NOT ac_Last.

I've corrected the original post
 
you can use the Pop-up's recordset to goto bottom:
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![PopUp_Form_Name_Here].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
        rs.MoveLast
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True

This code will fo to the other form, not in pop up.
 
arnelgp said:
you can use the Pop-up's recordset to goto bottom:
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![PopUp_Form_Name_Here].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
        rs.MoveLast
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True

This code will fo to the other form, not in pop up.
Click to expand...

Where do I need to put this code?
 
Minty said:
The On Activate event won't be triggered from another VBA event. Other than Got focus events, no Control events are fired by other VBA code acting on those controls, this is deliberate to avoid infinite loops being created.

What you would need to do is probably requery the form, then got the last record.
This all needs to be achieved from the other form
Click to expand...
.
Click to expand...

When you say this all needs to be achieved from the 'Other' form do you mean from the Main form or the actual Pop up form?
 
arnelgp said:
you can use the Pop-up's recordset to goto bottom:
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![PopUp_Form_Name_Here].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
        rs.MoveLast
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True

This code will fo to the other form, not in pop up.
Click to expand...

Thanks I seemed to have this working now but is there anyway of moving to the say last record -5 so that I always see the bottom 5 records?
 
Minty said:
The On Activate event won't be triggered from another VBA event. Other than Got focus events, no Control events are fired by other VBA code acting on those controls, this is deliberate to avoid infinite loops being created.

What you would need to do is probably requery the form, then got the last record. This all needs to be achieved from the other form.
Click to expand...

Thank you, this is very useful to know :)
 
you can use the Pop-up's recordset to goto bottom:
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
	if rs.RecordCount < 1 Then
		.Painting=True
		DoCmd.Echo True
		Exit Sub
	End If
        rs.MoveLast
        If rs.RecordCount > 4 Then
            rs.Move -5
        End If
        If (rs.BOF Or rs.EOF) Then rs.Move 1
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True

experiment changing the (-5) value in rs.Move -5, either less or more
 
arnelgp said:
you can use the Pop-up's recordset to goto bottom:
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
	if rs.RecordCount < 1 Then
		.Painting=True
		DoCmd.Echo True
		Exit Sub
	End If
        rs.MoveLast
        If rs.RecordCount > 4 Then
            rs.Move -5
        End If
        If (rs.BOF Or rs.EOF) Then rs.Move 1
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True

experiment changing the (-5) value in rs.Move -5, either less or more
Click to expand...
Hi arnelgp, I have played around with changing the numbers but cannot figure out why sometimes it works and other times it doesnt. The size of the form has room to display 20 records before the need to scroll. When it does work it only shows 5 records with lots of blank space (room for 15 records) below them even though there are records above the displayed 5 records.

What I really need it to do is display the 20 records and when the 21st record is added then it just needs to scroll up to display the new record thus hiding the first record and so on. Basically it needs to show the bottom 20 records all the time. Are you able help me with this please?
 
Code: 
 Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
	if rs.RecordCount < 1 Then
		.Painting=True
		DoCmd.Echo True
		Exit Sub
	End If
        rs.MoveLast
        If rs.RecordCount > 20 Then
            rs.AbsolutePosition= rs.Reccount - 20
        End If
        If (rs.BOF Or rs.EOF) Then rs.Move 1
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True
 
arnelgp said:
Code: 
 Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        Set rs = .RecordsetClone
	if rs.RecordCount < 1 Then
		.Painting=True
		DoCmd.Echo True
		Exit Sub
	End If
        rs.MoveLast
        If rs.RecordCount > 20 Then
            rs.AbsolutePosition= rs.Reccount - 20
        End If
        If (rs.BOF Or rs.EOF) Then rs.Move 1
        .Bookmark = rs.Bookmark
        rs.Close
        Set rs = Nothing
        .Painting = True
    End With
    DoCmd.Echo True
Click to expand...
I have updated my code with the above but it only works after you have added the 43rd record. I have been playing around with the numbers in the below section of the code and I just cannot get it to work when there are less than 43 records. Any Ideas?
If rs.RecordCount > 20 Then
rs.AbsolutePosition= rs.Reccount - 20
End If
Click to expand...

Just to add to the above, the form size displays 21 records and not 20.
 
here again and try, we set bookmark at the bottom record.
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        With .RecordsetClone
            .MoveLast
            If .RecordCount > 20 Then
                Forms![Pop-Up].Form.Bookmark = .Bookmark
                'rs.Move -5
                .AbsolutePosition = (.RecordCount - 20)
            End If
            If (.BOF Or .EOF) Then .Move 1
            Forms![Pop-Up].Form.Bookmark = .Bookmark
        End With
        .Painting = True
    End With
    DoCmd.Echo True
 
arnelgp said:
here again and try, we set bookmark at the bottom record.
Code: 
    Dim rs As DAO.Recordset
    DoCmd.Echo False
    With Forms![Pop-Up].Form
        .Painting = False
        .Requery
        With .RecordsetClone
            .MoveLast
            If .RecordCount > 20 Then
                Forms![Pop-Up].Form.Bookmark = .Bookmark
                'rs.Move -5
                .AbsolutePosition = (.RecordCount - 20)
            End If
            If (.BOF Or .EOF) Then .Move 1
            Forms![Pop-Up].Form.Bookmark = .Bookmark
        End With
        .Painting = True
    End With
    DoCmd.Echo True
Click to expand...

You are a star thank you so very much for helping me this appears to be working fine now. I can thank you enough much appreciated. You've helped me yet again.
 
you're welcome friend!
 
You must log in or register to reply here.

Similar threads

H
Auto complete
Replies
4
Views
360
hfsitumo2001
H
C
Sub or function not defined
Replies
12
Views
410
Pat Hartman
P
J
Solved Synchronzing Two Sub Forms to each other within a parent form
Replies
14
Views
523
Pat Hartman
P
M
Solved Pop-Up Datasheet Form Positioning and Scrollbars Questions
2
Replies
38
Views
1,874
Marshall Brooks
M
D
My mouse wheel cannot scroll the height of my form when my cursor is atop my continuous subform. Can I always have main form vertical scroll control?
Replies
13
Views
553
diamondDog
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom