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

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