Current record changes on resizing form - why?

[skiller]

Hi,

I'm fairly new to Access programming and, despite being very familiar with the syntax of VB/VBA, the behaviour of the Access data-bound forms can sometimes be quite alien to me.

I can go into the details of my particular coding "challenge" if asked, but I think my problem can be brought down to the following question:

Why does the current record change back to the first record after a particular resize of the form?

To elaborate, if I have a form which has a "Continuous Forms"-style view of its records, then I don't want the currently selected record to change simply because the form has been resized. But it seems to do exactly that - the focus moves back to the first record.

I've tried to pin down the criteria that cause this issue and it only seems to be a problem if the records are displayed within a subform, AND if the main form containing this subform also has Form Headers/Footers switched on. Unfortunately, I want to use subforms a lot and I want to use headers/footers a lot.

I've attached a tiny sample DB to illustrate the problem. It contains a few test forms:

frmMainWithoutSubform:
This is just a simple form with no subform and no form header/footer.

frmMainWithoutSubform(WithFrmHdrFtr):
As above but with form header/footer.

frmMainWithSubform:
A form containing a subform but no form header/footer.

frmMainWithSubform(WithFrmHdrFtr):
As above but with form header/footer.

To see my problem in action, just open one of the above 4 forms and click in any textbox from a record beyond the first.

Then resize the window as small as you can. (Actually, all you need to do is to make the height small enough so that the header and footer meet, if it has them.)

Then, when you resize the window back to a large enough size to enable you to see the records, you can see if the problem occurs or not.

If the main form has headers/footers AND the records are within a subform (ie the last of the 4 examples described above), then it appears that the FIRST record now has the focus. Otherwise, the focus seems to stay on whichever control it was on before the resize - which is where I would like it always to stay during a form resize.

I've added a load of debug statements to see if that helps explain:

frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: orderno_Exit
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: orderno_LostFocus
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Unload
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Close
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Open
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Load
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Resize
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: Current
frmMainWithSubform(WithFrmHdrFtr).frmTheSubform: orderno_Enter

It appears that the entire subform is unloaded when you resize the main form small enough, and then reloaded when you resize it large enough for it again. However, the act of unloading/reloading causes the current record to be reset to the first one.

I need such forms to be resizable so, as an interface designer, I need to cater for any eventuality. If I'm letting the user resize the form, I need to be able to cope with what happens if the user resizes to such an extreme, even if there isn't much likelihood that the user would do that. The point is, they may do.

So, can anyone explain why this happens? Have I made a schoolboy error that can easily be fixed?

Many thanks in advance to anyone who can help.

 

[arnelgp]

sorry, i tested in msa 2021, win 11 x64, but it does not occur to me.
it stays on the Same record when you resize the main form.

 

[The_Doc_Man]

It would seem to me that when you see that debug sequence, the answer is obvious. You have an Unload, Close, Open, Load, Resize, Current in order. When you have that Close event, you lose track of prior placement. When you re-open the form, you are in essence starting over and so you start at the first record of the subform. I seriously doubt that you see anything other than a flicker between the Unload and the subsequent Load events. The Current event usually isn't visible unless you have some display-affecting code in it. The Resize would probably be the last visible thing on the form.

Since a continuous form is actually a single form that just gets repeated row-by-row, you only have one set of controls. That means that you have to work a little bit to re-select a particular record. Here is one article on how the authors approached the problem.

How to Highlight the Current Record in a Continuous Form

Step-by-step instructions for applying a custom highlight to the currently selected record in a continuous form in Microsoft Access.

nolongerset.com

You could just copy the PK of the highlighted row before doing that Unload event and then use the stored PK to highlight that row again. OR you could try to figure out why you do a close/re-open and just prevent that from happening. According to the details of the Resize operation, there is no automatic closure.

Form.Resize event (Access)

Office VBA reference topic

learn.microsoft.com

 

[arnelgp]

