Full Version: No Duplicate Entries In A Control...
UtterAccess Forums > Microsoft® Access > Access Forms
mraetrudeaujr
How can I restrict what an enduser enters into a textbox/combobox?
For example, I have a popup form that allows the user to enter new 'training names' into a table.
These 'training names' are then used in another form (recording the actual training 'data' for an employee), using a combo box,
and then storing that value into another table.
So there are two tables; a 'data' table (can have duplicate training names of course) and a lookup table containing the training 'names'. I know that I can put a 'limit to list' property on the control, this is not what I'm looking for.
For example, if the training name "Mathematics 101" is stored in this table (example only... not real data stored in this table), and the user is entering training data for a person, and they somehow "overlook" the entry "Mathematics 101" in the drop down combobox, then they bring up the 'popup' to type in a new training name. When they type a new training name into this 'list', I want to prevent them from typing (storing) a training name that already exists in the table. So now, an 'error' popup will come up stating that "That entry is already contained in the Training Names table."
Does this make sense?
Thanks.
Al
Jeff B.
If you use a combobox, you can set the LimitToList property to Yes.
If you use a textbox, you'll have to create a procedure that searches the underlying data to see if there's a match.
(hint: it's a lot more work doing it the second way...)
mraetrudeaujr
Yes, I know about the combobox being set to "Limit To List". But the idea is to add new 'training names' (records) to the training names table.
So, I cannot limit the list.
That means using a text box. This is where the problem comes in.
Sure, I could have my 'popup' form with a command button 'Add New Record' and then type away in the 'Training Name' (control - text box). But I need a way to prevent a user from typing in 'duplicate' names. I know. I had another database where this was happening. It was too much administrative work. I constantly had to run "Find Duplicates" queries to get rid of them.
That's why I need a little help (code?) that I could put on the textbox that would check the entry against the existing entries in the table (After Update event?); and subsequently preventing this from happening.
Al
Jeff B.
Yes, you can LimitToList ... and then add code to the NotInList event that lets the user add a new record.
heck Access HELP for LimitToList and NotInList.
jleach
Aside from Jeff's reply regarding LimitToList and NotInList which probably is a better approach, you can do this with a textbox, but you should do it from the BeforeUpdate event rather than the AfterUpdate event. The builtin DLookup function returns a value or Null if there's no value, so you can call the function to look up an ID value from the table in question whose ID criteria will equal the value of the textbox: if DLookup returns Null, there's no previous entry. Code is a one-liner for the check and a three or four liner to cancel the update depending on coding style.
JonSmith
Hi Al,
If you want to check for duplicates then I think you want to look into DCount, if you DCount for lookup list for the new value, if its a duplicate it will return 1 if its new it will return 0. Its better than DLookup due to the fact it will never return a null and then you can act appropriately depending on the outcome.
Jon
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.