How to subscript image names

[Kobo77]

I have a catalogue page containing 64 (8x8) images and a text box associated with each image containing a part number. The images are named Image1 - Image64 and the text boxes Text1 to Text64. To populate the page I read in a record from a table containing amongst other things the Part number and extract an image from a file.

The code is:

Set Rs = Db.OpenRecordset("SELECT * FROM PartCat WHERE PartNo > 999999;")
i = 1
Do Until i > 64
Me.Controls("Text" & i) = Rs!PartNo
Rs.MoveNext
i = i + 1
Loop

This works fine for the Text but not for the Images Me.Controls("Image" & i) fails. I have been through microsoft's help but cannot find the trick to do it.Can anybody help?

[CJ_London]

but not for the Images Me.Controls("Image" & i) fails

help if you provided the full line, for all we know you have a typo.

[GinaWhipp]

It fails, does that mean you get an error message?

[Kobo77]

Sorry incomplete information

Set Rs = Db.OpenRecordset("SELECT * FROM PartCat WHERE PartNo > 999999;")
i = 1
Do Until i > 64
Me.Controls("Text" & i) = Rs!PartNo
Me.Controls("Image" & i) = strpath & Rs!PartNo & ".jpg"
Rs.MoveNext
i = i + 1
Loop

The line fails with 438 Object doesn't support this property or method. strpath contains a valid string.

[June7]

You need to explicitly set ControlSource or Picture property of Image control. If no property specified, Access defaults to Value property.

So you have 64 records for the images for each part?

Why is the criteria >99999 instead of: =" & Me.PartNo ?

Does strPath include ending \ character?

Is this a report or a form?

[GinaWhipp]

Please post what you are using for *strpath *. Could be it's picking up an invalid character or perhaps it's not the complete path.

[Kobo77]

strpath is fine it is used elsewhere and works perfectly. It is also 'confidential' so I don't want to post it in open forum.

The > 999999 is actually a parameter as I am printing a page at a time. Again that parameter is fine because the text part of the output is working perfectly. Currently I am subscripting the text instruction and then using 64 instances of:

Me!Image1.Picture = strpath & rs!PartNo & ".jpg"
rs.movenext
Me!Image2.Picture = strpath & rs!PartNo & ".jpg"
rs.movenext
......

This works but far too manual to put into production. I am an experienced VBA proggie but I am looking for the syntax to loop this properly.

I have tried :
Me.Picture("Image" & i) = strpath & Rs!PartNo & ".jpg"
Again to no avail.

As I've said I have a working solution but it is a bit clunky and would like something slicker. It must be out there in my experience but finding it is another issue!

[Kobo77]

It says Novice against my name but that is presumably because I have only just joined the forum out of frustration at not being able to find a solution. After over 40 years of professional coding I believe I am not a novice. BTW thanks for all the assistance so far, it is appreciated.

[GinaWhipp]

Yes, the *Novice* designation is because you just joined but you should be able to update in your Profile. Note, we don't use that to *judge* you.

As for the path name, okay but does it end with a backslash? If it's missing it might be including the image name in the path. Hmm, and...

Me.Controls("Image" & i).Picture

[June7]

Is this a report?

I have never used VBA to dynamically set Image control. I just set the ControlSource property with an expression. Example:

=CurrentProject.Path & "\Images\" & [PartNo] & ".jpg"

[Kobo77]

The path name ends in a "" as stated it works perfectly elsewhere. This is a report but the same issue would be with a form. It is the syntax to vary the image Source in the way that I am doing with the text field. It must be possible I just haven't found out how yet.
Thanks for your efforts anyway.

[June7]

The forum dropped the \ in your post. Switch to Advanced editor and uncheck "Automatically parse links" below edit window before saving post.

Suggested techniques are proven.

What event is the code in?

I still think can eliminate the VBA. Include the table with PartNo field in the report RecordSource and set ControlSource property of each Image control with an expression that builds the path. Discussed in numerous threads, here is one https://www.accessforums.net/showthread.php?t=73766

If you want to provide db for analysis, follow instructions at bottom of my post.

[CJ_London]

since the image controls are unbound have you tried


Me.Controls("Image" & I).controlsource = strpath & rs!PartNo & ".jpg"

[June7]

If you want to dynamically display image with VBA, must use Picture property, not ControlSource. AFAIK, ControlSource of controls on report cannot be directly set with VBA. At least, my attempts to do so fail (I can do this on form).

And cannot set ControlSource of Image control programmatically in form either. The property is not even available in VBA for Image control.

However, ControlSource can call a function that returns a path. That's what intrinsic CurrentProject.Path does.

Still don't know what event you call this code from.

I do now see how getting the 8x8 grid would be difficult with simply binding ControlSource property. I wonder if you could use Columnar page setup in report to get the 8x8 arrangement. Works for me. Guess depends how complex your report is.

[GinaWhipp]

Hmm, are you using an Image Control?