How to Set different images based on cell value

[TravelingCat]

Hello,
I have a continuous form, and i'm trying to set an image for each line in a cell named "assignType" based on its type. If type = 1 -> set ImageText, if type = 2 -> set ImageDecision. I tried to do this with Select Case, but the problem is it only remembers the last Case he was in. To make it more clear:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do While Not rs.EOF
Select Case rs("assignType")
    Case 1:
    ImageText.Visible = True
    Case 2:
    ImageDecision.Visible = True
    End Select
rs.MoveNext
Loop
 

So if the last record on a form is assign with type 2, then all the lines will have ImageDecision. Does anyone know how i can fix this?
Thanks in advance

[nico5038]

Hmmm, Access has a "problem" with unbound controls in a Continuous form. The only solution I found was to add the field to the table and make sure it's filled to display it correctly.

Nic;o)

[TravelingCat]

Hi Nico,
So i should create another column in my tblAssignments and put what in there?
Can you please give me a little bit more details as to what i should do?

[nico5038]

Yes, add a column for the Image (OLE-Object) and make sure (e.g. using an update query) that the correct image is filled before showing the form.

When no updates are needed you can just create a tblImage holding two records (ImageTest en ImageDecision) with a key 1 and 2 and link it to tblAssignment in a query that's used for the form.

This table might also be handy for the Update query to set the value of the Image field.

Getting the idea ?

Nic;o)

[NeoPa]

Nico is right. This is not an alternative solution, but an explanation of the problem for better understanding of the issue (Why Values in Unbound Form Controls do not Persist).

BTW You can still use your original approach if you prefer, store a Yes/No type value in the table, just setting the image depending on the value stored. Nico's approach is better though (They normally are).

I hope it helps.

PS. Welcome to full member status :)

[TravelingCat]

Thanks NeoPa, was i just a guest earlier?.. Anyway, before replying here i searched and i read since i'm new to ole objects, and i followed the steps that are listed here:
http://office.microsoft.com/en-us/ac...005280225.aspx
I created a column assignImage in tblAssignment, and have put a bitmap image, only in one line, so i could see it works first. On the form i've put a textbox and bound it to "assignImage", but all i see is something like binary signs. How do i actually display the image on a form? Maybe the fact that it's a subform has significance..
Thank you for your help

[nico5038]

Just place the image field (e.g. with drag and drop) from the table "fresh" on the form and Access will do the "translation".

Connecting an unbound (or text) field will give the garbage you see.

Nic;o)

[NeoPa]

Let's handle this point by point.

1. Members of <32 posts are labeled as Newbie. When the 32 posts mark is reached the title switches to Member. This is not an attempt to be derogatory, but it helps moderators to take inexperience into account when dealing with the stretching of any rules.
2. As a general rule, describing what you have done in the post, rather than simply linking to something similar, is likely to garner more interest from anyone with an interest in answering your question. My own attitude to such links, and similarly posts of type "My problem is in the attached database.", is that if someone cannot put the effort in to post what I need to see and make it available without my having to go to any lengths to find it, then I don't anticipate an easy ride dealing with that person, so I will often steer clear of such a thread. I'd rather spend my effort answering questions than trying to teach people how to ask them. That's handled in the FAQ.
   
   Please understand that I say this not to criticise, but to give an insight into how this is perceived from the other side, which can help you to post your questions in such a way that they are more likely to be answered.
3. The technical part of this question, how to display your image, is a bit of a deviation from the original thread. Sometimes this is closely enough related to cause little in the way of problems. One of the main reasons we try to encourage new questions to be posted in their own thread though, is that experts that have help to offer on one particular point, may have little idea about a new point (question) introduced. That is the case with me now. I have little experience with images, though a fair bit with the problems of continuous forms (The only suggestion I can think of would be to bind the [assignImage] field to an Image control rather than a TextBox).
   
   In this case, you may get lucky as I know that Nico can probably help if he's still around, but generally I'd still encourage you to post a separate thread. You benefit, as most experts will not be looking at a thread with so many responses to find a new question to help with. The potential pool of responders is therefore much greater with a new thread. Others benefit too, as threads on a single subject generally prove a lot easier to read and understand than those that switch between problems.

[TravelingCat]

NeoPa,
1. Thanks for the explanation.
2. I never wanted to appear as someone who isn't willing to put effort into writing a post, on the contrary. I thought link would be easier, not to mention that i've described my steps anyway, so the link was more like a supplement rather than the core of the question. I figured posting all that text here would be redundant and too much. So.. my intentions were good)
3. For some reason i didn't think there might be a case someone would want to only know how to set the images and not also show them on a form, 'cause it was my intention to begin with. You are right, i should've written this in my subject. Since Nico is indeed still here i'll first ask him.
As to your suggestion, i don't believe image control can be bound to anything, but of course i'm just a newbie (not by your definition, but i am:) ), so i don't know.

Nico, i have to admit i feel pretty dumb by now..
How do i drag and drop an image field from the table? Do you mean in tblAssignment, literally point on the "Bitmap image" in the assignImage column and drag it to the form? It doesn't want to be dragged..

[NeoPa]

TravelingCat: I figured posting all that text here would be redundant and too much. So.. my intentions were good)

That does it for me. Intentions are pretty well all.

TravelingCat: As to your suggestion, i don't believe image control can be bound to anything, but of course i'm just a newbie (not by your definition, but i am:) ), so i don't know.

I just checked, and you're right. There's probably a way to handle the Image control, but I expect it's still the right one to use. I'll leave you in Nico's more competent hands for further progress though.

[nico5038]

