Solved Avoiding Duplicate Values using Form-level validation in VBA - for dates (European format)

[AccessAllAreas]

Hello Experts

Firstly, I have used the answers given on this site to answer many questions I have had already. So a big thank you for all help already given.

I have trued to fund answers to this already and have tried two or three different solutions, adapted to fit my needs, but with no joy! Hence, as I have been trying to learn VBA for access, I thought it best to ask a question rather than stumble blindly on!

My Goal: On a bound popup form, I wish to avoid any records being recorded which are duplicate to any single record already saved in the table that has an identical date to the one now trying to be entered.

The table:
Four fields in the table:
ID (Autonumber) (Primary Key)
Type (Number)
Date (Date/Time) (Format - dd/mm/yyyy) (Required - Yes) (Validation Rule - <= Now()
Attendance

The form:
Main form with a command button that opens a popup form.
There are three controls: Type (combo box), Date (with date selector), formatted as dd/mm/yyyy, and Attendance (text box, accepting only integer values).

Below is the VBA code that I have currently. This has been mashed together after having tried at least two different solutions. I have tried to get the correct syntax, but believe I am missing something fundemental now.

Private Sub MeetingDate_BeforeUpdate(Cancel As Integer)
' Note: MeetingType 1 = Management; MeetingType 2 = Staff
' Note: 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday
Dim NewMeetingAttendanceDateRecord As Date
' Dim strNewMeetingAttendanceDateCriteria As String
' Dim strNewMeetingAttendanceDateCriteria As Date
Dim strNewMeetingAttendanceDateCriteria As Variant
Dim iWeekNo As Integer


iWeekNo = Weekday([MeetingDate], vbMonday)
    If Me.MeetingType = 1 And (iWeekNo = 6 Or iWeekNo = 7) Then
        MsgBox "Management Meetings occur between Monday and Friday." _
            & vbCrLf & "The date you have entered corresponds to either a Saturday or a Sunday." _
            & vbCrLf & "Please alter your date to one equivalent to a Monday through Friday.", vbInformation, _
            "Check Your Meeting Type and Date"
        Cancel = True
        Me.MeetingDate.Undo
    Exit Sub
        Else: Me.MeetingAttendance.Enabled = True
    End If
    If Me.MeetingType = 2 And (iWeekNo = 1 Or iWeekNo = 2 Or iWeekNo = 3 Or iWeekNo = 4 Or iWeekNo = 5) Then
        MsgBox "Staff Meetings occur at the weekend." _
        & vbCrLf & "The date you have entered corresponds to a weekday." _
        & vbCrLf & "Please alter your date to one equivalent to either a Saturday or a Sunday.", vbInformation, _
        "Check Your Meeting Type and Date"
        Cancel = True
        Me.MeetingDate.Undo
    Exit Sub
        Else: Me.MeetingAttendance.Enabled = True
    End If

NewMeetingAttendanceDateRecord = Me.MeetingDate.Value
strNewMeetingAttendanceDateCriteria = "[MeetingDate] = #" & NewMeetingAttendanceDateRecord & "# "
' strNewMeetingAttendanceDateCriteria = "[MeetingDate] = # & NewMeetingAttendanceDateRecord & #"
    If Me.MeetingDate = DLookup("[MeetingDate]", "Tab_Meeting_Attendance", "([MeetingDate] = #NewMeetingAttendanceDateRecord#)") Then
        MsgBox "This meeting date, " & NewMeetingAttendanceDateRecord & ", has already been entered into the database." & vbCrLf & vbCrLf & _
        "Please check selected date again." & vbCrLf & vbCrLf & _
        "If you are certain it is correct, pleace check existing reports to search for duplicate entry.", _
        vbInformation, "Duplicate Meeting Date Found"
        Cancel = True
        Me.MeetingDate.Undo
    Exit Sub
        Else: Me.MeetingAttendance.Enabled = True
    End If
End Sub

The issue is in the second block of code, starting NewMeetingAttendanceDateRecord.

As you can see from the REM'D out Dim statements at the top of the code, I have tried different variable types (string, Date, and Variant).

I have tried running through th code step by step, using the locals window. But no matter which way I go, I get one of a variety of runtime errors: 13 (type mismatch), 3075, 3045, 2645.

I am not very experienced with VBA. Hence, this plea to the experts!

Thank you in advance for your time and wisdom.

 

[Gasman]

Make the format mm/dd/yyyy for the dlookup.

I've used this in the past

Public Const strcJetDateTime = "\#mm\/dd\/yyyy\ hh:nn:ss#"  'Needed for dates in queries as Access expects USA format.

Then just use that as the Format option.

HTH

 

[AccessAllAreas]

Hi Gasman

Many thanks for being the first person to reply!

I have inserted under the Private Sub I am using the code you kindly shared, as below (removed the Public, as it is a private sub):

Const strcJetDateTime = "\#mm\/dd\/yyyy\ hh:nn:ss#"

I am afraid I am unsure of how to "just use that as the Format option", as you suggest. Perhaps in the table's format option?

I have re-run the code and inserted what would be a duplicate record via the Form. This is the error I get:

Run-time error 3075

Syntax error in date in query expression '([MeetingDate] = #NewMeetingAttendanceDateRecord#'

When I put a break in the code and debug, in the Locals windows, it shows the following values for variables (I am using a date of 1st July 2020) when it stops at the following line of code:

If Me.MeetingDate = DLookup("[MeetingDate]", "Tab_Meeting_Attendance", "([MeetingDate] = #NewMeetingAttendanceDateRecord#)") Then

Variable	Value	Type
NewMeetingAttendanceDateRecord	#01/07/2020#	Date
strNewMeetingAttendanceDateCriteria	"[MeetingDate]=#01/07/2020#"	Variant/String
Cancel	0	Integer
srtcJetDateTime	"\#mm\/dd\/yyyy\ hh:nn:ss#"	String

Honestly, I do not know what the type should be, whether the variables shown just above are what they should be or not.

Please treat me as an idiot in this matter! I have got to the point where I am totally lost on how to proceed in this matter!

Again, many thanks in advance!

 

[Gasman]

Ass you are already using NewMeetingAttendanceDateRecord, try

NewMeetingAttendanceDateRecord  = Format(Me.MeetingDate,strcJetDateTime)
If Me.MeetingDate = DLookup("[MeetingDate]", "Tab_Meeting_Attendance", "[MeetingDate] = " & NewMeetingAttendanceDateRecord) Then

 

[arnelgp]

you can leave out the "hh:nn:ss" part if the MeetingDate doesn't have the time element.

 

[Isaac]

Agree with everything already posted, but FYI - to me it seems logically easier to follow to simply code this as:
If Dcount()>0

Rather than "if current control = dlookup", which makes less sense, although might work.

Your goal, simply stated, is to disallow something if a record with that date is already in the table.
So count the records with that date already in the table. You'll end up with fewer comparisons and casting/formatting to do.

So I'd do one line of code:
something like:

if dcount("*","Tab_Meeting_Attendance","MeetingDate=#" & format(Me.MeetingDate.Value,"mm/dd/yyyy") & "#")>0 then...

And, like arnel, I'm suggesting or assuming that hour and minute and second probably don't factor into the equation. If I am wrong, then things need to be more complex.

 

[AccessAllAreas]

To Gasman, Darnel, and Isaac

A big thank you. I have incorporated elements of all of your suggestions and found a solution (your suggestions were fundemental and I would not have been able to work this out without you, nor understand what is going on within the code).

Both the DLookup and DCount solutions work wonderfully. And they have both help expose some deeper errors in my DB.

A big thank you:

Quick question: Are their processing speed benefits to using DCount rather than DLookup?

Finally, for those that want to know and may find useful, here is the final code, including all relevant points from the suggestions above(note, my date field does NOT need a time component):

Private Sub MeetingDate_BeforeUpdate(Cancel As Integer)
' Note: MeetingType 1 = Midweek; MeetingType 2 = Weekend
' Note: 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday
Dim NewMeetingAttendanceDateRecord As String
Dim strNewMeetingAttendanceDateCriteria As Variant
Dim iWeekNo As Integer
' Const strcJetDateTime = "\#mm\/dd\/yyyy\ hh:nn:ss#"  'Needed for dates in queries as Access expects USA format. Use full expr if hrs etc needed
Const strcJetDateTime = "\#mm\/dd\/yyyy\#"    'Needed for dates in queries as Access expects USA format


NewMeetingAttendanceDateRecord = Format(Me.MeetingDate.Value, strcJetDateTime)

    ' If Me.MeetingDate = DLookup("[MeetingDate]", "Tab_Meeting_Attendance", "[MeetingDate] = " & NewMeetingAttendanceDateRecord) Then ' This is the solution if you prefer to use DLookup.  See below for DCount'
    If DCount("*", "Tab_Meeting_Attendance", "MeetingDate=#" & Format(Me.MeetingDate.Value, "mm/dd/yyyy") & "#") > 0 Then
        MsgBox "This meeting date, " & Me.MeetingDate.Value & ", has already been entered into the database." & vbCrLf & vbCrLf & _
        "Please check selected date again." & vbCrLf & vbCrLf & _
        "If you are certain it is correct, pleace check existing reports to search for duplicate entry.", _
        vbInformation, "Duplicate Meeting Date Found"
        Cancel = True
        Me.MeetingDate.Undo
    Exit Sub
        Else: Me.MeetingAttendance.Enabled = True
    End If
End Sub

Hope that helps someone else!

Again, a big thank you to Gasman, Darnel and Isaac

 

[Gasman]

I'll be pedantic here. and it will probably help you in the future.
Change
Const strcJetDateTime = "\#mm\/dd\/yyyy\#"
to
Const strcJetDate = "\#mm\/dd\/yyyy\#"
then you could use both when a time element is required.

FWIW if you did not have time element in the date field, then I believe the original would have worked just as well, so only one constant needed.?

 

[AccessAllAreas]

Gasman

Good point. Your suggestion clarifies matters, for sure. I will change that and no doubt know much easier whic one to use in the future!

 

[Isaac]

A big thank you:

Quick question: Are their processing speed benefits to using DCount rather than DLookup?

Glad you got it working!!
If I had to take a guess, it would be: Since DLookup randomly returns the "first" matching record in the table, it might be the same speed as DCount. I am not really sure.

 

[Gasman]

Glad you got it working!!
If I had to take a guess, it would be: Since DLookup randomly returns the "first" matching record in the table, it might be the same speed as DCount. I am not really sure.

I would have thought from that statement that Dlookup() would be quicker?
If it found the criteria in the first 10 records it would stop, whereas Dcount() has to process the whole recordset?

 

[Isaac]

I would have thought from that statement that Dlookup() would be quicker?
If it found the criteria in the first 10 records it would stop, whereas Dcount() has to process the whole recordset?

True, but then again DCount only needs a count whereas DLookup needs to read the value --Honestly you may be right, I have no idea. Someone here has probably done some tests

 

[Gasman]

True, but then again DCount only needs a count whereas DLookup needs to read the value --Honestly you may be right, I have no idea. Someone here has probably done some tests

Colin (isaldogs) would be the person for that.

 

[Isaac]

Yep - I stumbled across an older thread from years ago. Not long ago but I can't find it. It had extensive - like weeks and weeks - of testing all kinds of those things. I'm not much for doing that in Access but thankfully some are good at it and have saved it for posterity. Can't locate it at the moment. Probably on smaller data not much of a big deal.