In Access 2010 Display random pictures on form using attachment field

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.

Who is Participating?
MacroShadowConnect With a Mentor Commented:
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
Please upload a sample file.
shogun5Author Commented:
...of the picture attachment? It's just a .jpg. I can't send the entire database file as it contains student names and pictures.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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.
Jeffrey CoachmanMIS LiasonCommented:
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...
shogun5Author Commented:

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?
Jeffrey CoachmanMIS LiasonCommented:
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.
shogun5Author Commented:

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.
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 :-)
shogun5Author Commented:

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

How would I use the code above in this database?
Turned out a little different...
shogun5Author Commented:

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.

shogun5Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.