?
Solved

Access Graph data source based on forms control

Posted on 2013-01-09
19
Medium Priority
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 1600 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 400 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

762 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