Mar 23 2009, 04:55 AM
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.
Mar 23 2009, 06:32 AM
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.
Mar 23 2009, 07:03 AM
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?
Mar 23 2009, 07:53 AM
You could try something like this.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intCount As Integer
intCount = DCount("*", "Table1", "[Associate] = '" & Me!Associate & "' AND " & "[DateEntered] = #" & Me.DateEntered & "#")
If intCount > 1 Then
MsgBox "This record has already been added."
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Mar 23 2009, 10:18 AM
You could use the two fields in question as a Unique Values index.
Mar 23 2009, 11:08 AM
I recommended that earlier but the user already has a field in that table that he needs to have set as the PK.
Mar 23 2009, 12:03 PM
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?
Mar 23 2009, 12:16 PM
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.
Mar 23 2009, 12:20 PM
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.
Mar 23 2009, 04:23 PM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here