Posts Tagged ‘Microsoft Access Help’

Microsoft Access Tips for VBA Developers

Friday, May 27th, 2011

Microsoft Access developers often use the same code to perform functions in many procedures. One recurring task is to find if a form exists, or a report exists or if the form or report is loaded. Here are a suite of Microsoft Access functions that will help you check for the existance of forms and reports. There are five functions.

  • Check if a form is loaded
  • Check if a table exists
  • Does a report exist
  • Does a field exist in a table
  • Does an external file exist

Read more about them on our latest Microsoft Access Database Development page

Microsoft Access Requery, Recalc, Refresh and Repaint

Tuesday, August 17th, 2010

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.


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 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 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.


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.