asked on

Creating custom error message for Table Property validation in MS Access

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"
Me!Response_Date.SetFocus
Exit Function
End If

End Function

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"
With Me
.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

.Refresh
End With

Case "Save"
With Me
.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

.Refresh
End With

End Select

End Sub

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?

Thanks!

mildurait

Answer to (1)
Case "Save"
'Test for validity
If Not ValidData() Then Exit Sub

Answer (2)
Having the form validation in a separate function is a good practice as it will make your code more readable, and you will be able to re-use the function elsewhere if need be.
I'd simply rewrite the ValidData function to check the values in the controls themselves.
Good practice in form writing would be to rename the textboxes and other controls with something meaningful, and something different from the underlying field name. I've used txtResponseDate and txtReceivedDate in this sample below.

Private Function ValidData() As Boolean
 
    dim sMsg as string
    ValidData = True
 
     'Check for values in date textboxes      
     If isnull(me.txtResponseDate.value) Then
         call msgbox("Please enter a value in the response date field.")
         me.txtResponseDate.setfocus()
         ValidData = false
         Exit Function
     End IF
     
     If isnull(me.txtReceivedDate.value) Then
         call msgbox("Please enter a value in the received date field.")
         me.txtReceivedDate.setfocus()
         ValidData = false
         Exit Function
     End IF
 
     'Check for acceptable values
     If me.txtResponseDate.value < me.txtReceivedDate then
         Call msgbox("The Response Date entered is prior to the Date Received." & vbcrlf & "Please enter a valid Response Date.")
	 ValidData = false
         me.txtResponseDate.Value = null
     End If
 
     'Your other criteria here  
 
End If

Open in new window

mildurait

...sorry let me try again with that code snippet.

Private Function ValidData() As Boolean
 
    g
    ValidData = True
 
     'Check for values in date textboxes      
     If isnull(me.txtResponseDate.value) Then
         call msgbox("Please enter a value in the response date field.")
         me.txtResponseDate.setfocus()
         ValidData = false
         Exit Function
     End IF
     
     If isnull(me.txtReceivedDate.value) Then
         call msgbox("Please enter a value in the received date field.")
         me.txtReceivedDate.setfocus()
         ValidData = false
         Exit Function
     End IF
 
     'Check for acceptable values
     If me.txtResponseDate.value < me.txtReceivedDate then
         Call msgbox("The Response Date entered is prior to the Date Received." & vbcrlf & "Please enter a valid Response Date.")
	 ValidData = false
         me.txtResponseDate.Value = null
         me.txtResponseDate.setfocus()
         Exit Function
     End If
 
     'Your other criteria here  
 
End If

Open in new window

ASKER CERTIFIED SOLUTION

mildurait

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ppontius

ASKER

The above solution worked, except it's not necessary to have the () after SetFocus.

Thanks for the reminders about putting txt before the name of things. I've gotten in the habit of cmd and cmbo for command buttons and combo boxes respectively, but still need to remind myself about txt for good naming practices.

One follow on question. The part (1) solutiod, adding
If Not ValidData() Then Exit Sub
to the Case "Save" cmdEdit button code worked, but nowit allows edits, even though I still have .AllowEdits = False

Any suggestions for where to place the .AllowEdits = False so that after a user is finished editing (and validating/correcting) the record Edits are no longer allowed?

Thanks!

ppontius

ASKER

The final one worked, but I had to take out the () after the setfocus. Also, it has slightly messed up my allowedits = true or false. see followon question

mildurait

I'd throw in a few msgbox commands in each major block of your code to see what path it is taking.
eg
In Edit Node of Select Statement
Call Msgbox("Edit")

In Save Node of Select Statement
Call Msgbox("Save Before Validation")
Call Msgbox("Save After Validation")

At the end of the sub
Call Msgbox("Code completed")

and see if the code executes as you would expect it to.

Also, it may be a case of where you put the ValdData() function call - hard to say without the form in front of me.

Good luck.