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

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

Sry...

Vlado

"Vladimír Cvajniga" <(E-Mail Removed)> píše v diskusním pøíspìvku
news:(E-Mail Removed)...
> 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

In news:(E-Mail Removed),
Vladimír Cvajniga <(E-Mail Removed)> wrote:
> 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?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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" <(E-Mail Removed)> píše v diskusním pøíspìvku
news:(E-Mail Removed)...
> In news:(E-Mail Removed),
> Vladimír Cvajniga <(E-Mail Removed)> wrote:
>> 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?
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

In news:%(E-Mail Removed),
Vladimír Cvajniga <(E-Mail Removed)> wrote:
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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/ex...ributors2.html for contact
info.


"Vladimír Cvajniga" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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" <(E-Mail Removed)> píše v diskusním pøíspìvku
> news:(E-Mail Removed)...
>> In news:(E-Mail Removed),
>> Vladimír Cvajniga <(E-Mail Removed)> wrote:
>>> 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?
>>
>> --
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>>
>>
>

In news:(E-Mail Removed),
'69 Camaro <(E-Mail Removed)_SPAM> wrote:
>
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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/ex...ributors2.html for contact
info.


"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> In news:(E-Mail Removed),
> '69 Camaro <(E-Mail Removed)_SPAM> wrote:
>>
>> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

> 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" <(E-Mail Removed)_SPAM> píše v
diskusním pøíspìvku news:(E-Mail Removed)...
> 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/ex...ributors2.html for contact
> info.
>
>
> "Vladimír Cvajniga" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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" <(E-Mail Removed)> píše v diskusním pøíspìvku
>> news:(E-Mail Removed)...
>>> In news:(E-Mail Removed),
>>> Vladimír Cvajniga <(E-Mail Removed)> wrote:
>>>> 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?
>>>
>>> --
>>> Dirk Goldgar, MS Access MVP
>>> www.datagnostics.com
>>>
>>> (please reply to the newsgroup)
>>>
>>>
>>
>
>

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/ex...ributors2.html for contact
info.


"Vladimír Cvajniga" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)_SPAM> píše v
> diskusním pøíspìvku news:(E-Mail Removed)...
>> 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/ex...ributors2.html for contact
>> info.
>>
>>
>> "Vladimír Cvajniga" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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" <(E-Mail Removed)> píše v diskusním pøíspìvku
>>> news:(E-Mail Removed)...
>>>> In news:(E-Mail Removed),
>>>> Vladimír Cvajniga <(E-Mail Removed)> wrote:
>>>>> 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?
>>>>
>>>> --
>>>> Dirk Goldgar, MS Access MVP
>>>> www.datagnostics.com
>>>>
>>>> (please reply to the newsgroup)
>>>>
>>>>
>>>
>>
>>
>