You are here:

Using MS Access/Dcount date in ms access vba

Advertisement


Question
QUESTION: Hello Scott,

I have a sub-form and a main form where I need to add a validation check to see if the date already exists for a particular staff. A staff attends a session and I add a date to confirm attendance, but I don't want the same date to be entered for the same staff again.

On main form I have staff name combo box and department name,
On sub form I have date, completed [yes/no - check box], entered by.

I have set the both fields to indexed: yes duplicates ok

I don't get any error messages but it still save the record with same date/staff name. I have a pop up calendar when the user click on date field. Date format is dd/mm/yyyy.

This is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
If DCount("*", "[staff Details]", "[sessiondate] = #" & Me.[SessionDate] & "# AND [StaffName] = '" & Forms![Add Session]![Combo15].Column(1) & "'") > 0 Then
MsgBox "This date already exists, please choose another date."
Cancel = True
End If


Thanks.

ANSWER: I would set an Multi-field unique index on the combination of StaffID and SessionDate.

But I would use the this code to check the date:

If Not IsNull(DLookup("[SessionDate]","[Staff Details]","[SessionDate] = #" & Me.SessionDate & "# AND StaffID = " & Me.Combo15) Then
MsgBox "This date already exists, please choose another date."
Cancel = True
End If

You should NOT be using names as key fields.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I have tried your method but it does not allow me to add another session for the same staff with different date, as both are set to unique now.

I have one to many relationship between staff name table and staff details table with enforce referential integrity ticked. staffid is pk in staff name table and staffid is fk in staff details table.

When I am in staff name table, I see staffid and department fields, with plus (+) mark on staffid field, when i click on + i see sessiondate, completed, inputtedby fields from staff details table. I want only one date to be entered for each staff, e.g. a staff attends session on 15/11/2012 and another staff attends same session on 15/11/2012 but i dont want same date to be entered again for same staffid. Hope this makes sense.

Many thanks,
Haroon

ANSWER: Then you didn't try my method. If both the staffID and date are set to unique, then you did not create a multi-field index. Its very hard to describe exactly how to do one without illustrations, but if you look it up in Access help, you should find instructions on how to do it.

Did you also try the code I gave you?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Unique indexes will not work because I may have the same staffid
being used on different days. I just want to prohibit duplicate
entries on the date. If I put indexed on the date, then I would only
be able to enter one entry per day...regardless of staffid. For example, staffid = 23 attends session on 21/05/2012 and staff = 25 attends session on 21/05/2012, and all i want is to prevent users from entering same date for same staff twice. I tried your code but does not work as i can still enter same date twice for the same staffid.

Thanks.

Answer
Did you look up multi-field indexes as I suggested? A multi-field index allows you to require that the COMBINATION of values be unique. So each staffID can only have ONE date. But you can have multiple staffIDs with different dates. I have used this feature frequently. It works if you do it right. That's why I suggested it.

The code I gave you should return a message if a date already exists for a specific staffer. If you enter the object names properly it should have worked.

I'm sorry, I can't enter the code or setup the index for you. I can only tell you how it should be done.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.