Solved

Whats missing from this query?

Posted on 2011-02-14
18
356 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
ID: 34888371
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 total points
ID: 34888686
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
ID: 34888703
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:smods
ID: 34888765
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
ID: 34889289
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
ID: 34892695
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
ID: 34892799
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
ID: 34893589
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
ID: 34895516
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
 
LVL 11

Expert Comment

by:RgGray3
ID: 34896556
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
ID: 34896878
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
ID: 34896978
Send me another copy so I see where you are....
Will look at it when I get back
0
 

Author Comment

by:smods
ID: 34897134
Here you go
Copy-of-ee5.mdb
0
 
LVL 11

Assisted Solution

by:RgGray3
RgGray3 earned 500 total points
ID: 34899415
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
ID: 34902557
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
ID: 34902644
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
ID: 34905797
Yes thank you! Lots more questions coming over the next week!  Please keep an eye out!

Chris
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34906215
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Programmer 14 49
object variable or with block not set 6 29
Delete QueryDef IF it Exists: Access VBA 5 35
Dcount using a date in a table compared to today's date 3 30
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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