Using count to avoid duplicates

Posted on 2011-10-11
Last Modified: 2012-05-12
I have an unbound combo box control labeled cboStoreMgr. Before I update the control I would like to make sure that this employee has not already been assigned. I am checking against my table tblEmployee field EmployeeID and would like to use the COUNT
Question by:Frank Freese
    LVL 119

    Accepted Solution

    private sub cboStoreMgr_beforeupdate(cancel as integer)

    if dcoun("*","tblEmployee","employeeid=" & me.cboStoremgr)>0 then
      msgbox "employee already assigned"
    end if

    end sub

    Author Comment

    by:Frank Freese
    thank you
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    I find it extremely annoying to think that I can do something (select an item in a list or combo, or click a command button), only to have the Click or some other event run a check and inform me that that selection is not currently available.

    I would modify the query that is the RowSource so that those which are already appeared do not appear in the combo box.  If you provide a little more information about your form and the rowsource query, I might be able to rewrite that SQL for you.
    LVL 119

    Expert Comment

    by:Rey Obrero

    if dcoun(   '<< missing "t"

    should  be

    if dcount(

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now