Passa al contingut principal

Use of listboxes as a data summary instead data forms

Hi all
This post is an extension of the previous post (how to develop a carousel).
Here I will show how to manage a summary form linked to a detail one, in order to show data and detail them.
I want to clarify that this is not "the way" to work with access, this is "a way". This is the way I use. After been developing Access applications, I've found this is a good solution, and I use it almost always.

I will improve the previous example, the used cars seller adding a new form.
The new form has a listbox and some buttons (add, delete, duplicate and modify) to perform those operations. All those operations are solved using VBA, as you may see.

The new form is named Main. You may see in the next screenshot I've added new records.
Once the form is loaded, the data are stored in the listbox using the Form_load event.


The objects are named as follows:

  • Button Add is named cmdAdd
  • Button Duplicate is named cmdDuplicate
  • Button Delete is named cmdDelete
  • Button Modify is named cmdModify
  • Button Close is named cmdClose
  • List box is named lstData

Let's take a minute to analyze the souce code on the form (remember, if you want to do this example step by step, you have to indicate Access you want an event procedure for each object, before to write the code. If you just write the code by copy-paste it will not work, because Access does not knows it):
Event Load Form
In this event, before to load the form, the code prepare the form.

Private Sub Form_Open(Cancel As Integer)
    Me.lstData.RowSource = "SELECT idVehicle, PlateNumber, Chassis, DateFirstReg, Brand, Model, Version FROM Vehicles ORDER BY idVehicle"
End Sub

With this sentence we may fill the listbox each time we open the form.
I've adjusted those properties for this listbox in the Format section:

  • Number of columns: 7. There are 7 fields in the selection query.
  • Columns height: 0 cm (I think in US will be 0 in). Like this, the first field, idVehicle, is hidden. Clearly is a serial number automatically generated, so we don't need to see it because we identify the vehicles by their plate.
  • Column Headings: Yes, like this we may see the field names. It is not necessary, but is useful. 

Button add: cmdAdd (event cmdAdd click)
This button opens the detail form to enter a new car in the database.

Private Sub cmdAdd_Click()
    DoCmd.OpenForm "Vehicles", acNormal,,, acFormAdd, acDialog
    Me.lstData.Requery
End Sub

Easy: opens the form Vehicles, only to add records, and as a Dialog, that is, it puts the window over the other windows, and you can't access the other windows until you close Vehicles.
After closing this window, it refreshes lstData to see the new vehicle added, by re-reading the data table.

Button delete: cmdDelete (event cmdDelete click)
This button deletes the car from the database. There's no form to show, only a message box to ensure the user whishes to delete the record. It only works if there's something selected in lstData.

Private Sub cmdDelete_Click()
    Dim sqlAction As String, answer As Long, Message As String
    
    If Not IsNull(Me.lstData) Then
        Message = "This operation cannot be undone." & vbCr & "Are you sure you wish to continue?"
        answer = MsgBox(Message, vbCritical + vbYesNo, "Delete vehicle")
        If answer = vbYes Then
            sqlAction = "DELETE * FROM Vehicles WHERE idVehicle = " & Me.OpenArgs
            CurrentDb.Execute sqlAction
        End If
        Me.lstData.Requery
    End If
End Sub


Button duplicate: cmdDup (event cmdDup click)
This button duplicates a car, without showing any form. Simply duplicate and refresh the listbox on Summary.

Private Sub cmdDuplicate_Click()
    Dim sqlAction As String, recData As Recordset
    
    If Not IsNull(Me.lstData) Then
        sqlAction = "SELECT PlateNumber, Chassis, DateFirstReg, Brand, Model, Version " & _
                    "FROM Vehicles " & _
                    "WHERE idVehicle = " & Me.-OpenArgs
        Set recData = CurrentDb.OpenRecordset(sqlAction)
        
        sqlAction = "INSERT INTO Vehicles (PlateNumber, Chassis, DateFirstReg, Brand, Model, Version) " & _
                    "VALUES ('" & recData.Fields("PlateNumber") & "', " & _
                            "'" & recData.Fields("Chassis") & "', " & _
                            "#" & Format(recData.Fields("DateFirstReg"), "mm/dd/yyyy") & "#, " & _
                            "'" & recData.Fields("Brand") & "', " & _
                            "'" & recData.Fields("Model") & "', " & _
                            "'" & recData.Fields("Version") & "'" & _
                    ")"
        CurrentDb.Execute sqlAction
        
        recData.Close
        Me.lstData.Requery
    End If
End Sub


Button modify: cmdMod (event cmdMod click)
This button opens the detail form to edit the car.

Private Sub cmdModify_Click()
    DoCmd.OpenForm "Vehicles", acNormal, , "idVehicle = " & Me.lstData, acFormEdit, acDialog
    Me.lstData.Requery
End Sub


Button close: cmdClose (event cmdClose click)
This button closes the form when is pressed.

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

Well, the rest of the example works the same than the previous week.
It's short and very simple, but will help in the next week. I will improve the example using system commands to import the pictures.
Enjoy it. You may download here.
Have a nice week.


Comentaris

Entrades populars d'aquest blog

How to export data from access to a text file

Introduction Hi all Today's post is about a set of functions to export data from Access to text files. I wrote the next functions years ago, when working on a big project and I had to export some of my data to another system, by text files, regularly. I needed something easy for the users, and transparent. I will explain what I did, and how. At the end of this post you will find a link to download the example of code. Feel free to modify it. Sometimes it happens that we need to export data. Of course, almost always we may write to another database. As I said before, I wrote this code to cover a need. This is a brief: I've been working for a company who mades canned fish. They had their ERP software, made by a external firm, and mantained across more than 20 years. When I entered, I had to write a software to control the production process and traceability. Of course, my processes (my software) was a subset of all the other software, and they had to communicate th...

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 sel...

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...