Avoid double descriptions

Posted on 2011-10-31
Last Modified: 2012-05-12

I have a large table with projects. The problem is that everyone can put in new projects.
User 1 puts in project : sannest vat 7320
User 2 can't find the project and set it to sannest vat7320. Two different projects.
I would like to check the description before updating and set a msgbox but everything I've tried so far is not working properly.
An idee?

Question by:sonmic
    1 Comment
    LVL 84

    Accepted Solution

    You can use either Form or Control BeforeUpdate to do this, but you'll have a difficult time determining duplicates with data like that, since "sannest vat 7320" is not the same as "sannest vat7320".

    You could try removing all the space characters and comparing those two, but that could be tricky (since you'd also have to remove the space characters from ALL the descriptions in the table, which could be a very large performance hit). It also might or might not do what you like.

    You could build a small routine that looks for the first "word" of that description, and then displays a MsgBox if a match is found. You could use the LIKE operator for this, in a simple SQL statement.

    Or you could use  a Combobox with AutoComplete to allow your users to enter the Description. This way, when User2 types in "sannest" they willl see that someone has already entered "sannest vat 7320" and will (hopefully) select that one.

    If User2 doesn't find it, then you can use the NotInList event of the Combo to add your description. The article below shows how to do that:

    The only real trouble with that is the Recordsource of that combo. You'd have to set it to something like "SELECT DISTINCT Description FROM YourTable". This would show all the current descriptions in your Projects table.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now