Data validation

[Guest]

Hi all

I have some code in the beforeupdate of a form which checks that the date
and person entered is unique and does not already exist. This works for a new
record but if you are changing an existing record it finds itself and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended record - I
think it would maybe work if I added a field as a flag and entered a value in
that field once record had been saved, then check this field when save takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue

 

[Alex Dybenko]

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add one more
criteria that primary key field do not equal current primary key field on a
form.
HTH

 

[Guest]

I tried incorporating the primary key check but when I try to save a record,
by closing form or using a navigation button, I get a run time error 2001 -
You cancelled the previous operation

Dim Msg, SpecName, SpecID As Integer, ItinID, RecExists As Boolean
Dim Jetwhere As String, CheckDate As Date

CheckDate = ReviewDate
SpecID = Specialist
ItinID = ItineraryID
RecExists = True

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
End If

Thanks
Sue

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add one more
criteria that primary key field do not equal current primary key field on a
form.
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi all

I have some code in the beforeupdate of a form which checks that the date
and person entered is unique and does not already exist. This works for a
new
record but if you are changing an existing record it finds itself and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended record - I
think it would maybe work if I added a field as a flag and entered a value
in
that field once record had been saved, then check this field when save
takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue

 

[Alex Dybenko]

hi
i think you have to write here:
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person or press Esc to undo changes"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True

then user will either enter other value, or undo whole record. You can also
send esc using snedkeys

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I tried incorporating the primary key check but when I try to save a
record,
by closing form or using a navigation button, I get a run time error
2001 -
You cancelled the previous operation

Dim Msg, SpecName, SpecID As Integer, ItinID, RecExists As Boolean
Dim Jetwhere As String, CheckDate As Date

CheckDate = ReviewDate
SpecID = Specialist
ItinID = ItineraryID
RecExists = True

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
End If

Thanks
Sue

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add one
more
criteria that primary key field do not equal current primary key field on
a
form.
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi all

I have some code in the beforeupdate of a form which checks that the
date
and person entered is unique and does not already exist. This works for
a
new
record but if you are changing an existing record it finds itself and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended record -
I
think it would maybe work if I added a field as a flag and entered a
value
in
that field once record had been saved, then check this field when save
takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") &
"#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue

 

[Guest]

Sorry, spotted an error in my critieria, changed and all working now thanks!!

Just one more thing, if the record has not been saved before the navigation
buttons on the form are used my error message is displayed correctly, but
then you get a run time error 2105 you can't go to specified record. I tried
putting a docmd.cancelevent in the code but it still tries to perform the
navigation code.

Thanks in advance for any help.
Sue

I tried incorporating the primary key check but when I try to save a record,
by closing form or using a navigation button, I get a run time error 2001 -
You cancelled the previous operation

Dim Msg, SpecName, SpecID As Integer, ItinID, RecExists As Boolean
Dim Jetwhere As String, CheckDate As Date

CheckDate = ReviewDate
SpecID = Specialist
ItinID = ItineraryID
RecExists = True

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
End If

Thanks
Sue

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add one more
criteria that primary key field do not equal current primary key field on a
form.
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi all

I have some code in the beforeupdate of a form which checks that the date
and person entered is unique and does not already exist. This works for a
new
record but if you are changing an existing record it finds itself and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended record - I
think it would maybe work if I added a field as a flag and entered a value
in
that field once record had been saved, then check this field when save
takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") & "#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue

 

[Guest]

Thanks. The trouble is though the record may be unsaved before a navigation
button is pressed. There is no opportunity to press esc or change details etc
then before the system tries to move to another record.

Sue

hi
i think you have to write here:
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person or press Esc to undo changes"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True

then user will either enter other value, or undo whole record. You can also
send esc using snedkeys

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I tried incorporating the primary key check but when I try to save a
record,
by closing form or using a navigation button, I get a run time error
2001 -
You cancelled the previous operation

Dim Msg, SpecName, SpecID As Integer, ItinID, RecExists As Boolean
Dim Jetwhere As String, CheckDate As Date

CheckDate = ReviewDate
SpecID = Specialist
ItinID = ItineraryID
RecExists = True

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
End If

Thanks
Sue

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add one
more
criteria that primary key field do not equal current primary key field on
a
form.
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Hi all

I have some code in the beforeupdate of a form which checks that the
date
and person entered is unique and does not already exist. This works for
a
new
record but if you are changing an existing record it finds itself and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended record -
I
think it would maybe work if I added a field as a flag and entered a
value
in
that field once record had been saved, then check this field when save
takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") &
"#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue

 

[Alex Dybenko]

Hi Sue,
i think if you place this code in form BeforeUpdate event - it will not go
to other record until cancel=true

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks. The trouble is though the record may be unsaved before a
navigation
button is pressed. There is no opportunity to press esc or change details
etc
then before the system tries to move to another record.

Sue

hi
i think you have to write here:
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person or press Esc to undo changes"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True

then user will either enter other value, or undo whole record. You can
also
send esc using snedkeys

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I tried incorporating the primary key check but when I try to save a
record,
by closing form or using a navigation button, I get a run time error
2001 -
You cancelled the previous operation

Dim Msg, SpecName, SpecID As Integer, ItinID, RecExists As Boolean
Dim Jetwhere As String, CheckDate As Date

CheckDate = ReviewDate
SpecID = Specialist
ItinID = ItineraryID
RecExists = True

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
End If

Thanks
Sue


:

Hi,
To check if record is new - you can use me.NewRecord property

Furthermore you can exclude current record from checking - just add
one
more
criteria that primary key field do not equal current primary key field
on
a
form.
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Hi all

I have some code in the beforeupdate of a form which checks that the
date
and person entered is unique and does not already exist. This works
for
a
new
record but if you are changing an existing record it finds itself
and
therefore fails to save changes (please see code below).

Is there a way to differentiate between a new and an amended
record -
I
think it would maybe work if I added a field as a flag and entered a
value
in
that field once record had been saved, then check this field when
save
takes
place? Or is there a better way to do this?

If UniqueRec([ReviewDate], [Specialist]) = False Then
Cancel = true
etc

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean
Dim Jetwhere As String
Jetwhere = "[ReviewDate] = #" & Format$(CheckDate, "mm\/dd\/yyyy") &
"#" &
"AND Specialist = " & Format(SpecID, "0")
UniqueRec = (DCount("*", "Itinerary", Jetwhere) = 0)

Thanks in advance for any help.
Sue