Refreshing main and sub forms after pop up form closes 2

[Moss100]

Hello,

I have got my program functioning but wonder whether there is a better way of doing the following:

I have a main form 'MAIN' with a sub form 'SUB'. (linked by master and child fields ref and link_ref.

I modify the data in the subform by opening a popup form 'POPUP'

When the pop up closes I want the MAIN and SUB to update.

At present I am using the on close event as follows:

forms!main.refresh

This works OK, but seems to flcker quite a lot.

I wonder if there are better ways to do this and whether using the onclose event is the correct one?

I also see that requery and recalc commands perform a similar way - are any of these better?

I note that the code forms!main.refresh works and also forms!main!refresh works. Which is correct and why - what differnce does the ! or . make?

Maybe my approach is incorrect?

Alot of questions but hopefully someone can help - I can't face many more 4 am sessions!!

Thanks Mark

 

[MajP]

http://www.projectperfect.com.au/blog/?p=425

Microsoft Access Requery, Recalc, Refresh and Repaint
Access has four methods that seem to be similar and often get new VBA users confused. Here is what each of the methods does, and how they are different.

Requery

This is the most powerful. It goes to the database and runs the underlying query again. For example, if you had a form which was used to add people. On that form you had a combo box that listed people. Now you open the form and you have 10 people in your table. You use the form to add another person. If you go to the combo, it will still only show 10 people. The 10 who were there when the form was opened. If you use the Got Focus to fire off a requery on the combo, you will have 11 people.

Recalc

Recalc does not get new records. It processes any pending screen changes. Here is an example. You have a unit price, and quantity field. There is a textbox that has a formula that gives a total price (e.g. = me.txtUnitPrice * me.txtQty). If you use recalc on the Lost Focus event of both quantity and price textboxes, you will recalculate the total price. It will not do anything to the underlying records.

Refresh

Refresh is sort of like requery except that it only updates the records on the screen. If someone has added another record in a multi user environment, it will not get display the new record. The good thing about refresh is that it leaves the cursor where it was. If you have a datasheet, and you are on the third row, use requery and you are back to the first row. Use refresh and you are still on the third row.

I have a particular application that has a subform which is a datasheet. Each record has a sequence number. On the main form, I have buttons to move records up or down. I select a record on the subform, then use the button on the main form to move it down. What happens is that it grabs the sequence number, finds the next in sequence and gives it a new sequence number (existing number -1) and gives the selected record a new sequence number (existing number + 1). I now need to resort them so can use refresh which leaves the cursor on the selected record. If I used requery, it would be back at the start. If I wanted to move down twice, I would have to find the record and select it again with requery.

Repaint

Finally we come to repaint. This is used to refresh the screen without interacting with the database. Where do you use repaint? Say you have a label “Update Processing” to warn users that a lengthy process is happening. If you set the visible property to false when you open the form, you can set it to true at the start of the update procedure. At the end you set it back to false. To make the screen display the change to the label use repaint after the change to visible.

It can be confusing and cause considerable frustration when you use a shotgun approach to the four terms. I hope this will make it clearer to people which method to use in which situation.

Tags: Microsoft Access Developer, Microsoft Access Development, Microsoft Access Help, Microsoft Access VBA, recalc, refresh, requery

 

[Moss100]

Thank you for your help.

Can anyone tell me whether, before opening the pop up I should perform a save command and / or when closing the pop up I should perform a save command. Would this be desirable or unnecessary?

Also can some one explain the difference between: forms!MAIN.requery and forms!MAIN!requery

Many thanks Mark

 

[MajP]

When you close a form or move to the next record all data is saved.

There is no such notation as forms!MAIN!requery. Requery is method not an item in a collection.

 

[dhookom]

If the POPUP is opened acDialog, it might change data on the previous form, and you want to refresh the records on the previous form, I would add requery or refresh code to the previous form following the open of the POPUP form.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

If the POPUP is opened acDialog, it might change data on the previous form, and you want to refresh the records on the previous form, I would add requery or refresh code to the previous form following the open of the POPUP form. "

Yes this is correct - it does change the data on the subform which is on the main form.

At present I am requerying the main form on the close event of the popup form.

Is this the best way??

Thanks

Mark

 

[MajP]

In a pure design sense this is not a good idea, because you create a dependancy in code from your pop up to main form. This limits the utility of your pop up. You want to develop popup forms so that they can be possibly called from many forms and locations. When you pop open a form in dialog, code stops in the calling form, and does not return until the dialog form closes. So the normal trick is

Docmd.openform "someform"...,acdialog
'The pop up form opens and code stops executing at this point
'Once the pop up form closes code resumes here.
'Since you did not add new records you can use refresh to update the visible records
'and ensure that you stay on the current record
me.refresh
'if you want to use requery then you would need additional code to return you to the record
'you were working on

Refresh is sort of like requery except that it only updates the records on the screen. If someone has added another record in a multi user environment, it will not get display the new record. The good thing about refresh is that it leaves the cursor where it was. If you have a datasheet, and you are on the third row, use requery and you are back to the first row. Use refresh and you are still on the third row

 

[dhookom]

I totally agree with MajP about the "dependency". Try to build your objects with as little dependency as possible. You can get into real messes if you don't.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thanks for your help here -

Docmd.openform "someform"...,acdialog
'The pop up form opens and code stops executing at this point
'Once the pop up form closes code resumes here.
Since you did not add new records you can use refresh to update the visible records'and ensure that you stay on the current record
me.refresh

The thing is though I AM adding new records to the subforms table. When my pop up form opens I set the ref to that of the main form and then add a record in the table that the subform also shares.

When I close the pop up form, the subform needs to be requeried to show the record added. I have found that by requerying the main form this also requeries the sub form.

So it works, but I'm thinking there must be a smoother or better way???

Thanks guys.

 

[MajP]

So like I said control it from the calling form. If the calling form is the main form, but you need to requery the subform

docmd.openform "popupform"...acdialog
'open form and wait for control to return
me.subformcontrolname.form.requery

 

[Moss100]

Oh I get it now - I didn't click when you said the code stops when the pop up opens


Ill try that tomorrow. Many thanks.