Here are some code snipits from the Visual Basic project.

Phone Mask
Position Search Function and SQL Statement
Related Applicant Skills Function and SQL Statement

Phone Mask Code (Applicant Form and Phone Mask Module)

Applicant Form

Private Sub txtPhone_KeyDown(KeyCode As Integer, Shift As Integer)
'Blocks the effect of the delete key stroke
If KeyCode = vbKeyDelete Then
KeyCode = "0"
End If

End Sub

Private Sub txtPhone_KeyPress(KeyAscii As Integer)
'Calls an method in a module(modPhoneMask)
Call PhoneMask(KeyAscii, txtPhone)

End Sub

Phone Mask Module

Public Sub PhoneMask(KeyAscii As Integer, TextBoxName As TextBox)

Dim iCount As Integer

'KeyAscii "8" is the code for backspace. This code is only used if backspace key is hit.
If KeyAscii = "8" Then

'Removes the effect of the backspace key
KeyAscii = 0 'is equal to null
'Generates the masks response to the backspace key
For iCount = 0 To 13
'Finds the last number entered in the phone mask
If IsNumeric(Mid(TextBoxName.Text, 14 - iCount, 1)) Then
'Selects and replaces the last number with an under score
TextBoxName.SelStart = 13 - iCount
TextBoxName.SelLength = 1 ' highlights 1 char
TextBoxName.SelText = "_" '
TextBoxName.SelStart = 13 - iCount
Exit Sub
End If
Next
End If

'Blocks the use of letters and other keys except backspace which is dealt with in the script above.

If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Or InStr(TextBoxName.Text, "_") = 0 Then
KeyAscii = 0 'i.e. don't allow anything to happen
Beep
Exit Sub
End If

Dim X As Integer

'If a number has been entered it is added to the mask
X = InStr(TextBoxName.Text, "_") ' Finds the 1st underscore
TextBoxName.SelStart = X - 1 'Place cursor before the first underscore
TextBoxName.SelLength = Len("1") ' Selects the first underscore
'Since the key stroke is not modified it is placed over the highlighted underscore

End Sub
<return to the top>

Position Search Function and SQL Statement

Public Function PositionSearch(ID As Long) As Variant

Dim strSQL As String

strSQL = "SELECT tblPosition.PositionTitle, tblPosition.PositionID " & _
                "FROM tblPosition, tblJunctionSkillPosition " & _
                "WHERE tblPosition.PositionID = tblJunctionSkillPosition.PositionID " & _
                "AND tblJunctionSkillPosition.SkillID In (SELECT SkillID " & _    'Embeded Select statement
                "FROM tblJunctionSkillApplicant WHERE ApplicantID = " & ID & _
                ") GROUP BY tblPosition.PositionTitle, tblPosition.PositionID " & _
                "ORDER BY Count(tblJunctionSkillPosition.SkilliD) DESC"

'Used to check for errors in the SQL statement
Debug.Print strSQL

'Connect to database
mDataAccess.Connect

'Retrieve data
PositionSearch = mDataAccess.RetrieveData(strSQL)

'Disconnect from database
mDataAccess.DisConnect

End Function
<return to the top>

Related Applicant Skills Function and SQL Statement

Public Function RelevantAppSkills(ID As Long) As Variant

Dim strSQL As String

strSQL = "SELECT tblApplicant.ApplicantID, tblJunctionSkillApplicant.SkillID, tblSkill.Name " & _
"FROM tblApplicant, tblJunctionSkillApplicant, tblSkill " & _
"WHERE tblApplicant.ApplicantID=tblJunctionSkillApplicant.ApplicantID " & _
"AND tblSkill.SkillID=tblJunctionSkillApplicant.SkillID " & _
"AND tblJunctionSkillApplicant.SkillID IN " & _
"(SELECT SkillID FROM tblJunctionSkillPosition WHERE PositionID= " & ID & ") " & _
"ORDER BY tblApplicant.ApplicantID"

'Used to check for errors in the SQL statement
Debug.Print strSQL

'Connect to database
mDataAccess.Connect

'Retrieve data
PositionSearch = mDataAccess.RetrieveData(strSQL)

'Disconnect from database
mDataAccess.DisConnect

End Function

<return to the top>