no duplicates problem

[Bill Moore]

I have a table with a 2-part required key.

DeptID (Required, default value 3)
Date (required, indexed, no duplicates)

Problem: If I try to enter a duplicate date in the table itself (with no form), it will give the appropriate error about a "no duplicates allowed". However, when I attempt to do the same thing via a form, it allows duplicates when it shouldn't.

Hum??

 

[dcx693]

Not sure why this is happening? You're sure that you've specified that the two fields are to be a joint primary key in the table (that is, they both have that little key symbol next to them in table design view)?

 

[Bill Moore]

integrity problem

Yes. Both fields have the key symbol. If I try to enter a value directly into the table, the enforcement holds as expected. The only thing I have a suspicion about is that in the form, I'm using the MicroSoft DatePicker to select the date with its ControlSource property being the date field in question. But, the bottom line is that using this picker control, the form will let me enter 2 records in the table with the same date.

While I'm on this subject, if I have a key field for a table that consists of 3 fields, is there a way to allow duplicates for each field in the key but allow NO duplicates for the key as a whole? In other words, for each record, at least one of the 3 fields in the key must be different.

Thx.

 

[dcx693]

Can't you set up each of the individual 3 fields as allowing duplicates? Whether or not they're indexed?

 

[Bill Moore]

That's what I've done. It works ok for getting the correct records into the table. What it doesn't prevent, I think, is getting the same record into the table multiple times which is the problem described in my initial post.

Thx.

 

[Bill Moore]

Pat,

Da!! I should have known about the time factor since I just dealt with it on another issue. I think you may have solved it because when I entered the value directly into the table, my input was "date" only. On the form, I'll bet it includes time.

I'll give it a try.

Thanks a lot.

 

[Bill Moore]

Yep. That was the problem. Now, to avoid it, could you give me an idea of the code I could use when selecting a date from the MicroSoft DatePicker control to have it eliminate the time and only give mm/dd/yyyy?

Thx.