Solved

Access Graph data source based on forms control

Posted on 2013-01-09
19
478 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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