VBA code after DoCmd.Close acForm

[Miles R]

If have a need to reopen a form with a new dataset and then adjust items on the form when a certain button is clicked.



The code in the button event handler is :-

DoCmd.Close acForm, "Visual"
DoCmd.OpenForm "Visual", , , strFilter

This seems to work correctly, although probably not best practice.

My question is why does this work. You might have thought that after the Close line that is it, and the OpenForm line can't be executed as the form is closed.

[orange]

But the Form was closed before you opened it the first time, so the close is one event and a subsequent open is a separate event.
What exactly is the confusing part?

[Miles R]

But the Form was closed before you opened it the first time, so the close is one event and a subsequent open is a separate event.
What exactly is the confusing part?

I forgot to mention that the code is within the VBA of the Visual form. So, I would have thought that when the Visual form is closed (from within itself), all VBA associated with the form is also closed and can't run anymore. Maybe that is not the case.

[Micron]

so the close is one event and a subsequent open is a separate event.

That would not be my interpretation. The event is the button click. The commands are to close the form and reopen it and while those are events, they are not written as such. I would have thought this would not work since no further code ought to run when the form is closed.

I agree with Miles R; it is not good practice. Better to re-assign the form recordsource and requery, or since you seem to have a filter property value at that point, just apply it. That will automatically requery the form.

[Miles R]

That would not be my interpretation. The event is the button click. The commands are to close the form and reopen it and while those are events, they are not written as such. I would have thought this would not work since no further code ought to run when the form is closed.

I agree with Miles R; it is not good practice. Better to re-assign the form record-source and re-query, or since you seem to have a filter property value at that point, just apply it. That will automatically re-query the form.

Thanks for your replies everyone. Even though it probably should not work it does perfectly. Maybe it is a timing issue and before the form has closed itself, it has already started the second open.
I did try re-querying with the new filter, which also worked (a bit more slowly). However due to an Access 2007 bug there was a problem with this. The form is a single form with a vertical scroll bar. If I am at the bottom of the scroll bar when I click the button to reload, the Form Header is slightly corrupted - looks like the Form Header bottom edge has been overwritten with what was on the previous page. This won't go away until the form is closed and re-opened from scratch. Very unsightly.
So I am stuck with using an unorthodox method that does work cleanly.

I was just curious if anyone knew why this works.

[orange]

I agree
-it's probably not good practice,
-I probably should have thought more about calling the Form close as an Event vs a button click etc.

[Micron]

If I am at the bottom of the scroll bar when I click the button to reload, the Form Header is slightly corrupted

If you want to trouble shoot that, post a compacted and zipped db copy or maybe pics. Forms do have a page header IIRC but I've never used them and I've heard they can be wonky, and I've never treated a form as a multi page window or document for that reason. Perhaps you are using a page header. You could also try Me.Repaint. Since it's a single form, I think there's no need to worry about losing your spot like you would on a datasheet or continuous view.

[Miles R]

If you want to trouble shoot that, post a compacted and zipped db copy or maybe pics. Forms do have a page header IIRC but I've never used them and I've heard they can be wonky, and I've never treated a form as a multi page window or document for that reason. Perhaps you are using a page header. You could also try Me.Repaint. Since it's a single form, I think there's no need to worry about losing your spot like you would on a datasheet or continuous view.

Thanks for your reply. I tried Me.Repaint but that does not clear the error. It's not worth trying to fix this as I have a work around, so have abandoned the version of code that does a re-query. Still not had an answer as to why the form open works after the form that has issued the request has supposedly been closed.

Just for your amusement, I have attached a picture showing the error - very minor but unacceptable in a working application. The top image is how the top of the form should look with the black line being the border of the Form Header (blue area). Below the black line is the Form Detail. On the lower image, the header border has gone and is replaced with part of the Form Detail - clearly a bug as the Form Header should never be overwritten by the Form Detail, but not worth worrying about.

[isladogs]

Coming back to the original question, all the code in the button click event will run even if the form has closed.
I have used similar code on numerous occasions to reload a form and see no reason why it should be considered as bad practice.

I haven't looked at your later issue

[Miles R]

Thanks islagdogs. I guess that wraps things up. Looks like Access 2007 does not fully close the forms VBA whilst it is still executing code in an event. I like to see under the bonnet of Access to confirm that, just out of curiosity.
Don't worry about the other issue - its once of those flaky Access things like flickering forms that you can't get rid of sometimes.