Unbound Object Doesn't Update

[kdave]

Hello All,

I am creating a database based on a tool that is currently in Excel. One part of Excel that I want to keep is the chart. (Side note: I have tried to recreate the chart in Access and cannot.) I'm using a report to view the chart. I have a command button on the report (for testing purposes) that runs the code that will launch Excel and run the macro that updates the chart. The chart updates it (I watch it happen.) The chart on the report will not update unless I go back into design view. I have searched high and low for an answer to this, but I cannot find one.

The code to run the Excel macro is:

Private Sub cmdGenerateCurve_Click()
Dim xl As Object

'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.workbooks.Open ("\\stnafddco001.us590.corpintra.net\h104\KCASAGR\Data\My Documents\VSO_DB\Integrator_2015-07-13.xlsm")

'Step 2: Make Excel visible
xl.Visible = True

'Step 3: Run the target macro
xl.Run "ShowFormAccess", [Engine], [Curve_Number]
DoEvents
DoEvents
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

'Step 5: Memory Clean up
Set xl = Nothing
End Sub

The macro in Excel works. Why do you think the unbound object will not update when I click that button?

 

[Micron]

I don't have an answer to your specific question, but I am but one person who thinks Access charts suck big time since version 97 or whenever first introduced. I agree with the sentiment I've seen before - that there will never be an improvement as long as MS can sell you a program that already works. Your issue is but one aspect of why you should reconsider and use the program that was meant to do this. You can always transfer data from Excel to Access or vice versa with commonly used methods. I always thought Access charts had to be on forms - you say you are using a report. Perhaps that is problem #1, but since I never use them any more I can't profess to have stayed current with any changes that may have occurred. Unless I've missed something with respect to your statement that you have the chart on a "report"...

 

[kdave]

I really want to get this database to do everything the Excel "database" can do and more. I'm not yet willing to tell my boss that we need to scrap the DB, but if I cannot get this part to work...that just maybe what we will need to do.

 

[Micron]

You don't - they can work in conjuction. It's common to have both in order to get the best that each has to offer.

 

[xenou]

This looks suspicious:

xl.Run "ShowFormAccess", [Engine], [Curve_Number]

Do the fields [Engine] and [Curve_Number] have the proper values in the them? Something should be pointing to the location of the macro.

Further Info On Run:
How do I use Application.Run in Excel

Side note: I don't think you need DoEvents in there.

 

[kdave]

Both fields contain data and, as I said, Excel updates just fine. I placed the DoEvents to possible allow Excel to finish updating and get the chart to work. Those fields are parameters in my macro. The chart just doesn't update until I go into Design view.

 

[Micron]

I've re-read your original post a few times and still cannot be sure where the chart is - even if it's in Access or Excel, and on a form or report. Also have no idea what your macro does, but I guess that's not the issue since it updates after switching views.
If it's on an Access form, try Me.YourChartName.Requery - either in your Access event or a refresh button on your form.
If it's a report as you say, I reiterate that Access charts were meant to be on forms.

If you want to delay code execution to ensure something else has time to finish, you might like this method better (it allows you to control the duration)

Public Function Pause(intSecs As Integer)
Dim Start As Variant
Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

To get a four second pause, you invoke this as Pause (4) .

 

[kdave]

We may actually have a workaround. Everything is finalized in a PDF with signatures. I may just include the PDF. Thanks everyone!

 

[kdave]

Final update, I got it to work with a form. I think I'll fake a report and print a form.