Chart Query (1 Viewer)

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
I have concatenated the data from 2 tables through the union query below to get my dates on Combobox for my modern charts.

SELECT Format([Date_Report],"mmmm yyyy") AS Month
FROM tblNcr_Sor
UNION
SELECT format([Date_Report],"mmmm yyyy")AS Month
FROM tblWir_Itp;

But when i use this query on my combo row source the data i will get in the combo but for the charts, it still shows me the error to Enter Parameter value?

1643006177113.png


Link child fields : Month
Link Master field ; cmbMonth (My combo)

When i use query form one of my any table to this combo its working fine but from union query its not. Any one who can explain where i am worng

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:05
Joined
Oct 29, 2018
Messages
21,357
Hi. Is that the Row Source of your Combobox or the SQL statement of a saved query?
 

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Hello. I create a separate new union query through the above SQL statement and then i use this query to my combo box rowsource .
1643007406697.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:05
Joined
Oct 29, 2018
Messages
21,357
Hello. I create a separate new union query through the above SQL statement and then i use this query to my combo box rowsource . View attachment 97755
Okay, you are saying when you double click that query from the nav pane, it opens without any prompt, right? But, when you select it as the row source for your combo, you get the prompt, right?
 

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Yes its opening form nav pane. but when i try to link it to my charts through combo then its prompts me and if i click cancel on this prompt i can see the data in the combobox but charts are giving me this prompt below is the property of chart.

1643009188598.png



Rowsource of combobox from this query

1643009297661.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:05
Joined
Oct 29, 2018
Messages
21,357
Not sure I'm following yet. Are you able to post a sample db? For one thing, using "Month" as an alias could be problematic, because it's also the name of a built in function.
 

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Alright. I have tried changing to Month_Year but it gives the same error. I am attaching DB as a sample.
 

Attachments

  • DB Sample.accdb
    1.6 MB · Views: 290

theDBguy

I’m here to help
Staff member
Local time
Today, 07:05
Joined
Oct 29, 2018
Messages
21,357
Alright. I have tried changing to Month_Year but it gives the same error. I am attaching DB as a sample.
Hi. Thanks. Does your attachment include the chart? If so, I'm afraid it's not available to my version of Access. Otherwise, I didn't get any error when I opened the form and clicked on the combobox or selected an item from it.
 

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Yes, it's having a chart when i am opening its giving me an error also i am using office 365. see below screenshot

1643017366210.png
1643017450746.png
 

Minty

AWF VIP
Local time
Today, 14:05
Joined
Jul 26, 2013
Messages
10,354
You need to add Month_Year as a named parameter to the transformed Chart query.
The query is effectively a crosstab, and you have to declare the parameters in a crosstab query.

Click on the design and then right click and select Parameters.

Edit: Having had a further look you are specifying Month_Year as the link child field, but I don't see that anywhere in the query or transformed data?
 

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Hi Minty Thank you for the suggestions i earlier found this on the internet i tried creating a crosstab query also and link to my combo box i was facing the same issue i am getting my data on combo but on charts it giving me the same error. Or do you mean to create a crosstab query and used it on chart rowsource? because for chart rowsource i have another query I need only for combo union query to get dates in the combo from two different tables. Could you please give it a try you may resolve this issue?

Edit: I have used Month_Year from the union query to my combo resource. and link to chart
Link child fields : Month_Year
Link Master field ; cmbMonth (My combo)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:05
Joined
May 7, 2009
Messages
19,169
see changes in qrySor and the AfterUpdate event of the combobox on the form.
also remove the parent/child link fields.
 

Attachments

  • DB Sample.accdb
    1.6 MB · Views: 297

waseem0888

Member
Local time
Today, 14:05
Joined
Jul 25, 2020
Messages
51
Thanks arnelpg for your solution. you handle the problem in a nice way.
 

Users who are viewing this thread

Top Bottom