Ms Access 2016 how to display 10 images horizontially in continuous mode using vba

Question

I need a way to display 10 images horizontally and in continuous mode on an MS Access form using VBA.

At the moment I'm using a cross tab query As the query is slow and the temp table needs to be regenerated every time a different sort is needed, I was looking for a VBA solution.

I also need it to be able to select an individual image and get a popup form for more info (like in the cross tab pic), etc. and be able to sort the form according to various selections.

So far after a long search the only solution I've found is on another forum which I can post if needed but haven't been able to get the code to work fully.

This is a pic of the working cross tab form...

This is the current not working one...

I managed to get 10 images across but they just repeat each row.

The code I have so far is...

Private Sub genrpt()
    Dim I As Integer
    Dim X As Integer

    X = 0
    Do
        Me("Img" & X + 1).Picture = Me.lst0.Column(1, I)
        I = I + 1
        X = X + 1
    Loop Until X >= 10
End Sub

So if anybody can help I'd be eternally grateful.

Answer 1

I solved a similar problem where I wanted to display 15 images per page of a form. I set up a grid of unbound boxes on the form called 'imgPoster1', 'imgPoster2' etc. With manual next page and previous page buttons with Page 1 of 10 between them (as controls txtPageNumber and txtOfPages)

This code then put 15 records per page in a grid of images with the poster name beneath each image.

Private Sub Form_Current()
    Dim strSQL As String

    strSQL = "SELECT * FROM tblPosters"

    ShowPosters strSQL, 1
    Me.txtPageNumber.Value = 1
    Me.txtOfPages.Value = Round(0.4 + DCount("PosterID", "tblPosters") / 15) 'adding 0.4 just rounds up

End Sub

Sub ShowPosters(strSQL As String, intStartRecordNumber As Integer)
    Dim intControlNumber As Integer
    Dim intRecordNumber As Integer
    Dim rs As DAO.Recordset

    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    'On Error Resume Next 'for when poster image doesn't exist

    intControlNumber = 1
    intRecordNumber = 1

    Do While Not rs.EOF
      If intRecordNumber >= intStartRecordNumber And intRecordNumber < intStartRecordNumber + 15 Then
         If Len(rs.Fields("PosterCode")) > 0 Then
             Me.Controls("imgPoster" & intControlNumber).Picture = CurrentProject.Path & "\Attachments\" & rs.Fields("PosterCode") & ".jpg"
             Me.Controls("txtPosterCode" & intControlNumber).Value = rs.Fields("PosterCode") & " - " & rs.Fields("PosterGroup")
         End If

        Me.Controls("txtPosterName" & intControlNumber).Value = rs.Fields("PosterName")

        intControlNumber = intControlNumber + 1
     End If

        rs.MoveNext

        intRecordNumber = intRecordNumber + 1

   Loop

   'make any remaining controls blank

    Do While intControlNumber <= 15
         Me.Controls("imgPoster" & intControlNumber).Picture = ""
         Me.Controls("txtPosterCode" & intControlNumber).Value = ""
         Me.Controls("txtPosterName" & intControlNumber).Value = ""

        intControlNumber = intControlNumber + 1
    Loop
rs.Close
On Error GoTo 0
Me.Refresh
End Sub

Private Sub cmdNextPage_Click()

    Dim strSQL As String

    strSQL = "SELECT * FROM tblPosters"

    If CInt(Me.txtPageNumber.Value) < CInt(Me.txtOfPages.Value) Then
       Me.txtPageNumber.Value = Me.txtPageNumber.Value + 1
       ShowPosters strSQL, 1 + ((Me.txtPageNumber.Value - 1) * 15)
    End If

End Sub

Private Sub cmdPreviousPage_Click()

    Dim strSQL As String

    strSQL = "SELECT * FROM tblPosters"


    If CInt(Me.txtPageNumber.Value) > 1 Then
        Me.txtPageNumber.Value = Me.txtPageNumber.Value - 1

        ShowPosters strSQL, 1 + ((Me.txtPageNumber.Value - 1) * 15)
    End If
End Sub

Answer 2

My solution dispenses with the one you have been attempting. It would have established a row of pictures only - they would have been jammed together and I'm not sure if you would have been able to refer to each individually, configure them as controls or links, or display a short piece of text below each. If there is way around these problems, then I would actually like to know more about it.

Instead, my solution is based around a table of the following structure...