I need such forms to be resizable so, as an interface designer, I need to cater for any eventuality. If I'm letting the user resize the form, I need to be able to cope with what happens if the user resizes to such an extreme, even if there isn't much likelihood that the user would do that. The point is, they may do

you bring you main form in design view and click on the subform.
on Property Sheet->Format,

Horizontal Anchor = Both
Vertical Anchor = Both

the subform will resize with the main form.

 

[isladogs]

Just tested using A365 in Win10
Yes, I can confirm that that subform reverts to record 1 when you shrink the main form as described in post #1
Changing the anchoring as @arnelgp suggested doesn't solve your issue.

Not sure why you have to allow users to do this but if you must do so, I suggest you try one of the following:
1. set a minimum size for the subform so at least part of it is always visible
2. Store the current record before shrinking the height and revert to it after increasing the height

Just out of interest, have you considered using automatic form resizing?

Automatic Form Resizing 1

Use automatic form resizing (AFR) to ensure that forms and their controls are automatically resized and repositioned for any screen size & resolution. It can also be used to add a zoom feature to Access forms

www.isladogs.co.uk

 

[Pat Hartman]

You have no code that is resizing the subform.
I had no problem. The focus did not revert to the first row.

I added the following code to the main form's Resize event. It resizes the subform in proportion to the main form and it does not cause the subform to be unloaded.
The numbers I picked seem to be OK for this sample but you might have to change the numbers when you move it into your production copy.

Dbg "Resize"
Const LeftRightPadding = (0.125 + 0.25) * 1440
Const TopBottomPadding = (0.25 + 0.375) * 1440

Me.sfmOrders.Height = Me.InsideHeight - TopBottomPadding
Me.sfmOrders.Width = Me.InsideWidth - LeftRightPadding

 

[isladogs]

Just tried that code Pat.
It still reverts to the first record when shrunk vertically so the subform is no longer visible.
I also increased the values. No change

 

[skiller]

It would seem to me that when you see that debug sequence, the answer is obvious. You have an Unload, Close, Open, Load, Resize, Current in order. When you have that Close event, you lose track of prior placement. When you re-open the form, you are in essence starting over and so you start at the first record of the subform.

Hi The_Doc_Man,
I agree the answer lies in the fact that these events are occurring, as I said in my OP. The question is, why on Earth are they occurring? I don't want the Unload/Close events etc to occur at all. They don't happen if there are no headers/footers AND they also don't happen if the records aren't in a subform, so why do they happen when there IS a subform AND there ARE headers/footers?

I might also add, the problem doesn't occur if you minimize the form (UNLESS you try to resize the Section height on Resize, which is another thing entirely which I've avoided mentioning so far as I wanted to try to make this post as simple as possible!).

 

[skiller]

You have no code that is resizing the subform.
I had no problem. The focus did not revert to the first row.

I added the following code to the main form's Resize event. It resizes the subform in proportion to the main form and it does not cause the subform to be unloaded.
The numbers I picked seem to be OK for this sample but you might have to change the numbers when you move it into your production copy.

Dbg "Resize"
Const LeftRightPadding = (0.125 + 0.25) * 1440
Const TopBottomPadding = (0.25 + 0.375) * 1440

Me.sfmOrders.Height = Me.InsideHeight - TopBottomPadding
Me.sfmOrders.Width = Me.InsideWidth - LeftRightPadding

I find it bizarre that you are the second person to say that they had no problem and your focus did not revert to the first row.

In my actual code, I do indeed have plenty going on in the Resize event, including changing the size of the subform in a similar way to the code you suggested. But as Colin says, the Resize event code doesn't resolve it for me (or him).

Incidentally, I know I made it quite clear that my problem was occurring with "Continuous Forms", but the same issue applies if it's a "Single Form" too.

 

[skiller]

Not sure why you have to allow users to do this

The implication from this is that you don't have user-resizable forms at all in your apps - is that correct? Maybe I'm so old that I miss the days of MDI forms which all Office applications used to use and no longer do.

