Solved

Access Graph data source based on forms control

Posted on 2013-01-09
19
446 Views
Last Modified: 2013-01-10
Hi,

I am trying to add a graph to an access 2010 form.
The source of the graph is a union query, one part of which is using the value of a combo box on the form itself.

If I had code the value in the Union SQL it works fine but when I replace the value with the controls reference the graph fails to display although the union query works fine.

The message I get is -

"The Microsoft Waccess dtabase engine does not recognise '[Fomrs]![frmShellVolumeUsage]![cmbSelectDriver]' as a valid field name or expression."
Then I get an OLE server error.

Is there a way to use field controls as sources for graphs or am I doing something else wrong.

Here is the UNION SQL -

SELECT tblShellTransactions.[Fleet ID], Sum(tblShellTransactions.Volume) AS [Weekly Volume], DatePart("ww",CDate([Transaction Date & Time])) AS [Week Number]
FROM tblShellTransactions
GROUP BY tblShellTransactions.[Fleet ID], DatePart("ww",CDate([Transaction Date & Time]))
HAVING (((tblShellTransactions.[Fleet ID])=[Forms]![frmShellVolumeUsage]![cmbSelectDriver]))
UNION
SELECT "All" AS [Fleet ID], Avg(tblShellTransactions.Volume) AS AvgOfVolume, DatePart("ww",CDate([Transaction Date & Time])) AS WeekNum
FROM tblShellTransactions
GROUP BY "All", DatePart("ww",CDate([Transaction Date & Time]));

Thanks.
0
Comment
Question by:PaulBS
  • 7
  • 6
  • 3
  • +1
19 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Have you double-checked that the spelling and the path to the control are correct?

The syntax looks okay, assuming that cmbSelectDriver is the name of the combo you are referring to on frmShellVolumeUsage, and there are no subforms involevd.   You can also try:

SELECT tblShellTransactions.[Fleet ID], Sum(tblShellTransactions.Volume) AS [Weekly Volume], DatePart("ww",CDate([Transaction Date & Time])) AS [Week Number]
FROM tblShellTransactions
WHERE tblShellTransactions.[Fleet ID] =[Forms]![frmShellVolumeUsage]![cmbSelectDriver]
GROUP BY tblShellTransactions.[Fleet ID], DatePart("ww",CDate([Transaction Date & Time]))
UNION 
SELECT "All" AS [Fleet ID], Avg(tblShellTransactions.Volume) AS AvgOfVolume, DatePart("ww",CDate([Transaction Date & Time])) AS WeekNum
FROM tblShellTransactions
GROUP BY "All", DatePart("ww",CDate([Transaction Date & Time]));

Open in new window

0
 

Author Comment

by:PaulBS
Comment Utility
Hi mbizup,

Yes, the control reference is correct (checked by changing the name) also ran the first half of the UNION query and that ran fine. In fact the union query runs fine but gives this error when used as the source for the graph control (which is on the same form as the control).
I am thinking that when the graph control loads it tries to check its source data but because the form isn't completely loaded (or because the combo box has no value initially) the query doesn't return data?

BTW tried your suggested SQL and again works fine in UNION query but same error from the Graph control.

Cheers,

Paul
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
That's entirely possible - you get the same type of error referring to controls between subforms and mainforms if the timing isn't right.

I don't have Access in front of me at the moment (or the ability to test this), but can you try setting the source for the graph dynamically from the Open Event of the main form (to ensure that the main form is fully loaded before making the reference to the combo box)?

Save the SQL as a stored query for simplicity and try something like this in the main form's Open Event:

Me.MyChartObject.Recordsource = "YourQuery"

I'm not sure that recordsource is the correct property name for a chart object, but there is probably some similar property name for controlling this...
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Ah - your chart object has a RowSource property which contains it's SQL - so you would be setting it like this:

Me.MyChartObject.Rowsource = "YourQuery"

However, another, possibly easier approach would be to place a button on the form containing the combobox you need for the Chart's rowsource, and place the chart on it's own separate form.  Use the command button to open the form that the chart is on...

Docmd.OpenForm "YourChartFormName"

That's another way of ensuring that the form is loaded before it is needed by the chart object.
0
 

Author Comment

by:PaulBS
Comment Utility
mbizup,

Thanks for the suggestions.

I have tried updating the rowsource on open for the form and after update for the combo box but neither worked.
I have tried adding the graph to a new form opened after update from the combo box and still get the same error.

The only way so far of stopping the error is to hard code the "variable".

OR

