In my form to enter data in a table, I have set the following validation rule:
[response date] > [date received]
so that a user cannot say a letter was responded to before it was received. However, the automatic error message is in MS-Access-Speak. I would like to change it to: "The Response Date entered is prior to the Date Received. Please enter a valid Response Date."
Reading through the past questions, it seems people recommend functions to validate boxes instead of using table properties. E.g., I found the following function which almost works for me:
Private Function ValidData() As Boolean
ValidData = True
'this function tests if all data entered are logical. right now, it just tests for dates
'copy the if statement to add other tests and message boxes
If Me!Response_Date < Me!Date_Received Then
ValidData = False
MsgBox "The Response Date must be after the Date Received"
The trick is, the form is read only until the user clicks the "Edit Record" button. My code for the Edit Record button follows:
Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
Case "Edit Record"
.AllowEdits = True
.cmdEdit.Caption = "Save"
.cmdEdit.ForeColor = 128
.cmdEdit.FontBold = True
'the following deactivates buttons not associated with editing a case
.cmdAddRecord.Enabled = False
.cmdDelRecord.Enabled = False
.cmdGoCase.Enabled = False
.cmdSearch.Enabled = False
.cmdOpenFQForm.Enabled = False
'the following activates buttons associated with editing a case
.cmdSelf.Enabled = True
.cmdScdryApp.Enabled = True
.cmdAddRmk.Enabled = True
.cmdBrowse.Enabled = True
.AllowEdits = False
.cmdEdit.Caption = "Edit Record"
.cmdEdit.ForeColor = 0
.cmdEdit.FontBold = False
'the following activates buttons not associated with editing a case
.cmdAddRecord.Enabled = True
.cmdDelRecord.Enabled = True
.cmdGoCase.Enabled = True
.cmdSearch.Enabled = True
.cmdOpenFQForm.Enabled = True
'the following deactivates buttons associated with editing a case
.cmdSelf.Enabled = False
.cmdScdryApp.Enabled = False
.cmdAddRmk.Enabled = False
.cmdBrowse.Enabled = False
I get an error message at the .Refresh button and the code opens up, instead of the message box. Any suggestions for: (1) how to adjust the code for the cmdEdit_Click() so that it works with the ValidData() Function? Or, (2) how to change the table property validation error message text box and skip the ValidData() function all together?