Prevent duplicate data over multiple fields (new twist)

[NDBadger]

I have been reviewing previous posts covering this subject but can't seem to find any with my "unique" issue. I have a table [Orders] used to track employee's appointments. We use a form [frmOrders] to update the data in this table. On this form we have theses fields:

EmployeeID
Event
Start Date
End Date

plus several others that do not apply to this discussion. I am trying to come up with a way to prevent conflicting appointments. If I use a unique pk combination it only works if all of the fields are identical. My problem comes in when someone tries to enter an employee into an event that is between the start and end dates. For example:

Employee A is on vacation from 1/1/2009 to 1/12/2009. Supervisor B wants to schedule a training class for Employee A on 1/5/2009. No conflict will show as the start and end dates don't match.

What we want is after those fields are input into the form:

1) identify if there is a conflict of employee and dates
2) pop up a screen warning of a possible conflict
3) allow the conflict at the supervisors discretion as there are circumstances where an employee will have multiple events that occur.

Any help will be appreciated.

 

[pbaldy]

The way to test for date conflicts is by testing your start date against the end date in the table, and your end date against the start date in the table. In your example, your start and end dates are the same, but the test will work.

 

[NDBadger]

Thanks for the response PBaldy. I need a little more clarification on your suggestion. Are you talking about running query to find these conflicts?

 

[pbaldy]

Yes; query, recordset, DCount. Something along those lines. The tricky part here is the test. You can't compare start to start, end to end.

 

[NDBadger]

OK I used this test as a filter in a couple of queries where I want to list appointments between two dates so I got the general idea. How do you suggest using this as a test for conflicts? I have never been any good code writing so I am trying to find an easy way to do this.

 

[pbaldy]

I'd probably open a recordset on a query like that. If it comes up EOF, you have no conflicts, otherwise you do.

 

[NDBadger]

I'm embarrassed to say but I do not understand what you mean by open a recordset on a query.

 

[pbaldy]

Nothing to be embarrassed about. Here's generic recordset code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName WHERE IDField = " & Me.ControlName
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If rs.EOF Then
    'good to go
  Else
    'conflict found
  End If

  set rs = nothing
  set db = nothing

 

[NDBadger]

Double check my thinking. In the "SELECT * FROM line table name would be the name of the table I'm using, IDField would be the fields that I want to check for conflicts, and Me.controlname would be the criteria (i.e. employee name, start date, end date etc.)? Or am I missing something.

And then I would add this to a command button or maybe an afterupdate event?

 

[pbaldy]

That was just a sample. You would basically copy/paste the SQL from your working query, which I imagine would include date and employee criteria. You would have to concatenate the form references; there's a good tutorial here:

http://www.mdbmakers.com/forums/showthread.php?t=4895

 

[NDBadger]

Well after spending about an hour on this my mind is mush. I can't even get a query to come up with the results I need.

I have three fields EmployeeID, Start Date, and End Date. I know the criteria i want is start Date >=[forms]![frmOrder]![end date] and <=[forms]![frmOrder]![start date]

which I believe should produce any records between the two dates selected on my form. But I'm at a loss.

 

[pbaldy]

Can you post the db? Right off, your greater than/less than is reversed. You want to test that your start date is less than or equal to the end date on the form, and vice versa.

 

[NDBadger]

here it is. I compacted and zipped and it is still 428 kb. I appreciate all the help

 

[pbaldy]

What table are we looking in? The only one I see with start and end dates is orders, but none of the employee ID's are filled out there.

Are in the AF? My daughter is.

 

[NDBadger]

Yes the table (ORDERS) is where we keep track of the appointments. What we want is some type of check when we use the form (frmOrders) that the individual selected does not have an appointment already for that time period. The three boxes I believe we would use is customerID (which is the employee name), order date (start Date) and shipped date (End Date). If there is a way to even go further and incorporate the start time and end time to be even more specific that would be good too. However we don't want to NOT allow the conflict as supervisors can schedule multiple events that appear as conflicts but in fact are not.

And Yes I am with the Air Force. I spent 22 years in and retired in 05. I am now a civilian working in Vehicle Maintenance

 

[pbaldy]

Try this:

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT * FROM orders WHERE CustomerID = '" & Me.Combo98 _
         & "' AND OrderDate <= #" & Me.ShippedDate _
         & "# AND ShippedDate >= #" & Me.OrderDate & "#"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If rs.EOF Then
    MsgBox "good to go"
  Else
    MsgBox "conflict found"
  End If

  Set rs = Nothing
  Set db = Nothing

Daughter recently transferred to Mildenhall in England, so looks like I have to go there now for visits! She's a maintenance officer.

 

[NDBadger]

It works; however it pops up a conflict no matter who we try to load. Even new personnel. Any thoughts?

I was stationed at Mildenhall from 1989 - 1992 and Lakenheath from 1994 - 2000. I loved it over there.

Change 1 - I cleared the Orders Table and started new. Each time I load a person it comes back as conflict found.

 

[pbaldy]

I tested and it worked as expected. Because that form is bound to the same table, you have to make sure you don't save the record first, or of course it's comparing to itself, so there would always be a conflict. You might want to use an unbound form or unbound controls, so you don't have to worry about that.

Her husband (my son-in-law) is at Lakenheath (he's a pilot). She was worried when she first found out they were at different bases, fearing they'd be far apart. Someone she knows had been stationed there, and said (referring to the base where she was stationed at the time):

"you know where our west gate is?"

"yes"

"you know where our east gate is?"

"yes"

"that's about how far apart those 2 bases are"

She relaxed then. I'm looking forward to visiting the area. I've never really been to England, other than an overnight layover in London on a trip to Italy.

 

[NDBadger]

Paul,

Outstanding. It works perfect. I was saving the record before running the test. Thanks so much for the help.

One thing about living over there there is so much to see and do that you can't see/do it all if you're ther for only a few days. I would go back in a heart beat.

Thanks again

 

[pbaldy]

No problem, glad we got it sorted out.