codes

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then, add the following code to your form’s Current event:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast

Me![txtTotal] = Me.RecordsetClone.RecordCount

End Sub

===================================================================

The following Microsoft Access Visual Basic code demonstrates several different forms of field validation.

1) Checking if the date entered is after today’s date:

Private Sub Date_Entered_AfterUpdate()

If Me.Date_Entered>date() then        ‘ Date_Entered is form field name

msgbox “Please enter a date less than or equal to today’s date.”

Me.Date_Entered.setfocus      ‘ set cursor back in the date field

end if

End Sub

==============================================================

2) Check to see if the difference between two date fields is greater than 30 days.

Private Sub Date_Entered_AfterUpdate()

If datediff(‘d’, Me.Date_Received,Me.Date_Entered) > 30 then

msgbox “Warning: The date received is more than 30 days ” & _

“past Date Entered, please verify.”

end if

End Sub

================================================================

3) Check to see if a name already exists in a table.  This example demonstrates a highly compressed method of programming, which I hope you would never use (we don’t).

Private Sub Emp_Name_AfterUpdate()

If Nz(DLookup(“Emp_Name”, “Employees”, “Emp_Name=’” & _

Me.Emp_Name & “‘”), “zzzz”) <> “zzzz” Then MsgBox & _

“That name already exists in the employee table.”

‘ note that the two lines above actually need to be on one line to run

=====================================================================

CODES TO TRAP DUPLICATE ENTRY

 Put this codes in  the beforeupdate event

 Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

 '*********************************

'Code sample courtesy of srfreeman

'*********************************

    Dim SID As String

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value

    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber

    If DCount("strStudentNumber", "tblStudentDetails", _

              stLinkCriteria) > 0 Then

        'Undo duplicate entry

        Me.Undo

        'Message box warning of duplication

        MsgBox "Warning Student Number " _

             & SID & " has already been entered." _

             & vbCr & vbCr & "You will now been taken to the record.", _

               vbInformation, "Duplicate Information"

        'Go to record of original Student Number

        rsc.FindFirst stLinkCriteria

        Me.Bookmark = rsc.Bookmark

    End If

    Set rsc = Nothing

End Sub

Now when we attempt to enter a duplicate Student Number, we are presented with the following message box:
Warning message when attempting to enter a duplicate record.

When we click the OK, the duplicate record is undone, and we are taken to the Student that we tried to duplicate.

===========================================================

Before Update Event — Event Procedures Example

‘ this codes use to validate due_date or any other date validation

Private Sub Date_Due_BeforeUpdate(Cancel As Integer)

If Date_Due < Date + 15 Then

MsgBox ” Date is incorrect. Please reenter”, vbOKOnly + vbExclamation, “Error”

Cancel = True

Else

cancle = False

End If

End Sub

====================================================

KeyPress Event — Event Procedures Example

The following example converts text entered in a text box to uppercase as the text is typed in, one character at a time.

To try the example, add the following event procedure to a form that contains a text box named MI.

Private Sub MI_KeyPress(KeyAscii As Integer)

    Dim strCharacter As String



    ' Convert ANSI value to character string.

    strCharacter = Chr(KeyAscii)

    ' Convert character to upper case, then to ANSI value.

    KeyAscii = Asc(UCase(strCharacter))

End Sub

======================================================================

KeyPreview Property Example

In the following example, the KeyPreview property is set to True (–1) in the form’s Load event procedure. This causes the form to receive keyboard events before they are received by any control. The form KeyDown event then checks the KeyCode argument value to determine if the F2, F3, or F4 keys were pushed.

Private Sub Form_Load()

    Me.KeyPreview = True

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    Select Case KeyCode

        Case vbKeyF2

            ' Process F2 key events.

        Case vbKeyF3

            ' Process F3 key events.

        Case vbKeyF4

            ' Process F4 key events.

        Case Else

    End Select

End Sub

================================================================================

Codes to trap character entry

Private Sub IDNUMBER_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
‘ 65 To 90 and 97 To 122: These are all alphas, upper and lowercase
‘ 8 Backspace, 9 Tab Key, 32 Space Key
Case 1 To 64, 111 To 8, 9, 32
‘Case 65 To 90, 97 To 122, 8, 9, 32
Case Else
‘Setting KeyAscii to zero cancels the key input
KeyAscii = 1
MsgBox “Only numbers are allowed  for this entry!”, vbOKOnly + vbCritical, “System Message”

End Select
End Sub

