Microsoft Access Database: Access VBA Database Refresh or Requery?

Microsoft Access Database: Access VBA Database Refresh or Requery?

access vba database

 

One of the quickest ways when using an Access VBA database and is also a shortcut to relisting and rebuilding data lists and control values is to call the Refresh or Requery command using the Me object which is very handy indeed.

 

Take a look at the following article…

Microsoft Access – Refresh Or Requery?

When using Microsoft Access, many novice developers have difficulty understanding the difference between Me.Refresh and Me.Requery. So, let’s take a look at the differences between the two.

Here’s the short version:

Both Refresh and Requery save any changes made to the current record, however…

Me.Requery pulls all new data from the underlying table or query, including new additions and deletions;

Me.Refresh only updates the records in the current form, but will not show new or deleted records.

Here’s the long version:

Me.Requery forces the entire recordset (underlying data) for the form to reload. This means ALL of the records in your current form will reload. Your current position will be lost, so if you’re sitting on record 10 of 100, you’ll find yourself back on the first record. Me.Requery is essentially the same as closing and reopening the form. Any new records added by other concurrent users will be available. Likewise any records that have been deleted will disappear. Requery essentially “re-runs the query” that pulled the data into the form in the first place. You can also use requery to update the data in a list box or combo box.

Me.Refresh saves the current record that you’re working on. It will also retrieve any changes (but not additions or deletions) to any records shown in the current form. Any calculations on the form (unbound fields) are recalculated. Refresh does NOT reload the recordset. You do not lose your position in the form (you stay on the current record). Any new records added by other users will not be shown.

If you want to open another form or, especially, a report that contains the data on the current form, you need to issue a Me.Refresh command. I do this in my tutorial when we create the invoice based on the current order. You need a Me.Refresh to save the data to the table so that it will print correctly. You couldn’t use Requery because it will put you back on record 1 of the recordset, which might not be what you want.

There are also two other commands that get confused with refresh and requery a lot: Repaint and Recalc.

The Me.Repaint command simply redraws the current form and all of its controls on the screen. This is especially useful when you’re running a form with timers and long event loops and you want to force something on the screen (perhaps a counter) to update as the event is running, so the user doesn’t just sit there looking at nothing happening. Repaint doesn’t effect data.

The Me.Recalc command forces all of the calculated controls on the form to be reevaluated. For example, if you’re just showing Items * UnitCost in a text box, and it’s not updating, you could use Me.Recalc to force it to update. I’ve never honestly HAD to use Recalc before. Access is pretty good about recalculating automatically.

In my tutorials, I cover the Refresh command in a lot of different places. I start by showing you the Refresh macro command in Access 204. We then see it as a VB command in Access 302. I cover Requery in Access 206, 306 and 307. As of right now, I haven’t had to use Repaint or Recalc in any of my tutorials. Again, they’re very uncommon, but they’re available if you need them.

Richard Rost is President of 599CD Computer Training and Microsoft Access Tutorial.

Article Source: http://EzineArticles.com/?expert=Richard_Rost

Microsoft Access Database: Access VBA Database Refresh or Requery?

Learning Access VBA database programming is a powerful and efficient way to automate your database application. The learning curve is not as steep as you may think and with some effort and time, the language syntax can be mastered quicker than expected.

I use VBA on my utilities which is open for users to modify and edit accordingly. Take a look at some of my products that are available.

This entry was posted in Forms, Modules, MS Access, VBA and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.