Help populating image boxes from other records in the query.

[hinsdale1]

What a great forum!.. thanks to all the helpful Access contributors (been great reading!).



I am a beginner so I apologize for my ignorance up front. I have a form (based on query results something like the table below) for a wardrobe database:

Oufit ID	Picture	Relationship
3	\pictures\3A.jpg	88
5	\pictures\5A.jpg
9	\pictures\9A.jpg
11	\pictures\11A.jpg	88
15	\pictures\15A.jpg	88

I want to show not only the picture of the outfit on each record, but also want to include small pictures of all the related outfits (share same relationship code).

I have (10) ten (will never be more than 10 in a relationship) empty Image boxes on the layout of each record but can't figure out how to populate each image box (imagebox1, imagebox2, etc) with small pictures of the related (same relationship code) outfits.

I was thinking maybe i need to use a DAO. recordset loop but don't know how to assign each resulting record (image address) to a different imagebox (imagebox1,2,etc).

Any ideas would be greatly appreciated!

[June7]

Each image is a single record? Or there are 10 image fields?

Use the Image control ControlSource property.

Looks like the images are stored externally from the database so need full path to the image, like: "C:\pictures\11A.jpg". If the drive and full path is not stored in table, can be concatenated with the field that has partial path/name: ="C:\path" & [Picture]

If the images are small and number of records will remain low, can embed the images into Attachment field(s) then bind Image control to field.

[hinsdale1]

