Solved Avoid Duplicated Records by dates - but by a range, not exact date match

[AccessAllAreas]

Hello Access Experts

I hope you are well during these strange times!

I have a pop-up form for entering data. This data includes a "date" component and a type of "event" component. Almost every week, there will be two "events". There will be one "event" held midweek, there will be another type of "event" held during any weekend. The midweek "event" could be held on any midweek day (Mon-Fri). The weekend "event" could be held on an day during the weekend (Saturday or Sunday). However, there can never be more than 1 midweek or 1 weekend "event".

I have set up code to ensure that no records could be added that are a midweek "event" with the exact same date as an existing record. The same is true for the weekend "event".

What I wish to do is implement some validation code that prevents any records being saved that are either within the same Midweek period (Mon-Fri) of an existing record. And I wish to prevent being able to record any code that would be in the same weekend period (Saturday or Sunday) as an already existing record.

What would be the best way of going about this?

All help gratefully received and appreciated, as always!

Thanks in advance.

 

[Gasman]

Instead of your criteria being equal to the date, have your criteria check Between Monday date and Friday Date. The same logic with Weekend dates.

 

[bob fitz]

Hello Access Experts

I hope you are well during these strange times!

I have a pop-up form for entering data. This data includes a "date" component and a type of "event" component. Almost every week, there will be two "events". There will be one "event" held midweek, there will be another type of "event" held during any weekend. The midweek "event" could be held on any midweek day (Mon-Fri). The weekend "event" could be held on an day during the weekend (Saturday or Sunday). However, there can never be more than 1 midweek or 1 weekend "event".

I have set up code to ensure that no records could be added that are a midweek "event" with the exact same date as an existing record. The same is true for the weekend "event".

What I wish to do is implement some validation code that prevents any records being saved that are either within the same Midweek period (Mon-Fri) of an existing record. And I wish to prevent being able to record any code that would be in the same weekend period (Saturday or Sunday) as an already existing record.

What would be the best way of going about this?

All help gratefully received and appreciated, as always!

Thanks in advance.

The attached db shows one way that you might accomplish this:

 

[Pat Hartman]

You don't need code at all unless you want it to give the user a nice error message. Use a variable for MidWeek/Weekend and use yyyyww as the week number and either the full date or just the weekday if you need to know exactly which day the event takes place. Then you would have a unique index on
ForeignKey (the link to the parent record)
EventType
EventWeek

You might still want the validation code OR you can trap the "duplicate" error and substitute your user friendly error message.

For this method to work, you need to use Monday as the first day of the week. The default is Sunday but you can change it in your functions when you calculate yyyyww.

 

[arnelgp]

you use DCount() to return if there is already an event on "midweek/weekend"
on form, add validatation (beforeupdate event) to "date" textbox:

private sub date_beforeupdate(cancel as integer)
dim tfWeekEnd as boolean
dim dteMonday as date
dim dteSaturday as date
'* assumed that week starts with monday and ends on sunday
'* check whether the date entered is weekday or weekend
tfWeekEnd = (WeekDay([date]) = 1 Or WeekDay([date]) = 7)
'* get the monday of this date
dteMonday = DatePrevWeekday([date])
'* get the saturday of this date
dteSaturday = dteMonday + 5
'* now check if our date is already entered to the table
if tfWeekEnd Then
    Cancel = DCount("1", "table", _
    "[DateField] Between " & Format(dteSaturday,"\#mm\/dd\/yyyy\#") & " And " & _
    Format(dteSaturday + 1, "\#mm\/dd\/yyyy\#") > 0

    if Cancel Then
        Msgbox "Already have event for the weekend!", vbinformation
    end if

Else
    Cancel = DCount("1", "table", _
    "[DateField] Between " & Format(dteMonday,"\#mm\/dd\/yyyy\#") & " And " & _
    Format(dteMonday + 4, "\#mm\/dd\/yyyy\#") > 0

    if cancel then
        msgbox "there is already an event for the weekday!", vbInformation
    end if
end if

end sub



Public Function DatePrevWeekday( _
  ByVal datDate As Date, _
  Optional ByVal bytWeekday As VbDayOfWeek = vbMonday) _
  As Date

' Returns the date of the previous weekday, as spelled in vbXxxxday, prior to datDate.
' 2000-09-06. Cactus Data ApS.

  ' No special error handling.
  On Error Resume Next

  DatePrevWeekday = DateAdd("d", 1 - Weekday(datDate, bytWeekday), datDate)

End Function

 

[AccessAllAreas]

Wow! Four suggestions so quickly! Thank you all.

I will try to implement these options and see what works!

I will get back to you!

Once again, thank you.

 

[bob fitz]

Wow! Four suggestions so quickly! Thank you all.

I will try to implement these options and see what works!

I will get back to you!

Once again, thank you.

You're welcome. Post back if you have any questions on the example that I posted for you.

 

[AccessAllAreas]

Hello Bob, Darnel, Gasman and Pat Hartman

Many thanks for the solutions. I put together a couple of the solutions and found one that I believe works pretty well for me!
Only thing left to do is get rid of the automated error message box "The value violates the validation rule for the field or record."

Thanks for all your help.

 

[bob fitz]

Hello Bob, Darnel, Gasman and Pat Hartman

Many thanks for the solutions. I put together a couple of the solutions and found one that I believe works pretty well for me!
Only thing left to do is get rid of the automated error message box "The value violates the validation rule for the field or record."

Thanks for all your help.

Pleased that you have a solution to your problem. Always glad to help if I can