+----------------+------------+------------------------------------------------------------------------------+
| fldID          | AutoNumber | An automatically generated  number used to uniquely identify a `movie`.      |
| fldDescription | Short Text | A short description of the `movie`, typically just the `name` of the `movie` |
| fldPicture     | Short Text | The full path to the `picture` file                                          |
| fldSynopsis    | Long Text  | A lot of text about the `movie` - for testing the details form               |
+----------------+------------+------------------------------------------------------------------------------+

It is not clear from your Question if you are storing a reference to the location of the pictures or a copy of the image itself in your database. I have chosen to go with the former option here.

I use three forms in this solution. The first is frmMain, which is host to a title and a subform control called subFrmSub that is linked to the form frmSub.

The second form, frmSub, has its Default Views property set to Continuous Forms. Its Record Source is the following query, which I have called qryPicturesPivoted...

TRANSFORM MAX( prePivot.columnValue ) AS MaxOfcolumnValue
SELECT prePivot.groupNumber AS recNumber
FROM ( SELECT ( RecordNumber - 1 ) \ 10 + 1 AS groupNumber,
              "ID" & FORMAT( ( RecordNumber - 1) MOD 10 + 1, "00" ) AS columnNumber,
              fldID AS columnValue
       FROM qryRecordNumberFinder
       UNION ALL
       SELECT ( RecordNumber - 1 ) \ 10 + 1,
              "Description" & FORMAT( ( RecordNumber - 1) MOD 10 + 1, "00" ),
              fldDescription AS columnValue
       FROM qryRecordNumberFinder
       UNION ALL
       SELECT ( RecordNumber - 1 ) \ 10 + 1,
              "Picture" & FORMAT( ( RecordNumber - 1) MOD 10 + 1, "00" ),
              fldPicture
       FROM qryRecordNumberFinder
     ) AS prePivot
GROUP BY prePivot.groupNumber
PIVOT prePivot.columnNumber;

This query is itself based around the query qryRecordNumberFinder, which follows...

SELECT COUNT( * ) AS RecordNumber,
       leftTable.fldID AS fldID,
       leftTable.fldDescription AS fldDescription,
       leftTable.fldPicture AS fldPicture
FROM tblPictures AS leftTable
INNER JOIN tblPictures AS rightTable ON leftTable.fldID >= rightTable.fldID
GROUP BY leftTable.fldID,
         leftTable.fldDescription,
         leftTable.fldPicture;

qryRecordNumberFinder selects the fields from each record in one instance of tblPictures (which I have given the alias of leftTable to) that are relevant to frmSub and appends to each a copy of each record from another instance of tblPictures (rightTable) that has an fldID value less than or equal to that of the record from leftTable. It then groups the records by the values from leftTable and returns a count of records in each group, effectively appending a record number to each record from tblPictures.

qryRecordNumberFinder is based on JokoSumanto's Answer to the Question at How to show row number in Access query like ROW_NUMBER in SQL.

Sadly, there is no row number facility such as SQL-Server's ROW_NUMBER() in Access.

Since there appears to be no ready way in Access to pivot more than two fields with just one of the fields becoming the new field names, qryPicturesPivoted starts of with a subquery that recreates the results of qryRecordNumberFinder in three fields.

The first is groupNumber. It is will become the pivoted data's record number. It is calculated by first subtracting 1 from RecordNumber, turning 1 through 10 into 0 through 9, 11 through 20 into 10 through 19, etc. This operation is necessary so that the \ operator (also known as the DIV operator and not to be confused with the division operator /) will return the same number for each RecordNumber in a group of 10 records. 1 is then added to the number produced by the \ operation so that the first value of groupNumber is 1 and not 0.

A similar operation that uses the MOD operator instead of the \ operator is used to generate the columnNumber field, which will become the pivoted data's field names.

columnValue will contain the data from tblPictures selected by qryRecordNumberFinder.

qryPicturesPivoted is based on Gord Thompson's Answer to the Question at Pivot on multiple fields and export from Access.

Each instance of frmSub places the values for its record from qryPicturesPivoted into one of 30 controls, which are comprised of 10 groups of 3 controls - one for a movie's image, one for the movie's description / name (which has its Tab Stop property set to No) and one for the movie's fldID value (which has its Visible property set to No). The record number is placed in another (invisible to the user) control for reference purposes.

Each group of 3 controls has placed over it a command button that has no Caption and has its Back Style and its Border Style set to Transparent, effectively making the button invisible but still available to the user. This is done so that the user may click anywhere on the image or text field or the black-space contained within the button's bounds to summon the details form. Each command button uses a variation on the following code to open the details form to the selected record...

DoCmd.OpenForm "frmDetails", , , "fldID = " & Me.txtID01, acFormReadOnly

The third form I mentioned is frmDetails. It is based on tblPictures and contains the image, the description / name, and the synopsis.

