Refresh an unbound form

[patjones]

Hi:

There are many posts both here and on other forums about this topic, but nothing quite matching what I have...

I have an unbound form called frmChecks which displays information by means of an ADO recordset. Whenever I search for something, I have a globally defined subroutine, UpdateForm, that uses a SQL string to pull information into an ADO recordset and display the data on the form.

I also have a form called frmModify which exists for the purpose of editing the data on frmChecks. It opens up with the same information that is in frmChecks. The user can make any changes they want, click a "Save" command button (which causes the UpdateForm subroutine to run), and frmModify closes to take the user back to frmChecks.

The problem is that the changes don't appear on frmChecks. The bizarre thing is that when I breakpoint my code and step through it to see what the problem is, it all works fine...frmModify closes and frmChecks appears with the correctly modified data. It's only when I try to do this under normal execution that it doesn't work properly.

Why would it function correctly in one circumstance and not the other? I can post code if need be. Thank you...

[RuralGuy]

I need to start by asking why frmChecks is unbound?

[Stewart Ross]

Hi. Access uses a fairly slack approach to screen refresh, so your form update sub is likely to need to repaint the screen (using the form's repaint method). Repaint updates the screen, not the underlying recordset - but as you have updated that yourself repaint should then force the changes made to be reflected on screen.

-Stewart

Remarks
Microsoft Access sometimes waits to complete pending screen updates until it finishes other tasks. With the Repaint method, you can force immediate repainting of the controls on the specified form. You can use the Repaint method:

* When you change values in a number of fields. Unless you force a repaint, Microsoft Access might not display the changes immediately, especially if other fields, such as those in an expression in a calculated control, depend on values in the changed fields.

* When you want to make sure that a form displays data in all of its fields. For example, fields containing OLE objects often don't display their data immediately after you open a form.

This method doesn't cause a requery of the database, nor does it show new or changed records in the form's underlying record source. You can use the Requery method to requery the source of data for the form or one of its controls.

[patjones]

Hi. Access uses a fairly slack approach to screen refresh, so your form update sub is likely to need to repaint the screen (using the form's repaint method). Repaint updates the screen, not the underlying recordset - but as you have updated that yourself repaint should then force the changes made to be reflected on screen.

-Stewart

I have actually used Repaint (or tried to use it) - to no avail. In fact, I can't think of a circumstance yet where Repaint has helped me in anything. Even minimizing and maximizing the Access window doesn't do anything, and refreshing the form doesn't do anything.

To address why the form is unbound...I don't have a very good answer to that. Maybe binding it to a recordset is the way to go, and I will look into that. I'm just still perplexed about the current behavior; why does the program behave one way when I'm stepping through the code, as opposed to another way when it's executing normally?

[RuralGuy]

I'm just still perplexed about the current behavior; why does the program behave one way when I'm stepping through the code, as opposed to another way when it's executing normally?

That is almost certainly a timing issue. You might try some DoEvents in your code to give Access some cpu time to update the other form.

[patjones]

That is almost certainly a timing issue. You might try some DoEvents in your code to give Access some cpu time to update the other form.

That had occurred to me also, so I put a MsgBox line in the code right after the call to update the other form so I could wait a few seconds and see if that was indeed the problem, but it didn't matter.

What do you mean by DoEvents?

[missinglinq]

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause problems. An example would be a button that runs a series of monthly reports.

DoCmd.OpenReport "ReportA"
DoCmd.OpenReport "ReportB"
DoCmd.OpenReport "ReportC"
 

Access will attempt to immediately send all three to the printer, and this may possible eat up all the memory assigned to your printing queue, causing an error to occur. The answer is to use DoEvents.

DoCmd.OpenReport "ReportA"
DoEvents
DoCmd.OpenReport "ReportB"
DoEvents
DoCmd.OpenReport "ReportC"

DoEvents returns control to Windows, allowing ReportA to complete printing before starting to print ReportB. It then allows ReportB to finish printing before starting ReportC.

DoEvents is an easy, safe bet when encountering what may be timing issues. Stepping thru your code works because when you step thru your code because you're completing one "step" before starting the next.

Linq ;0)>

[patjones]

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause problems. An example would be a button that runs a series of monthly reports.

DoCmd.OpenReport "ReportA"
DoCmd.OpenReport "ReportB"
DoCmd.OpenReport "ReportC"
 

Access will attempt to immediately send all three to the printer, and this may possible eat up all the memory assigned to your printing queue, causing an error to occur. The answer is to use DoEvents.

DoCmd.OpenReport "ReportA"
DoEvents
DoCmd.OpenReport "ReportB"
DoEvents
DoCmd.OpenReport "ReportC"

DoEvents returns control to Windows, allowing ReportA to complete printing before starting to print ReportB. It then allows ReportB to finish printing before starting ReportC.

DoEvents is an easy, safe bet when encountering what may be timing issues. Stepping thru your code works because when you step thru your code because you're completing one "step" before starting the next.

Linq ;0)>

Thank you for that explanation; I understand what you are saying, and it gives me a plausible reason for why this may be happening. I'll try it out when I get to work tomorrow and let you know how it goes.

Pat

[patjones]

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause problems. An example would be a button that runs a series of monthly reports.

DoCmd.OpenReport "ReportA"
DoCmd.OpenReport "ReportB"
DoCmd.OpenReport "ReportC"
 

Access will attempt to immediately send all three to the printer, and this may possible eat up all the memory assigned to your printing queue, causing an error to occur. The answer is to use DoEvents.

DoCmd.OpenReport "ReportA"
DoEvents
DoCmd.OpenReport "ReportB"
DoEvents
DoCmd.OpenReport "ReportC"

DoEvents returns control to Windows, allowing ReportA to complete printing before starting to print ReportB. It then allows ReportB to finish printing before starting ReportC.

DoEvents is an easy, safe bet when encountering what may be timing issues. Stepping thru your code works because when you step thru your code because you're completing one "step" before starting the next.

Linq ;0)>

Good morning:

Unfortunately, the DoEvents strategy doesn't work. I actually inserted a DoEvents at a couple of different places in my code, to no avail.

What did work though, was putting the call to my UpdateForm subroutine in the Activate event for frmChecks. That ended up working perfectly.

Thanks so much for your help!