March 2008 ~ LEARN MS-ACCESS TIPS AND TRICKS

Double-Action Command Button

a.p.r. pillaiMarch 28, 2008Command Buttons, msaccess forms 8 comments

Introduction.

Double-Action doesn't mean that with one click you can execute two different procedures. For that matter, you can do several things with one click. Here, the emphasis is on programming the same Command Button for doing two different Actions i.e., when the Button is clicked a second time it will execute a different action than it did for the first one.

To make the point more clear, let us look into a real-world example. When the user opens a Data Editing Screen we want the screen to be kept locked for the safety of the data and to prevent users from changing the field values accidentally.

But, when it is time to make some changes in one or more fields in the current record the user will click the Command Button to unlock the Form for editing. After editing, the User will click the same button again to lock the screen.

Let us try an example. But, before starting with the design task, let us note down the above points with more specifics so that you will have a general idea of what we are trying to do when we proceed with the implementation steps.

Consider the Requirements

(a) When the Form is open for the first time, or when moved from one record to the other, the Form must be locked for editing. Why, when moving from one record to the other? Well, the User clicked the Button to unlock the Form for editing, but he forgot or didn't bother to click a second time to lock the Form. So we will apply an automatic Lock when she moves into another record.

(b) When the user adds a New Record the Form is fully open for keying in values and our Double-Action Command Button will be in a disabled state.

(c) When the user Clicks the Double-Action Command Button (as we call it now) for the first time, unlock the Form for editing.

(d) When she Clicks the same button a second time, lock the form again, to prevent the change of data by mistake.

The only question at this point is that how do we determine which click what action and if the user repeats the clicks several times, then what happens? It is very simple, read on.

Design Task

Open one of your existing Projects with a Data Editing Screen already available in it.
Create a Command Button at the Footer of the Form.
Click on the Command Button to select it, if it is not already in a selected state, display the Property Sheet (View - - > Properties).
Change the Name Property to cmdEdit.
Change the Caption Property (very important) Value to Edit.
To take care of the points that we have noted at (a), and (b) above, display the VBA Module of the Form (View - - > Code) copy and paste the following Code into the Module.
```
Private Sub Form_Current()
If Me.NewRecord Then
    With Me
        .cmdEdit.Caption = "Edit"
        .cmdEdit.ForeColor = 0
        .cmdEdit.FontBold = False
        .AllowEdits = True
        .cmdEdit.Enabled = False
    End With
Else
    With Me
        .AllowEdits = False
        .cmdEdit.Caption = "Edit"
        .cmdEdit.ForeColor = 0
        .cmdEdit.FontBold = False
        .cmdEdit.Enabled = True
    End With
End If
End Sub 
```
The above code will take care of the New Record entry event as well as apply the automatic Lock when the User didn't Click the Command Button to Lock the Form directly.
Now we can concentrate on the Button Click part i.e. (c), and (d) points given above. Here, we have to check for the state of the Button when Clicked by the User. The trick is checking the Caption Value of the Button, and decide what is the user's intentions, and setting the Form Locked or Unlocked state. At every click on the Button, we will change the Caption Value to Edit, or Lock interchangeably.
- If the Caption Value was Edit when the user clicked the button, then he/she wants to edit values on the Form.
- If the Caption was Lock, then the user wants to lock the Form after editing the data.
The following code will do the trick. Copy and paste the VBA Code into the Form's Module and save the Form.

The VBA Code

Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
     Case "Edit"
         With Me
          .AllowEdits = True
          .cmdEdit.Caption = "Lock"
          .cmdEdit.ForeColor = 128
          .cmdEdit.FontBold = True
          .Refresh
         End With
    Case "Lock"
        With Me
          .AllowEdits = False
          .cmdEdit.Caption = "Edit"
          .cmdEdit.ForeColor = 0
          .cmdEdit.FontBold = False
          .Refresh
        End With
End Select
End Sub

Experiment with your design and check whether it works every time correctly as expected.

If any suggestions for improvement or found that it didn't work the way you expected it, then please post them in the comments.