I prefer to make my forms user-resizable if at all possible. Many controls are easily "stretchable" and I've spent decades writing Resize event code in VB3/4/5/6.

but if you must do so, I suggest you try one of the following:
1. set a minimum size for the subform so at least part of it is always visible
2. Store the current record before shrinking the height and revert to it after increasing the height

Point 1 sounds interesting. How can I set a minimum size which prevents a user from resizing below that value?

 

[skiller]

Colin, I don't know why, but this forum refused to let me reply to your comment about your AFR link while quoting the URL. But thanks for that link. I haven't had chance to go through it thoroughly yet (as it includes a couple of hour-long YT videos) but it certainly looks interesting.

Apologies for the multiple posts above - it was a consequence of being unable to post and I was trying to eliminate where the offending portion of my overall post was.

 

[Pat Hartman]

I also do not normally develop resizable forms. The Access features ar awkward at best but I have in the past used Peter's Strecher/Shrinker product when a client had widely differing monitors/resolutions.

Not sure you why you have more than a couple of lines in your resize event. The forms you included do not have code in the resize event. Have you tried using the version of code that I use?

 

[isladogs]

Colin, I don't know why, but this forum refused to let me reply to your comment about your AFR link while quoting the URL. But thanks for that link. I haven't had chance to go through it thoroughly yet (as it includes a couple of hour-long YT videos) but it certainly looks interesting.

Apologies for the multiple posts above - it was a consequence of being unable to post and I was trying to eliminate where the offending portion of my overall post was.

New users aren't allowed to post links. This restriction will go once you reach a minimum number of posts.

One way of preventing this issue is to move the subform to the main form footer (or header) section. No code needed
There is a side effect but it may not concern you.

If that's not satisfactory, I'll try to provide code for my other suggestions

Send me a PM or email if you have any questions about automatic form resizing

 

[skiller]

Not sure you why you have more than a couple of lines in your resize event. The forms you included do not have code in the resize event.

When I said I had "plenty going on" in my Resize event, I was referring to the code in my live applications, where I reposition/resize various controls. The sample DB that I attached to this thread in post #1 was deliberately an extremely simple example in order to highlight and demonstrate the problem. I don't think any of the resize code is going to help.

The problem boils down to the fact that as soon as the InsideHeight property of the main form becomes less than the sum of the heights of the header and footer, then any subforms are automatically unloaded, which subsequently causes the focus to restart at record 1 when the form is made large enough for the subform to be reloaded.

Note that this does NOT happen if the user minimizes the form and then restores the form - except if I resize the Section(acDetail).Height during the Resize event code. I didn't mention this in my original post, but I do like to have the following line in my Resize events:

Section(acDetail).Height = nNonNeg(InsideHeight - Section(acHeader).Height - Section(acFooter).Height)

(nNonNeg is a simple function that returns zero if the argument is negative, otherwise returns the argument itself.)

And if I do have this line of code (which I find essential in order for the vertical scrollbar to function correctly), then the problem also occurs when the form is minimized (as well as when it is resized very small).

To sum up, the problem occurs as per the following table:

	Resize form sufficiently small	Minimize form
Change detail Section height in Resize event	Subform unloads - A BAD THING	Subform unloads - A BAD THING
DON'T change detail Section height in Resize event	Subform unloads - A BAD THING	Subform does NOT unload - A GOOD THING

Have you tried using the version of code that I use?

Yes I have tried using your resize code, but as I said in post #9, it doesn't resolve the problem (and Colin says the same too), so I'm surprised that you say that it did resolve the issue in your test.

New users aren't allowed to post links. This restriction will go once you reach a minimum number of posts.

Ah, that would explain it!

One way of preventing this issue is to move the subform to the main form footer (or header) section. No code needed
There is a side effect but it may not concern you.

I'm not sure how feasible that would be. Some of my forms have multiple subforms dotted around the main form. Furthermore, surely it would defeat the purpose of the header and footer, which is that they are fixed areas of the form, as opposed to the detail which is record-specific and can be scrollable.

