• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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
0
smods
Asked:
smods
  • 9
  • 9
2 Solutions
 
smodsAuthor Commented:
0
 
RgGray3Commented:
OK...  I think I see what you are trying to do and what is happening...

You seem to be trying to filter the Subform on your Discrepencies tab by the two subform on the main form

however you are not using the LinkchildField and Link Master field...  which with your form's configuration...  I don't believe you can...  the two subforms on the top of the page are unbound.

The filter in the query qry_Discrepencies is only filtering on the Month.

      WHERE tbl_Months.MonthID=tbl_Discrepencies.MonthID;

So you need to fine tune your where clause...  

What I do in situations like this is I create a "Global VAR" (or vars in this situation) to hold my where filter criteria


IE:   In a standard module create the following declarations
       Public strMonth as string    
       Public strCashier as string

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


Create two functions to retrieve those vars

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

Do the same for the Cashier

Now in your query qry_Discrepencies that is your record source for the subform subfrm_Discrepencies
becomes

SELECT tbl_Months.Order, tbl_Discrepencies.*, tbl_Discrepencies.Cashier
FROM tbl_Discrepencies, tbl_Months
WHERE (((tbl_Months.MonthID)=GetStrMonth()) AND ((tbl_Discrepencies.Cashier)=GetStrCashier()));

Basicly your not filtering for the cashier at all in your query

your query
SELECT tbl_Months.Order, tbl_Discrepencies.*
FROM tbl_Discrepencies, tbl_Months
WHERE tbl_Months.MonthID=tbl_Discrepencies.MonthID;

You are only filtering for the Month

Hope this helps
0
 
RgGray3Commented:
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
smodsAuthor Commented:
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
0
 
RgGray3Commented:
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

0
 
smodsAuthor Commented:
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
0
 
smodsAuthor Commented:
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?
0
 
RgGray3Commented:
Did you create a Standard/Global module and put the block of code I posted in it??

It is defined there

0
 
smodsAuthor Commented:
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
0
 
RgGray3Commented:
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
0
 
smodsAuthor Commented:
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
0
 
RgGray3Commented:
Send me another copy so I see where you are....
Will look at it when I get back
0
 
smodsAuthor Commented:
Here you go
Copy-of-ee5.mdb
0
 
RgGray3Commented:
OK...    

Issue 1,  the control "Order" has two columns

1st is what your displaying  the Month yyyy data
2nd is your display order. (order)

You have the second column bound to the control

Your tbl_Discrepencies has the text for the Month yyyy  (MonthID) and no reference to Order

Not obvious to me initially...

When I changed the Bound column to Month yyyy data the application stopped working...
 so your other logic is actually bound to the "Order" value
Long Story Short...

Changed the global var I used to use the 2 digit string representing order
Modified the query to filter using the ORDER field... and I think we have it...
Added a Subform Requery to force a refresh of the data

See the attached file

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

Back to the attached file....
I have left intact the debug code to show a message box displaying the vars I have used  
Feel free to delete or comment it out.

I left them as it is a good tutorial on how to pass variables to a query

Let me know if there are any other problems

Rich


Final-Copy-of-ee5.mdb
0
 
smodsAuthor Commented:
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.
0
 
RgGray3Commented:
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?

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

Chris
0
 
RgGray3Commented:
Will do... and your welcome.

Rich
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now