Earlier Post Link References:

SENDING ALERTS TO WORKSTATIONS

a.p.r. pillaiMarch 22, 2008Emails and Alerts, msaccessQuery No comments

Introduction.

Let us look into a scenario to understand the importance of this method.

MS-Access Front-End Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update the information in this Table and complete their side of the work only after the main records are introduced by Application A Users. All records are not very urgent in nature, but some of them are and need immediate action from B & C Users.

There must be a way to inform Application A Users to give priority to certain Data for updating in the System. When they do, B & C Users must get Alerts on the updated status of those records, so that they can open their Applications and act on their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.

Functional Diagram

A Functional Diagram of this arrangement is given below:

Click to Enlarge

The Solution:

Create a new table with the name Alert_Param with similar fields given below in the BE Database:
- Create Key fields like Supplier Code, Invoice Number, and Invoice Date to link with the Main Table contents. These Fields together must be defined as the Primary Key.
- One Text Field, with the name: WrkStation, to record the Workstation name on the Windows Network. This will be recorded automatically when B & C Users enter the Key Field Values from their respective Workstations and will be used by the Machine connected to Application A to send Alerts to others.
- One Logical Field (Yes/No) with the name Updated to mark as an alert sent to Workstations and to avoid repeating the Alerts for the same item. If Application B & C Users missed the live alerts (PC was not On when the alert was targeted) this flag can be viewed as an indication that the relevant records are updated in the main table.
  
  Note: Managing the parameter Table contents (adding, editing, deleting records) will be done by B & C Users only. Application A Users will only refer to this list and give priority to these cases to clear from their side.
- Design a Form with the name Alert for the parameter table.
- Display the Property Sheet of Wrkstation Field and change the Enabled property to No.
- Repeat this for the Logical Field with the name Updated. Both these fields are managed and used by the program only.
- Link the parameter table Alert_Param in BE to all the three Applications (A, B & C).
- Install the Alert Form in all three Applications.
- In Application A, open the Alert Form in the design view. Display the Form's Property Sheet and change the following Properties to prevent modifying the parameter data manually:
- Allow Edits = No
- Allow Deletions = No
- Allow Additions = No
- Allow Design Changes = Design View Only
Display the Code Module of the Form (View - - >Code), copy and paste the following code in the Module, and save the Form:
```
Private Sub Form_BeforeUpdate(Cancel as Integer)
   Me![WrkStation] = Environ("COMPUTERNAME")
End Sub
```

Create a Query to JOIN Alert_Param Table with the Main_Table on the Key Fields (Supplier Code, Invoice Number & Invoice Date) in BE Database to pick the records that match for sending Alerts.

SELECT DISTINCTROW Alert_Param.*
FROM Main_Table 
INNER JOIN Alert_Param ON (Main_Table.Supl_Code = Alert_Param.SuplCode) AND (Main_Table.INVOICE = Alert_Param.INV_NO) AND (Main_Table.INVOICE_DATE = Alert_Param.INV_DATE) 
WHERE (((Alert_param.Updated)=False));

Save the above Query with the name Alert_inQ.
Create a second Query as shown below using Alert_inQ as Source and save it with the name Alert_in_ParamQ.
```
SELECT Alert_inQ.WrkStation
FROM Alert_inQ
GROUP BY Alert_inQ.WrkStation;
```
Copy and paste the code below into a Global Module of your Project and save it.

Public Function WKAlert()
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date : 01/03/2008
'All Rights Reservered by msaccesstips.com
'--------------------------------------------------------------
Dim wrkStn() As String, xlnvoice As String, msg() As String
Dim cdb As Database, rst1 As Recordset, rst2 As Recordset
Dim reccount, j As Integer, T As Double, flag As Boolean

On Error GoTo WKAlert_Err

reccount = DCount("* ", "Alert_in_ParamQ")

