Passa al contingut principal

Table and form for window Options in your application

Introduction

Today I will explain how to do an Options dialog box with parameters for your application in MS Access.
An options window is a simple dialog form.
It may be linked to a table, but I prefer to do it using VBA, to have a more professional look and a very good maintenance.
This post is organized in six steps: the table, the form, the records, the code in a module, finishing the form, and the code for the form.
At the end of this post you may download a small Access file with the form options, the table and the code to manage it.

First step: The table

For the table, I usually create a table with three fields. The name of the table is Options:

  • parName (text, 50, Primary key): the name of the parameter
  • parValue (text, 50): the value of the parameter
  • Details (text, 255): a brief explanation text field to have information about the field, like a comment.
Later in this example we will see that there are different types of values, but the field is always a text field. Our code will translate our data.

Second step: The form

Once I've created the table, we may create the form.
It's a simple form where we will put the some controls for the parameters.
The form must be created as a dialog box, that is, in the properties box you have to configure:
  • Record selector: No
  • Navigation buttons: No
  • Scroll bars: Neither
And in the Data section, at the Row source, nothing.
After that, we have to put two command buttons for "Save and close", and for "Close" without saving. I usually put them in the right bottom side of the footer. Let's name them cmdSaveClose and cmdClose. We will see later the code for those command buttons.

Third step: the records

Ok, the table and the form are made, the next step is to fill some data in the table.
Let's fill those simple records, each field separated by semicolon:

Name;Value;Details
AppName;MyApplication;The name of my application
AppVersion;1.0;The version of my application
AppBoolean;1;Example of a boolean expression (0: false, 1: true)
AppNumber;12.3;Example of a number expression

Fourth step: the code in a module

It may seem little bit strange to put the code in a module, but you will see it's better.
In this step I will use a function and a sub to manage the data.
As you will see, I always declare all may variables, this is due to I use to put the command

Option Explicit

At the top of all my modules. Of course you may not use that, but like this we may be sure of all variables are well declared.

So, we need this code:

This function reads the value of a parameter given by ParName in the Options table.

Public Function getParam(ParName As String) As String
    Dim sqlAction As String
    Dim record As Recordset
    Dim retValue As String
    
    sqlAction = "SELECT parValue FROM Options WHERE parName = '" & ParName & "'"
    Set record = CurrentDb.OpenRecordset(sqlAction)
    retValue = IIf(Not IsNull(record.Fields(0)), record.Fields(0), "")
    
    getParam = retValue
End Function

And this one writes the value of the parameter in the table:
Public Sub setParam(ParName As String, ParValue As String)
    Dim sqlAction As String
    
    sqlAction = "UPDATE Options " & _
                "SET parValue = " & ParValue & " " & _
                "WHERE parName = '" & ParName & "'"
    CurrentDb.Execute sqlAction
End Sub

Those two functions, in some cases, are the most used in your app when it runs.
There are two notes about them:
  • They are in a Module, because they are not only made for the Options form.
  • They are declared as Public because they will be used by all the application.

Fifth step: finishing the form

Well, once we have created the table, and we have entered some data, and we have entered the basic code to manage this data on the table, we will have to finish our form.
Earlier in this post I've been talking about four parameters. Now, we will have to "draw" three text fields in our form and a checkbox.
The name for those controls is your choice, but I usually like to name them like the parameters on the table. Or almost.
Following the example, we may put:
  • A text field named txtAppName, for the name of the application
  • A text field named txtAppVersion
  • A check box named chkAppBoolean
  • A text field named txtNumber

Sixth step: code for the form

Well, this is the final step.
In this section we will put the code for the buttons, and the code that loads and saves de values of the parameters for the application.

First, a procedure to close the form:
Private Sub CloseForm()
    DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

After, a procedure to save the data:
Private Sub SaveData()
    setParam "AppName", "'" & Trim(Me.txtAppName) & "'"
    setParam "AppVersion", "'" & Me.txtAppVersion & "'"
    setParam "AppBoolean", IIf(me.chkAppBoolean,"1","0")
    setParam "AppNumber", "'" & Trim(Str(Me.txtAppNumber)) & "'"
End Sub

The following procedures must be written by clicking on each button, and selecting Event procedure on the OnClick event in the Events tab of the Properties box.
Now, an event procedure to close the form when the command button is pressed:

Private Sub cmdClose_Click()
    CloseForm
End Sub

Next, and event procedure to save the data. To write it, just click on the button (in design mode), and in the Properties, click on Events, and after On Click, select Event Procedure.

Private Sub cmdSaveClose_Click()
    SaveData
    CloseForm
End Sub

This procedure must be written by selecting the OnOpen event at the properties of the form.
An event procedure to load the form:

Private Sub Form_Open()
    Me.txtAppName = Trim(getParam("AppName"))
    Me.txtAppVersion = Trim(getParam("AppVersion"))
    Me.txtAppBoolean = (getParam("AppBoolean")=1)
    Me.txtAppNumber = Val(getParam("AppNumber"))
End Sub

 Is it simple?
I hope you like it.

Ending note

I'm updating this post on feb. 15, after reading that a user has some problems with the example.
In fact, it's true, perhaps I posted it too fast and I forgot to correct some mistakes.
Those errors are now corrected on the code posted avobe.
I have to aknowdlege to +Brian Welch , I've learned a good lesson.

Download

Click this link to download an example. Feel free to modify it.
Etiquetes de comentaris:

Comentaris

Publica un comentari a l'entrada

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
Publica un comentari a l'entrada
More info »

How to manage attached files with access. Options.

Hi all This week's post is about how to manage attached files with MS-Access and VBA. Introduction Sometimes is useful and/or necessary to store attached files in our applications. Let's see some examples: A used car seller may have a database with all the vehicles on sale, and may need to have photos of the cars, to show them to the potential customers before seeing the cars. And also may want to store digitalized the papers of the car. A real state business, has a file with each property, and may want to show the photos to the potential customers before visit, to restrict the number of properties to visit. And not only the photos, but also legal papers about the property. A doctor, may want to save information about their patients, but sometimes this information is not suitable to be stored in a record, for example a bone scan or the results of some tests, but is useful to attach to the patient file to find it more easily. A farmer may need to store data about the
Publica un comentari a l'entrada
More info »

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
Publica un comentari a l'entrada
More info »