Solved

In Access 2010 Display random pictures on form using attachment field

Posted on 2016-08-21
13
45 Views
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.


tblStudent
Thanks!
0
Comment
Question by:shogun5
  • 6
  • 5
  • 2
13 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41764782
Please upload a sample file.
0
 

Author Comment

by:shogun5
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.
0
 
LVL 26

Expert Comment

by:MacroShadow
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.
0
 
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, ...you 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...
, ...so lets save this other concern for another question...
0
 

Author Comment

by:shogun5
ID: 41766951
Jeff,

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?
0
 
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:shogun5
ID: 41767949
Jeff,

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:
http://viewpure.com/f23bnZaWHNE?start=313&end=328

....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.
0
 
LVL 26

Expert Comment

by:MacroShadow
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

    Randomize
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' 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
        rs.MoveLast
        rs.MoveFirst
        Max = rs.RecordCount
    End If

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

    With rs

        Do Until .EOF = True
            rs.Edit
            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")
                            Do
                                DoEvents
                            Loop Until Now >= dtWaitTime
                            '                                !selected = True
                            i = i + 1
                            Debug.Print Res(N)
                        End If
                        '                        End If
                    Next N
                End If
                .Update
                .MoveNext
            End If
        Loop
    End With

Cleanup:

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

End Sub

Open in new window


I hope this helps :-)
0
 

Author Comment

by:shogun5
ID: 41768666
MacroShadow,

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

How would I use the code above in this database?
test-DB.accdb
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41769874
Turned out a little different...
test-DB.accdb
0
 

Author Comment

by:shogun5
ID: 41770262
MacroShadow.

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.

Thanks!
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 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 http://www.cpearson.com/excel/PlaySound.aspx.
test-DB.accdb
0
 

Author Closing Comment

by:shogun5
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.
Cheers!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now