In A2007/2010 you can use the "Add Existing Fields" button, else insert an image control and connect that with the field from the form's recordsource.

Nic;o)

[TravelingCat]

Nico, but HOW do i connect image control with the field? Since image control can't be bound
(i have office 2003)

[nico5038]

You need to use the "bound" image control (has xyz in the icon)

Nic;o)

[TravelingCat]

Only it's called object frame and not image control, so i didn't even look at it before.
Thank you very much for your patience! It works now.

[NeoPa]

Sorry. That was probably me misleading you.

The Bound Object Frame control refers to a control that handles bound OLE objects. In this case an image. I understand it's a little more flexible than a simple image control, as it handles anything that OLE can, but that includes an image.

[TravelingCat]

I have one more question for Nico, if i may
Your second message was "add a column for the Image (OLE-Object) and make sure (e.g. using an update query) that the correct image is filled before showing the form", now i added this column but i don't know how to write the update query (when i manually set an image in the table it shows ok on the form). I tried

strImage = "C:\Documents and Settings\alinab\Desktop\T.bmp"
CurrentDb.Execute "Update tblAssignments SET assignImage = " & strImage & " WHERE assignType = 1;"
 

but i get "syntax error (missing operator) in query expression "C:\.....".
Can you please help me with this?

[nico5038]

My advise is to create first a tblImage holding with as key 1 (being the assignType) the first image, key for the second assigntype image, etc.

Next use:

UPDATE tblAssignments SET assignImage = DLOOKUP("assignImage","tblImage","assignType = " & tblAssignments.assigntype & ";"
 

Yourerror message will be cause by the fact that the filename isn't embedded in single quotes.
so: SET assignImage = " & strImage & " WHERE
Should be: SET assignImage = '" & strImage & "' WHERE

Nic;o)

[TravelingCat]

I did everything you said, and wrote this:

CurrentDb.Execute "UPDATE tblAssignments SET assignImage = ' " & DLookup("assignImage", "tblAssignImages", "assignType = " & assignType) & ";"

(your code didn't compile, i added ) , and tried many code variations)
But now the error is syntax error in string in query expression "?/
I just can't make it work...
If you have any more suggestions i'll be very glad to hear.
Also when i checked in immediate window for

DLookup("assignImage", "tblAssignImages", "assignType = " & assignType)

i got this: ?/
!????????????????? ? ???h ? ?? 6 ( #
 ? ? ?????????????????? and so on, not sure if it's normal..

[NeoPa]

TravelingCat: I did everything you said, and wrote this:

CurrentDb.Execute "UPDATE tblAssignments SET assignImage = ' " & DLookup("assignImage", "tblAssignImages", "assignType = " & assignType) & ";"

It seems to me that is not so far wrong.

The problem here appears to be that you applied instructions meant for two different situations into the same result. The single-quote (') that you added in is only required in the situation where you're setting a string value of the address of the image (which is not the case here). In that case it would need one leading into the string and another to mark the end of it.

In this case, because your are not using the string directly as Nico was, but are having first to describe it in VBA so that it can then be passed as SQL to .Execute, it is a little more tricky. Please try the following to see if it works for you :

CurrentDb.Execute "UPDATE tblAssignments " & _
                  "SET assignImage = DLookup('assignImage'," & _
                                            "'tblAssignImages'," & _
                                            "'assignType=' & [assignType] & ');"

[TravelingCat]

Wait..was i supposed to put Nico's update string in image's control source and not in vba? That's why i didn't understand the "update" without db.Execute.. If so, it still isn't working.
And your string copied exactly also gives syntax error in string. I'm gonna shoot myself in a while...

[NeoPa]

Did you copy the string or the code? I'm a little confused.

Try to explain exactly what you did more clearly.

[TravelingCat]

I've put the code you suggested into vba onLoad event -> syntax error.
Then i tried to put Nico's code -

UPDATE tblAssignments SET assignImage = DLOOKUP("assignImage","tblImage","assignType = " & tblAssignments.assigntype & ";"

into the control source of the Bound Object Frame control on the form, and it also didn't work (i had to put = before the code line and it messed up the syntax).
What is the right way?

[NeoPa]

Well, you can't bind an object to an action query that's for sure.

Is the database set up with the referred to fields available in a table of that name?

[nico5038]

My SQL is created to run in the query editor.
Thought you needed a once off job.
When you save that query you can execute that instead of the string (Looks like: docmd.execute ("qryUpdate") ), else repace all " by ' and embed the entire statement between double quotes (")

Nic;o)

[NeoPa]

I think the OP found that you'd left off the closing parenthesis Nico. Other than that, that was what I posted (at least so I thought) in post #19, but the OP reports that as failing. It's not clear if it was copied correctly though, and/or entered into the procedure correctly.

[TravelingCat]

Guys... i can't believe my eyes, but it finally works! It really was so easy, if only Nico would have elaborated on that crutial post for a dummy like myself:) All i had to do was to create the simplest update query:

UPDATE tblAssignments INNER JOIN tblAssignImages ON tblAssignments.assignType = tblAssignImages.assignType SET tblAssignments.assignImage = [tblAssignImages].[assignImage];

and run it in vba - CurrentDb.Execute ("qryAssignType"). I'm new to this, and i get by with basic stuff like recordsets, basic functions. I guess update query is also basic, but i'm still learning.
That should've taken 5 minutes..
Thanks you both very much for your help and the time you've spent on this thread, it can now be closed.

[NeoPa]

Hi TC. Pleased you got it working.

We don't close threads as such, but if you'd like to select a post as the Best Answer for anyone else finding the same problem, then that will indicate no further solutions are necessary.