A2007 mis-feature?

[Vladimír Cvajniga]

When I edit a table with a unique primary key, that is composed of more than
one field, and enter duplicate values (ie. duplicate key) I can't leave the
record (which is OK) but Access 2002 doesn't tell me that I entered
duplicate key (which is NOT OK). Is that just a mis-feature of A2002?

In A97 there's a message box telling user that a duplicate key was entered.
Is there any workaround for A2002, eg. global DB settings?

TIA

Vlado

 

[Vladimír Cvajniga]

Wrong title, correct one should be: A2002 mis-feature?

Sry...

Vlado

 

[Dirk Goldgar]

In

When I edit a table with a unique primary key, that is composed of
more than one field, and enter duplicate values (ie. duplicate key) I
can't leave the record (which is OK) but Access 2002 doesn't tell me
that I entered duplicate key (which is NOT OK). Is that just a
mis-feature of A2002?
In A97 there's a message box telling user that a duplicate key was
entered. Is there any workaround for A2002, eg. global DB settings?

I just made a test case and got an error message about the duplicate
key. I made a table with a compound primary key, opened it in datasheet
view, entered a record with one pair of key values, then tried to enter
another record with the same key values. I got the standard
duplicate-key message: "The changes you requested to the table were not
successful because they would create duplicate values in the index,
primary key, or relationship."

Are you sure you aren't trapping this error somehow, and ignoring it?
Did you try this in a table datasheet, or in a form?

 

[Vladimír Cvajniga]

I had to set RecordsetType = 1. It seems there's some problem in A2002
because with RecordsetType = 0 there was one more problem: I couldn't easily
change date field (dbDate) in new record in edit mode (F2). When I tried to
edit date (CZ format) in edit mode MS Access deleted the date string, eg.
1) DefaultValue = Date() ... 28.3.2007
2) in new record I clicked on the digit 8 and pressed Delete (I wanted to
change 28 to 29)
3) the result: blank field after I pressed Delete

After some tests (when no event procedures were active) I discovered that
all works OK if I set RecordsetType to 1. BTW, with RecordsetType = 0 (in
query) MS Access doesn't display default values which are set in DB table
design. And, weird, sometimes it displays default values and sometimes it
does NOT display default values when I run the query.

The SQL is one of the simplest:
SELECT FO.*, BaVypis.ID_Cis2ss06
FROM FO INNER JOIN BaVypis ON FO.ID_BaVypis = BaVypis.ID_BaVypis;

Now it seems that with frm.RecordsetType = 1 all goes OK. To perform some
tests in query I had to set frm.RecordsetType to 1 as well. I think I will
have to make some research on this behaviour... :-/

Vlado

 

[Dirk Goldgar]

In

I had to set RecordsetType = 1. It seems there's some problem in A2002
because with RecordsetType = 0 there was one more problem: I couldn't
easily change date field (dbDate) in new record in edit mode (F2).
When I tried to edit date (CZ format) in edit mode MS Access deleted
the date string, eg. 1) DefaultValue = Date() ... 28.3.2007
2) in new record I clicked on the digit 8 and pressed Delete (I
wanted to change 28 to 29)
3) the result: blank field after I pressed Delete

After some tests (when no event procedures were active) I discovered
that all works OK if I set RecordsetType to 1. BTW, with
RecordsetType = 0 (in query) MS Access doesn't display default values
which are set in DB table design. And, weird, sometimes it displays
default values and sometimes it does NOT display default values when
I run the query.
The SQL is one of the simplest:
SELECT FO.*, BaVypis.ID_Cis2ss06
FROM FO INNER JOIN BaVypis ON FO.ID_BaVypis = BaVypis.ID_BaVypis;

Now it seems that with frm.RecordsetType = 1 all goes OK. To perform
some tests in query I had to set frm.RecordsetType to 1 as well. I
think I will have to make some research on this behaviour... :-/

So this is not happening when directly editing a table, but rather on a
form based on a query involving two tables. That's a more complicated
situation than I originally understood from your first post, and not one
I can readily investigate without copies of the tables involved. If
this exact same query and form behaved differently in Access 97, I
suppose the behavior under Access 2002 could be due to the change from
Jet 3.51 to Jet 4.0, and you might look into that. I would definitely
want to be sure that the query and form behaved differently under Access
97, with no other changes.

 

['69 Camaro]

Hi, Vlado.

I couldn't easily change date field (dbDate) in new record in edit mode
(F2).

Is the dbDate column on the one side of the 1:N relationship? If so, you
need to enable cascade update on the relationship between the two tables.

BTW, with RecordsetType = 0 (in query) MS Access doesn't display default
values which are set in DB table design.

Only a Form object has a RecordsetType Property. Queries don't, so I'm
confused about what you're referring to here.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Dirk Goldgar]

In

Only a Form object has a RecordsetType Property. Queries don't, so
I'm confused about what you're referring to here.

Actually, Access stored queries do. You'll find it on the queries
design-view property sheet. It appears to be identical to the
equivalent property of a form. I can't say I've ever had occasion to
use it, though.

 

['69 Camaro]

Hi, Dirk.

Actually, Access stored queries do. You'll find it on the queries
design-view property sheet.

You're right. Thanks. I wonder why it isn't in the Object Browser as part
of the Access object model. The Object Browser only lists RecordsetType for
the Form object (and FormOld if hidden members are shown).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Vladimír Cvajniga]

Is the dbDate column on the one side of the 1:N relationship?
No, it's dbLong.

Only a Form object has a RecordsetType Property. Queries don't, so I'm
confused about what you're referring to here.

You're wrong, Gunny. Queries do have RecordsetType property as well!!!

Vlado

 

['69 Camaro]

Hi, Vlado.

No, it's dbLong.

You identified your date column as dbDate in your previous post. That's why
I asked about dbDate (which I'm assuming is really the DAO data type, not
the name of the column). Regardless, is the date column in the new record
you were trying to change (but couldn't) on the one side or the many side of
the relationship to the other table? If it's on the one side, you have to
enable cascade updates before you'll be able to update that column.

You're wrong, Gunny. Queries do have RecordsetType property as well!!!

You're right, and Dirk Goldgar pointed this out earlier, too. For some
reason, the RecordsetType for QueryDef objects isn't included in the Object
Browser for the Access Object Model. It's only included for the Form
Object.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Vladimír Cvajniga]

I set the query RecordsetType if I need to change it to 1. It's the only way
to test that the Dynaset (Inconsistent Updates) query performs OK.

BTW, there's a bug in query and form design view in Czech version of A2002.
I'll post the bug soon.

Vlado

 

[Vladimír Cvajniga]

Yes, it's date-field with inproper behaviour. It's on the "N" side of the
query. But it's not a "relation-field". That's why I didn't assume that it
had something to do with cascade.

See http://img151.imagevenue.com/img.php?image=37483_Qry_122_406lo.jpg.

Vlado

 

[Vladimír Cvajniga]

Hmmm, I just can't remember why I needed BaVypis.ID_Cis2ss06 in that
query... :-/
V.