If that's not satisfactory, I'll try to provide code for my other suggestions

Many thanks for offering to help (and that extends to all the contributors to this thread, of course), but given the added complication that I recently mentioned (regarding the fact that minimizing the form also causes the subform to be unloaded), then I'm not sure if the problem is even solvable. Although I still find that hard to believe. I can't be the only person who uses a resizable form, with headers/footers and a subform, can I?

 

[Pat Hartman]

Instead of using your own code to resize the form, look into Peter's shretcher/shrinker. That worked very well for me. It is also not very expensive and it is not an add in. You get the code module and then need to use a special command when you open any form. When I use my resizing code it is only to resize a subform that is continuous or DS and only one subform at that. Moving around all the forms becomes much more complex.

How often does the user shrink the form to nothing? Is this something you really need to worry about? If he does it, so what if it causes the subform to unload. If the users don't like the result, they'll stop doing it.

 

[isladogs]

I spent over an hour on this earlier but with only partial success
I'm not sure its worth spending any more time on it as I doubt its going to occur often or be a major issue if it does happen.

Possible ways forward:
1. Putting the subform(s) in the footer definitely works (tested)
Why can't you arrange your subforms in the footer in the same way as you currently do in the detail section? I'm not sure I see the problem.

2. I do have code that prevents forms shrinking below a certain size but they haven't solved the issue here with a subform in the detail

3. I tried adding code like this to the Form_Resize event:

Section(acDetail).Height = InsideHeight - Section(acHeader).Height - Section(acFooter).Height - 300

