Creating a picture grid dynamically

[TOPSie]

I want to create a form which has a "grid" of thumbnail pictures. I had the code working by designing the form and placing 36 image boxes in a 6x6 grid. But the number of pictures has increased (current total is 79) and the user wants to see them all on one page. So I have been "hacking" away with minimal success at trying to add the image boxes dynamically. At one stage the image grid did appear, then I got memory full, a further iteration I added click events to the code - and the code module looks reasonable. But now having (apparently) added all the new controls and code when I try and save the form the whole thing just disappears (the code exits completely with no error message.



This is the startup form

Private Sub Form_Load()
DeclareColours
GAKELoad
GAKECreateGrid
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "GAKE-Dahlia"


End Sub




Private Sub GAKECreateGrid()
Dim tempfile As String
Dim wThisCount As Integer
Dim bPrimePic As Boolean
Dim wPrimePic As String
'imgBox.Visible = False
'lblCurPos.Caption = ""
bPrimePic = False
'cmdPrime.Visible = False
Dim wT As Integer
Dim wW As Integer
Dim wH As Integer
Dim wL As Integer
Dim wName As String
Dim wMDL As Module
'tempfile = "C:\G-Apps\GAKE-2022\GAKE.txt"
Set db = CurrentDb()
Set rs = db.OpenRecordset("Dahlias", dbOpenDynaset)
rs.MoveLast
GAKENumPics = rs.RecordCount
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'@
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DoCmd.OpenForm "GAKE-Dahlia", acDesign, , , , acHidden
Set wMDL = Forms![GAKE-Dahlia].Module
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'@ Now try and create the required number of pictures dynamically
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
wPics = 6
wPicd = GAKENumPics / wPics
If GAKENumPics Mod wPics <> 0 Then
    wPicd = wPicd + 1
End If
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'@ If this is not the first time then we must delete the controls
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
For i = 1 To wPicd
    For j = 1 To wPics
        wName = "i" & i & j
       For Each wctl In Forms![GAKE-Dahlia].Controls
            If wctl.Name = wName Then
                 DeleteControl "GAKE-Dahlia", wName
            End If
        Next
   Next
Next
rs.MoveFirst
For i = 1 To wPicd
    For j = 1 To wPics
        wT = wPicT + ((i - 1) * (wPicH / wPics))
        wH = wPicH / wPics
        wW = wPicW / wPics
        wL = wPicL + ((j - 1) * (wPicW / wPics))
        wName = "i" & i & j
        Set ctlimage = CreateControl("GAKE-Dahlia", acImage, , , , wL, wT, wW, wH)
        
        ctlimage.Name = wName
        ctlimage.Picture = GAKEPictures & rs!DahliaPicture & ".jpg"
        ctlimage.Tag = wCurPos & "£" & GAKEPictures & rs!DahliaPicture & ".jpg" & "$" & rs!DahliaName
        ctlimage.Visible = True
        ctlimage.Properties.Item("OnClick") = "[Event Procedure]"
        strcode = "Private Sub " & wName & "_Click()" & vbCr _
        & "BigImage (" & wName & ".Tag)" & vbCr _
        & "End Sub"
        wMDL.InsertText strcode
       
        wCurPos = wCurPos + 1
        rs.MoveNext
        If rs.EOF Then
           GoTo wOut
        End If
    Next
Next
wOut:
 'DoCmd.OutputTo acOutputModule, wMDL, acFormatTXT, tempfile
 'Set wMDL = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing


MsgBox "closing"
DoCmd.Close acForm, "GAKE-Dahlia", acSaveYes
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'@ This next line is never executed - it just crashes silently
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
MsgBox "closed"






End Sub

[moke123]

Not sure if it still applies in later versions of access but there is/was a lifetime limit on the number of controls on a form.
I would add all the image controls possibly needed and arrange them with code as needed. Instead of using an event procedure I would use a function in the onclick event .
I would put the path to the image in the tag property and in the function use screen.activecontrol.tag.

[orange]

Can you post a copy of the database?

[Micron]

Further to moke123's reply, the limit is 754 and includes sections in the count.
I agree - loop over the controls in code, then it won't matter how many you add. If they're all the same size, easy enough to situate them anywhere in design view and perhaps even control their visibility if any don't have pic data. That enables you to show controls with images and have no gaps but you have to put all of them outside of the presentation area in design view, or stack them all on top of each other at the top left of that area.

[CJ_London]

Agree with Moke create enough controls that will fit on the page

[moke123]

here's an example of moving the image controls and sizing them.
There are 50 image controls on the page, top left corner.
Add more photos to the image folder.

changing the modulus number will change how many images you go horizontally

[CJ_London]

you can do something similar to moke's suggestion using a crosstab query - then your form only needs enough controls for the width of the page

Depends on your table structure but you need to determine a 'rowcount' value -which could be as simple as using dcount if your table has a PK or some other unique value. Could even use [DahliaPicture] if these are unique.

dcount("*","Dahlias","PK<=" & PK)

which will give you values from 1 to the number of records

then you apply to a crosstab query - something like this for 5 pictures across

TRANSFORM First([DahliaPicture] & ".jpg") AS FirstOfDahliaPicture
SELECT "R" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\5)+1 AS Row
FROM Dahlias
GROUP BY "R" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\5)+1
PIVOT "C" & ((DCount("*","Dahlias","PK<=" & [PK])-1) Mod 5)+1 In ("C1","C2","C3","C4","C5")

