Validation rule

[Guest]

In the beforeupdate event of a control I have some code (see below) to check
record does not already exist. It produces an access system error though (see
below). I want it to remove the ReviewDate but leave the rest of the data
hence me.reviewdate.undo - but this does not work.

If I put it in the afterupdate event of the control I don't get the
violation error but the undo still does not work.

I am doing it this way as I want the validation checking for the data to
take place before a save record is performed. That way if the navigation
buttons are pressed I won't get any errors from an unsuccessful save.

Can anyone help with this as I am going round in circles and its doing my
head in...

Thanks

Acess system error produced:
"The value in the field violates a validation rule for the record or
field." "For example you may have changed a validation rule without
verifying whether the existing data matches the new validation rule."

My validation code:
(called from Private Sub ReviewDate_BeforeUpdate(Cancel As Integer))

Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & _
Format(SpecID, "0") & "AND ItineraryID <> " & ItinID
RecExists = (DCount("*", "Itinerary", Jetwhere) = 0)
If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
Me.ReviewDate.Undo
End If

 

[Chriske911]

In the beforeupdate event of a control I have some code (see below) to check

record does not already exist. It produces an access system error though (see
below). I want it to remove the ReviewDate but leave the rest of the data
hence me.reviewdate.undo - but this does not work.

If I put it in the afterupdate event of the control I don't get the
violation error but the undo still does not work.

I am doing it this way as I want the validation checking for the data to
take place before a save record is performed. That way if the navigation
buttons are pressed I won't get any errors from an unsuccessful save.

Can anyone help with this as I am going round in circles and its doing my
head in...

Thanks

Acess system error produced:
"The value in the field violates a validation rule for the record or
field." "For example you may have changed a validation rule without
verifying whether the existing data matches the new validation rule."

My validation code:
(called from Private Sub ReviewDate_BeforeUpdate(Cancel As Integer))

Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & _
Format(SpecID, "0") & "AND ItineraryID <> " & ItinID
RecExists = (DCount("*", "Itinerary", Jetwhere) = 0)
If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
Me.ReviewDate.Undo
End If

normally you would use an index for that
create a new index on the table and select all fields wich make a
distinct record
set the unique flag to YES

grtz

 

[Guest]

It is a unique index in table but sometimes the form closes without prompting
so the user is unaware of it not saving. Plus if it does prompt it is only
Access's standard wording which isn't very user friendly so I have my own msg
box instead and allow them to change the data or abandon it.

In the beforeupdate event of a control I have some code (see below) to check
record does not already exist. It produces an access system error though (see
below). I want it to remove the ReviewDate but leave the rest of the data
hence me.reviewdate.undo - but this does not work.

If I put it in the afterupdate event of the control I don't get the
violation error but the undo still does not work.

I am doing it this way as I want the validation checking for the data to
take place before a save record is performed. That way if the navigation
buttons are pressed I won't get any errors from an unsuccessful save.

Can anyone help with this as I am going round in circles and its doing my
head in...

Thanks

Acess system error produced:
"The value in the field violates a validation rule for the record or
field." "For example you may have changed a validation rule without
verifying whether the existing data matches the new validation rule."

My validation code:
(called from Private Sub ReviewDate_BeforeUpdate(Cancel As Integer))

Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & _
Format(SpecID, "0") & "AND ItineraryID <> " & ItinID
RecExists = (DCount("*", "Itinerary", Jetwhere) = 0)
If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
Me.ReviewDate.Undo
End If

normally you would use an index for that
create a new index on the table and select all fields wich make a
distinct record
set the unique flag to YES

grtz

 

[Chriske911]

It is a unique index in table but sometimes the form closes without prompting

so the user is unaware of it not saving. Plus if it does prompt it is only
Access's standard wording which isn't very user friendly so I have my own msg
box instead and allow them to change the data or abandon it.

just set the close statement with the parameter to prompt for a save
access will do the rest if a double records is being created

or use the if me.dirty then... clause

grtz