What do I need to add to this code?

Hi guys,

I have a form with a button on that when pressed adds a list of cashier names to a continuous form for the selected month.  The code behind the button is as follows:

Private Sub cmdAddCashiers_Click()

    CurrentDb.Execute "INSERT INTO tbl_Cashier_KPI (Cashier, MonthID) " & _
                      "SELECT Cashier, """ & MonthID & """ " & _
                      "FROM tbl_CashierDetails " & _
                      "WHERE NOT EXISTS (SELECT Null " & _
                      "FROM tbl_Cashier_KPI " & _
                      "WHERE Cashier = tbl_CashierDetails.Cashier AND " & _
                      "MonthID = """ & MonthID & """)", _
                      dbFailOnError
    frm_KPI_Sub.Requery

End Sub

Open in new window


This has been working fine but now some people are leaving and so I don't want those poeple adding for the latest months.  If I add another field to the Cashier Details table that says Active and set it to yes or no then how can I adjust the code to only add people that have a "yes" as active?

Thanks

Chris
smodsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
I'd imagine you would need to change it to:
    CurrentDb.Execute "INSERT INTO tbl_Cashier_KPI (Cashier, MonthID) " & _
                      "SELECT Cashier, """ & MonthID & """ " & _
                      "FROM tbl_CashierDetails " & _
                      "WHERE NOT EXISTS (SELECT Null " & _
                      "FROM tbl_Cashier_KPI " & _
                      "WHERE Cashier = tbl_CashierDetails.Cashier AND " & _
                      "MonthID = """ & MonthID & """) AND [Active] = 1", _
                      dbFailOnError

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smodsAuthor Commented:
Thanks! Should it not be [Active] = Yes",  ???
0
Carl TawnSystems and Integration DeveloperCommented:
Possibly, couldn't remember off the top of my head. Although I imagine it would accept both.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

smodsAuthor Commented:
I'm getting the following error:

Run-time error '3464':

Data type mismatch in criteria expression
0
Carl TawnSystems and Integration DeveloperCommented:
What data type have you used for your "Active" column?
0
smodsAuthor Commented:
Text as the result in the column is either a yes or no
0
Carl TawnSystems and Integration DeveloperCommented:
In that case you'll need to treat the value as text by using:
[Active] = 'yes'

Open in new window

0
smodsAuthor Commented:
I no longer get the error but it just does nothing at all now when clicked.  

From that code is [Active] looking at table tbl_CashierDetails??
0
Carl TawnSystems and Integration DeveloperCommented:
Yes, it is an additional clause in the WHERE statement. Is that not the table it should be looking at? Simplify the query first so that you are only pulling back active cashiers, then once you are sure that part is working you can add the EXISTS back in.
0
smodsAuthor Commented:
Sorry I am unsure how to do this?
0
Carl TawnSystems and Integration DeveloperCommented:
Create a dummy query in your database and set its query as:
SELECT * FROM tbl_CashierDetails WHERE [active] = 'yes'

Open in new window

And execute it. That will tell you if the check against the active column is working correctly or not.
0
smodsAuthor Commented:
Ahhhh! My fault! I won't even tell you how dumb I was being haha!

Thanks for your help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.