Posts Tagged ‘Microsoft Access VBA’

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.

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: , , , , , ,
Posted in Uncategorized | No Comments »

Microsoft Access History File

Tuesday, July 13th, 2010

There are a number of approaches to recording history in Microsoft Access.  We have recently been looking at how to apply them to an existing application.  The goal is to record who made what changes and when they made them.  Sort of an audit facility for Access.  Basically you want to capture:

  • New records
  • Changes to existing records
  • Deletion of records

It might seem a waste of time to think of it as three separate activities but you need to handle them differently for recording history.

  • New records only have an ‘after’ record.
  • Changes have a ‘before’ and ‘after’
  • Deletions only have a ‘before’

Here are the three main approaches.

  1. Use the OldValue and Value  of the control triggered by a BeforeUpdate event.  This works where you are adding or updating a record.  Use the OnDelete event to capture deletions.
  2. Create a temporary table of values before you carry out any changes, then compare it with values in the table after update.  Write the differences to a history file.  This is good where you are doing a bulk update.  For example, if you have a price list, and are updating all prices from a particular supplier by x%.  You create a copy of the price table before changes, then compare it with the table after update.  Write the differences to a history file.
  3. Create custom writes to a history table for particular events.  An example may be where you are creating a copy of a record such as a user profile.  You copy all the values of an existing person to a new person record.  Once established, you might make modifications to the person record.  In this case you create a specific SQL statement to write the new record details to your history file.  Another example might be where you are updating some system parameters that are held in the registry.  They are not in Access so you need to capture the current registry entry, the changes, and write them to a history file.

Another consideration is that history files can get big.  Given you are capturing everything from integers to memo fields, the first instinct is to cater for the biggest.  You make the old value and new value fields memos.  Unfortunately you take up the room for a memo in Access.  If there is one or five thousand characters, the space taken up is the same.  The trick is to have two history tables.  One has old and new values as text and the other as memo.  Run code to check the length of the old and new values, and if too big, write to a the table with the memo field.  For reporting purposes, you can combine the two.

In subsequent blogs, we will cover how to actually achieve what we are talking about here.  The purpose of this blog is to get a helicopter view of capturing history in Access.  Once you understand the terrain, you can better understand the wealth of information available from other resources.

Tags: , , , , ,
Posted in Uncategorized | No Comments »

Adding Bullets in Microsoft Access

Monday, May 17th, 2010

Ever wanted to add bullets in Microsoft Access 2003?  I have usually inserted a dash, but playing around recently found I could create a button to insert a bullet.  Here is the solution.

Assume you have a textbox called txtInfo. You need to track where the cursor is in the textbox.  To do this create a place to record the position.

Dim intCursorPosition As Integer

In the On Click and On Keydown events for the textbox do the following.

Private Sub txtInfo_Click()
intCursorPosition = Me.txtInfo.SelStart
End Sub

Private Sub txtInfo_KeyDown(KeyCode As Integer, Shift As Integer)
intCursorPosition = Me.txtInfo.SelStart
End Sub

Put a button on the form beside the textbox.  Call it btnBullet  Post the following code into the On Click event

Private Sub btnBullet_Click()

Me.txtInfo.SetFocus
Me.txtInfo = Left(Me.txtInfo, intCursorPosition) & vbCrLf & Chr(149) & “  ” & Mid(Me.txtInfo, intCursorPosition + 1, Me.txtInfo.SelLength – intCursorPosition)
Me.txtInfo.SetFocus
Me.txtInfo.SelStart = intCursorPosition + 5

End Sub

How it works

The On Click and Keydown events will have told you where the cursor was located prior to the button being selected.  That information is stored in intCursorPosition.  The first thing that needs to happen is to return the focus to the textbox.

You now construct the contents of the textbox by taking the text to the left of the cursor position, adding a return, adding Chr(149) which is a bullet, adding two spaces, and finally adding the text to the right of the cursor position.  The rest of the code is to put the cursor after the bullet and two blank spaces.

Tags: , , ,
Posted in Microsoft Access Development | 1 Comment »

Microsoft Access Help Function

Friday, April 30th, 2010

Traditionally if you want to create help for an application, you would use Microsoft Access help file. Each field needs to be set up to display a section of a Microsoft help file. We have created a different approach. The help is contained in a table within your application and uses native Access functionality to display. There is no separate help file. This means:
- You don’t need separate tools to create a help file
- There is no separate help file to create and maintain
- You can allow users to update the files themselves if you wish
To illustrate the help function, we have created a sample database. To download click here. The web page also provides information on how to create it within your own application.

Tags: , , , ,
Posted in Uncategorized | No Comments »

Microsoft Access – Creating an expanded text box

Wednesday, April 22nd, 2009

We can assist with your development of Microsoft Access applications however if you are doing the work yourself, we have a number of tips and examples to help.

In our Project Administrator Project Management Software, we use an expanded text box on some fields.  Where the amount of text is likely to be larger than is displayed in the field, you can right click and a box will be displayed that enables you to read, enter or edit text.  Close the box and the form is updated.

To see the code to create an expanded text box click here.

Tags: , ,
Posted in Microsoft Access Development | 2 Comments »