When the form frmMain is loaded I use the following code to set the height of the subform control, the detail section and the form so as to ensure that all the rows of pictures are displayed in frmMain without need for a scroll-bar within the subform control...

Private Sub Form_Open(Cancel As Integer)
    Dim recordCount As Integer
    Dim subFrmSubHeight As Integer


    recordCount = DCount("fldID", "tblPictures")
    subFrmSubHeight = (((recordCount - 1) \ 10) + 1) * 6

    Me.InsideHeight = (2 + subFrmSubHeight + 1) * 567
    Me.Detail.Height = (2 + subFrmSubHeight + 1) * 567
    Me.subFrmSub.Height = subFrmSubHeight * 567
End Sub

When using VBA to set a height you can not specify the unit of measurement (such as with 3cm) and are instead forced to use TWIPS. Please see accessbob-pdx's Answer to the Question at https://social.msdn.microsoft.com/Forums/office/en-US/f64af695-21f7-421f-9c7c-b398e15ab085/unit-of-measure-in-access?forum=accessdev.

There is no need for a list-box in my solution.

I have not yet been able to reliably highlight a specific movie by setting that movie's command button's Border Style to Solid. Every method I've tried either sets no button's Border Style to Solid, or it sets the Border Style to Solid for every instance of that control, effectively highlighting the entire column. I have a Question posted on this matter, but for now I'm not going to spend any more time on that problem.

I will post a link to a copy of the database as soon as I find a suitable hosting service.

I hope that this solution will be fast enough for you. If not, there are a number of refinements that can be made to your database to make it more efficient, but it is difficult to comment on what they are without knowing a number more details about your database.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Appendix 1

To allow for refining of the output to just those movies starting with a chosen letter I have defined the following function in VBA...

Private Function GetRefineToLetterString(parameterLetter As String) As String
    GetRefineToLetterString = "TRANSFORM Max(prePivot.columnValue) AS MaxOfcolumnValue " & _
                              "SELECT prePivot.groupNumber AS recNumber " & _
                              "FROM ( SELECT ( RecordNumber - 1 ) \ 10 + 1 AS groupNumber, 'ID' & FORMAT( ( RecordNumber - 1 ) MOD 10 + 1, '00' ) AS columnNumber, fldID As columnValue FROM ( SELECT COUNT( * ) AS RecordNumber, leftTable.fldID AS fldID FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS leftTable INNER JOIN ( SELECT fldID FROM tblPictures  WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS rightTable ON leftTable.fldID >= rightTable.fldID GROUP BY leftTable.fldID ) AS qryRecordNumberFinderID " & _
                              "       UNION ALL SELECT ( RecordNumber - 1 ) \ 10 + 1, 'Description' & FORMAT( ( RecordNumber - 1 ) MOD 10 + 1, '00' ), fldDescription FROM ( SELECT COUNT( * ) AS RecordNumber, leftTable.fldID AS fldID, fldDescription FROM ( SELECT fldID, fldDescription FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS leftTable INNER JOIN ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS rightTable ON leftTable.fldID >= rightTable.fldID GROUP BY leftTable.fldID, leftTable.fldDescription ) AS qryRecordNumberFinderDescription " & _
                              "       UNION ALL SELECT ( RecordNumber - 1 ) \ 10 + 1, 'Picture' & FORMAT( ( RecordNumber - 1 ) MOD 10 + 1, '00' ), fldPicture FROM ( SELECT COUNT( * ) AS RecordNumber, leftTable.fldID AS fldID, fldPicture FROM ( SELECT fldID, fldPicture FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS leftTable INNER JOIN ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS rightTable ON leftTable.fldID >= rightTable.fldID GROUP BY leftTable.fldID, leftTable.fldPicture ) AS qryRecordNumberFinderPicture " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description02', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 = 1 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description03', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 2 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description04', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 3 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description05', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 4 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description06', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 5 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description07', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 6 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description08', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 7 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description09', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 8 " & _
                              "       UNION ALL SELECT COUNT( * ) \ 10 + 1, 'Description10', NULL FROM ( SELECT fldID FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) AS matchingCount02 HAVING ( SELECT COUNT( * ) FROM tblPictures WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "' ) MOD 10 BETWEEN 1 AND 9 " & _
                              "     )  AS prePivot " & _
                              "GROUP BY prePivot.groupNumber " & _
                              "PIVOT prePivot.columnNumber;"
End Function

Then for each letter's command button I added the following code to the On Click event, with that button's letter inseide the brackets...

Me!subFrmSub.Form.RecordSource = GetRefineToLetterString("A")

