What is the VBA syntax for Refreshing the form in the After Update event of one of its controls ?

Hi All,,,

Poor me !"in VBA"....lol.

What is the VBA syntax for Refreshing the form in the After Update event of one of its controls ?
Issa S.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
You would not normally need to "refresh" a form after updating a single control, that control would normally display the value you entered.

However, if you are dealing with some form of cascading controls (where one list or combo box depends on the value of another), then you might try something like:

Private Sub cboOne_AfterUpdate

    me.cbo_Two.Requery

    'if you have more than one other control that is dependent, or cbo_Three 
    'is dependent upon cbo_Two then you would also need to requery that control
    me.cbo_Three.Requery

End Sub

Open in new window

Then you would requery cbo_Three in the AfterUpdate of cbo_Two.

HTH
Dale
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
IF ... you attempt to do this in the AU of a Control ... and the Form is Dirty (in edit mode) ... Access will at that moment attempt to Save your record - which is likely NOT desired at that moment.

Instead ... if necessary ... use the Form AfterUpdate event to do this ...
either
Me.Requery
or
Me.Refresh

depending on what you are trying to accomplish.
Issa S.Author Commented:
Hi Dale,,,

That is to say: there is nothing like "Refresh Form".   Correct Me Pls.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
There are two options:

Me.Refresh  ' Refreshes the current existing dataset - but no new records other may have added

Me.Requery ' Refreshes entire recordsource, including any new records that may have been added.

That's it :-)
Dale FyeOwner, Dev-Soln LLCCommented:
As Joe indicates, you could use me.Refresh  but all that would refresh the recordset and overwrite your changes.

If you want to write that changed value to the table, then you need:

Me.Dirty = False

The Dirty property of the form gets set to True when the value of any control on the current record changes.  To actually write that data to the table, you must save the record  which can be done in a variety of ways:

1.  move off the record
2.  Docmd.RunCommand acCmdSave
3.  Me.Dirty = false

#1 occurs automatically if you move off the current record or click on a control that is in a parent or subform.

HTH
Dale
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"but all that would refresh the recordset and overwrite your changes."
Not after the record has been saved ...
Dale FyeOwner, Dev-Soln LLCCommented:
Joe,

The OP didn't say the record was saved.  the question was (emphasis mine):

"What is the VBA syntax for Refreshing the form in the After Update event of one of its controls ? "
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well sure I get that ... but I went beyond the 'control' aspect ... in that such does not make much sense.
Guess we don't know for sure what the OP intends :-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
Joe,

Hope you are having a great day, buddy.

That was why I actually brought up the point of needing to save the current record, before doing the refresh.  I can understand that if there is potential for a multi-user environment for a recordset to get dirtied by another user, where the user might want to refresh the forms recordset, but not sure why they would want to after updating a single control.

I have seen applications where the developer committed the record after any control is updated, but I'm against such overkill as it provides no method to allow the user to undo changes to the current record.

Guess we will know what the OP had in mind when he/she checks back in.

Dale
Issa S.Author Commented:
Lovely Joe & Dale.

Although I read of a lot about these thing but I never understood these differences as clear as from you now.

Many Many Thanks
Issa S.Author Commented:
Very Helpfullllll
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.