The Form_BeforeUpdate should be the best way to validate data inserted in the form, but on my side it does not work, at all.
Here is the code for that event:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'### check if mandatory fields are blank/null ###'
Dim ctl As Control
Dim testo As String
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If ctl.Tag = "Required" And IsNull(ctl) Then
testo = ctl.Controls(0).Caption
testo = Left(testo, Len(testo) - 1)
MsgBox "The following field is required: " & vbCrLf & vbCrLf _
& "- " & testo
Cancel = True
ctl.SetFocus
Exit Sub
End If
End Select
Next ctl
'### Check lenght of Latitude field ###'
If Len(txtLat) <> 7 Then
MsgBox "Latitude is not correct, please complete all components of the field."
Cancel = True
txtLat.SetFocus
Exit Sub
ElseIf IsNull(txtLat) Then
Dim textMessage As Integer
textMessage = MsgBox("Latitude data is not mandatory, but the distance calculation feature in the " _
& "flight data center will not be available. Do you want to add latitude?", vbYesNo)
If textMessage = vbYes Then
txtLat.SetFocus
Exit Sub
Else
End If
Else
txtLat = UCase(txtLat)
End If
'### Check lenght of Longitude field ###'
If Len(txtLon) <> 8 Then
MsgBox "Longitude is not correct, please complete all components of the field."
Cancel = True
txtLon.SetFocus
Exit Sub
ElseIf IsNull(txtLon) Then
Dim textMessage2 As Integer
textMessage2 = MsgBox("Longitude data is not mandatory, but the distance calculation feature in the " _
& "flight data center will not be available. Do you want to add longitude?", vbYesNo)
If textMessage2 = vbYes Then
txtLon.SetFocus
Exit Sub
Else
MsgBox "All data you inserted will be saved shortly."
End If
Else
txtLon = UCase(txtLon)
End If
'### checks for the Latitude field components ###'
If Mid(txtLat, 2, 2) >= 90 Then
MsgBox "Latitude cannot be higher than 90° degrees, please edit the field accordingly"
Cancel = True
txtLat.SetFocus
ElseIf Mid(txtLat, 4, 2) >= 60 Then
MsgBox "Minutes of Latitude field cannot be higher than 60, please edit the field accordingly"
Cancel = True
txtLat.SetFocus
ElseIf Mid(txtLat, 6, 2) >= 60 Then
MsgBox "Seconds of Latitude field cannot be higher than 60, please edit the field accordingly"
Cancel = True
txtLat.SetFocus
Else
txtLat = UCase(txtLat)
End If
'### checks for the Longitude field components ###'
If Mid(txtLon, 2, 3) >= 180 Then
MsgBox "Longitude cannot be higher than 180° degrees, please edit the field accordingly"
Cancel = True
txtLon.SetFocus
Exit Sub
ElseIf Mid(txtLon, 5, 2) >= 60 Then
MsgBox "Minutes of Longitude field cannot be higher than 60, please edit the field accordingly"
Cancel = True
txtLon.SetFocus
ElseIf Mid(txtLon, 7, 2) >= 60 Then
MsgBox "Seconds of Longitude field cannot be higher than 60, please edit the field accordingly"
Cancel = True
txtLon.SetFocus
Else
txtLon = UCase(txtLon)
End If
End Sub
Could anyone suggest me a code for the SAVE button in the form, which has to do this actions:
1. fire the BeforeUpdate event to check if all required fields are ok, if the Lat/Lon fields are completed in correct way;
2. save the current record into the bound table;
3. move to the next new record.
I have also tried with the Me.dirty property but no luck again.
Thanks bye.
Riccardo