codes

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then, add the following code to your form’s Current event:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast

Me![txtTotal] = Me.RecordsetClone.RecordCount

End Sub

===================================================================

The following Microsoft Access Visual Basic code demonstrates several different forms of field validation.

1) Checking if the date entered is after today’s date:

Private Sub Date_Entered_AfterUpdate()

If Me.Date_Entered>date() then        ‘ Date_Entered is form field name

msgbox “Please enter a date less than or equal to today’s date.”

Me.Date_Entered.setfocus      ‘ set cursor back in the date field

end if

End Sub

==============================================================

2) Check to see if the difference between two date fields is greater than 30 days.

Private Sub Date_Entered_AfterUpdate()

If datediff(‘d’, Me.Date_Received,Me.Date_Entered) > 30 then

msgbox “Warning: The date received is more than 30 days ” & _

“past Date Entered, please verify.”

end if

End Sub

================================================================

3) Check to see if a name already exists in a table.  This example demonstrates a highly compressed method of programming, which I hope you would never use (we don’t).

Private Sub Emp_Name_AfterUpdate()

If Nz(DLookup(“Emp_Name”, “Employees”, “Emp_Name=’” & _

Me.Emp_Name & “‘”), “zzzz”) <> “zzzz” Then MsgBox & _

“That name already exists in the employee table.”

‘ note that the two lines above actually need to be on one line to run

=====================================================================

CODES TO TRAP DUPLICATE ENTRY

 Put this codes in  the beforeupdate event

 Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

 '*********************************

'Code sample courtesy of srfreeman

'*********************************

    Dim SID As String

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value

    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber

    If DCount("strStudentNumber", "tblStudentDetails", _

              stLinkCriteria) > 0 Then

        'Undo duplicate entry

        Me.Undo

        'Message box warning of duplication

        MsgBox "Warning Student Number " _

             & SID & " has already been entered." _

             & vbCr & vbCr & "You will now been taken to the record.", _

               vbInformation, "Duplicate Information"

        'Go to record of original Student Number

        rsc.FindFirst stLinkCriteria

        Me.Bookmark = rsc.Bookmark

    End If

    Set rsc = Nothing

End Sub

Now when we attempt to enter a duplicate Student Number, we are presented with the following message box:
Warning message when attempting to enter a duplicate record.

When we click the OK, the duplicate record is undone, and we are taken to the Student that we tried to duplicate.

===========================================================

Before Update Event — Event Procedures Example

‘ this codes use to validate due_date or any other date validation

Private Sub Date_Due_BeforeUpdate(Cancel As Integer)

If Date_Due < Date + 15 Then

MsgBox ” Date is incorrect. Please reenter”, vbOKOnly + vbExclamation, “Error”

Cancel = True

Else

cancle = False

End If

End Sub

====================================================

KeyPress Event — Event Procedures Example

The following example converts text entered in a text box to uppercase as the text is typed in, one character at a time.

To try the example, add the following event procedure to a form that contains a text box named MI.

Private Sub MI_KeyPress(KeyAscii As Integer)

    Dim strCharacter As String



    ' Convert ANSI value to character string.

    strCharacter = Chr(KeyAscii)

    ' Convert character to upper case, then to ANSI value.

    KeyAscii = Asc(UCase(strCharacter))

End Sub

======================================================================

KeyPreview Property Example

In the following example, the KeyPreview property is set to True (–1) in the form’s Load event procedure. This causes the form to receive keyboard events before they are received by any control. The form KeyDown event then checks the KeyCode argument value to determine if the F2, F3, or F4 keys were pushed.

Private Sub Form_Load()

    Me.KeyPreview = True

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    Select Case KeyCode

        Case vbKeyF2

            ' Process F2 key events.

        Case vbKeyF3

            ' Process F3 key events.

        Case vbKeyF4

            ' Process F4 key events.

        Case Else

    End Select

End Sub

================================================================================

Codes to trap character entry

Private Sub IDNUMBER_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
‘ 65 To 90 and 97 To 122: These are all alphas, upper and lowercase
‘ 8 Backspace, 9 Tab Key, 32 Space Key
Case 1 To 64, 111 To 8, 9, 32
‘Case 65 To 90, 97 To 122, 8, 9, 32
Case Else
‘Setting KeyAscii to zero cancels the key input
KeyAscii = 1
MsgBox “Only numbers are allowed  for this entry!”, vbOKOnly + vbCritical, “System Message”

End Select
End Sub

