Posts Tagged ‘Microsoft Access Development’

Microsoft Access Development Tip. Saving email hyperlinks in a table.

Monday, November 21st, 2011

A recent Microsoft Access development project caused us to look for a creative way to generate an email from Microsoft Access, save the email as a .MSG file, and create a hyperlink in an Access table.  We came across a piece of software called MessageSave which we have been able to integrate into the process.  The process is:

  • Microsoft Access opens a new email in Outlook.  Access then pauses.
  • MessageSave saves the email as a .MSG file and calls a VBScript
  • The VBScript writes a text file with the name of the saved .MSG file.
  • Microsoft Access resumes.  It reads the text file and saves the file location as a hyperlink in a table

We decided to make the sample code available as a download.  You can read all about the process used, see the sample code, and download a free Microsoft Access database from our website.  Click here for more information, and to download the free Microsoft Access database.

Microsoft Access function to calculate an end date for employee leave

Wednesday, September 28th, 2011

This is a generic function you can use to calculate the end date for employee leave given a start date and a number of days.  It allows you to calculate the end date taking into account holidays and weekends.
First set up a table to store holiday dates.  We called it tblHoliday and it had a field HolidayDate.  You might also want to add fields for the name of the holiday and a primary key (although the date is a unique field).
Next add a form with three text boxes (txtStartDate, txtEndDate, txtNoOfDays) and a button (btnCalc).  The code for the form is as follows.

Private Sub btnCalc_Click()
Dim dteCalcEndDate As Date                                      ‘ Date to be calculated

dteCalcEndDate = funCalcEndDate(Me.txtStartDate, Me.txtNoOfDays) ‘ Pass the start and number of days
Me.txtEndDate = dteCalcEndDate                                  ‘ Put the end date on the form

End Sub

Function funCalcEndDate(dteStart As Date, lngNoOfDays As Long) As Date
Dim dteTest As Date                                             ‘ Date to be tested
Dim intCounter As Integer                                       ‘ Count the number of days

dteTest = dteStart                                              ‘ Set it to the start date
intCounter = 0                                                  ‘ Count the number of valid days

Do While intCounter < lngNoOfDays                               ‘ Loop until you have found valid days
If Weekday(dteTest) > 1 And Weekday(dteTest) < 7 Then       ‘ 1 = Sun, 7 = Sat
‘ Test if a holiday is found in the holiday table
If IsNull(DLookup(“HolidayDate”, “tblHoliday”, “HolidayDate=#” & Format(dteTest, “mm/dd/yyyy”) & “#”)) Then
intCounter = intCounter + 1                         ‘ This is a valid date
funCalcEndDate = dteTest                            ‘ Update the function
End If
End If

dteTest = DateAdd(“d”, 1, dteTest)                          ‘ Select the next day
Loop

End Function

The key things in the code are the use of weekday.  In Microsoft Access, weekday is 1 to 7 starting on Sunday.  Sunday is 1 and Saturday is 7.  The other check is to the dLookUp to see if the date being tested exists in tblHoliday.  If it doesn’t it returns a null.

This function was originally created to check that the start and end dates corresponded to the number of leave days for staff.  You could use it for any calculation of days.  For example in a project if a task starts on a certain date, and takes so many days, when does it end?  In a purchasing system, when is a delivery due given a certain order day and leadtime for delivery.

For and against using Microsoft Access

Saturday, September 3rd, 2011

We are often asked if Access is the best solution and the answer has to be “it depends”.  Access has a bad name in some areas but that is often due to amaturish attempts to build a database.  At a superficial level Access is easy to use, but so is a gun.  The value of Access databases is best realised by having them professionally developed. A professional Microsoft Access development company will ensure you have a robust solution to meet your needs.  More importantly, they will tell you when Access is not the right solution.

There is a good sucinct article on the pros and cons of using Access at this web address.

 

 

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