Compare two form fields, prevent duplicate combination

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form for creating classes in a school. I want to make sure I don't
double-book a room with two classes during the same time. I have a field
called ROOM, & a field called TIME. How can I make sure that I can't enter
the same room number AND time together? Thanks...
 
In your table, highlight both fields by holding down control and clicking
them. While they are highlighted, click the "key" icon. This will create a
compound key. You can look at the indexes to see what it did.

You should be able to search the help file for "compound index" and get more
details.
Rick B
 
That worked well. Thanks! Now, I noticed that the message that appears when
I have a possible duplicate is written in "Access". I would like to have an
"English" message appear to warn users that they can't input the same room at
the same time. What is the best way to do that?
 
Are you entering data directly into a table? If yes, then no you cannot
intercept that message. But then, you should not enter data directly into a
table anyway, you should use a form.

When you use a form, you can run code on the form's BeforeUpdate event to
test if you are entering a duplicate pair of data, and then inform the user
of that and intercept the ACCESS message.

Post back with more info about your setup and we'll assist you on this.

Also, TIME should not be used as the name of a field in a table (nor a field
in a query nor a control on a form nor a control on a report). It's a
reserved word, and you can create major confusion for ACCESS when you use it
this way (Time is the name of a VBA function in ACCESS). See this Knowledge
Base article for more information:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top