How to develop a carousel

Introduction

Hi all!
This week's post is a mix of part of the previous made practical. I will make grow the present example in following posts.
We have seen how to organise an application, how to manage attached files, and how to work with options. This week we will join some of those characteristics to develop a Carousel.

This is an example I've made some years ago to help a user with a carousel for used cars.
It's made thinking on the spanish market, and I've used the plate number usual in Spain, that is, a number between 0000 and 9999 and three characters B-Z (avoiding the vowels). It's made with Access 2003.

What is a carousel and when may be useful?
A carousel is a form to show images, pictures, and when is loaded shows, automatically, a picture each time. In this case is each 5 seconds (5000 milliseconds).
This may be useful, for example, in a business where you want to show photos of your goods to your customers: used cars, real estate..., that can't be shown directly due to diffent reasons.
The example shows cars, but may be done with real state, or any other good suitable of be shown to the potential customer in an office before to visit or to be shown.

Example approach

This is, what is the idea for this example.

I did a table to store data about cars. I've dowloaded a set of pictures of a car, and I entered some data about this car.
After, I did two forms, the first show the data of one car, of course is an example, and the second shows the pictures.
There's also a litlle bit of code, in the forms and in a module.
As a reference to the previous posts:

• Organisation: this example is made using a simple file, because it's very small. I would like to show the photos. It will be upgraded in nexts posts to see how to work with more than an access file, not only to share data, but also to share modules. To do this I need to add more code, in order to automate the linking of the files, that is, because if you download the files they will not work because your folders are not my folders.
• Attached files: this example has 11 pictures, but those pictures are used but are not stored in the database. Like this, the database remains small, and this means, it has a good performance. In the other hand, if you use this example with, for example, 100 cars, the performance will not be affected, or at least, will not be affected by the pictures.
• Options: once we have seen this example, in nexts posts we will see how to upgrade this carousel using the options form. It's not done in this example to avoid an excess of code.
• Data management with VBA. It will be done in the next post, and used later to expand this carousel. The reason is the same. Consider this example as the first of many other examples.

The table

The table has five fields:

• idVehicle: a numeric automatic value
• PlateNumber: I used the plate number thinking on a used card business. Of course this may not be the best choice, but is easy to change it. It's a string field.
• Chassis: is a text field to enter the chassis number
• DateFirstReg: A date field to enter the first register of the car
• Brand: A text field to enter the brand
• Model: A text field to enter the model
• Version: A text field to enter the version

The important field is the PlateNumber, I use it to work.

The forms

There are two forms:

• Vehicles: this is a form to show the car. It's made automatically, using the wizard. In this post the important is the carousel, not this form. We will see in another post how to manage this kind of forms programatically, only with VBA.  As you may see, apart of the fields there are some buttons, four to show the different cars, one to close the form, and another one to see the pictures. It is a simple form. We will see how to do the same but using only VBA code in next posts.

• Carousel: this is a form to show the pictures. Initially, when this form is loaded shows a message "Loading photos... please, wait", and after, each five seconds it shows a photo. It's a simple form. It has, as you may see in the downloadable file (at the end of this post), it's made using a Picture control, and three text fields. It's controlled by VBA code. You may see that in the events section, on the Time interval there is a 5000, this means the form will run "someting" each 5000 milliseconds (5 seconds). The something will run is the OnTimer event.

The photos

The photos are stored in a subfolder of the main file. The name of the subfolder is photos, and in this folder there are folders named as the PlateNumber field for each car. In each of those subfolders there are the pictures of each of those cars named as the plate number followed by an order number between 01 and 99.

If you remember my previous post, I explained how to work with attached files in Access without having to store those files in the data file itself. Here he have an example. There are some pictures managed by the code in the form. Let's take a look at them.

The code in the forms

Vehicles

I will copy only the code for two buttons, cmdCloseForm and cmdSeePhotos. The code for the other buttons is generated by the Access Wizard to move along the records.

