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
☻" StUdEnTs LyF"☻
16 years ago