And then no code is required

just assign C1..C5 to each of the 5 image control controlsource.

Edit: You can also add a page number so user can move backwards and forwards in page chunks rather than a full scroll - assuming you have 10 rows of 5 columns

TRANSFORM First([DahliaPicture] & ".jpg") AS FirstOfDahliaPicture
SELECT "R" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\5)+1 AS Row, "P" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\50)+1 AS PageNum
FROM Dahlias
GROUP BY "R" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\5)+1,"P" & ((DCount("*","Dahlias","PK<=" & [PK])-1)\50)+1
PIVOT "C" & ((DCount("*","Dahlias","PK<=" & [PK])-1) Mod 5)+1 In ("C1","C2","C3","C4","C5")

[moke123]

I made a couple small changes.
Assuming you want to click on the thumbnail and open the image, I added a line to add a function to the double click event of the controls, as well as the function to open the image.

Public Sub ArrangeImageControls()
    Dim ctl As Control
    Dim WidthHeight As Long                         'height and width of image control
    Dim xStart As Long                              'Start position for horizontal rows
    Dim yStart As Long                              'start position for vertical rows
    Dim varOffset As Long                           '# of twips between controls
    Dim xpos As Long, ypos As Long, i As Integer
    Dim PCount As Long                              '# of images in folder
    Dim fol As Folder, fil As File, folPath As String
    
    Dim fso As New FileSystemObject
    
    folPath = CurrentProject.Path & "\Images"
   
    Set fol = fso.GetFolder(folPath)
    
    PCount = fol.Files.Count                        '# of images in folder
    
    WidthHeight = 1440
    xStart = 3300
    yStart = 2400
    varOffset = 150
    xpos = xStart
    ypos = yStart


    If PCount > 50 Then PCount = 50                 'set to # of image controls


    For i = 1 To PCount
    
        Set ctl = Me.Controls("Img" & i)
        
        ctl.Move xpos, ypos, WidthHeight, WidthHeight
        
        xpos = xpos + varOffset + WidthHeight
        
        ctl.OnDblClick = "= fClick(""" & ("Img" & i) & """)"    'set OnDblClick event to a function
        
        If i Mod 8 = 0 Then                                  'change mod number to # of images horizontally
            ypos = ypos + WidthHeight + varOffset
            xpos = xStart
        End If
        
    Next i
    
    i = 1
    
    For Each fil In fol.Files                           'iterate through images folder and set picture and tag properties
        Me.Controls("Img" & i).Tag = fil.Path
        Me.Controls("Img" & i).Picture = fil.Path
        i = i + 1
        If i > PCount Then Exit For
    Next
    
    Set fso = Nothing
    
End Sub


Function fClick(ctlName As String)
     
    Application.FollowHyperlink Me.Controls(ctlName).Tag

End Function

[orange]

This is a small adjustment to Moke123's sample database.
I pointed to one of my folders and processed only png files.
Produces 5 X10 grid of images.(cut off because of my lack of familiarity with the capture software)

Very nice code and easily adjusted.