Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


In Access 2010 Display random pictures on form using attachment field

Posted on 2016-08-21
Medium Priority
Last Modified: 2016-08-25
Dear Experts,

Need help with VBA code on an button onClick event to randomly display a student's picture and name on the form where classID equals studentClassID.

Does anyone have sample code I could work from? I saw some code online for images in a filed but wondering if it's possible with the attachment field.

Also would like to use a time and run through about 10 pictures before the students see the final picture select displayed on the form.

Question by:shogun5
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
LVL 27

Expert Comment

ID: 41764782
Please upload a sample file.

Author Comment

ID: 41764783
...of the picture attachment? It's just a .jpg. I can't send the entire database file as it contains student names and pictures.
LVL 27

Expert Comment

ID: 41764787
The database without any data would do fine...
I'm having a dificult time understanding what it is exactly that you are trying to accomplish.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41766936
randomly display a student's picture and name
A random pic of just that student,... or see a random pic of any student?
So are you saying that in one students attachment field, have multiple images?

Why would you want to display a random "Name"?
So you have multiple "Names" stores somewhere, ...if so, ...where?

Like the first expert, ...I am confused about the need for this,
Can you clearly explain why you need to display random images and random names?

Also would like to use a time and run through about 10 pictures before the students see the final picture select displayed on the form.
The first part of your question needs to be cleared up first...
, lets save this other concern for another question...

Author Comment

ID: 41766951

The idea is to create a button that opens a form [frmRandom] and after the form loads, student pictures AND/OR names are displayed on the form in the timer control at one second (1000) intervals flashing a different picture each time and then eventually landing on one student picture.

I was think of making the record source of the form tblStudent and start the timer when the form loads and then run a query that I created that randomizes the student name

SELECT TOP 1 Rnd([studentID]) AS Expr1, tblStudent.studentClassID, tblStudent.studentPic, tblStudent.studentID
FROM tblStudent
WHERE (((tblStudent.studentClassID)=[Forms]![frmRandom]![studentClassID]));

Open in new window

In theory I was thinking that each time the runQuery command us executed in the timer control a different picture would show on the screen then set a counter so after 10 pictures stop the timer.

Would this work?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767237
We can get anything to work, ...I am just still confused about the "need" for doing this...?

In any event, ...again, a sample database would go a long way in clearing things up.

Author Comment

ID: 41767949

Having taught in education for over 30 years I know that elementary students love to get called on and get excited when they see a random student pictures pop up on the screen something like this:

....and then I call on that student to answer a question or to lead a group, etc. So students are selected by the 'luck of the draw' so to speak.

This is the 'why' and need for doing this.
LVL 27

Expert Comment

ID: 41768246
My take on it would be to loop through n random records and dislaying them using DoCmd.GoTo.
Here is a sample based on Chip Pearsons' UniqueRandomLongs function.

Option Explicit

Public Function UniqueRandomLongs(Minimum As Long, Maximum As Long, _
                                  Number As Long, Optional ArrayBase As Long = 1, _
                                  Optional Dummy As Variant) As Variant
' UniqueRandomLongs
' This returns an array containing elements whose values are between the Minimum and
' Maximum parameters. The number of elements in the result array is specified by the
' Number parameter. For example, you can request an array of 20 Longs between 500 and
' 1000 (inclusive).
' There will be no duplicate values in the result array.
' The ArrayBase parameter is used to specify the LBound of the ResultArray. If this
' is omitted, ResultArray is 1-based.
' The Dummy argument is to be used only when the function is called from a worksheet.
' Its purpose is to allow you to use the NOW() function as the Dummy parameter to force
' Excel to calculate this function any time a calculation is performed. E.g.,
'       =UniqueRandomLongs(100,199,10,NOW())
' If you don't want to recalulate this function on every calculation, omit the Dummy
' parameter. The Dummy argument serves no other purpose and is not used anywhere
' in the code.
' The function returns an array of Longs if successful or NULL if an error occurred
' (invalid input parameter).
' Note: The procedure creates its own array of size (Maximum-Minium+1), so very large
' differences between Minimum and Maximum may cause performace issues.

    Dim SourceArr() As Long
    Dim ResultArr() As Long
    Dim SourceNdx As Long
    Dim ResultNdx As Long
    Dim TopNdx As Long
    Dim Temp As Long

    ' Test the input parameters to ensure
    ' they are valid.
    If Minimum > Maximum Then
        UniqueRandomLongs = Null
        Exit Function
    End If
    If Number > (Maximum - Minimum + 1) Then
        UniqueRandomLongs = Null
        Exit Function
    End If
    If Number <= 0 Then
        UniqueRandomLongs = Null
        Exit Function
    End If

    ' Redim the arrays.
    ' SourceArr will be sized with an LBound of
    ' Minimum and a UBound of Maximum, and will
    ' contain the integers between Minimum and
    ' Maximum (inclusive). ResultArray gets
    ' a LBound of ArrayBase and a UBound of
    ' (ArrayBase+Number-1)
    ReDim SourceArr(Minimum To Maximum)
    ReDim ResultArr(ArrayBase To (ArrayBase + Number - 1))
    ' Load SourceArr with the integers between
    ' Minimum and Maximum (inclusive).
    For SourceNdx = Minimum To Maximum
        SourceArr(SourceNdx) = SourceNdx
    Next SourceNdx

    ' TopNdx is the upper limit of the SourceArr
    ' from which the Longs will be selected. It
    ' is initialized to UBound(SourceArr), and
    ' decremented in each iteration of the loop.
    ' Selections from SourceArr are always in the
    ' region including and to the left of TopNdx.
    ' The region above (to the right of) TopNdx
    ' is where the used numbers are stored and
    ' no selection is made from that region of
    ' the array.
    TopNdx = UBound(SourceArr)
    For ResultNdx = LBound(ResultArr) To UBound(ResultArr)
        ' Set SourceNdx to a random number between 1 and
        ' TopNdx. ResultArr(ResultNdx) will get its value from
        ' SourceArr(SourceNdx). Only elements of SourceArr
        ' in the region of the array below (to the left of)
        ' TopNdx (inclusive) will be selected for inclusion
        ' in ResultArr. This ensures that the elements in
        ' ResultArr are not duplicated.
        SourceNdx = Int((TopNdx - Minimum + 1) * Rnd + Minimum)
        ResultArr(ResultNdx) = SourceArr(SourceNdx)
        ' Now, swap elements SourceNdx and TopNdx of SourceArr,
        ' moving the value in SourceArr(SourceNdx) to the region
        ' of SourceArr that is above TopNdx.  Since only elements
        ' of SourceArr in the region below TopNdx (inclusive) are
        ' possible candidates for inclusion in ResultArr, used
        ' values are placed at TopNdx to ensure no duplicates.
        Temp = SourceArr(SourceNdx)
        SourceArr(SourceNdx) = SourceArr(TopNdx)
        SourceArr(TopNdx) = Temp
        ' Decrment TopNdx. This moves the effective UBound of SourceArr
        ' downwards (to the left), thus removing used numbers from the
        ' possibility of inclusion in ResultArr. This ensures we have
        ' no duplicates in the ResultArr.
        TopNdx = TopNdx - 1
    Next ResultNdx

    ' Return the result array.
    UniqueRandomLongs = ResultArr

