Link graph to Access form and report
-
- NewLounger
- Posts: 6
- Joined: 12 Dec 2019, 19:36
Link graph to Access form and report
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?
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Link graph to Access form and report
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.
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
Hans
-
- NewLounger
- Posts: 6
- Joined: 12 Dec 2019, 19:36
Re: Link graph to Access form and report
Thanks twice! Great service!
-
- NewLounger
- Posts: 6
- Joined: 12 Dec 2019, 19:36
Re: Link graph to Access form and report
My next struggel is to use a parameter from my form to run the query generating data for the graph. Is that possible ?
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Link graph to Access form and report
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.
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:
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:
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
Code: Select all
strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3='" & Me!txtParam & "'"
Code: Select all
strSQL = "SELECT Field1, Field2 FROM MyTable WHERE Field3=#" & Format(Me!txtParam, "mm/dd/yyyy") & "#"
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 6
- Joined: 12 Dec 2019, 19:36
Re: Link graph to Access form and report
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?
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Link graph to Access form and report
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.
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
Hans
-
- NewLounger
- Posts: 6
- Joined: 12 Dec 2019, 19:36
Re: Link graph to Access form and report
Ok, got it!