Archive for the ‘Microsoft Access Development’ Category

Microsoft Access referring to controls on subforms

Friday, March 23rd, 2012

I recently replied to a question on a newsgroup and thought it was worth reproducing here.  The question was how do you refer to a text box on a form within a form within a form.  In other words, the main form has a subform and the subform has a subform.  To refer to a control that is on a sub sub form, you need to understand the following.

In the example there are three forms. frmMainForm, frmSubForm, frmSubSubForm

On frmMainForm is a subform control called subSubform Within that control the source object is frmSubForm. The thing to remember is that you refer to the control not the subForm within the control. You refer to Forms![frmMainForm]!subSubForm] not to Forms![frmMainForm]![frmSubform].

Think of it this way. If you only had one form, and you wanted to refer to the value in a textbox, you would refer to the textbox name, not the textbox data. If you had a textbox control called txtCustomer containing a table field name which might be tblCustomers.CustomerName, you would refer to the value as Me!txtCustomer rather than Me!CustomerName. Same with subForms. Refer to the control name, not the value (form name) within the control.

You have now referred to the control on the main form. The next step is to say what part of that control do you want to refer to? You want to refer to the Form part (Form object). You now add .Form to the path. Notice it is a dot rather than exclamation mark because it is not a field but a property. It could have been a property such as .Height or .Visible if you were referring to properties. If it was a control on the subform,you would have used “!” instead of “.”

Now you have made it to the subform, you want to get to the subsubform. Once again refer to the control on the subform which we will call subSubSubForm. We want to look at a control on the subsubform so we use .Form again and then the name of the control.

The full path is below.


Forms – Collection of forms in the database
![frmMainForm] – We are referring to a user created object so prefix with an exclamation mark. The form we are interested in is frmMainForm
![subSubform] – Refer to a control on frmMainForm which holds a subform called frmSubForm. We don’t need to mention the subForm name, only the control name
.Form – The form that sits within the control is now the focus
![subSubSubForm] – Find a control on the subform that contains the second subform
.Form – The value we are interested in the subsubform is the form object itself
![txtOnSubSubForm] – Finally we reach the control on the subsubform that we want to use.

Hope all this makes some sense. Just remember you only ever refer to the main form by name. The rest of the form names are irrelevant. It is the control names that matter.

Microsoft Access Phone Number Display

Thursday, January 5th, 2012

In a Microsoft Access database that is used for recording contact details, phone numbers are often stored in a string format with no spaces.  This enables checking if a number has been entered previously.  For example, a landline in Australia may have a two digit area code and an eight digit number.  If the area code is not used, the number is assumed to be in the calling area code.  If the number was 02 1234 5678 it may be stored as 0212345678 so it could be compared with numbers previously entered or analysis carried out to find all phone numbers starting with 02 which is the state of NSW.

Add to this the country code for some numbers (in Australia this is 61) and the fact that if you use the country code you drop the zero on the area code (61212345678) and you have a string that is difficult to dial correctly.  Complicating this is that there may be a number of phone numbers on the screen (business, personal, fax, mobile, direct).  How many people have never dialled a fax number when trying to contact a company?

One solution is to have a pop up screen and use a big font to display the number with logical spaces. I used Calibri 22 bold. The pop up appears if you double click the phone number.

To start, create a blank form. Only  the detail is visible.  I made it about 7cm wide by 3.6cm high.  Add a single text box called txtPhone.  It is 6.6cm wide and 1cm high.

On the original form in the phone number text box I put the following double click event

‘ Procedure : txtPhoneNumber_DblClick
‘ Author : Neville Turbit
‘ Date : 18/11/2011
‘ Purpose : Open the pop up phone number

Private Sub txtPhoneNumber_DblClick(Cancel As Integer)
Dim strFormName As String

On Error GoTo Error_txtPhoneNumber_DblClick

strFormName = “frmPhoneNumberPopUp”
subOpenForms strFormName, Nz(Me.txtPhoneNumber, ” “)

On Error GoTo 0
Exit Sub


MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: Form_frmContactsPhoneSub” & vbCrLf & _
“Type: VBA Document” & vbCrLf & _
“Calling Procedure: txtPhoneNumber_DblClick” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_txtPhoneNumber_DblClick

End Sub

You will notice I am using another function called subOpenForms. This is a function I use to open all forms as it has some error handling and better suits my needs. For some applications I incorporate security into the form opening. You can just use docmd.openform if you want.

‘ Procedure : subOpenForms
‘ Author : Neville Turbit
‘ Date : 04/06/09
‘ Purpose : This function is used in the Click event of command buttons that opens forms

Public Sub subOpenForms(strFormName As String, Optional strLinkCriteria As String, Optional strQuery As String, Optional strWhere As String)

On Error GoTo Error_subOpenForms

‘ Open specified form.
DoCmd.OpenForm strFormName, , strQuery, strWhere, , , strLinkCriteria

On Error GoTo 0
Exit Sub


MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: modGeneric” & vbCrLf & _
“Type: Module” & vbCrLf & _
“Calling Procedure: subOpenForms” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_subOpenForms

End Sub

On the pop up form I use the following code in the On Open event.

‘ Procedure : Form_Open
‘ Author : Neville Turbit
‘ Date : 18/11/2011
‘ Purpose : Display the phone number with spaces

Private Sub Form_Open(Cancel As Integer)
Dim strPhoneNo As String
Dim lenPhoneNo As Integer

On Error GoTo Error_Form_Open

strPhoneNo = Me.OpenArgs
lenPhoneNo = Len(strPhoneNo)

Select Case lenPhoneNo
Case Is = 8
Me.txtPhone = Left(strPhoneNo, 4) & ” ” & Right(strPhoneNo, 4)
Case Is = 10
If Left(strPhoneNo, 2) = “04” Then
Me.txtPhone = Left(strPhoneNo, 4) & ” ” & Mid(strPhoneNo, 5, 3) & ” ” & Right(strPhoneNo, 3)
Me.txtPhone = Left(strPhoneNo, 2) & ” ” & Mid(strPhoneNo, 3, 4) & ” ” & Right(strPhoneNo, 4)
End If
Case Else
Me.txtPhone = strPhoneNo
End Select

On Error GoTo 0
Exit Sub


MsgBox “An unexpected situation arose in your program.” & vbCrLf & _
“Please write down the following details:” & vbCrLf & vbCrLf & _
“Module Name: Form_frmPhoneNumberPopUp” & vbCrLf & _
“Type: VBA Document” & vbCrLf & _
“Calling Procedure: Form_Open” & vbCrLf & _
“Error Number: ” & Err.Number & vbCrLf & _
“Error Description: ” & Err.Description

Resume Exit_Form_Open

End Sub

You will need to adjust this for your own phone number formats but it should not be too difficult. Basically you find the number of characters to see what sort of phone number you are dealing with and then reconstruct the phone number with spaces.

When I first introduced it to a company the response was incredible.  It was all people talked about when I asked them about the latest changes.  It made their life much easier.  In terms of return for investment, half an hours work got much more favourable response than days on other parts of the system.  Think about using it in your applications.

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

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.

Why use Microsoft Access

Wednesday, September 28th, 2011

Came across a good balanced article on the benefits of using Microsoft Access.  If you are considering Access you should read this article.  Click Here.  The key message is that 95% of Access programs never develop any further.  Just like spreadsheets they either address a short term need (maybe a few years), automate a niche function, or address a problem that does not require a fully blown IT development project.

One quote I liked was “Empowerment: Ability to create what you really want without going through someone else (people don’t use other people to write documents or create spreadsheets any more)”