Using MS Access/Dcount date in ms access vba
I have created multi field index for staffid and sessiondate and set both to unique=yes. But when I add same date for same staffid again it gives me error:
The changes you have requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
The code you gave me does not bring up custom message and I have modified it to this:
If Not IsNull(DLookup("[SessionDate]", "[Staff Details]", "[SessionDate] = #" & Me.SessionDate.Value & "# AND StaffID = " & Forms![add session]!Combo15)) Then
MsgBox "This date already exists, please choose another date."
Cancel = True
It gives me error message:
Run-time error '3075':
Syntax error (missing operator) in query expression '[SessionDate] = #05/11/2012# AND StaffID = Tony Hill'.
"set both to unique=yes"
No that's wrong. When creating a multi-field index, you put a name for the index in the Index Name column on the FIRST row only. You then select the fields you want in the index in the Field Name column, LEAVING THE INDEX NAME COLUMN BLANK! You then set Unique = Yes only on the row with the name (the other rows will not have properties). See screen shot.
This will restrict uniqueness to the COMBINATION of values.
As to the code, I see your problem. I told you earlier you should NOT be using names as a primary key. Names may not be unique. If your StaffID field is a text field (again not a good idea), then you have use the apostrophe as a delimiter. So that line should be:
If Not IsNull(DLookup("[SessionDate]", "[Staff Details]", "[SessionDate] = #" & Me.SessionDate.Value & "# AND StaffID = '" & Forms![add session]!Combo15 & "'")) Then
However, you need to fist check the data type of StaffiD and also the bound column and RowSource of Combo15.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA