Link graph to Access form and report

Emc2
NewLounger
Posts: 6
Joined: 12 Dec 2019, 19:36

Link graph to Access form and report

Post by Emc2 »

I got this one to work http://eileenslounge.com/viewtopic.php?f=29&t=14105#" onclick="window.open(this.href);return false; - :) . However, I cannot figur out how to display the graph on my form and my report. The graph shows up without linked to any object I can see in design view. I have tried to link it to an unbound object field without sucess. Any hints asksa copy and paste guy?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Link graph to Access form and report

Post by HansV »

Welcome to Eileen's Lounge!

The code in the thread that you refer to uses an Image control on a form or report.
The Picture property of the Image control is set to the path+filename of the exported chart.
Best wishes,
Hans

Emc2
NewLounger
Posts: 6
Joined: 12 Dec 2019, 19:36

Re: Link graph to Access form and report

Post by Emc2 »

Thanks twice! Great service!

Emc2
NewLounger
Posts: 6
Joined: 12 Dec 2019, 19:36

Re: Link graph to Access form and report

Post by Emc2 »

My next struggel is to use a parameter from my form to run the query generating data for the graph. Is that possible ?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Link graph to Access form and report

Post by HansV »

I would update the SQL of the query dynamically.
Let's say that your query is named MyQuery and that is has SQL like this:

SELECT Field1, Field2 FROM MyTable;

You want to filter another field Field3 to be equal to the value of the text box txtParam on your form.

Code: Select all

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3=" & Me!txtParam
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("MyQuery")
    qdf.SQL = strSQL
This code assumes that Field3 is a number field. If it is a text field, you must enclose the value of the text box in (single or double) quotes:

Code: Select all

    strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3='" & Me!txtParam & "'"
If it is a date field, you must enclose the value of the text box in hash characters # and make sure that the date is formatted as mm/dd/yyyy or yyyy-mm-dd. SQL does not "understand" European date format dd/mm/yyyy:

Code: Select all

    strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3=#" & Format(Me!txtParam, "mm/dd/yyyy") & "#"
Best wishes,
Hans

Emc2
NewLounger
Posts: 6
Joined: 12 Dec 2019, 19:36

Re: Link graph to Access form and report

Post by Emc2 »

Working- Thanks! However, I seem to have an issue with Excel not closing - error msg something like "the xlsx is open, close and try again" although it looks like it is not running at all. This happens after interrupting the code from running to the end caused by my mistakes. How do I make sure Excel is closed before running the code?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Link graph to Access form and report

Post by HansV »

I assume that you have code at the end to close/save the workbook and perhaps to quit Excel.

When a problem occurs, click Debug, and then don't simply end code execution, but drag the yellow arrow in the left margin of the module window to the line that closes the workbook, then press F5 to run automatically, or press F8 to execute line by line.
Best wishes,
Hans

Emc2
NewLounger
Posts: 6
Joined: 12 Dec 2019, 19:36

Re: Link graph to Access form and report

Post by Emc2 »

Ok, got it!