severe incompatibility access 2007 <-> previous versions

[Alain Bourgeois]

Dear all,

I have a form PatientsAdd (in add mode, only for adding new record)
based on a table, patients.
PK of patients is [ID Patient].
I have a zone named SIS. Althigh this zone is not a unique key, I would
like to warn user if a
duplicate [SIS] value is added in the table.

In the before update event of the zone, I execute the following macro:
Condition Action
[sis] is null
stopmacro
DLookup("[SIS]";"Patients";
"[SIS]=forms![PatientsAdd]![Sis] and
[ID Patient]<>forms![PatientsAdd]![ID Patient]") is NULL
stopmacro
MsgBox
"warning, duplicate".


This macro has always run ok in the application (running on 60 sites
with access versions '97, 2K, XP, 2K3).

But in access 2007, I can't edit the zone! If I enter zone, set
something in it, I can't exit the zone, the cursor keeps blocked in it.
I don't get a message box and it seems stopMacro invalidates the update.
(cursors stays in the zone and I get an error if I click on "save
record" icon). I only can make "Escape" and leave the zone empty.

PS the db is still in Access 2K format.

Regards,
Alain Bourgeois.

 

[Alain Bourgeois]

To reproduce the bug:

Download the sample:
http://www.kineuro.com/bug2007.mdb

Open form PatientsAdd

-> Open it with access versions 2000->2003, you will be able to set value 7
in NIS zone.
-> Open it with access version 2007, you will NOT be able to set value 7 in
NIS zone.

Regards,
Alain

 

[Bruno Campanini]

To reproduce the bug:

Download the sample:
http://www.kineuro.com/bug2007.mdb

Open form PatientsAdd

-> Open it with access versions 2000->2003, you will be able to set value
7
in NIS zone.
-> Open it with access version 2007, you will NOT be able to set value 7
in
NIS zone.

I was unable to adjust your macro.

But
1 - you can set to "YES" the property for field "SIS" in table "Patients"
2 - you can use this Event Procedure (VBA routine):

Private Sub Sis_BeforeUpdate(Cancel As Integer)
If DLookup("[SIS]", "Patients", "[SIS]='" & [Sis] & "'") Then
Cancel = True
MsgBox [Sis] & " already exists!"
Undo
End If
End Sub

Bruno

 

[Alain Bourgeois]

I don't want to find a workaround.
I want an access 2007 bug fix.

Your solution works, but requires manually adapting 85 forms only for THIS
application (there are other applications)! only bcs of an obvious bug in
access 2007, for beforeUpdate event.
What guarantee do I have that I also don't have to adapt afterUpdateEvents,
other events, and finally rewrite / retest the whole application?

Alain

To reproduce the bug:

Download the sample:
http://www.kineuro.com/bug2007.mdb

Open form PatientsAdd

-> Open it with access versions 2000->2003, you will be able to set value
7
in NIS zone.
-> Open it with access version 2007, you will NOT be able to set value 7
in
NIS zone.

I was unable to adjust your macro.

But
1 - you can set to "YES" the property for field "SIS" in table "Patients"
2 - you can use this Event Procedure (VBA routine):

Private Sub Sis_BeforeUpdate(Cancel As Integer)
If DLookup("[SIS]", "Patients", "[SIS]='" & [Sis] & "'") Then
Cancel = True
MsgBox [Sis] & " already exists!"
Undo
End If
End Sub

Bruno

 

[Bruno Campanini]

I don't want to find a workaround.
I want an access 2007 bug fix.

Then write to Microsoft!

Bruno