You are here:

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
End If

It gives me error message:

Run-time error '3075':
Syntax error (missing operator) in query expression '[SessionDate] = #05/11/2012# AND StaffID = Tony Hill'.



Multi-field index
Multi-field index  
"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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.