If reccount > 0 Then
 'check the number of workstations involved
     ReDim wrkStn(l To reccount) As String, msg(l To reccount) As String
    Set cdb = CurrentDb
    Set rst1 = cdb.OpenRecordset("Alert_in_paramQ", dbOpenDynaset)
    For j = 1 To reccount
        wrkStn(j) = rst1![WrkStation]
    Next
    rst1.Close
Else
    Exit Function
End If

Set rst1 = cdb.OpenRecordset("Alert_inQ", dbOpenDynaset)
For j = 1 To reccount
    rst1.MoveFirst
    flag = False
    Do While Not rst1.EOF
    If flag = False Then
        msg(j) = " UPDATED "
        flag = True
    End If
     'add the Supplier Invoice details.
    If rst1![WrkStation] = wrkStn(j) Then
        msg(j) = msg(j) & "Supl.Code: " & rst1![SuplCode] & ", " & "Invoice: " & rst1![Inv_No] & ", " & "Inv.Date: " & rst1![Inv_Date] & ", : "
        rst1.Edit
        rst1![Updated] = True
        rst1.Update

    End If
        rst1.MoveNext
    Loop
    'Use the NET SEND command and format the message
     msg(j) = Left(msg(j), Len(msg(j)) - 2)
    msg(j) = "NET SEND " & wrkStn(j) & msg(j)
    msg(j) = msg(j) & " on " & Now()
Next

For j = 1 To reccount
    Call Shell(msg(j))
' send message through Network
     T = Timer
    Do While Timer < T + 0.5
       DoEvents 'Delay Loop for the next message
   Loop
Next

WKAlert_Exit:
Exit Function

WKAlert_Err:
MsgBox Err.Description, , "WKAlert"
Resume WKAlert_Exit
End Function

Note: DOS Command NET SEND works under Windows XP only, later Versions of Windows use the MSG Command. Type MSG /? On the Command prompt for help and optional parameter listing.

Consider Options for Sending Alerts to Workstations.

Automated Alert.

a) This is the recommended method when several records are added to the Main Table in batch mode (i.e. At the end of some intermediate processing within Application A) pertains to the parameters created by B & C Users. Call the Function WkAlert() at the end of the Process Steps.

In such situations, all updated record references belonging to a particular Workstation can be combined into a single message through the above Queries and sent without repeating each record. This is the most efficient way, which will send the Alerts automatically and will reduce the number of messages. The VBA code presented here uses this method and runs after adding the records to the Main Table.

b) Run the Program from the After Update Event of a particular record updated in Application A which will reference the parameter table created by B & C Application Users and if it matches with the record updated in the Main Table then sends an Alert to the Workstation for that record. Sets the Updated Flag on the Parameter Table for that record.

Sending Alerts Manually

If the above methods are not convenient then Alerts can be sent manually to the respective Workstations by clicking a Command Button after updating all or some of the records that have been requested through the parameter table.

The Button Click can invoke the Program and send a single message to each Workstation for a group of records belonging to them based on the parameter table.

This method depends on manual intervention from Users and it cannot be classified as an efficient one. But, this will be better than sending Alerts at the record level, as explained under item b above.

Download.

Download AlertDemo.zip

Refresh Dependant Combo Box Contents

a.p.r. pillaiMarch 14, 2008Combo Boxes, msaccessQuery 5 comments

Introduction.

Creating a Combo box on a Data Entry/Editing Screen is easy. Click on the Combo box Tool Button on the Toolbox, select the required Fields from the Source Table or Query on the Wizard, select the target field on the Form and you are through. Well, maybe not quite, few more changes may require on the Property Sheet of the combo box to adjust the width of individual Columns of List, Total List Width property, List Rows Property, Limit to List, and On Not in List Event Procedure to prevent entering values other than not defined on the combo box.

There are instances of more than one combo box on a form and it is likely that the contents of one combo box depend on the values of another one. In such situations, limiting the contents of the second combo box with the data related to the current item selected in the first one is more appropriate, rather than displaying all of them always. The User can then select the item that he wants from a limited list instead of scrolling through a long list of items to find the required one.

A few lines of Code in the On Click Event Procedure of the first combo box will do the trick and can make life easier for the user. He/She can do more work with the extra time otherwise spent on scrolling the combo box.

Preparing for the Test Run.

We need three Tables from the Northwind.mdb sample database for our test run. We are hitting this database more often to take ready-made data for our examples. If you are not sure where you can find this sample database on your PC, visit the Page Saving Data on Form not in Table for its location references.

Microsoft Access new users can find plenty of sample Tutorial materials in this database to keep them engaged for some time and to learn the basics of database design. When you grow out of it or you want something different and more interesting, then start hitting this site for help. I will be happy if you find time for a change of scene and visit here for clues. I have plans to keep you occupied for a long time.

Import the following Tables from the above sample database into your current project:
- Categories
- Products
- Order Details
Click on the Order Details Table, select Form from Insert Menu, select Auto Form: Columnar from the Wizard, and click OK.
Save the Form with the name Order Details.
Before going for changes on the Form let us have a look at the contents appearing in the Form just now created. Open the Form in a normal view. There is already a Combo box on the Form for ProductID Field.
Click on the Combo box and scroll down the list and read through some of the items appearing in the list, if you can understand them. To tell you the truth, many of the names appearing there are alien to me, especially the ones with one or more dots above the letters, besides others. But, one thing I am sure about is that they belong to different Categories (we have imported the table) of items and they all got mixed up in that list.
Change The Form Design.
We will create another Combo box to organize and show them in a better way. We will now proceed with the design changes to the Form.
Select Design View from View Menu, if you are still keeping the Form open in normal view. If you are not comfortable without a heading for your Form, expand the Header Section of the Form create a Label there and change the caption to Order Details and change the font size to make the heading look like a Heading.
Select all the controls and labels except the OrderID control and Label, and drag them down to make room to create a combo box below the OrderID field.
You can select all the controls by clicking somewhere down on an empty area on the form, holding the mouse button down, and dragging over all the controls to select them except the OrderID field and label. When you are sure that you have covered all the controls and all of them are selected release the mouse button. Hover the mouse pointer over the selected controls to turn the mouse pointer to the image of a hand. Now click and hold the mouse pointer and drag them down to make room for another field and label above. We are going to create another Combo box with the source data from the Categories table.
If the Toolbox is not visible, select Toolbox from View Menu. Make sure the Control Wizard button (top right button) on the Toolbox is selected.
Click on the Combo box button on the Toolbox and draw a Combo box above the ProductID combo box, about the same size as the ProductId combo box.
Select the Categories Table from the displayed list and click Next.
Click the >> button to select both fields displayed from the Categories Table and click Next, and Next again.
On the displayed control ensure that the option Remember the Values for later use (in our Code) is selected and click Finish.
We have not created a new field on the Order Details table to store the value from this Combobox when selected. That is not necessary here because we will take the Product Category Code inserted by the user into the Unbound Textbox (combo box) and use it to filter the contents of the Products combo box. The item selected from the Products combo box is only stored in the Order Details Table.
Click on the combo box, display the Property Sheet (View - -> Properties) and change the Name property to cboCat.
Change the size of the Label control to match the size of others.
Display the Form's Code Module. (View - - > Code)

The Form Class Module VBA Code

Copy and paste the following code into the VBA Module:

Private Sub cboCat_Click()
Dim xsql0 As String, xsql2 As String, xsql As String
Dim crit As String

xsql0 = "SELECT DISTINCTROW [ProductID], " & "[ProductName] FROM Products WHERE ("
xsql2 = " ORDER BY [ProductName];"
crit = "[CategoryID] = " & cboCat & ") "
xsql = xsql0 & crit & xsql2

Me.ProductID.RowSource = xsql
Me.ProductID.Requery
End Sub

Save and close the VBA Window (File - -> Close and Return to Microsoft Access). Save the Order Details Form with the changes.
Open the Form in the normal view. Click and select an item from the new Combo box, say Meat/Poultry.
Now click the Products Combo box. The List of items appearing there belongs to the Meat/Poultry Category only. Experiment with other Categories of items also.