codes

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then, add the following code to your form’s Current event:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast

Me![txtTotal] = Me.RecordsetClone.RecordCount

End Sub

===================================================================

The following Microsoft Access Visual Basic code demonstrates several different forms of field validation.

1) Checking if the date entered is after today’s date:

Private Sub Date_Entered_AfterUpdate()

If Me.Date_Entered>date() then        ‘ Date_Entered is form field name

msgbox “Please enter a date less than or equal to today’s date.”

Me.Date_Entered.setfocus      ‘ set cursor back in the date field

end if

End Sub

==============================================================

2) Check to see if the difference between two date fields is greater than 30 days.

Private Sub Date_Entered_AfterUpdate()

If datediff(‘d’, Me.Date_Received,Me.Date_Entered) > 30 then

msgbox “Warning: The date received is more than 30 days ” & _

“past Date Entered, please verify.”

end if

End Sub

================================================================

3) Check to see if a name already exists in a table.  This example demonstrates a highly compressed method of programming, which I hope you would never use (we don’t).

Private Sub Emp_Name_AfterUpdate()

If Nz(DLookup(“Emp_Name”, “Employees”, “Emp_Name=’” & _

Me.Emp_Name & “‘”), “zzzz”) <> “zzzz” Then MsgBox & _

“That name already exists in the employee table.”

‘ note that the two lines above actually need to be on one line to run

=====================================================================

CODES TO TRAP DUPLICATE ENTRY

 Put this codes in  the beforeupdate event

 Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

 '*********************************

'Code sample courtesy of srfreeman

'*********************************

    Dim SID As String

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value

    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber

    If DCount("strStudentNumber", "tblStudentDetails", _

              stLinkCriteria) > 0 Then

        'Undo duplicate entry

        Me.Undo

        'Message box warning of duplication

        MsgBox "Warning Student Number " _

             & SID & " has already been entered." _

             & vbCr & vbCr & "You will now been taken to the record.", _

               vbInformation, "Duplicate Information"

        'Go to record of original Student Number

        rsc.FindFirst stLinkCriteria

        Me.Bookmark = rsc.Bookmark

    End If

    Set rsc = Nothing

End Sub

Now when we attempt to enter a duplicate Student Number, we are presented with the following message box:
Warning message when attempting to enter a duplicate record.

When we click the OK, the duplicate record is undone, and we are taken to the Student that we tried to duplicate.

===========================================================

Before Update Event — Event Procedures Example

‘ this codes use to validate due_date or any other date validation

Private Sub Date_Due_BeforeUpdate(Cancel As Integer)

If Date_Due < Date + 15 Then

MsgBox ” Date is incorrect. Please reenter”, vbOKOnly + vbExclamation, “Error”

Cancel = True

Else

cancle = False

End If

End Sub

====================================================

KeyPress Event — Event Procedures Example

The following example converts text entered in a text box to uppercase as the text is typed in, one character at a time.

To try the example, add the following event procedure to a form that contains a text box named MI.

Private Sub MI_KeyPress(KeyAscii As Integer)

    Dim strCharacter As String



    ' Convert ANSI value to character string.

    strCharacter = Chr(KeyAscii)

    ' Convert character to upper case, then to ANSI value.

    KeyAscii = Asc(UCase(strCharacter))

End Sub

======================================================================

KeyPreview Property Example

In the following example, the KeyPreview property is set to True (–1) in the form’s Load event procedure. This causes the form to receive keyboard events before they are received by any control. The form KeyDown event then checks the KeyCode argument value to determine if the F2, F3, or F4 keys were pushed.

Private Sub Form_Load()

    Me.KeyPreview = True

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    Select Case KeyCode

        Case vbKeyF2

            ' Process F2 key events.

        Case vbKeyF3

            ' Process F3 key events.

        Case vbKeyF4

            ' Process F4 key events.

        Case Else

    End Select

End Sub

================================================================================

Codes to trap character entry

Private Sub IDNUMBER_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
‘ 65 To 90 and 97 To 122: These are all alphas, upper and lowercase
‘ 8 Backspace, 9 Tab Key, 32 Space Key
Case 1 To 64, 111 To 8, 9, 32
‘Case 65 To 90, 97 To 122, 8, 9, 32
Case Else
‘Setting KeyAscii to zero cancels the key input
KeyAscii = 1
MsgBox “Only numbers are allowed  for this entry!”, vbOKOnly + vbCritical, “System Message”

End Select
End Sub

codes

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then, add the following code to your form’s Current event:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast

Me![txtTotal] = Me.RecordsetClone.RecordCount

End Sub

===================================================================

The following Microsoft Access Visual Basic code demonstrates several different forms of field validation.

1) Checking if the date entered is after today’s date:

Private Sub Date_Entered_AfterUpdate()

If Me.Date_Entered>date() then        ‘ Date_Entered is form field name

msgbox “Please enter a date less than or equal to today’s date.”

Me.Date_Entered.setfocus      ‘ set cursor back in the date field

end if

End Sub

==============================================================

2) Check to see if the difference between two date fields is greater than 30 days.

Private Sub Date_Entered_AfterUpdate()

If datediff(‘d’, Me.Date_Received,Me.Date_Entered) > 30 then

msgbox “Warning: The date received is more than 30 days ” & _

“past Date Entered, please verify.”

end if

End Sub

================================================================

3) Check to see if a name already exists in a table.  This example demonstrates a highly compressed method of programming, which I hope you would never use (we don’t).

Private Sub Emp_Name_AfterUpdate()

If Nz(DLookup(“Emp_Name”, “Employees”, “Emp_Name=’” & _

Me.Emp_Name & “‘”), “zzzz”) <> “zzzz” Then MsgBox & _

“That name already exists in the employee table.”

‘ note that the two lines above actually need to be on one line to run

=====================================================================

CODES TO TRAP DUPLICATE ENTRY

 Put this codes in  the beforeupdate event

 Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

 '*********************************

'Code sample courtesy of srfreeman

'*********************************

    Dim SID As String

    Dim stLinkCriteria As String

    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value

    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber

    If DCount("strStudentNumber", "tblStudentDetails", _

              stLinkCriteria) > 0 Then

        'Undo duplicate entry

        Me.Undo

        'Message box warning of duplication

        MsgBox "Warning Student Number " _

             & SID & " has already been entered." _

             & vbCr & vbCr & "You will now been taken to the record.", _

               vbInformation, "Duplicate Information"

        'Go to record of original Student Number

        rsc.FindFirst stLinkCriteria

        Me.Bookmark = rsc.Bookmark

    End If

    Set rsc = Nothing

End Sub

Now when we attempt to enter a duplicate Student Number, we are presented with the following message box:
Warning message when attempting to enter a duplicate record.

When we click the OK, the duplicate record is undone, and we are taken to the Student that we tried to duplicate.

===========================================================

Before Update Event — Event Procedures Example

‘ this codes use to validate due_date or any other date validation

Private Sub Date_Due_BeforeUpdate(Cancel As Integer)

If Date_Due < Date + 15 Then

MsgBox ” Date is incorrect. Please reenter”, vbOKOnly + vbExclamation, “Error”

Cancel = True

Else

cancle = False

End If

End Sub

====================================================

KeyPress Event — Event Procedures Example

The following example converts text entered in a text box to uppercase as the text is typed in, one character at a time.

To try the example, add the following event procedure to a form that contains a text box named MI.

Private Sub MI_KeyPress(KeyAscii As Integer)

    Dim strCharacter As String



    ' Convert ANSI value to character string.

    strCharacter = Chr(KeyAscii)

    ' Convert character to upper case, then to ANSI value.

    KeyAscii = Asc(UCase(strCharacter))

End Sub

======================================================================

KeyPreview Property Example

In the following example, the KeyPreview property is set to True (–1) in the form’s Load event procedure. This causes the form to receive keyboard events before they are received by any control. The form KeyDown event then checks the KeyCode argument value to determine if the F2, F3, or F4 keys were pushed.

Private Sub Form_Load()

    Me.KeyPreview = True

End Sub



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

    Select Case KeyCode

        Case vbKeyF2

            ' Process F2 key events.

        Case vbKeyF3

            ' Process F3 key events.

        Case vbKeyF4

            ' Process F4 key events.

        Case Else

    End Select

End Sub

================================================================================

Codes to trap character entry

Private Sub IDNUMBER_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
‘ 65 To 90 and 97 To 122: These are all alphas, upper and lowercase
‘ 8 Backspace, 9 Tab Key, 32 Space Key
Case 1 To 64, 111 To 8, 9, 32
‘Case 65 To 90, 97 To 122, 8, 9, 32
Case Else
‘Setting KeyAscii to zero cancels the key input
KeyAscii = 1
MsgBox “Only numbers are allowed  for this entry!”, vbOKOnly + vbCritical, “System Message”

End Select
End Sub