General Date not OK as PK?

  • Thread starter Thread starter Jim Shaw
  • Start date Start date
J

Jim Shaw

BlankContext: Access 2002 SP/3 installed:

In a table I'm trying to use a compound Primary Key consisting of EmpID (a
SSN) and Posted (=now()).
Having successfully added the first record and then waiting 5 mins, I
attempt to add a second record having
the same EmpID value and a different "Now" value. The datevalues are the
same, but the timevalues are different.
Both fields are indexed w/dups ok.

The second add fails for duplicate PK. Does not Access look at the time
component of Posted?

Thanks
Jim
 
Jim Shaw said:
Context: Access 2002 SP/3 installed:

In a table I'm trying to use a compound Primary Key consisting of
EmpID (a SSN) and Posted (=now()).
Having successfully added the first record and then waiting 5 mins, I
attempt to add a second record having
the same EmpID value and a different "Now" value. The datevalues are
the same, but the timevalues are different.
Both fields are indexed w/dups ok.

The second add fails for duplicate PK. Does not Access look at the
time component of Posted?
Click to expand...

How are you getting this Now() value into the field of the record?
 
Immanuel;
The data type is "date" with a format of "general date"
It is indexed w/Duplicates OK
Jim
 
Jim Shaw said:
Dirk;
I'm getting in with an assignment statement:
rs!Posted = Now()
Jim
Click to expand...

I don't quite follow you. Your "rs!" qualifier seems to imply that
you're using a recordset object. But you're posting to the .forms
newsgroup, so I would normally assume that you're adding records by way
of a form, so I don't see where a separate recordset would come into it.
Would you mind explaining how you have your form set up -- assuming
there is one -- and post the whole procedure within which that statement
is being executed?
 
I can't reproduce this. I created a table with two fields, one long integer,
one date/time. I made the default value of the date/time field =Now(), and
created a primary key on the combination of the two fields. I entered
multiple records into the table, all with the same value in the long integer
field, without any difficulty.

I think Dirk must be on the right track - it must have something to do with
how you're assigning the values via the recordset.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks. At least I know the problem is mine and not a "feature" of Access.
I'll look things over more closely
Thanks
Jim
 
Dirk,
I'm sorry, perhaps I should have posted to the "modulesdaovba" newsgroup.
The form is unbound with many controls used to gather data. It has a
command button called cmdSave.
The cmdSave_click() event code is huge containing data validation routines,
and insertion of records into several tables. If you still want, I don't
mind posting the code, but the issue is moot as I know that the bug is mine
and not a misunderstanding of how Access works.
Thanks for responding to my question.
Jim
 
Jim Shaw said:
Dirk,
I'm sorry, perhaps I should have posted to the "modulesdaovba"
newsgroup. The form is unbound with many controls used to gather
data. It has a command button called cmdSave.
Click to expand...

Okay, now the use of the recordset makes sense.
The cmdSave_click() event code is huge containing data validation
routines, and insertion of records into several tables. If you still
want, I don't mind posting the code, but the issue is moot as I know
that the bug is mine and not a misunderstanding of how Access works.
Thanks for responding to my question.
Click to expand...

You're welcome. It does make debugging a lot easier to rule out a
system bug; then one can concentrate on one's own code. Good luck, and
come back if you need help.
 
Back
Top