How it works.

Even though the Combo Box that we have created displays only one Column of Items (Description) we have selected two Fields CategoryID and CategoryName from the Categories Table through the Combo Box Wizard. If you display the property sheets of both Combo Boxes and look at the Column Widths property you can see that the value set for the first column width is zero in both cases, which will hide the CategoryID Code Number that we are selecting by clicking the Description. The Description is more important to the User and easy to understand which item he/she is selecting.

But, when a selection is made in the combo box, the CategoryID number corresponding to that item is recorded in the Combo Box control. We are using this number to filter the items that belong to this category from the Products Table by building a SQL string and using the CategoryID Value as the criterion. This SQL string is used as a Row Source for the Products Combo Box.

If you open the Products Table and view the Data, you can see that Category Description is part of this Table also. When you click on one of the Category fields you can see that it is a Combo Box. Here also the same kind of Combo box is created (no filtering of data) while designing the Table Structure to display the Products Category from the Category Table.

Open the Products Table in Design View. Click on the CategoryID field and select the Lookup Tab from the Property Sheet displayed below. On the Property Sheet, you can see similar settings that we found on our Combo box on the Form. The CategoryID Source Data for the Combo box on the Products table is taken from the Category Table and the Column width Property is set to zero to hide the numeric code and to display the Category Name instead.

NB: Any suggestions for improvement or showing a better method, to arrive at the same results are welcome.

Filtering Data for different Users

a.p.r. pillaiMarch 07, 2008msaccessQuery, Queries 5 comments

Introduction.

In a Network-based MS-Access Application several Users may involve updating information into a common Master Table from different Locations or for different categories. When a particular User Logs in to the Application he expects to see or work with the records that belong to his area only rather than struggling with the full size of the Table.

It is always a good idea to save the User IDs of the Users and Data Entry/Editing Date and Time (Time Stamp) into the records when they are added or updated. This can help in so many ways when needed later.

For example, a User may come up and say he made some changes to a wrong record but he doesn't know which one. He needs to find and correct the record, but he doesn't know how to find it. With approximate date and time from him and with the aid of the User ID and Time Stamp, we can prepare a listing of records or filter records and this may help him to find the record for corrections.

Please see my earlier Article on Who changed the Data, where we are dealing with a more serious issue and explains how to update User IDs and Time Stamp into Records.

These kinds of situations may not occur every day, but what we are now going to look at is an everyday requirement and we need the User IDs saved into the records for this exercise.

Limiting User Access to Records

For limiting access to the records that belong to a particular User we need to create a Select Query on the main table using the User ID as criteria. This Query must be re-defined each time when different User(s) opens his Instance of the same Application on their respective Workstations. For all practical purposes, this main Query will be used as a Source for the Data Entry/Editing Forms, Report preparations, and so on. If you have a main Table with a large amount of data this method will reduce the volume of records that a particular user works with.

But there may be other Users who belong to a more privileged Group like Admins Group or Managers Group or Supervisors Group that need full data for viewing or for Report preparations. In all these situations we have to redefine the Query as the situation demands.

Here, we need to look at three things:

The Query must be redefined immediately after the User opens his Instance of the Application before he is able to do anything else with it.
Find the User ID and Workgroup, he belongs to.
Redefine the main Query based on the User ID and the Workgroup Status

Sample Data for Trial Runs

Since we don't have a ready-made Table with User IDs recorded on them, we will use the Orders Table from the NorthWind.mdb sample Database. If you don't know where to find this Database, please visit the Page Saving Data on Forms not in Table for its location references and Import the table Orders into your Project.

The Orders table has a country name, and field (ShipCountry) at the end of the field list and we will take the value from this field as User IDs for our example. We will deal with the User IDs and User Groups in the VBA code as they should be and to see the data filtering action we will use the Country Names instead of User IDs. If you already have a Table with User IDs in a Secured Database then replace the Table and Field Names in the SQL string in the VBA Code.

Changing Filter Query Definition through VBA

