How to manage data in forms using only VBA. And why. How to use System Windows and stored files.

Introduction

Hi all!
Today's subject is about how to use a form to manage data using only VBA.
As you know, manage forms with data in Access is very easy, practical, and may be done from the first minute.
When you build a data source, a table or a query, you may create a form linked to them. This means that Access will do all the tasks related to manage the data, and when the form will be opened it will show those data automatically.
This is very useful, especially when the user is a beginner, or just when neither the time nor security are really important.
I will try to explain myself better.

When we use the wizard to build a form, it fills automatically the RowSource property for the form, and the same for each field.
As I said, this is useful, but may have some problems:

• When there are a big amount of records (more than 250.000) and/or a big amount of fields, the load process may be a little bit slow, and also if the form shows a large set of records or it has to select a single record.
• By default, when the user enters a record using the form, if he/she closes the form, Access writes the information of the unfinished record. Sometimes it causes bad records.
• There are users that does not know how to delete records by pressing on the left button and after pressing the delete key, they simply delete the contents of the fields.

To correct that, or simply to change the way of working, is possible to write some VBA code to act as Access. Note that this means to do a little bit more of job. The main advantage is that the control of data is for the programmer, neither for Access nor the user.

But what kind of forms they are suitable for working in this way?
I like, almost always, to use a form with one or two list boxes as a summary, and to manage the data, a single form. You may see it in this post. Then, the single form is used to a single record.

A form unlinked

Let's explain it with an example. I've improved the same example I've been using lately, adding a new form to explain myself better. You may see a screenshot here:

We will talk later about the right side of the detail.
As you may see, le left side has the same information as in the previous post, but we will see how this is managed.
If you remember, the last week we were analyzing the code for the main form, and we used, to open the detail form, the next line:

DoCmd.OpenForm "Vehicles", acNormal, , "idVehicle = " & Me.lstData, acFormEdit, acDialog

This is, opens the form Vehicles, in normal mode, that is, to view information, and passing the condition to filter the data ("idVehicle = " & Me.lstData), that means, where idVehicle = the content of the first column of lstData. It has be opened to edit, and as a dialog box.

This time I've changed the command. Take a look:

DoCmd.OpenForm "Vehicles",acNormal,  , , acFormEdit, acDialog, Me.lstData

There's not condition, but there's a parameter, lstData.

After that, if you open the Vehicles form in desing mode, you will notice, in the properties area, in the Data section, there's nothing to identify the data source. In the Format section, the left bar (record selector), the navigation buttons, record separator and the scroll bars are deactivated.

Let's take a look at the code.
Form Open: when the form is opened, it has to load the data. To prevent runtime errors, the form checks if there's an incoming parameter (OpenArgs):

Private Sub Form_Open(Cancel As Integer)
    Dim sqlAction As String, record As Recordset
    
    If Not IsNull(Me.OpenArgs) Then
        sqlAction = "SELECT PlateNumber, Chassis, DateFirstReg, Brand, Model, Version " & _
                    "FROM Vehicles " & _
                    "WHERE idVehicle = " & Me.OpenArgs
        Set record = CurrentDb.OpenRecordset(sqlAction)
        
        Me.PlateNumber = record.Fields("PlateNumber")
        Me.Chassis = record.Fields("Chassis")
        Me.DateFirstReg = record.Fields("DateFirstReg")
        Me.Brand = record.Fields("Brand")
        Me.Model = record.Fields("Model")
        Me.Version = record.Fields("Version")
        
        record.Close
        Me.lstFiles.RowSource = "SELECT idVehicle, FileName " & _
                                "FROM VehiclesFiles " & _
                                "WHERE idVehicle = " & Me.OpenArgs
    End If
End Sub

If you remember the post about the Options box, you may see the idea is very similar.

So, when the form is loaded after clicking the Modify button on the main form, the code executed is:

Private Sub cmdModify_Click()

    If Not IsNull(Me.lstData) Then

        DoCmd.OpenForm "Vehicles", acNormal, , , acFormEdit, acDialog, Me.lstData

        Me.lstData.Requery

    End If

