Change chart image

[igillesp]

Hi!

I've got an XY chart (objScatterGraph) which I use for mapping. The background image is the whole of England and Wales, and I'd like to be able to change this to specific regions depending on the choice of a combo box (cmbRegion). However, the following...

Dim RegName As String
Dim FilePath As String
Dim objChart As Graph.Chart
 
RegName = Me.cmbRegion
FilePath = "S:\Images\" & RegName & ".jpeg"
Set objChart = Me!objScatterGraph.Object
 
With objChart.ChartArea.Fill
    .UserPicture picturefile:=FullFilePath
    .Visible = True
End With
 
Me.objScatterGraph.Requery

... on an OnChange event gives me Run-time error '5' Invalid procedure call or argument.

Clicking Debug highlights

    .UserPicture picturefile:=FullFilePath

Any ideas? I'm running Access 2003 and have referenced the Microsoft Graph 11.0 object library.

Thanks,

Iain

 

[ChrisO]

Just a guess but wouldn’t

picturefile:=FullFilePath

have to be

picturefile:=FilePath

You may need to turn Option Explicit on and start compiling your code.

Regards,
Chris.

 

[igillesp]

Good spot, but that was just a typo when I was copying the code over.

I did have...

Dim FilePath, FullFilePath As String
        FilePath = "S:\Images\"
        FullFilePath = FilePath & RegName & ".jpeg"

...but realised that I could combine the two whilst copying.

 

[ChrisO]

“Good spot, but that was just a typo when I was copying the code over.”

There is no such thing as ‘just a typo’, if there was then there would be just compilers that could handle them.
But compilers aren’t just…they tend in fact to be rather brutal.

In effect you placed crap information in your question on which people are supposed to base a correct answer.
(Please don’t think me harsh but it is by no means the first time it has been done.)

BTW, why are you now dimensioning FilePath as a Variant when you originally had it as a string?

To avoid any further unnecessary work please post a copy of your database.

 

[igillesp]

Actually I think you're being incredibly harsh.

I think that it should be obvious, considering that this is my seventh post, that I'm a VBA novice, and that I and others like me should be encouraged rather than slated for making amateurish mistakes.

The fact is that I was actually trying to save the experts from having to read all my code..

Private Sub cmbRegion_Change()
'Define variables
Dim RegName, sSQL, Xmin, Xmax, Ymin, Ymax As String
Dim FilePath As Variant
Dim db As Database
Dim rs As Recordset
    'Return the min and max co-ordinates from MapXY table depending on the region selected
    
    RegName = Me.cmbRegion
    sSQL = "Select * from MapXY where MapXY.LabRegion='" & RegName & "';"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)
    
    If rs.EOF Then ' i.e. if region = 'YORK&HUM' (SQL doesn't like the ampersand...)
        Xmin = 350000
        Xmax = 550000
        Ymin = 370000
        Ymax = 525000
    Else
        Xmin = rs!Xmin 'otherwise get the co-ords for the other regions
        Xmax = rs!Xmax
        Ymin = rs!Ymin
        Ymax = rs!Ymax
    End If
         
    'Set the axes and all the other gubbins
    
    With Me![objChart].Axes(2) ' This is the X axis
        .minimumscale = Xmin
        .maximumscale = Xmax
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
    
    With Me![objChart].Axes(1) ' This is the Y axis
        .minimumscale = Ymin
        .maximumscale = Ymax
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
      
    'Define the background image
     
    FilePath = "S:\Images\" & RegName & ".jpeg"
           
    With Me![objChart].ChartArea.Fill
        .UserPicture PictureFile:=FilePath
        .Visible = True
    End With
    
    Me.objChart.Requery
    
End Sub

...to get to the crux of the problem, but this was clearly an oversight.

I cant send the database as its patient data and I dimensioned FilePath as a Varient as the Visual Basic Help for the UserPicture method says

'PictureFile Required Variant. The name of the specified picture file.'

 

[ChrisO]

I think people should not make the assumption that, just because a member may have a low post count that that person is not very intelligent… and the opposite is also true.

So forget the low post count and forget you may be new to VBA. What matters, and it has nothing to do with programming, is to provide the best information you can when asking a question.

The data in your question keeps changing, example: -

Dim FilePath As String
became
Dim FilePath, FullFilePath As String
became
Dim FilePath As Variant

And there are other places that there are problems.

Anyhow, the code you posted: -

    FilePath = "S:\Images\" & RegName & ".jpeg"
           
    With Me![objChart].ChartArea.Fill
        .UserPicture PictureFile:=FilePath
        .Visible = True
    End With

Does work provided the FilePath string is correct.

So MsgBox FilePath to check if it is correct.

Regards,
Chris.

 

[igillesp]

AAAARRRGGGHHHHH!!!!!

Solved!

I'd tried MsgBox FilePath previously and it seemed fine, but following your post I tried Call Shell instead and got File Not Found.

Its amazing the difference you get when you put "jpg" instead of "jpeg" into...

FilePath = "S:\Images\" & RegName & ".[COLOR=red]jpeg[/COLOR]"