The Code below is run from the Startup Screen's Form_Unload Event Procedure. The Query Definition of OrdersQ is changed depending on the Current User and his Group status and then closes the Start-up Screen and opens the Control Screen (Main Screen).

Private Sub Form_Unload(Cancel As Integer)
Dim xsql As String, xsql0 As String, xfilter As String
Dim usrName As String, grpName As String
Dim i As Integer, j As Integer, usrFlag As Boolean
Dim wsp As Workspace, cdb As Database, QryDef As QueryDef

On Error GoTo Form_Unload_Err

xsql0 = "SELECT Orders.* FROM Orders "

xfilter = "WHERE (((Orders.ShipCountry)= '"

'enable this line with changes to Table and Field Names
'xfilter = "WHERE (((Table.UserID) = '"

usrName = CurrentUser

Set wsp = DBEngine.Workspaces(0)
i = wsp.Users(usrName).Groups.Count

If i = 1 Then ' he has only one Group that is USERS
    GoTo NextStep
End If

'check the User's Group status.
usrFlag = False
For j = 0 To i - 1
    grpName = wsp.Users(usrName).Groups(j).Name
    If usrFlag = False And (grpName = "MANAGERS" Or grpName = "SUPERVISORS" Or grpName = "Admins") Then
       usrFlag = True
       Exit For
    End If
Next

NextStep:

If usrFlag Then ' MANAGERS, SUPERVISORS or member of the Admins Group
    xsql = xsql0 & ";" 
        ' give full data access
Else
   ' xfilter = xfilter & usrName & "'));"
 'enable this line if Workgroups exists
    xfilter = xfilter & "USA" & "'));" 'try with different Country Names

    xsql = xsql0 & xfilter
End If

'change the Query definition
Set cdb = CurrentDb
Set QryDef = cdb.QueryDefs("OrdersQ")
   QryDef.SQL = xsql 
   cdb.QueryDefs.Refresh
   Set cdb = Nothing
   Set QryDef = Nothing

'Open the Main Control Screen
     DoCmd.OpenForm "Control", acNormal

Form_Unload_Exit:  
Exit Sub

Form_Unload_Err:  
MsgBox Err.Description, , "Form_Unload()"  
Resume Form_Unload_Exit
End Sub

Users and Workgroups

We assume that a particular User can belong to one of four groups:

Admins
MANAGERS
SUPERVISORS
Users

Users Group is the default Workgroup for all Users and a particular User can also belong to other groups like serial numbers 1 to 3 above qualifying him for special privileges.

If you try this Code in a Workstation where MS-Access Security is not implemented the default User ID will be Admin, a member of the Admins Group and you are automatically logged in by MS-Access without asking for your User ID or Password. You may not be able to open a Secured database with this UserID because all privileges to the Admin User will be removed by the Workgroup Administrator, otherwise, you cannot call it a fully Secured Database and you can open such a Database after re-installing MS-Access.

Go through the MS-Access & Security Pages to learn more about Microsoft Access Workgroups and methods for securing MS-Access Applications and related issues.

We are checking the Current User's Group Status, in the Code. If he belongs to only the Users Group then re-define the Query with his UserId to filter and provide the records belonging to him.

If the User belongs to the MANAGERS or SUPERVISORS or ADMINS GROUP then he will get full data access, all others will be treated as part of the common Users Group or similar Groups created by the Workgroup Administrator. In either case, he will be provided with the records that belong to him.

Running of the above VBA Code

You can implement this Code on your Startup Screen's Form Unload Event Procedure or save it in a Global Module with the change on the Subroutine name line to something else like Public Function RedefineQuery()

Don't forget to remove the Cancel as Integer parameter declaration.

If you have saved the Code in a Global Module with the above Change you can run the code from within an Autoexec Macro. Call the Function from the Macro using the RunCode Action and name the Macro as Autoexec. The Macro runs automatically when the user opens the database and the Query definition changes instantly.

Check Keyboard Shortcuts Page for AutoKeys Macro and its usage.