End Sub

That is, if there's something selected, opens the Vehicles form, and when it's closed, requery the list on the main form, to reflect the changes. If you don't check if there's something selected, it will occur a runtime error while passing a null value to the Vehicles form.

Forget, by now, the line referred to Me.lstFiles.Rowsource, we will talk later about it.

Well, a little bit more about the Vehicles form. Below, there are three buttons: "Watch photos...", you know this is to open the Carousel, "Save and close", and "Close form".

Let's see the code for those buttons:

Watch photos:

DoCmd.OpenForm "Carousel", acNormal, , , acFormEdit, acDialog, Me.PlateNumber

As you may see, the way we launch the Carousel form is the same way we used to launch the Vehicles form, giving a parameter at the end of the line.

Save and close:
Private Sub cmdSaveClose_Click()
    SaveData
    CloseForm
End Sub

Close form:
Private Sub cmdCloseForm_Click()
    CloseForm
End Sub

You may see that in both Subs they only call other Subs. I like to work this way to avoid duplicate lines. So, let's see those Subs:

CloseForm:

Private Sub CloseForm()

    Me.lstFiles.RowSource = ""             'we will see it later

    DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

SaveData:

Private Sub SaveData()

    Dim sqlAction As String

    

    If IsNull(Me.OpenArgs) Then         'save new record

        sqlAction = "INSERT INTO Vehicles (PlateNumber, Chassis, DateFirstReg, Brand, Model, Version) " & _

                    "VALUES ('" & Me.PlateNumber & "', " & _

                            "'" & Me.Chassis & "', " & _

                            "#" & Me.DateFirstReg & "#, " & _

                            "'" & Me.Brand & "', " & _

                            "'" & Me.Model & "', " & _

                            "'" & Me.Version & "" & _

                    ")"

    Else                                'update existing record

        sqlAction = "UPDATE Vehicles " & _

                    "SET PlateNumber = '" & Me.PlateNumber & "', " & _

                        "Chassis = '" & Me.Chassis & "', " & _

                        "DateFirstReg = #" & Me.DateFirstReg & "#, " & _

                        "Brand = '" & Me.Brand & "', " & _

                        "Model = '" & Me.Model & "', " & _

                        "Version = '" & Me.Version & "' " & _

                    "WHERE idVehicle = " & Me.OpenArgs

    End If

    CurrentDb.Execute sqlAction

End Sub

That is, if the incoming parameter is null, it means we will enter a new vehicle. If it's not null, it's because we want to modify an existing record.

Ok, I know, let's talk now about the right side of the form before to close it.

Upgrading the Options dialog

I've been added some controls to the Options form. We have seen it in this post.

Now, I've added a Header, a Footer, a Tab control and in the second tab, a text field, called vehDocsFolder.

I want to link some documents to each car. To do this I've made a new table, VehiclesFiles, and I've created a 1 to n referential integrity relation between this table and Vehicles, using the idVehicle field.

There, in the VehicleFiles table, I plan to store the name of each file attached to the car. But I want to store only the name of the file, not all the complete path. Why?, because if one day we decide to move the folder where the files are, we only have to modify one parameter.

So, what does we will store in this new parameter?. If you take a look at the Options table, you may see there's a new parameter. It's called DocsFolder. This is the main folder to store the files.

In the first post about Options, we have seen the code to manage the values of the parameters. In the case of DocsFolder, we want to enter a path, and we will use a function to do this. The result looks very nice. Let's take a look at the OnClick event procedure:

Private Sub vehDocsFolder_Click()

    Dim Message As String, value As Long, aux As String

    

    If IsNull(Me.vehDocsFolder) Or Me.vehDocsFolder = "" Then

        aux = FolderPicker

    Else

        Message = "Do you want to select another folder?"

        value = MsgBox(Message, vbYesNo + vbQuestion, "Select folder")

        If value = vbYes Then

            aux = FolderPicker

        Else

            aux = Me.vehDocsFolder

        End If

    End If

    Me.vehDocsFolder = aux

