Question Dlookup image for attachment with criteria

[Fran Smith]

Hi,

I'm a newbie learner who has learned DAO (somewhat), but not used control source (I was told not to), but in this instance, I'm simply looking up an image to display. This is my expression:

DLookUp("[People]![Picture]","People","[People]![PeopleID]=txtIDMother")

I'm wanting to retrive into my form the attachment "Picture" (automatically named by Access for my attachment table field) from the "People" table for the field where the "txtIDMother" txt box (already retrived by DAO on form_load procedure) equals the "PeopleID" primary key in the "People" table.

The control source accepts the code, but it doesn't load onto the form - only flashes briefly.

Edit: Incidently, I just discovered that I can actually put an SQL statement in the control source. I tried, but the results are exactly the same as above, only faster. Here is my SQL:

="SELECT People.PeopleID,People.Picture.FileData,People.Picture.FileName, People.Picture.FileType
FROM People
WHERE (People.PeopleID)=" & [txtIDMother]

What have I got wrong?

Thanks in advance.

 

[vbaInet]

You didn't make any reference to your other thread:

http://www.access-programmers.co.uk/forums/showthread.php?p=1104894

In there Gina showed you the best approach and if you had followed that way of saving images, displaying it would have been a breeze.

In order to show an image from your Attachment field, you need an Image Control's Control Source set to the .FileName of the Attachment field.

 

[Fran Smith]

Hi vbaInet,

Thanks for your reply. I didn't ignore Gina's reply. I did follow the tutorial (in fact I discovered that image controls in Access 2007 do not appear to accept .jpeg images - a bit of a worry). And I did thank her for her help. The fault is probably mine - I probably didn't describe what I was trying to do appropriately.

However, my form requires particular records loaded based on criteria. For example, I have loaded names and IDs from a table based on family position such as mother, father, child etc - one set of controls for each person. A type of family tree, and the only way I could see that happening is with unbound controls. Bound controls seem (to me) to need to sift through records, rather than retrieve a particular predetermined record for each control, or retrieve a particular record based on the previous form (which is my future intention - to click on a picture/name/family position to bring up a detailed personal form). The NorthWind DB also seems to sift through controls rather than allow a particular predetermined record to be accessed.

I did try bound records but, of course, it simply brings up the first record in the table.

I'd love to find a work around to add criteria to bound fields, particularly since I'm wanting to retrieve many records (people) from the one table on the one form to display, each with a group of controls, as described above based on family position as described.

Perhaps what I'm trying to do is undoable for a beginner. Perhaps I'm misunderstanding some major concept with bound fields. The control source seemed briefly a way to achieve this, but alas I may have to dispense entirely with my design for a simple, boring listbox...

And I definite get what you are telling me - attachments must use a control source, but without criteria. Thanks for your reply . I would appreciate it if you could tell me where my thinking might be wrong and how it really would work this way.

 

[vbaInet]

Alright Fran. I didn't mean that you ignored Gina's post. What I meant was it's good to make reference to your threads if it's similar to what you're doing.

Let's look at the future -->

Bound controls seem (to me) to need to sift through records, rather than retrieve a particular predetermined record for each control, or retrieve a particular record based on the previous form (which is my future intention - to click on a picture/name/family position to bring up a detailed personal form).

Does the pop-up details form work?

Why don't you just link the image to that record via the query?

From your last post I'm a bit confused as to what you actually want to. Could you briefly explain exactly what you want. Two or three bullet points might help.

 

[Fran Smith]

Hi vbaInet,

Sorry about the late reply (weekend away ). Perhaps dot points might work.

• 10 people displayed on the form from the same table (People).
• Each person has a group of controls displaying a picture, name and their position in the family (mother, grandmother, child etc).
• It's a bit like a family tree (each branch has a name, picture and family position).
• Each group of controls displays a label displaying "Mother", "Grandmother" etc, and loads the person from that family who has that position - like a family tree.

Does this explain it better?

The good thing is that I found a way to do it - finally at this address:

databasedevelopment.co.uk/examples.htm

(Storing Picture Data) - I'm actually not using 1000s of pictures, probably no more than 100 or so. This provides a sample access file that is amazingly easy to understand - I adapted it easily. I find it easiest when I see an actual working form - I can work out how it works, then adapt it.

Yes, I did manage to get the new form open from all three controls (label, image and textbox). The new form is the editing form where the picture can be added/edited (together with other info). It even requeries the original form so that the new picture loads on closing the second form , although the reloaded pictures do blink before settling.

I appreciate your offer of help. I'm also rather elated that I finally managed to solve my problem on my own (although I did search for several days before posting). I'm all good now. Stand by for when I run into the next major problem, though .

 

[vbaInet]

10 people displayed on the form from the same table (People).

• Each person has a group of controls displaying a picture, name and their position in the family (mother, grandmother, child etc).
• It's a bit like a family tree (each branch has a name, picture and family position).
• Each group of controls displays a label displaying "Mother", "Grandmother" etc, and loads the person from that family who has that position - like a family tree.

Does this explain it better?

Yes it does

The good thing is that I found a way to do it - finally at this address:

databasedevelopment.co.uk/examples.htm

He's one of the forum's well respected VIPs (known as LPurvis). I was actually going to give you that link but I just wasn't clear what your objective was.

I appreciate your offer of help. I'm also rather elated that I finally managed to solve my problem on my own (although I did search for several days before posting). I'm all good now. Stand by for when I run into the next major problem, though .

I'm sure it feels good to have solved on your own. I shall keep my eyes peeled.