Displaying pictures in a continuous form

[johnkrytus]

I have one picture for each record. They are stored outside of the app in folders on a mapped drive. Each picture file has the same name however some of them are profile_pic.jpg and some are profile_pic.png

Is it possible to display each corresponding picture in a continuous form?

 

[sneuberg]

An ImageFrame will display both .png an .jpg type files if that's the question.

 

[johnkrytus]

No, the trouble seems to be that the image frame only retains the the id of the first record. So first I thought that it was a problem because I had it set to oncurrent. But I cant even get it to work on click of that record. When I try to grab the id of the record to plug it into the file path it comes back as null, which I realize sounds stupid because I can certainly retain the id when I'm calling a form onclick. It's only when I'm trying to assign it to the image frame that it returns null.

 

[sneuberg]

I don't understand what you mean by the "image frame only retains the id of the first record". Where would an image frame retain an id?

We have images in our continuous forms and it works fine. The form has an ImageFrame whose control source is a text box which has the image file path. That's all you need

If you could upload your database or a copy with the portion where you have the problem I'll see what I can figure out.

 

[taifoor]

ID in an image frame? Whats this ID.... ? Try to clarify ur question in simple way

 

[byllc]

I think what John means is that he has a continuous form, he is trying to display an image on each row of the form. That image path is created from an id , which is the id of the record being displayed. But it displays only the image for the first or last id in the continuous form. So the continuous form displays the same image on every row when it should be displaying the image for the specific record in that row.

 

[byllc]

I believe a solution to this is to store the full path to the file in the database. As is mentioned in post 223496. But I feel like there must be a better solution for such a common requirement.

 

[sneuberg]

Is this the same problem as posted in http://www.access-programmers.co.uk/forums/showthread.php?t=260242 except that now continuous forms are being used?

If so one thing that could be tried is the following:

1. Rework the code that worked in that post into a function that returns the image file path
2. Add that function to the record source query of the continuous forms as an expression
3. Add a hidden text box to the continuous form with the expression as the control source
4. Make this text box the control source of the image frame.

I''m not sure if this will work and so might be a waste of time, but if you upload your database I'd waste my time on it.

 

[johnkrytus]

Yes sneuberg, it is. Same exact thing.
I'd love to have you try, I'm just unsure about the process of sharing the database.. The front end is pretty easy, it's about 7MB. However the backend is a SQL Server database 350MB condensed with sensitive information.

edit: I use a Mac running a VM. The way I work with byllc is we share my screen.

 

[sneuberg]

I tested the concept in a simplified way and it works. If you unzip the attached folder you will find a database and two folders named "1" and "2" which have image files in them. The form People Form displays these images. The image frame in the forms has as its control source the text box named ImagePath. The control source of ImagePath text box is an expression in the query "Peoples Query" This expression uses a function in the module Get Image Path Module. This GetImagePath function is very simple compared to what you need but it demonstrates that an expression will work.

 

[sneuberg]

I think a function that might work for you would be"

Public Function GetImagePath(ctrl_people_id As Long) As String
    Dim vFolderPath As String, strFile As String, dirFile As String
    vFolderPath = DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")
    dirFile = Dir(vFolderPath & ctrl_people_id & " *", vbDirectory)
    strFile = "\profile_pic." + "*"
    If dirFile <> "" Then
        GetImagePath = vFolderPath & dirFile & strFile
    Else
        'GetImagePath =  some image that says no image
    End If
End Function

but I'd work hard to get rid of the DLook in this. I suspect there must be someway of joining the tblCodes-FolderControl to the record set query of the form. If you do this with the DLookup I think you will find the images displaying in a painfully slow manner.

 

[johnkrytus]

I can't get your code to work with "*"

 

[sneuberg]

The function in my demo doesn't attempt to do that, but I though you had that problem solved in the code you were using before. If you give me information on the tables, queries(field names and type) and folder structures involved, I'll make my demo more like your system and get this "*" thing working.

 

[johnkrytus]

Your right. The wildcard is solved on a single form. So.. it's part two of my continuous form issue (I forgot that I hadn't mentioned that). I had thought that maybe the issue was how we were writing our query but your example proves that it is likely something else.

Here is my (byllc's) query. If you specify the extension, it loads the first picture in the continuous form, where the imageframe has the control of imagePath:

Dim vFolderPath As String
  vFolderPath = Nz(DLookup("FolderName", "tblCodes_FolderControl", "FolderKey = '" & "Profile" & "'"))
  
  sortField = parse_sort_field
  'finalQry = "select ((Nz(FirstName, legal_name)) & (' '+ MiddleName) & ' ' & LastName) as fullname,* from qry_people_not_merged where " & criteriaQry & " order by " & sortField
   finalQry = "select ((Nz(FirstName, legal_name)) & (' '+ MiddleName) & ' ' & LastName) as fullname,('" + vFolderPath + "' & EmployeeId & '\profile_pic.jpg') as imagePath,* from qry_people_not_merged where " & criteriaQry & " order by " & sortField

 

[sneuberg]

Sorry, but that function I suggested before had the broke code in it. I meant:

Public Function GetImagePath(ctrl_people_id As Long) As String

Dim vFolderPath As String, dirFile As String, strFile As String
vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'"))
dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory)
strFile = dirFile & "\profile_pic.*"
On Error Resume Next
If Dir(strFile) <> vbNullString Then
    GetImagePath = dirFile & "\" & Dir(strFile)
Else
    GetImagePath = "X:\~stuff\profile_icon.png"
End If

End Function

 

[johnkrytus]

Do you have an sql server if I strip down my database?

 

[sneuberg]

I have sql server install on my system but I've never used it and I'm not even sure if it works. If you could give me a stripped down front end I think that might be enough. I'd also like to see some examples of the actually paths to the image files. Just go to the security tab of the properties of them and copy and paste the Object Names into your post.

 

[sneuberg]

On the other hand the front end alone won't show me the field names and types of the linked tables. Could you send me screen shots of the following tables/queries in design view.

tblCodes-FolderControl
qry_people_not_merged

and any other tables or queries that are involved, e.g., tables behind qry_people_not_merged

 

[byllc]

Sneuberg, It turns out that a secondary issue of file path inconsistency was the most problematic. The file paths are not always just id based but include bits of names. This makes it hard to algorithmically decide what the path is in a function without a human eye. This is why John wanted to use "*" as he is able to do elsewhere. The solution that worked here was to use calculated fields in the query itself to create two file paths based in the employee id and each is displayed in an image frame. As only one type will every exist, that is the one that shows up. This solved the jpg/png problem, then the path inconsistencies otherwise were solved by fixing them with a script so all paths are id based.

 

[johnkrytus]

Here is the query

finalQry = "select ((Nz(FirstName, legal_name)) & (' '+ MiddleName) & ' ' & LastName) as fullname,('" + vFolderPath + "' & EmployeeId & '\profile_pic.jpg') as ImagePathJPG, ('" + vFolderPath + "' & EmployeeId & '\profile_pic.png') as ImagePathPNG,* from qry_people_not_merged where " & criteriaQry & " order by " & sortField