Preventing duplicate entries

[cytruden]

You guys are SO smart!! I have been finding answers to my questions on this
site for 2 years, and I have finally reached a point where I feel I need to
ask my own question.

I am developing a database to schedule classrooms in our building. I would
like the database to inform the user if there is a scheduling conflict. The
fields I have in my table are Classroom Day StartTime and EndTime.

What I would like to be able to do is write code into my form that looks at
the records that I have already put in, and warns me if I am trying to
schedule the same Classroom on the same Day that starts between the StartTime
and EndTime when that classroom is already scheduled. For example, if
Classroom A is scheduled on Monday starting at 9:00 AM and ending at 9:50 AM,
I want to be warned that there is a conflict if I try to schedule Classroom A
on Monday at 9:30 AM.

I have looked at other posts using the DLookup and DCount functions, but the
problem I am running into is the BETWEEN time issue. Not quite sure how to
resolve this, or if it is possible.

Thank you in advance for any help!

 

[Ryan]

The way I handle this is to join the 3 fields in the underlying query. Go
into the query the form is built on and and a field like this
Conflict[Classroom]&""&[Day]&""&[StartTime]&""&[EndTime])
This will join all 4 fields into one string. Next you would add a hidden
field on your form that joins what the user is requesting. Then on your
"OnClick" event of your save button you would run your Dlookup on the
Conflict column in your query with the hidden field on the form and tell it
not to add the record if it already exists. I would be happy to give you the
code for that if you give me the field names on your form.

 

[Douglas J. Steele]

Take a look at what Allen Browne has at
http://www.allenbrowne.com/appevent.html

 

[Douglas J. Steele]

It may be me, but I don't see how that will work. Let's say room A is
currently booked from 9:00 to 10:00, and someone wants to book it from 8:00
to 11:30. How will you detect the conflict?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The way I handle this is to join the 3 fields in the underlying query. Go
into the query the form is built on and and a field like this
Conflict[Classroom]&""&[Day]&""&[StartTime]&""&[EndTime])
This will join all 4 fields into one string. Next you would add a hidden
field on your form that joins what the user is requesting. Then on your
"OnClick" event of your save button you would run your Dlookup on the
Conflict column in your query with the hidden field on the form and tell
it
not to add the record if it already exists. I would be happy to give you
the
code for that if you give me the field names on your form.

You guys are SO smart!! I have been finding answers to my questions on
this
site for 2 years, and I have finally reached a point where I feel I need
to
ask my own question.

I am developing a database to schedule classrooms in our building. I
would
like the database to inform the user if there is a scheduling conflict.
The
fields I have in my table are Classroom Day StartTime and EndTime.

What I would like to be able to do is write code into my form that looks
at
the records that I have already put in, and warns me if I am trying to
schedule the same Classroom on the same Day that starts between the
StartTime
and EndTime when that classroom is already scheduled. For example, if
Classroom A is scheduled on Monday starting at 9:00 AM and ending at 9:50
AM,
I want to be warned that there is a conflict if I try to schedule
Classroom A
on Monday at 9:30 AM.

I have looked at other posts using the DLookup and DCount functions, but
the
problem I am running into is the BETWEEN time issue. Not quite sure how
to
resolve this, or if it is possible.

Thank you in advance for any help!

 

[Ryan]

Douglas is correct. I gave you a way to check for exact matches, but not
overlapping times.

It may be me, but I don't see how that will work. Let's say room A is
currently booked from 9:00 to 10:00, and someone wants to book it from 8:00
to 11:30. How will you detect the conflict?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The way I handle this is to join the 3 fields in the underlying query. Go
into the query the form is built on and and a field like this
Conflict[Classroom]&""&[Day]&""&[StartTime]&""&[EndTime])
This will join all 4 fields into one string. Next you would add a hidden
field on your form that joins what the user is requesting. Then on your
"OnClick" event of your save button you would run your Dlookup on the
Conflict column in your query with the hidden field on the form and tell
it
not to add the record if it already exists. I would be happy to give you
the
code for that if you give me the field names on your form.

You guys are SO smart!! I have been finding answers to my questions on
this
site for 2 years, and I have finally reached a point where I feel I need
to
ask my own question.

I am developing a database to schedule classrooms in our building. I
would
like the database to inform the user if there is a scheduling conflict.
The
fields I have in my table are Classroom Day StartTime and EndTime.

What I would like to be able to do is write code into my form that looks
at
the records that I have already put in, and warns me if I am trying to
schedule the same Classroom on the same Day that starts between the
StartTime
and EndTime when that classroom is already scheduled. For example, if
Classroom A is scheduled on Monday starting at 9:00 AM and ending at 9:50
AM,
I want to be warned that there is a conflict if I try to schedule
Classroom A
on Monday at 9:30 AM.

I have looked at other posts using the DLookup and DCount functions, but
the
problem I am running into is the BETWEEN time issue. Not quite sure how
to
resolve this, or if it is possible.

Thank you in advance for any help!

 

[cytruden]

Thank you both for your replies. This link looks promising. I'll post again
if I can't get it to work.

Thanks again!