Maintaining a simple table

[Guest]

I have to do what I thought would be a simple task.
I have a continuous form that maintains a table containing one column.
I need to trap 3 possible errors.
(1) Records cannot be deleted if they are used in another table
(2) Records cannot be inserted if it would create a duplicate name
(3) Records cannot be renamed if it would create a duplicate name
I'm using the BeforeInsert, BeforeDelete and BeforeUpdate events (with
DLookups)to check these conditions. However, I keep getting a message 'you
cancelled the previous operation'. I have no idea what this means and Help
did not help me. Here is the code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim X As Variant
X = DLookup("TechAssigned", "tblTechAssigned", "TechAssigned='" &
Me.TechAssigned & "'")
If Not IsNull(X) Then
MsgBox "Cannot add team with a name already in list", , "Duplicate team"
Cancel = 1
Exit Sub
End If
Cancel = 0
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim X As Variant
X = DLookup("TicketNo", "tblCSTickets", "LastAssignedTo='" &
Me.TechAssigned.OldValue & "'")
If Not IsNull(X) Then
MsgBox "Cannot rename team - used by a ticket", , "In use"
Cancel = 1
Exit Sub
End If
X = DLookup("TechAssigned", "tblTechAssigned", "TechAssigned='" &
Me.TechAssigned & "'")
If Not IsNull(X) Then
MsgBox "Cannot rename team to a name already in list", , "Duplicate
team"
Cancel = 1
Exit Sub
End If
Cancel = 0
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim X As Variant
X = DLookup("LastAssignedTo", "tblCSTickets", "LastAssigned='" &
Me.TechAssigned & "'")
If Not IsNull(X) Then
MsgBox "Cannot delete team - used by a ticket", , "In use"
Cancel = 1
Exit Sub
End If
Cancel = 0
End Sub

 

[Michel Walsh]

For the first condition, try a referential data integrity with NO cascade.

For the other two conditions, add an index not allowing duplicated values.

Under form, the error would have to be catch in the form-error procedure
handling:


--------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)

.....

End Sub
--------------------



You can analyze the error through the DataErr argument. You can assign the
Response argument a little bit like you do for the NotInList event for a
combo box.

Doing these constraints at the database level, rather than at the form
level, insures you that every developer, or even if you are alone, in each
and every use you will do of these tables, the consistency will be present,
even if you would have forgot about it, for a second or two.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I already have whaat you suggest but I want to trap the errors myself. I
can't have Access putting out its own messages in an application.

 

[Michel Walsh]

Then, in the said event, test for the error number, display the message you
want, and specify the Response argument appropriately:


Response=acDataErrContinue 'tell Access to not display an error message
if(DataErr=3022 ) then
MsgBox .... ' your message about a repeated value

else if(DataErr=3200 ) then
MsgBox ... 'your message about other records referring to this value

else
Response= acDataErrDisplay ' tell Access to proceed as usual
end if




Hoping it may help,
Vanderghast, Access MVP