To create two graphs each based on one side of the union, in which case it works.

So far variable + UNION query = error :(
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Can you upload a sample db with any sensitive data masked or removed?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Preferably .mdb format.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Paul,
try saving the Union query as "qryUnion", then create a new query using the "qryUnion" as domain

use the new query as the source for your graph
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
Comment Utility
FWIW

For a Group By Query, I typically use a variable to store the value, then use a Function to retrieve it , and use it as the criteria:

Something "Roughly" like this:

In a Module:
Public lngpubYourVal as long

Public Function GetYourVal() as Long
    GetYourVal=lngpubYourVal
End Function

In your code somewhere:
lngpubYourVal =[Forms]![frmShellVolumeUsage]![cmbSelectDriver]

In your query:
SELECT
FROM
GROUP BY
HAVING xxxx=GetYourVal()
...

JeffCoachman
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:PaulBS
Comment Utility
Hi capricorn1,

Thanks for helping. I have now tried creating a new source for the graph that is a query with its domain as the old query but that gave the same error.

mbizup,

Attached is a basic copy of the mdb.

Cheers,

Paul
test.mdb
0
 

Author Comment

by:PaulBS
Comment Utility
Hi boag2000,

Tried your suggestion by having the form on load fill the variable with "" then when combo box updated populate the variable with the combo box (reference with me.cmbselectEmployee) and then docmd.requery the graph control and.....

It worked!

There must be some wierd combination of union query with reference to a form etc... that causes a problem.

Using a public variable got around it (and it is IMO cleaner).

Thank you all for your help.

Paul
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
Comment Utility
you can do it without using a function, try this


SELECT tblShellTransactions.[Fleet ID], Sum(tblShellTransactions.Volume) AS [Weekly Volume], DatePart("ww",CDate([Transaction Date & Time])) AS [Week Number]
FROM tblShellTransactions
WHERE tblShellTransactions.[Fleet ID] =Eval("[Forms]![frmShellVolumeUsage]![cmbSelectEmployee]")
GROUP BY tblShellTransactions.[Fleet ID], DatePart("ww",CDate([Transaction Date & Time]))
UNION SELECT "All" AS [Fleet ID], Avg(tblShellTransactions.Volume) AS AvgOfVolume, DatePart("ww",CDate([Transaction Date & Time])) AS WeekNum
FROM tblShellTransactions
GROUP BY "All", DatePart("ww",CDate([Transaction Date & Time]));
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
capricorn1 is also correct, in that Eval() will work in most cases as well.
(and requires less modification to your app)

The reason I use a function is that:
A Function is code based, so you can add validation and comments
As a function it is more "Portable" (can be moved to another app, if you change it once, it will be changed everywhere it is called)

So it is good to know more than 1 way to deal with an issue.
;-)

OK, so how about the Graph?

JeffCoachman
0
 

Author Comment

by:PaulBS
Comment Utility
Hi all, yes the graph works with the function solution.
I am at home and can't test the eval() option so will test it in the morning and award points.
Cheers
Paul
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Jeff (boag2000) has clearly nailed this, but just a note on the function - you can include the form reference right in the function itself so that you don't have to declare a global variable or set it in your combo's After Update event:

Public Function GetYourVal() as Long
    GetYourVal= [Forms]![frmShellVolumeUsage]![cmbSelectEmployee]
End Function

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
:-)
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Another thing worth looking into is the TempVars collection:
http://blogs.msdn.com/b/thirdoffive/archive/2006/04/13/570539.aspx

This can be used to store values from form controls, and you can include them in code/queries/property sheets etc without using long form references.

Its a powerful alternative to global variables in .accdb files in Access 2007 and higher (but shouldn't be used if you've got a mixed version environment including versions earlier than that).

No points wanted since this is solved already - just wanted to toss that out there as a very cool feature which I think those of us, myself included, who have been using Access since before the .accdb days tend to forget about :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
mbizup,

And cap was also correct in the option of using Eval().

Again, the Global Variable\Function approach is what I reach for first, ...only because that that was the first way I learned to deal with that situation (comes up in Crosstabs as well)
So it is good that you guys keep reminding me of Eval()
;-)

I saw MX mentioned also on how great Tempvars was, ...I will research more on this myself...
;-)

Jeff
0
 

Author Closing Comment

by:PaulBS
Comment Utility
Thanks to you all for the excellent solutions.
Multiple ways of getting around this odd issue that can also help in many other ways.
Cheers, Paul.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

6 Experts available now in Live!

Get 1:1 Help Now