Compare records on continious form?

[Aweiher]

I have a continuous form based on a query that is based on multiple tables, and has criteria that's based upon a control in the form (a date box). The form is a schedule of multiple employees, with appointment start and end times, and I would like it to prompt if an employee is double booked.



So basically I need something like...
if Previous Record.Technician = CurrentRecord.Technician then
if Previous Record.End Time >Current Record.Start Time then
msgbox"double booked something something"
end if
end if

Is there is a syntax for doing something like this?

[ranman256]

use the query wizard to make a FIND DUPLICATEs query on that query.
Only the doubles will show.

[Aweiher]

That won't work because the records aren't duplicated. For instance it might look like this...

Employee Start Time End Time
John Smith 8:00am 11:00am
John Smith 9:30am 1:00pm

[pbaldy]

See if this helps:

http://www.baldyweb.com/OverLap.htm

FYI, I deleted your duplicate thread.

[Aweiher]

I can't really seem to translate the SQL in your link to a VBA code for my form. This seems so simple but I just can't get it. Referring to the "previous record" isn't as simple as it sounds.

Also I cross posted because this is also a vba type question, maybe even more so than a Form question. Sorry. Maybe just move the thread?

[pbaldy]

I'm not sure I would focus on "previous record", as you'd have to be sure the previous record was the only one that the new record could conflict with. I'd use an adaptation of that SQL to compare the new record against all records. You could have a saved query that used the new record in the criteria, and see if it returns any records.

Most of the people who answer questions check all the forums, so I'm not real picky about which forum a question is in, as long as it's in the ballpark. I'll certainly move this one if you'd prefer.

[Aweiher]

I tried your SQL, changing the related info. This is what I used
SELECT tblSchedule.scInspector, tblSchedule.scStartTime, tblSchedule.scEndTime, tblSchedule.scDate
FROM tblSchedule
WHERE (((tblSchedule.[scStartTime])<=[Forms]![frmSchedule].[scEndTime]) AND ((tblSchedule.[scEndTime])>=[Forms]![frmSchedule].[scStartTime]) AND ((tblSchedule.scDate)=[forms]![frmSchedule]![txtGoToDay]));

These were my results.



This doesn't help at all. I don't think I'm conveying what I mean properly. Look at the attached picture of my form. See how Ed Day is scheduled to be in two places at once? I want that to alert the user when they try to close the form.


Thank you!

[pbaldy]

You would have to account for the person as well. Can you attach the db here?

[rpeare]

I think we'd really need to know how your form is set up, if it's based on a crosstab query that's one thing
if it's an unbound form that's another
if it's a main form/subform arrangement that's yet another.

That being said, pbaldy is steering you in the right direction, you don't want to check 1 record (either 'before' or 'after') you want to check against all records for that person on the same day to make sure there are no overlapping spans.

Have you got a sample database we could take a look at?

[Aweiher]

I'm trying to add a copy but it's 112mb. I tried zipping it and that's not working either (it's just timing out). ugh..

[Aweiher]

I think we'd really need to know how your form is set up, if it's based on a crosstab query that's one thing
if it's an unbound form that's another
if it's a main form/subform arrangement that's yet another.

That being said, pbaldy is steering you in the right direction, you don't want to check 1 record (either 'before' or 'after') you want to check against all records for that person on the same day to make sure there are no overlapping spans.

Have you got a sample database we could take a look at?

It's a bound continuous form, with a crosstab subform in the Header. I'm only concerned with the Details section of the form, not the Crosstab portion. You might as well pretend there's no subform for the sake of the question.

[rpeare]

you can make your database smaller by only importing the objects needed for this question into a different database and removing all but a sample of the data (leave enough sample data to replicate the problem) then compressing/zipping the file. We don't need the actual database just the portions that don't work the way you want along with any objects (queries, tables, reports, etc) necessary to make it run.

I would think in the code running in the ON CLICK event of the 'add new appointment' button would be the perfect place to put the check to make sure the 'new' appointment is not overlapping any other appointments for the same person on the same day, basically a validation rule. This is why I prefer unbound forms because you can do this type of validation check prior to any data being added to your table.

[Aweiher]

I deleted everything that wasn't pertinent and compressed it and its still 3.36mb, which is too large (500k max). Can I email it?

[rpeare]

I'll send you a PM with my email address

[Aweiher]

ok repeare I just emailed it to you. Thanks and sorry for the slow response.