The first reason why I have gone with this rather large and ugly function is that Access does not like using parametrised queries (or queries that are ultimately based on a parametrised query) as the basis for a PIVOT. As such the search criteria need to be coded into the statement rather than passed through as a parameter. The statement used here is based on the queries mentioned earlier, qryPicturesPivoted and qryRecordNumberFinder, with the code for the two queries having been merged into one statement. Further, since we want to display a refined dataset rather than all the records, I have replaced all references to tblPictures with variations on the following code...

SELECT fldID,
       fldDescription,
       fldPicture
FROM tblPictures
WHERE LEFT( fldDescription, 1 ) = '" & parameterLetter & "'

Once the parameter letter has been incorporated, the string become something like...

SELECT fldID,
       fldDescription,
       fldPicture
FROM tblPictures
WHERE LEFT( fldDescription, 1 ) = 'A'

The statement proceeds from there largely as before, with two modifications. Firstly, I have removed unnecessary field selections. Secondly, I have added UNION's that will buffer out our results to ensure that each field in the instance of frmSub corresponding to the last row will always be connected to a value (NULL or otherwise). Without the buffer data it we get a #NAME? message for each unoccupied Description field.

I have also added a button that enables you to search for movies that start with a digit.

Quite frankly I feel that many of the problems encountered thus far can be easily avoided or corrected for if we implement a solution that displays the records on the main form in pages of, say, 50 entries and dispenses with the subform altogether. I shall proceed to develop such a solution.

Appendix 2

One alternative approach to this problem is to dispense with the subform approach and break the record-set up into pages of 40 records to be displayed on the main form. I did try for pages of 50, but Access complained that the PIVOT query produced too many fields (there is a limit of 255 fields in Access).

The query used to populate the form when it is opened follows...

TRANSFORM MAX(columnValue) AS MaxOfcolumnValue
SELECT groupNumber AS recNumber
FROM ( SELECT ( RecordNumber - 1 ) \ 40 + 1 AS groupNumber,
              "ID" & FORMAT( ( RecordNumber - 1 ) MOD 40 + 1, "00" ) AS columnNumber,
              fldID AS columnValue
       FROM ( SELECT COUNT( * ) AS RecordNumber,
                     leftTable.fldID,
                     leftTable.fldDescription
              FROM tblPictures AS leftTable
              INNER JOIN tblPictures AS rightTable ON ( leftTable.fldDescription > rightTable.fldDescription OR
                                                        ( leftTable.fldDescription = rightTable.fldDescription AND
                                                          leftTable.fldID >= rightTable.fldID ) )
              GROUP BY leftTable.fldID,
                       leftTable.fldDescription
              ORDER BY leftTable.fldDescription
            ) AS subqueryID
       UNION ALL
       SELECT ( RecordNumber - 1 ) \ 40 + 1,
              "Description" & FORMAT( ( RecordNumber - 1 ) MOD 40 + 1, "00" ),
              fldDescription
       FROM ( SELECT COUNT( * ) AS RecordNumber,
                     leftTable.fldDescription
              FROM tblPictures AS leftTable
              INNER JOIN tblPictures AS rightTable ON ( leftTable.fldDescription > rightTable.fldDescription OR
                                                        ( leftTable.fldDescription = rightTable.fldDescription AND
                                                          leftTable.fldID >= rightTable.fldID ) )
              GROUP BY leftTable.fldID,
                       leftTable.fldDescription
              ORDER BY leftTable.fldDescription
            ) AS subqueryDescription
       UNION ALL
       SELECT ( RecordNumber - 1 ) \ 40 + 1,
              "Picture" & FORMAT( ( RecordNumber - 1 ) MOD 40 + 1, "00" ),
              fldPicture
       FROM ( SELECT COUNT( * ) AS RecordNumber,
                     leftTable.fldID,
                     leftTable.fldDescription,
                     leftTable.fldPicture
              FROM tblPictures AS leftTable
              INNER JOIN tblPictures AS rightTable ON ( leftTable.fldDescription > rightTable.fldDescription OR
                                                        ( leftTable.fldDescription = rightTable.fldDescription AND
                                                          leftTable.fldID >= rightTable.fldID ) )
              GROUP BY leftTable.fldID,
                       leftTable.fldDescription,
                       leftTable.fldPicture
              ORDER BY leftTable.fldDescription
            ) AS subqueryPicture
     )  AS prePivot
GROUP BY groupNumber
PIVOT columnNumber;

As Access can not pivot more than two fields this query turns tblPictures into a table three fields, consisting of -

