Archive for the ‘Microsoft Access Development’ Category

More Microsoft Access Button Tricks

Saturday, June 11th, 2011

We already showed Microsoft Access programmers to use images behind buttons in a previous post.  Here is another trick for button.

You can use an image or label for a button.  This technique gives you the ability to give a “pushed in” appearance to Microsoft Access buttons.

The following example shows two labels.  It is simpler than showing two images.  When you click the left hand button (which is actually a label) it appears sunken and the caption is changed to “On”.  It will look like the right hand image.

Microsoft Access button using a label

Using a label for a sunken or raised effect

As a Microsoft Access programmer, this is not complex.  I will not cover the code to make the button do what you want, but only the code to show how to change the appearance.

Private Sub lblButton_Click()

If Me.lblButton.Caption = “Off” Then    ‘ Turning it on
With Me!lblButton
.Caption = “On”                   ‘ Change the caption
.SpecialEffect = 2                ‘ Make it sunken
End With
With Me.lblButton                   ‘ Turning it off
.Caption = “Off”                  ‘ Change the caption
.SpecialEffect = 1                ‘ Make it raised
End With
End If

End Sub

You are using the label’s caption to decide what code to run.  If the caption is currently “Off” you are obviously trying to turn it on.  If it is not “Off” it must be on so you are turning it off.  The SpecialEffect is 1 for raised and 2 for sunken.

An Access programmer can add code into the “if” statement.  For example it may be to display a part of the form or hide it.  In the “On” part of the if statement you might add something like

Me.subDetails.Visible = True

In the  “Off” part you add

Me.subDetails.Visible = False

Screen design and appearance can make a lot of difference in the acceptance a programmer receives when a new application is presented.  Simple things like the way buttons appear and work can be the difference between acceptance and rejection.  If you are doing Microsoft Access programming, take the time to add the finishing touches.

Microsoft Access Buttons – Using images

Tuesday, June 7th, 2011

Let’s face it.  Microsoft Access buttons are boring.  As a Microsoft Access programmer, we tend to accept the simple solution and use a wizard to create a button.  If we use an image it is usually just the standard icon from Access.  Here is another technique that can make your Microsoft Access application more attractive.

If you have a button that works just fine, this is a simple solution.  You can add a custom image to a button, but there are size limitations.  You often get the message “Microsoft Access doesn’t support the format of the file …. or file is too large.  Try converting the file to .BMP or .GIF format.”  The solution is easy.  Make the image the same size as the button.  Add the image to the form, and put the button on top of it.  Change the button’s Transparent value to Yes.  Make sure the button is brought to the front (Format, Bring to Front) and you have an attractive button.

If you want to make the clickable area a bit bigger than the image, you can do it by making the button bigger.  This is a help for inaccurate mouse clickers.

You could of course use the “On Click” event for the image to undertake whatever action is required.  On the other hand, many applications already have a button and the visual appearance can be enhanced by adding a few images.  Another reason for Microsoft Access programmers to use a button is that you can use the wizard to create the button.

Below is an example from a new product we are developing for recording meeting agendas, minutes and action items.  This is the switchboard.  We created buttons starting with a background from a Visio shape and fill.  Using Fireworks, we add a layer for each text item.

Technique for using images in Microsoft Access buttons

Transparent Microsoft Access buttons over an image

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. Adding a field to a linked table

Friday, January 28th, 2011

Ever wanted to add a field to a linked Access database table from the frontend?  Perhaps you have copies of your software running in different environments and want to release a new frontend but not have to manually update every backend.  You can use VBA to create a field.  You need to import the backend table into the frontend, add the field, then export it to the backend database.  After that you need to relink the table.

To see the code click here. You can cut and paste the code into your own module, link a table and use the intermediate window to create a new field.

If you would like to add some comments or suggestions, leave a comment below.

Point of Sale system in Microsoft Access

Friday, December 17th, 2010

We have done some strange Access development but this must be one of the most unusual. We helped a sandwich shop in Tokyo develop a Point of Sale system in Access. Just to prove it really happened, they sent us the before and after pictures.
This is what happened in the past. All the orders were handled manually.

POS system in Microsoft Access

This is the screen displaying each order, the quantity of sandwiches ordered and made for the day and delivery schedule.

POS System in Microsoft Access

The POS system also calculates total cost, generates invoices and lots more.  If you are ever in Tokyo to “The Earl” sandwich shop and see Microsoft Access POS system in action.