Solved

Access Graph data source based on forms control

Posted on 2013-01-09
19
470 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
ID: 38758397
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
ID: 38758725
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
ID: 38758780
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Expert Comment

by:mbizup
ID: 38758888
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
ID: 38759180
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
ID: 38759282
Can you upload a sample db with any sensitive data masked or removed?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38759288
Preferably .mdb format.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38759292
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
ID: 38759376
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
 

Author Comment

by:PaulBS
ID: 38759550
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
ID: 38759572
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 38759659
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
ID: 38759830
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
ID: 38760058
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
ID: 38760134
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38760145
:-)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38760236
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
ID: 38760437
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
ID: 38762732
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

831 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