Compare records on continious form?

[rpeare]

What button do I need to click to get to the form you are working on, I will pare down the database and post a sample for everyone else to look at as well.

[rpeare]

ok I think I found it

just the 'schedule' button

Just curious here. This seems like an overly complex design, are you married to it?

[rpeare]

EDIT: if anyone else wants a copy of the database I can forward one through email but because of the design I can't get it under 500k without destroying parts that might be relevant.

I haven't noticed any problems with your data structure but your user interface more complex than it needs to be.

For instance, just on your frmSchedule you have:

1. The form with the crosstab in it (the 'main' form for the purposes of your example)
2. A form to add a new scheduled item (frmAddNewAppointment/frmAddNewAppointmentsubform)
3. A form to find a scheduled item (frmFindAppointments)
4. A result form (2 of them) for the find scheduled item (frmFindAppointmentResultsWithoutLocation/frmFindAppointmentResults)
4. A form for unscheduled appointments (frmUnscheduledAppointments)
5. A form to edit appointments (frmEditAppointment)

All of which have something to do with the table tblSchedule and all of them on bound forms. All of these are supported by queries in your database window. I'm not sure what's chewing up space in your database the smallest I could get it was 4.4 meg before zipping it so I don't know that I can upload it either because of the complexity of having multiple forms all referencing the same table I have to include them. The smallest I could get it with all the objects included was 853kb with 7-zip.

All of these functions I'm fairly certain could be done on a single form using unbound controls and your users wouldn't have to go from form to form to form. The reason I'm bringing this up is that your original question was to prevent your database from allowing overlapping schedules but that function is not actually performed on the form you put a screen shot of. It's actually done on either frmAddNewAppointment or frmEditAppointment (maybe other places as well, it's hard to wade through your logic on all these forms to get an idea of what you were trying to do).

If you want to keep this structure mostly intact, I would put the overlapping span check on frmEditAppointment or frmAddNewAppointment and instead of having the employees be a subform have it be an unbound control with a list box and on your 'save' button relating to adding an employee check the span of the appointment against any others for that same employee.

[Aweiher]

owe, I am kind of married the design. But I say that mostly because I would rather not incorporate this feature then reformat the design. You bring up a valid point about having the overlapping span check on the forms frmEditAppointment and frmAddNewAppointment. What kind of code would check to see if the span of the appointment against existing ones of the same employee? I don't even understand how that can be accomplished because the database for example might know that Employee X is starting an appointment at 8:00am, and will be done at 11:00am, but doesn't know that the employee is busy at 8:01am, 8:02am, etc.

If you can put me on the right path to write the code that can check the span of two times then I can probably take it from there, otherwise I may give up. Either way I want to thank you for all of the effort you've put into this.

[sstiebinger]

Compare the new appointment against any found records.

So you create a new appt for John Smith.
You look for existing records for john smith for that day, and for each you check if the current appoint is within the same start/end time, the example from the baldyweb link would work for this.
If the new record isn't within the bounds, then create the new record.

[rpeare]

I am struggling with this because you have some odd things happening in your forms, I am stripping it down to the tables and an example unbound form that will perform a check on a new appointment request I am not dealing with your current forms at all, they are too cumbersome. You really should not start to lock down your database in terms of functions (closing windows, pop-up/modal, etc) before your database is functioning correctly otherwise you get all kinds of problems when trying to perform edits.

I'll try to have something out to you later today

[rpeare]

Example database using an unbound form

aweiher_modfied.zip