Using count to avoid duplicates

Posted on 2011-10-11
Medium Priority
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
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36948853
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
ID: 36948911
thank you
LVL 49

Expert Comment

by:Dale Fye
ID: 36948920
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36948954

if dcoun(   '<< missing "t"

should  be

if dcount(

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

807 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