A group number based on the position of the record from tblPictures in a list that is sorted into ascending order of the values in fldDescription. Each group is 40 tblPictures record in size, except the final group, which has how many records are left at that stage.

Each value from each relevant field from each record from tblPictures.

A field containing the name of the field that the value from tblPictures will be placed in once the PIVOT has been performed.

The applicable values of each record from fldPictures are placed within a group of controls on the form. Each group consists of -

A text box called txtID01 (or whatever number corresponds to the position on the page) that stores the value of fldID. This control is for the program's reference purposes and does not need to be made available to the user. As such it's Visible property is always set to False. An image control called imgPicture01 (or whatever number corresponds to the position on the page) that is used to display the image referred to by fldPicture. A text box called txtDescription01 (or whatever number corresponds to the position on the page) that displays the value of fldDescription. This control is for display purposes, but does not need to be navigated to by the user. As the control will be covered by a transparent command button, thus preventing it be clicked, it only needs to have its Tab Stop property set to No.

Each group is covered by a command button that has no Caption and has its Back Style property set to Transparent. This is the button that will enable the user to choose the record regardless of whether or not they click on the Picture, Description or the "white-space" immediately surrounding them.

Each group's command button's On Click event has the following VBA code associated with it...

Private Sub cmd01_Click()
    DoCmd.OpenForm "frmDetails", , , "fldID = " & txtID01
End Sub

The above code opens the form frmDetails, which displays the details of the selected record, including the field fldSynopsis.

Each group's command button's Got Focus event has the following code associated with it...

Private Sub cmd01_GotFocus()
    Me.cmd01.BorderStyle = 1
End Sub

This code will change the Border Style property for the command button from 0 (Transparent) to 1 (Solid), causing the group of controls to be surrounded with a border highlighting their selection.

Each group's command button's Lost Focus event has the following code associated with it...

Private Sub cmd01_LostFocus()
    Me.cmd01.BorderStyle = 0
End Sub

This code will change the Border Style property for the command button from 1 (Solid) to 0 (Transparent), causing the border highlighting the group's selection to be removed when it ceases to be the current selection.

Across the top of the page are 28 buttons, one for each letter of the alphabet, one with the digit 9 and one with the character *.

When each of the letter buttons are clicked the following code is executed...

Private Sub cmdRefineToA_Click()
    RefineToLetter ("A")
End Sub

The summoned procedure RefineToLetter() follows...

Private Sub RefineToLetter(parameterLetter As String)
    Me.RecordSource = GetRefineToLetterString(parameterLetter)
    recordCount = DCount("*", "tblPictures", "LEFT( fldDescription, 1 ) = 'A'")

    SetNavigationControls
    ShowHideControls
End Sub

This procedure starts by getting a copy of the SQL statement that is the same as the one initially used to set the form's RecordSource property, but with each reference to tblPictures replaced with the following code...

SELECT fldID,
       fldDescription,
       fldPicture
FROM tblPictures
WHERE LEFT( fldDescription, 1 ) = 'A'

Unfortunately, Access does not like trying to PIVOT the results of parametrised queries, so the above statement needs to be constructed in VBA, then applied. Also, please note that you can spread a statement out across multiple lines by turning each line into a string ending with " & _, but there is a limit to the amount of lines you can concatenate across in this way.

Once the form's RecordSource property has been set to the above query, the form will be refilled with all those records whose fldDescription value starts with that letter.

RefineToLetter() then recalculates the global variable recordCount (which holds the number of record from tblPictures meeting the current search criteria, then calls the procedures SetNavigationControls and ShowHideControls, which enable / disable the navigation buttons depending on the value of recordCount and hide / show the command buttons and Description controls corresponding to each record from tblPictures depending on whether or not they have a record to show. By hiding the command buttons when they have no corresponding record we can prevent the user from attempting to choose a nonexistent record (thus avoiding any errors this could generate) and we also prevent the user from being able to tab to the control. By hiding the Description control we hide the error message it displays when there is no value to poulate it with.

SetNavigationControls and ShowHideControls are also called when the page loads / changes by adding calls to the form's ON Current event.

The 9 button uses a variation on the above query that returns every record that starts with a digit.

The * button resets the RecordSource property to the query used to initially populate the form.

At the top and the bottom of the form are a pair of buttons that can be used to navigate between pages. The back buttons use the following code to move back 1 page...

DoCmd.GoToRecord , , acPrevious

The forward buttons use the following code to move forward 1 page...

DoCmd.GoToRecord

Please note that when DoCmd.GoToRecord encounters no parameters it defaults to going to the next record in the form's record-set.

I hope to have a copy of what I have done so far up shortly.