Solved

Whats missing from this query?

Posted on 2011-02-14
18
328 Views
Last Modified: 2012-05-11
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
Comment
Question by:smods
  • 9
  • 9
18 Comments
 

Author Comment

by:smods
Comment Utility
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 total points
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
Did you create a Standard/Global module and put the block of code I posted in it??

It is defined there

0
 

Author Comment

by:smods
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
Send me another copy so I see where you are....
Will look at it when I get back
0
 

Author Comment

by:smods
Comment Utility
Here you go
Copy-of-ee5.mdb
0
 
LVL 11

Assisted Solution

by:RgGray3
RgGray3 earned 500 total points
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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
 

Author Comment

by:smods
Comment Utility
Yes thank you! Lots more questions coming over the next week!  Please keep an eye out!

Chris
0
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
Will do... and your welcome.

Rich
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now