Link to home
Start Free TrialLog in
Avatar of smods
smods

asked on

Whats missing from this query?

Hi all,

Please see the attached database.  You'll see on the form frm_KPI_main that when a month and person are selected their KPI's are returned.  Then if you click on the discrepencies tab you'll see it picks up anything for January, not just Person A's.  How would I update this so only the discrepencies are picked up for the month and person selected on the form?

Regards

Chris
Avatar of smods
smods

ASKER

ASKER CERTIFIED SOLUTION
Avatar of RgGray3
RgGray3

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
BTW,  you could make references in your query to the subforms on the top of the page...  but the syntax is a pain...  and I never get it right...   so using these global vars always seems to get the job done
Avatar of smods

ASKER

Thanks RgGray3.

Within the After update of your two subforms on the top of your form I would set these vars with the criteria

   strMonth =  Me.Order   (for your select month

   strCashier = Me.Cashier


Where are you referring to here sorry?  The after updates in the discrepencies subform? or just in the main form?  The cashier and month are only picked in the KPI form, not in the discrepencies form.

Create two functions to retrieve those vars

Public Function GetStrMonth() as string
    GetStrMonth = strMonth
end Function

Do the same for the Cashier

Are these modules?

Thanks again for your help

Regards

Chris
In your after update in each of the two list boxes on the top of your page...
That would set your vars as you update them

If these list boxes were bound... they could be used as filters for the subforms... but you can't use the Link child/parent properties with unbound forms

So setting and passing the variables allows you to communicate between the forms

Yes it is a standard code module

Create a general module copy and paste the attached code (or create a similar set of declarations and Functions with your own naming convention

set the vars in the after update of each of the subforms on top of the page
place the calls to these functions in your selection criteria in your query...

WHERE (((tbl_Months.MonthID)=GetStrMonth()) AND ((tbl_Discrepencies.Cashier)=GetStrCashier()));


You would also need to requery the subform with the after update to refresh the data if someone changes either of the list boxes
Public strMonth as string    
Public strCashier as string

Public Function GetStrMonth() as string
    GetStrMonth = strMonth
end Function

Public Function GetStrCashier() as string
    GetStrMonth = strMonth
end Function

Open in new window

Avatar of smods

ASKER

Okay thanks! I've got most of it but I'm getting an error now with some existing code.  

When clicking the discrepencies tab I get the following error:

Run-time error '3079':

The specified field 'Cashier' could refer to more than one table listed in the FROM clause of your SQL statement.

I click debug and the following code is highlighted in yellow.

Private Sub DisplayDiscSubForms()

        If DCount("*", _
              "qry_Discrepencies", _
              "Cashier = """ & Cashier & """ AND " & _
              "Order = """ & Order & """") = 0 Then
        subfrm_Discrepencies.Visible = False
    Else
        subfrm_Discrepencies.Visible = True
    End If
End Sub

Open in new window


This looks like the code that says if no match is found in the query to not display the subform.

Any ideas?

Thanks for your help!

Chris
Avatar of smods

ASKER

Also getting a compile error when clicking the cashier saying variable not definded in the after update.  and your line of code is highlighted:  strCashier = Me.Cashier

Do I need more code in here?
Did you create a Standard/Global module and put the block of code I posted in it??

It is defined there

Avatar of smods

ASKER

Hi RgGray3.

I've tried again this morning when not so tired and don't get the error stating the variable is not defind now but am still getting the run time error message due to the existing code I mentioned above.

Regards

Chris
In your query, remove the checkmark for the Cashier field

I believe you are confusing Access by bringing in 2 copies of the field
1 with the tbl_Discrepencies.* and one with the criteria...

I noticed I was a sloppy with my cut and paste....
Notice that the GetStrCashier  I am manipulating the strMonth var ...

so the proper code
Public Function GetStrCashier() As String
    GetStrCashier = strCashier
End Function

I am stepping out for a few hrs....  will look for your response
Avatar of smods

ASKER

In your query, remove the checkmark for the Cashier field

I believe you are confusing Access by bringing in 2 copies of the field
1 with the tbl_Discrepencies.* and one with the criteria...

This appears to have done the trick!

I noticed I was a sloppy with my cut and paste....
Notice that the GetStrCashier  I am manipulating the strMonth var ...

so the proper code
Public Function GetStrCashier() As String
    GetStrCashier = strCashier
End Function

Amazingly I actually noticed this before you posted and changed it!! I'm not very good with access so am chuffed I noticed this.

You would also need to requery the subform with the after update to refresh the data if someone changes either of the list boxes

This appears to be the only issue now.  I select a cashier and a month and it displays that discrepency, however when I change the month the discrepency remains.  How would I resolve this?

Many thanks

Chris
Send me another copy so I see where you are....
Will look at it when I get back
Avatar of smods

ASKER

Here you go
Copy-of-ee5.mdb
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of smods

ASKER

Cheers Rich

Now a question back at you...  Why don't you use a tab control instead of the option box?
Just curious

Do you mean for the KPI & discrepency tab?

If you see this question I wanted to change the tab colour to make it look better and a work around was supplied.
OK..  I will usually sacrifice some "Look and Feel" for "Ease of Use" and "Ease of Programming"

I fancy mine up but I'll only go so far...

So did it work.......   are we done?

Avatar of smods

ASKER

Yes thank you! Lots more questions coming over the next week!  Please keep an eye out!

Chris
Will do... and your welcome.

Rich