A2007 mis-feature?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vladimír Cvajniga - 28 Mar 2007 16:22 GMT

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

Reply to this Message

Vladimír Cvajniga - 28 Mar 2007 16:29 GMT

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

Sry...

Vlado

> 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
[quoted text clipped - 8 lines]
>
> Vlado

Reply to this Message

Dirk Goldgar - 28 Mar 2007 17:43 GMT

> 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
[quoted text clipped - 3 lines]
> 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?

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Vladimír Cvajniga - 28 Mar 2007 22:06 GMT

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

>> 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
[quoted text clipped - 14 lines]
> 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?

Reply to this Message

Dirk Goldgar - 29 Mar 2007 16:24 GMT

> 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
[quoted text clipped - 18 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

'69 Camaro - 30 Mar 2007 20:04 GMT

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.

>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
[quoted text clipped - 40 lines]
>> 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?

Reply to this Message

Dirk Goldgar - 30 Mar 2007 21:29 GMT

> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

'69 Camaro - 30 Mar 2007 21:53 GMT

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.

>> 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.

Reply to this Message

Vladimír Cvajniga - 31 Mar 2007 11:54 GMT

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

Reply to this Message

Vladimír Cvajniga - 31 Mar 2007 10:28 GMT

> 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

> Hi, Vlado.
>
[quoted text clipped - 63 lines]
>>> 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?

Reply to this Message

'69 Camaro - 31 Mar 2007 10:49 GMT

Hi, Vlado.

>> Is the dbDate column on the one side of the 1:N relationship?
> 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.

>> Is the dbDate column on the one side of the 1:N relationship?
> No, it's dbLong.
[quoted text clipped - 72 lines]
>>>> 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?

Reply to this Message

Vladimír Cvajniga - 31 Mar 2007 11:56 GMT

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

> Hi, Vlado.
>
[quoted text clipped - 103 lines]
>>>>> 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?

Reply to this Message

Vladimír Cvajniga - 31 Mar 2007 13:00 GMT

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

> 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
[quoted text clipped - 112 lines]
>>>>>> 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?

Reply to this Message