End Function

Function IsArrayAllocated(V As Variant) As Boolean
' Ensure we have an allocated array.
    On Error Resume Next
    IsArrayAllocated = Not (IsError(LBound(V)) And IsArray(V)) And (LBound(V) <= UBound(V))
End Function

Open in new window

Then put this code on the forms load event:
Private Sub Form_Load()
    On Error GoTo Cleanup

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim Res As Variant
    Dim Min As Long, Max As Long, N As Long

    Dim i As Long

    Dim dtWaitTime As Date

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM tblStudent")

    Min = 1
    N = 10

    If Not (rs.BOF And rs.EOF) Then
        Max = rs.RecordCount
    End If

    Res = UniqueRandomLongs(Minimum:=Min, Maximum:=Max, Number:=N)

    With rs

        Do Until .EOF = True
            If i < N Then
                If IsArrayAllocated(Res) Then
                    For N = LBound(Res) To UBound(Res)
                        ' if you have a 'selected' field in the table, it will insure that no student will be selected more than once
                        ' to incorporate it, add a yes/no field to the table and uncomment the following commented lines
                        '                        If !selected = False Then
                        If !studentID = Res(N) Then
                            ' change frmNameofYourForm to the name of your form
                            DoCmd.GoToRecord ObjectType:=acDataForm, ObjectName:="frmNameofYourForm", Record:=acGoTo, Offset:=!studentID
                            dtWaitTime = Now + TimeValue("00:00:01")
                            Loop Until Now >= dtWaitTime
                            '                                !selected = True
                            i = i + 1
                            Debug.Print Res(N)
                        End If
                        '                        End If
                    Next N
                End If
            End If
    End With


    If Not rs Is Nothing Then
        Set rs = Nothing
        Set db = Nothing
    End If

End Sub

Open in new window

I hope this helps :-)

Author Comment

ID: 41768666

Thanks! As requested, I stripped the database and am using fictitious students names.

How would I use the code above in this database?
LVL 27

Expert Comment

ID: 41769874
Turned out a little different...

Author Comment

ID: 41770262

Thanks! But there is a bug in the code and i can't seem locate where it's going wroing. When I press [Flash] five records get 'selected' checked but it should only be the 'last' student that shows up that shold get the selected check. Then when i press flash again all students show up 'but' the one that was selected before. Now within three clicks of the [flash] button all students are checked.

Somehow I need to have the students flash on the screen, like all nine of them in random order then when it stops on one a bell should sound so we know it's not 'searching' and that 'last' student will have the boxed check. I think this is a great start but not sure where in the code I can fix that.

LVL 27

Accepted Solution

MacroShadow earned 2000 total points
ID: 41770662
I hopw this version will suffice (not tested).
Note, instead of sounding a bell, a simple beep will be sounded. It is possible to play a music file but that is beyond the scope of this question (you may ask a new question). For code to play a wav file please refer to

Author Closing Comment

ID: 41771028
Hmmm...this is actually making all records 'selected' when I first run the event. This will not work for me and honestly I don't this is the best approach to handle quickly displaying a succession of records and then finally stopping on one. I am going to close this thread. Thank you for your efforts however and because you at least gave an attempt to assist I am going to close this question and mark your answer as the best solution for now. However, I do believe there is a better way to do this.
Thanks again for trying to make this work for me.

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question