End Sub

Basically, when we click on this control, if there's nothing stored, Access calls the FolderPicker function and puts the result on the aux variable.

If there's something, Access prevents before to delete it.

Let's see what's in the FolderPicker function. It's stored in the Tools Module:

Public Function FolderPicker() As String

    Dim value As String

    

    value = Picker("Folder")

    

    FolderPicker = value

End Function

There's not a lot of information, itsn't?. This is because I'm using the function Picker in two purposes. We will see how later. By now, let's take a look at the Picker function. It's not original, it's copied and modified. Unfortunately I have not the link, I've lost it. I've used this function so many times in my developments:

Private Function Picker(TypeDialog As String) As String

    Dim value As String

    

    'Declare a variable as a FileDialog object.

    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.

    If TypeDialog = "Folder" Then

        Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    Else

        Set fd = Application.FileDialog(msoFileDialogFilePicker)

    End If

    'Declare a variable to contain the path

    'of each selected item. Even though the path is a String,

    'the variable must be a Variant because For Each...Next

    'routines only work with Variants and Objects.

    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.

    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.

        'The user pressed the action button.

        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.

            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.

                'You can use any file I/O functions that you want to work with this path.

                'This example simply displays the path in a message box.

                'MsgBox "The path is: " & vrtSelectedItem

                value = vrtSelectedItem

            Next vrtSelectedItem

        'The user pressed Cancel.

        Else

        End If

    End With

    'Set the object variable to Nothing.

    Set fd = Nothing

    

    Picker = value

End Function

This function shows, as you may see in the screenshot, a system window to select a folder:

The result, when closing the window, is a string with the complete path to a folder, wich will be stored in the field of Documents folder in the Options dialog box.

Stored data(Part 2)

In this previous post, I've been talking about how to manage attached files.

In this example, the Used Cars Seller, we have seen, how to do a Carousel to show photos of the cars, using photos stored in the HDD, but Access has not stored anything about them.

This may be useful for that example and similars, but not always. 

We will see now how to use attached files, but storing some of its information.

Let's use the previous example. You may download the new update in the end of this post.

Each car may have one or more attached documents. In this case, we will want to list those documents, as you may see in the right side of the Vehicles form. In this example there's only a picture, mainform.png, but the idea is to store files as pdfs with, for example, the idintification of the seller, a pdf of the papers of the car, ... 

How is it made?. The list shows the contents of the VehiclesFiles table. This table has two fields, idVehicle and FileName. Those two fields are the Primary Key of this table.

When the Vehicles file is loaded, apart of fill the fields as seen avobe, fills the List Box with this code:

Me.lstFiles.RowSource = "SELECT idVehicle, FileName " & _
                        "FROM VehiclesFiles " & _
                        "WHERE idVehicle = " & Me.OpenArgs

That is, put in the list box, all the records stored in the table where the idVehicle field is given by the OpenArgs parameter (given by the main form when opening).

There are also three buttons on the right side, to Add, Delete or Show the files in the list.
The files will be stored on the folder given by the options box, explained above, but, to prevent duplicated filenames, each vehicle will have a subfolder with its plate number.
The chosen folder to the files in the options dialog is the files folder in the screenshot.
The folder for each car, in the files folder will be crated automatically when needed.
Let's take a look at the code:

The Add button
This button opens a system window to pick one file. Thinking on the real processes in an office, the original file may be stored in any folder. Why?, because often those files may be downloaded or generated with a pdf printer, and usually those programs uses their own folders to store their files.
When choosing Add, then, the program stores the path and filename for the original file in a variable named Candidate, and in a variable called FileName, the value to write in the table, this is, the name of the file, by running a function we will see later.
After that, it checks if there's any file for this vehicle with the same name, to prevent duplicates. If not, it stores the file name and the number of the vehicle in the table, checks if exists any folder in the Documents folder called as the plate number, and if not, it creates this folder.
In the next step, it copies the original file to the destination folder, and deletes the original.
Last, it makes a Requery for the list box to show the new file.
The code:

Private Sub cmdFileAdd_Click()
    Dim Candidate As String, FileName As String, sqlAction As String
    
    Candidate = FilePicker()
    FileName = NameOnly(Candidate)
    If DCount("*", "VehiclesFiles", "idVehicle = " & Me.OpenArgs & " AND FileName = '" & FileName & "'") = 0 Then
        sqlAction = "INSERT INTO VehiclesFiles (idVehicle, FileName) " & _
                    "VALUES (" & Me.OpenArgs & ", " & _
                            "'" & FileName & "'" & _
                    ")"
        CurrentDb.Execute sqlAction
        If Not FolderExists(GetParam("DocsFolder") & "\" & Me.PlateNumber) Then
            MkDir GetParam("DocsFolder") & "\" & Me.PlateNumber
        End If
        FileCopy Candidate, GetParam("DocsFolder") & "\" & Me.PlateNumber & "\" & FileName
        Kill Candidate
        Me.lstFiles.Requery
    Else
        MsgBox "This file is already stored in the database for this vehicle.", vbOKOnly + vbCritical, "Add file to vehicle"
    End If
End Sub

The Delete button
To prevent errors at runtime, first check if there's something selected in the listbox. If done, there's a warning to the user: This operation cannot be undone. The file wil be deleted too.
If you choose yes, it will delete the record and then the file, and requery the list.
The code:
Private Sub cmdFileDelete_Click()
    Dim Message As String, Answer As Long
    Dim sqlAction As String
    
    If Not IsNull(Me.lstFiles) Then
        Message = "This operation cannot be undone." & vbCr & _
                  "The file will be deleted too." & vbCr & _
                  "Are you sure you wish to continue?"
        Answer = MsgBox(Message, vbYesNo + vbQuestion, "Delete file")
        If Answer = vbYes Then
            Kill GetParam("DocsFolder") & "\" & Me.PlateNumber & "\" & Me.lstFiles.Column(1)
            sqlAction = "DELETE * FROM VehiclesFiles WHERE idVehicle = " & Me.OpenArgs & " AND FileName = '" & Me.lstFiles.Column(1) & "'"
            CurrentDb.Execute sqlAction
            Me.lstFiles.Requery
        End If
    Else
        MsgBox "No file selected. Please, select a file and retry.", vbOKOnly + vbInformation, "Delete file"
    End If
End Sub

The Show button:
This is the most simple button. It shows the file with the default application:
Private Sub cmdShow_Click()
    Dim FullPathFN As String
    
    If Not IsNull(Me.lstFiles) Then
        FullPathFN = GetParam("DocsFolder") & "\" & Me.PlateNumber & "\" & Me.lstFiles.Column(1)
        FollowHyperlink FullPathFN
    End If
End Sub
Of course, if the file is a PDF, it will be loaded with Adobe PDF viewer.

As you can see in all those three buttons, it uses always the DocsFolder parameter by the GetParam function stored in the Options table, the PlateNumber field, and Me.lstFiles.Column(1).

When using listboxes, the first field is always the 0 field.

Other functions used

Those functions are stored in the Tools module.

FileExists. 

This is not done by me, it's copied from Allen Browne's blog. This function returns True if a given file exists, or False if not.

FolderExists.

This is a copied function too.

GetParam

This function was shown in another post, when talking about the Options dialog box. It gives the value of a stored parameter.

SetParam

This function stores the given value of a given parameter. Also seen in the Options post.

NameOnly.

This function is used after the execution of FilePicker function, to extract only the file name.

Final note

Well, this is the post for today.

I hope you enjoyed it. As always, feel free to pint it, play with the example and ask some doubts.

You may download it following this link.

Hope you like it. Have a nice week.

Oh, and note the length of the file: by now it's less than 1 MB. With all those files attached.