Private Sub cmdCloseForm_Click()

    DoCmd.Close acForm, Me.Name, acSaveYes 

End Sub

I think it does not need more explanation: closes the form given by Me.Name (Vehicles)

Private Sub cmdSeePhotos_Click() 

    DoCmd.OpenForm "Carrousel", acNormal, , , acFormEdit, acWindowNormal, PlateNumber 

End Sub

There's not a lot to explain here. Open the form Carousel giving the PlateNumber field as a parameter.

Carousel

The code here is very siple again. There are two subs:

Private Sub Form_Open(Cancel As Integer) 

    Me.Number = 1 

End Sub

This is, when opening the form, set 1 to the field Number (right bottom of the detail, is not visible)

Private Sub Form_Timer() 

    Dim pathToFile As String, OrderNumber As String 

    If IsNull(Me.OpenArgs) Then 

        DoCmd.Close acForm, Me.Name, acSaveYes

    Else 

        OrderNumber = IIf(Number < 10, "0", "") & Trim(Str(Me.Number))

                                  pathToFile = CurrentProject.Path & "\pictures\" & Me.OpenArgs & "\" & Me.OpenArgs & OrderNumber & ".jpg"
            Me.FileName = pathToFile
            If FileExists(pathToFile) Then
                Me.CurrentPicture.Picture = Me.FileName
                Me.Number = Me.Number + 1
            Else
                MsgBox "There are not more files to show.", vbInformation + vbOKOnly, "Carousel"
                DoCmd.Close acForm, Me.Name, acSaveYes
            End If
        End If
    End Sub

First, to prevent errors, the sub verifies that there's an opening parameter.
This parameter is needed later as you will see to build the name of the file to show.
If there's not a parameter, the form closes.

If there's a parameter, the plate number, the sub gives a value to the OrderNumber taking the value of the field Number (right bottom). If this number is less than ten, it adds a zero on left.

The pictures are named using the plate number and an order number from 01 to 99 and a .jpg extension.

So, like this we may compose the pathToFile variable, given by:

• CurrentProject.Path, this a System variable, it's the full name of the folder for the current database file
• "\pictures\", this is, the name of the folders where are the pictures
• Me.OpenArgs, this is, the plate number, plus "\" and plus another time the plate number, plus the order number and ".jpg".

Let's suppose the main file is stored in C:\Documents\Carousel, and the first of the pictures is 000HHH01.jpg. The value of pathToFile for the first picture should be:

pathToFile = "C:\Documents\Carousel\pictures\000HHH\000HHH01.jpg"

This information is written in the FileName field on the bottom, to show it, it's not necessary, but it's made to be more clear.
Then, to avoid run time errors, and once the complete file name is composed, a check: does the file exists?. We will see the code in the next section.
If the file exists:

 Me.CurrentPicture.Picture = Me.FileName

This sets to the property Picture of the control CurrentPicture the value given in FileName.
To show another image, we increase the number of the Number control. It will be found after 5 seconds.

When all the files have been shown, this is, when the program does not founds a file, it stops, shows a message box, and after, closes the Carousel form. We will see in other posts more possibilities.

The code in the module

And finally, the code on the module.

This function is made by Allen Browne on june 2006. Is a very useful function.

Public Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean

    'Purpose:   Return True if the file exists, even if it is hidden.

    'Arguments: strFile: File name to look for. Current directory searched if no path included.

    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.

    'Note:      Does not look inside subdirectories for the file.

    'Author:    Allen Browne. http://allenbrowne.com June, 2006.

    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.

    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then

        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.

    Else        'Strip any trailing slash, so Dir does not look inside the folder.

        Do While Right$(strFile, 1) = "\"

            strFile = Left$(strFile, Len(strFile) - 1)

        Loop

    End If

    'If Dir() returns something, the file exists.

    On Error Resume Next

    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)

End Function

Well, I hope you like this post and you found it useful.

Hope you will like, pin and comment the post.
You may download the file here. Enjoy it and feel free to modify it.