Hi everyone,

I have a form (frmAll) that populates the current date (text box = CurrentDate). This form is built off of a table called tblProjects. What I'm trying to do is prevent duplicate dates for an associate. The associate names are populated automatically on the form open procedure in a text box called AssociateName (same name in the table) and the CurrentDate (again, same name in the table) populates from a combo box called cmboProject.

Let's say the associate fills out frmAll for 3/23/2009. What I'd like to do is if they tried to create a record again for the same date, a msg box would appear saying the date has already been used for that associate and that particular record would not be saved.

I searched for quite a while on the forum and didn't see an answer to this specific issue so any assistance with this situation would be greatly appreciated.

Thanks in advance.

You could handle it a few different ways but the best way would be to set a primary key on the associate and date fields, which would prevent duplicate values from being entered.

Thanks for the response. Unfortunately I need the PK set on something else. Is there a way to do this successfully w/o setting the field as the primary?

You could try something like this.

You could use the two fields in question as a Unique Values index.

Hi Frank,

I recommended that earlier but the user already has a field in that table that he needs to have set as the PK.

But you can still do it, can't you Dan? It won't be the PK, but you can still use the fields in a unique index, can't you? Or am I all wet?

You're dry as a bone. Just about every table that uses an autonumber
for a primary key would benefit from having a unique index defined on
the natural primary key.

Frank,

You can assign each individual field as a unique field by itself but I don't know of any way that you would be able to combine those 2 fields as a unique index while you still have the existing PK. You would have to combine all of them together.

I'm by no means an expert in all aspects of Access 2007. I don't get the chance to develop with it too often, so, I'm not saying that it might not be possible, I just don't know of a way to do it. Maybe, one of the MVPs or someone more familiar with Access 2007 then myself can help to answer that one.

Well, it is possible of course that the current data will not allow it. But if you open the table in design view, and click the Indexes button, I think you can do it, even if data has been entered into the table.