Avoiding selecting the same entry twice

[Cardinal]

I have a room reservation application that is shaping up nicely thanks
to the kind suggestions (and code help) of several forum members.
Thank you very much for that. One of the drop downs deals with "Start
Times" where a user can select a start time for the event that is to
take place. The list of choices for "Start Time" comes from a query
that pulls all the different half hour time increments for a given
business day. Is there any way that I can keep the user from selecting
the same start time as an event that has already been entered? Please
see the graphic that accompanies this explanation. Thanks very much.

http://209.200.80.147/Elite/Test/avoid.html

 

[Beetle]

You could use DLookup to check if the selected time has already been
used for that day.

This example assumes you have a table (tblEvents) with fields
EventDate and StartTime that are Date/Time data types.
You would do this in the Before Update event of the StartTime
control on your form.

Private Sub StartTime_BeforeUpdate (Cancel As Integer)

If DLookup("StartTime", "tblEvents", "EventDate=#" & Me!EventDate _
& "# And StartTime=#" & Me!StartTime & "#")>0 Then
MsgBox "Please select a different start time"
Cancel = True
Me!StartTime.SetFocus
End If

End Sub

 

[Cardinal]

Thank you for your code suggestion.
I modified your code to reflect my field names. The StartTime that you
refernece in your code above is actually a value and not a Date/Time.
This is because the Event.StartTimeID field actually references
another table that pulls in all the available start times. I used the
code below and it throws an error message that states: "Syntax error
in date in query expression 'StartDate=#11/19/2008# And
StartTimeID=#2#


Private Sub StartTimeID_BeforeUpdate(Cancel As Integer)
If DLookup("StartTimeID", "Event", "StartDate=#" & Me!StartDate _
& "# And StartTimeID=#" & Me!StartTimeID & "#") > 0 Then
MsgBox "Please select a different start time"
Cancel = True
Me!StartTimeID.SetFocus
End If
End Sub

 

[Brian]

....or you could make the primary key of the Event table a multi-field key
that includes the room, date, and start time. Then, capture the error that
occurs when a user violates the PK by selecting an already-used
room/date/time combination and give the user a nice message telling him to
select a different time or date or room.

 

[Pete D.]

One is to do a check on the file and make sure you don't duplicate a
combination of fields such as room, date, time. A quick test with a query
and popup box will help prevent this. I also add the ability to over ride
it, for instance, same room, date, time is already in use...would you like
to replace this reservation with this update? Yes, No and then according to
the answer continue normally or return them back to the form to select a
different room. To take it one step further why not make it so user must
select date, and time first and then display a list of rooms still available
to select from with a query combobox. Just food for thought and if you try
it and come back with specific questions, help here will assist you through
it.

 

[Beetle]

In that case, assuming StartTimeID is numeric, then it should be;

Private Sub StartTimeID_BeforeUpdate(Cancel As Integer)
If DLookup("StartTimeID", "Event", "StartDate=#" & Me!StartDate _
& "# And StartTimeID=" & Me!StartTimeID) > 0 Then
MsgBox "Please select a different start time"
Cancel = True
Me!StartTimeID.SetFocus
End If
End Sub

 

[Cardinal]

Thank you very much. Now when I make a time selection that has been
previously used on that day I get a window that states, "Please select
a different start time," just as your code would indicate. This is
perfect and exactly what I am after. However, when I click on OK to
get out of that window, the app throws a "Run-time error 2108," and
states, "You must save the field before you execute the GoToControl
action, the GoToControl method, or the Set Focus method. Any thoughts?
Thanks again for all your help.

 

[Beetle]

Sorry. Minor brain cramp on my part. We can't use the SetFocus method
in this case (the Before Update event) because the focus hasn't left the
control yet. Just eliminate the line

Me!StartTimeID.SetFocus

and you should be good.

 

[Cardinal]

I removed the set focus and it worked perfectly. Here is the code that
works.

Private Sub StartTimeID_BeforeUpdate(Cancel As Integer)
If DLookup("StartTimeID", "Event", "StartDate=#" & Me!StartDate _
& "# And StartTimeID=" & Me!StartTimeID) > 0 Then
MsgBox "Please select a different start time"
Cancel = True
End If
End Sub

Would you entertain one more question on this thread? If I wanted to
add one more item on here that it could not match, how could I do it?
I now realize that there are two items that cannot match, for a given
date in time. StartTimeID (which the above code refects, and RoomID
which the above code does not reflect. I did try and paste RoomID in
using the pattern in the code above but I could not seem to get it
working. Thank you once again.

 

[Beetle]

If RoomID is a numeric value, then you should be able to use this;

Private Sub StartTimeID_BeforeUpdate(Cancel As Integer)
If DLookup("StartTimeID", "Event", "StartDate=#" & Me!StartDate _
& "# And StartTimeID=" & Me!StartTimeID _
& " And RoomID=" & Me!RoomID) > 0 Then
MsgBox "Please select a different start time"
Cancel = True
End If
End Sub

Although, it may actually be a bit more efficient to use the method that
Brian suggested a couple of days ago, an put a unique key on those fields
with a message box that displays when the key is violated. It's up to you,
whichever your more comfortable with.