That triggers error 2100 when the height gets too small (and so the record doesn't change).
If you can find a way of handling that error in a 'positive way' you may have another solution

Although I still find that hard to believe. I can't be the only person who uses a resizable form, with headers/footers and a subform, can I?

Thinking about it, I probably don't allow this setup in my forms as I want them to work without issue for end users
I do use overlapping windows display and most of my forms have headers and footers
Users can resize certain forms (single/continuous & datasheet) but not where it affects functionality

EDIT:
Before you spend money on Peter's Shrinker/Stretcher, I recommend you first look at my resizing code which is free.
From memory, mine has a larger set of features including resizing on the 'fly' that I've just added. See attached short video

 

[gemma-the-husky]

^^^
It occurred to me that maybe a subform is treated differently to a form. Maybe when it's not visible because of its size, it drops out of memory. When it becomes visible again, it might need to be requeried as if it was being populated for the first time. I'm not bothered to check, but maybe it re-runs the open/load events rather than the activate event when it becomes visible again.

 

[skiller]

Instead of using your own code to resize the form, look into Peter's shretcher/shrinker.

But using my own code to resize the form is irrelevant. Even without any Resize event code running (as in my sample DB attached to this thread), the subform gets unloaded regardless.

How often does the user shrink the form to nothing? Is this something you really need to worry about?

I think you're missing the point. As I said originally, the user probably won't resize it so drastically small, but the possibility is there and we have to cater for all eventualities - you can't write code on the assumption that the user won't do something. But even if we assume that they won't resize the form too small, they have a perfect right to minimize it, and I would expect them to do so. Minimizing also causes the subform to unload. In fact, this is precisely what caused me to look at this in the first place. My user came to me and said they wanted to minimize the form while they looked at another form but they couldn't as I'd made the first form modal.

If he does it, so what if it causes the subform to unload. If the users don't like the result, they'll stop doing it.

You seem to think that the subform unloading and reloading again is simply an annoyance to the end user as it reverts the focus back to record 1. If that were the only issue, then I'd agree it's not a big deal. But that's the least of the trouble. The point is that the following events will occur (assuming the current focus is at a control called "ctl"):

ctl_Exit
ctl_LostFocus
Form_Unload
Form_Close
Form_Open
Form_Load
Form_Resize
Form_Current
ctl_Enter

All of the above events I will have potentially coded for and do not want them to happen simply because the user has minimized the form and restored it - or resized it slightly too small and made bigger again.

I spent over an hour on this earlier but with only partial success
I'm not sure its worth spending any more time on it as I doubt its going to occur often or be a major issue if it does happen.

I would have agreed at first as it was something I needed to get to the bottom of for completeness as all good developers need to cover all bases, but then I noticed it occurs when the form is minimized, so it definitely isn't one of those "hardly ever going to happen" scenarios. It will happen many times every day.

Possible ways forward:
1. Putting the subform(s) in the footer definitely works (tested)
Why can't you arrange your subforms in the footer in the same way as you currently do in the detail section? I'm not sure I see the problem.

Without doubt, I'm not as familiar as you are with the behaviour of headers/footers, but how can I put things that are record-related into the footer? I haven't really explored that possibility but it doesn't seem right to me, for the reasons I gave in post #14.

3. I tried adding code like this to the Form_Resize event:

Section(acDetail).Height = InsideHeight - Section(acHeader).Height - Section(acFooter).Height - 300

That triggers error 2100 when the height gets too small (and so the record doesn't change).
If you can find a way of handling that error in a 'positive way' you may have another solution

Not sure if that is going to help. I agree with your sentiment - ie if the height gets too low then do something, but what can be done? I bet if you move your mouse fast enough, the InsideHeight property will become too low anyway.

Before you spend money on Peter's Shrinker/Stretcher, I recommend you first look at my resizing code which is free.

Sorry, I'm afraid I haven't still yet looked thoroughly at your offerings, but can you say that it caters for minimizing forms, for example?

 

[The_Doc_Man]

Here is a thought you might not wish to entertain, but if your users are causing chaos, you need to prevent them from causing chaos. If their ability to shrink is killing something, don't let them. Yes, I know that it sounds radical, but there comes a point where you have to say that a given situation is more than you are willing to handle. Bending over backwards to handle a bunch of demanding, unwise users will give you back trouble. You want to prevent someone from shrinking the form below a certain set of dimensions? Follow the advice in this article:

Prevent users from resizing an Access form | TechRepublic

Even though users can resize Microsoft Access form windows, there may be times when you want to stop them from modifying the form. Learn how to do so in five simple steps.

www.techrepublic.com

According to the notes, minimizing a form leaves it open so it should retain location context.

DoCmd.Minimize method (Access)

Office VBA reference topic

learn.microsoft.com

 

[isladogs]

Sorry, I'm afraid I haven't still yet looked thoroughly at your offerings, but can you say that it caters for minimizing forms, for example?

As you stated in post #14, minimizing the form only causes the unload/requery due to your use of code setting the height of the detail section

I do have my own (more complex) code to adjust the size of each section so I thought I'd check (though this really isn't the primary purpose of my AFR example app...). Normally people wouldn't be minimizing forms
I've just tested on 2 different forms with a header/footer & subform (+lots of other controls).
In each case the subform is a datasheet
I added navigation buttons to the subforms moved away from record 1 then resized in different ways - minimizing & shrinking

I can confirm that in both cases shrinking the form drastically or minimizing the form did NOT cause the subform to unload/requery the records.
Two minor caveats with the shrink/stretch version of the form:
a) It is designed to maintain the aspect ratio so there is a limit to how far I can reduce it without it becoming unusable
b) Using the Restore Form Size button on the form closes the form & reloads it in its original dimensions ... so obviously the subform is also reloaded at record 1

I also tested on this form used to demonstrate datasheet resizing /& zoom:

Normal size datasheet

Zoomed datasheet:

Minimizing the form again had no effect on the subform record selected
Shrinking the form height vertically as in your tests was fine as long as part of the detail was still visible e.g. this was OK

Even this doesn't cause the subform to be unloaded

However, if the detail section is shrunk to absolutely nothing, it unloads the subform as in your example

I have to agree with both Pat & Doc in saying that I think you should stop worrying about this as being an unimportant issue.
I'm sure there are bigger concerns you should devote your time to in your development work