asked on

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

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smods

ASKER

Thanks! Should it not be [Active] = Yes", ???

Carl Tawn

Possibly, couldn't remember off the top of my head. Although I imagine it would accept both.

smods

ASKER

I'm getting the following error:

Run-time error '3464':

Data type mismatch in criteria expression

Carl Tawn

What data type have you used for your "Active" column?

smods

ASKER

Text as the result in the column is either a yes or no

Carl Tawn

In that case you'll need to treat the value as text by using:

[Active] = 'yes'

Open in new window

smods

ASKER

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??

Carl Tawn

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.

smods

ASKER

Sorry I am unsure how to do this?

Carl Tawn

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.

smods

ASKER

Ahhhh! My fault! I won't even tell you how dumb I was being haha!

Thanks for your help :)