Thank you for taking the replying. I am sure i somehow made it confusing but Its not about finding the image locations (i know where the images are i just didn't include the full path in the example). The images display fine on all of their respective records

Its about how to get the field (picture) from the other records (just ones who have the same relationship code) to propagate image boxes on the current record so i can see images of all the related outfits in addition to the main large image of the current record.

Each record (grouped by outfit ID) has a lot more information for each outfit that I am not including in my example (just included the 3 relevant fields).

My form query results in, for example, 6 records displayed in the form 1 record at a time - with all the associated fields (color, designer, style, dates last worn, etc) for each outfit (record) displayed. In addition to the Outfit image, I also want to display images of the other outfits in the query results that share the same relationship.

Am I making it clearer?

[June7]

Each image is a single record? This might be a situation where de-normalization of data could be justified. Maybe a crosstab query could produce the dataset. Stabilizing a crosstab to run perpetually in a form or report is tricky. Review http://www.blueclaw-db.com/report_dy...stab_field.htm
Then would be complication if don't want to show the outfit twice on the form.

[hinsdale1]

No each image is not a record, I am not sure what that means. The Image locations are just one field in a table of outfits. Images are not even particularly relevant to my question - it could be any field from the other records in the query results.

I am sorry I am not making it clearer. I think you are just used to experienced users asking challenging questions and are so are over-thinking what I am asking. I am just completely inexperienced with access.

I have a table that contains rows of outfits (ID, image location, designer, color, relationship, etc)

I created a query (with specific criteria) of the table that narrows results to, for example 6, records (that meet the requirements)

I created a form based on that query, that displays the resulting 6 records (including all the relevant fields for each record), 1 record at a time (as is shown in the attached picture above).

ALL I WANT to do is also show ON the current record.. additional fields (in this case the image locations) from some of the other records in other 5 query results records (in this example show the image field from any other records that have the same relationship code of 88).

Am i helping my cause at all or just making it worse?

[June7]

That is what I mean by each image is a record. Each image location for each outfit ID is a single record, as opposed to 10 fields in one outfit ID record for 10 image locations.

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

[hinsdale1]

Each record (outfit) has only one image.


I guess i am completely confused as to why this would be a hard question or even remotely complicated to accomplish - I just must be saying it wrong.

Thanks for the offer to look at the db but my very first access database is in very rough draft stages and is large - cant get it working anywhere close to 2mb limit. Not sure what i should do, just keep rereading my question and figure out where I am saying it wrong.

I just want to also include on the current record of the form - pictures of any other outfits (records in the same query results) that share the same relationship code (in this example I assigned a code of 88).

[June7]

2mb limit for attached file is a zip. Your file is still too big after compact and repair and zipping? Make copy and remove most records.

What is that relationship code - outfit ID? If so then each outfit has multiple images associated.

[hinsdale1]

I am sorry I am confused by the question, the relationship codes and Outfit ID's for each of the resulting records from my example query are outlined in my example in my first post?

There is only one image outfit ID and each outfit has only 1 image. Although records may share the same relationship code I am not sure what u mean by "each outfit has multiple images associated"?

[hinsdale1]

MAYBE IF I REWORD THE QUESTION IT WILL HELP?

I have created a wardrobe database with a table that contains rows of outfits (ID, imagelocation, designer, color, relationship, etc)


I created a query (with specific criteria) of the table that narrows results to, for example, 6 records (that meet the specific criteria):

FOR EXAMPLE

Record	Outfit ID	ImageLocation	Relationship	Designer	Color
1	3	C:\pictures\3A.jpg	88	xyz	blue
2	5	C:\pictures\5A.jpg		wang	green
3	9	C:\pictures\9A.jpg		olo	red
4	11	C:\pictures\11A.jpg	88	abc	yellow
5	15	C:\pictures\15A.jpg	88	xyz	aqua
6	21	C:\pictures\21A.jpg		zzz	brown

I created a form based on that query, that displays the resulting 6 records (including all the relevant fields for each record), 1 RECORD AT A TIME (NOT a datasheet view) with a large picuture of the outfit and all other fields


If you aslo wanted to incorporate into the current record of the form - pictures of any other related outfits (these are records in the same query results that share the same relationship code - in this example I assigned a code of 88), how do you do that?


At most 5 records will share the same relationship code so I have placed 5 empty image boxes (image1, image2, image3, etc) in the forms design layout to accept/propagate with the resulting imagelocations that share the same relationship code with the current record.


I just dont know how to have it look up which records share the same relationship code and assign the imagelocations of those matching records to the empty image boxes on the current record?

[hinsdale1]

I am not sure it is the right way to accomplish but I can use a DAO.Recordset to lookup the records that share same relationship code and, if any exist, assign their imagelocations to the first empty image box ( image1.picture) I have created on the current record..... but I dont know how to get it to loop and assign EACH subsequent resulting record (that shares relationship with the current record) to a different empty image boxes on the current record (image2.picture, then image3.picture, etc.....



Dim RS As DAO.Recordset
Dim SQL As String
SQL = "SELECT tblOutfits.[ID], tblOutfits.[Relationship], tblOutfits.[ImageLocation] FROM tblOutfits WHERE (tblOutfits.[Relationship])=[Relationship]); "


Set RS = CurrentDb.OpenRecordset(SQL)
Do While Not RS.EOF
Me!Image1.Picture = RS("ImageLocation")
Me!Image1.Visible = True
RS.MoveNext
Loop
RS.Close
Set RS = Nothing


I need it to assign each resulting imagelocation to a different existing image box on the form (Image1.picture, image2.picture, image3.picture, etc) but dont know how to have the loop change so that each resulting image is assigned to a different one of the available empty image boxes.


I also need it to skip assigning to a empty image box the imagelocation of the current record (which also shares the same relationshipcode but is already displayed largely on the current record) .

[June7]

I suppose VBA procedure could do what you want. My other thought was a crosstab query but on review the VBA approach probably better. You are on the right track but don't set Picture property, set ControlSource property of Image control.

Also, the Relationship criteria is a variable. Concatenate variables otherwise the SQL just references the literal string [Relationship] and not the value in the field.
SQL = "SELECT [ID], [Relationship], [ImageLocation] FROM tblOutfits WHERE ([Relationship])=" & Me.[Relationship]) & ";"

Need a counter in the loop to use in the Image control name. And a test against value of the image control on main form. Assume code is in Current event of main form:

i = 1
While Not rs.EOF
If Me.imagecontrolname <> rs!ImageLocation Then
Me.subformcontainer.Form.Controls("Image" & i).ControlSource = "=" & rs!ImageLocation
i = i + 1
End If
Wend

Wish I could test that for you but can't so hope it works.

[hinsdale1]

Getting closer!! I am so grateful for your help June7

(again remember i am coding-challenged)

I could get your code to work but only with a few modifications (like adding rs.movenext statement and using the image.picture control instead of control source).

Me!Image1.Visible = False
Me!Image2.Visible = False
Me!Image3.Visible = False
Me!Image4.Visible = False
Me!Image5.Visible = False
If Not IsNull(Me.[relationship]) Then
Dim i As String
Dim RS As DAO.Recordset
Dim SQL As String
SQL = "SELECT tblOutfits.[ID], tblOutfits.[Relationship], tblImage.[txtImageName] FROM tblImage INNER JOIN tblOutfits ON tblImage.ID = tblOutfits.Image WHERE ((tblOutfits.[Relationship])=" & Me.[relationship] & ");"
Set RS = CurrentDb.OpenRecordset(SQL)
i = 1
While Not RS.EOF
If RS("ID") <> [tblOutfits.ID] Then
Me("Image" & i).Picture = RS("txtImageName")
Me("Image" & i).Visible = True
i = i + 1
End If
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
End If



I am so happy its working .. THANK YOU! .. but I would like to implement your suggestion of setting the control source with the image location (hopefully that will avoid the having set pictures as visible and not visible) but

When I incorporate your line:

Me.subformcontainer.Form.Controls("Image" & i).ControlSource = "=" & rs!txtImageName

I get a "compile error - Method or Data member not found". I don't really understand the components of the command so can't come up with a tweak to make it work.. can you offer any help?

[June7]

Ooops, sorry forgot the rs.MoveNext.

Well, I have used a function called from the Image control ControlSource to construct the image path string but never a sub to set the ControlSource property. I wasn't positive it would work and you seem to have confirmed it won't. I think a function could be written to accomplish (the code would have to test against the ControlSource value of all the image controls, just a little more complication). If the images were displayed in a continuous form this would be necessary approach but since you have something working, maybe don't want to go that route.

[hinsdale1]

Thanks again .. would never have gotten over this hurdle without your insights.

Many more hurdles to overcome I am sure!