January 2008 ~ LEARN MS-ACCESS TIPS AND TRICKS

MsgBox with Options Menu

a.p.r. pillaiJanuary 25, 2008Animations, msaccess animation No comments

Introduction

This is an extension of my earlier Article on MsgBox with Office Assistant. Here, we will try to make use of Office Assistant to display a set of Menu Options and ask the User to select one of them. When the user clicks on one of the Options we will test for the selected value and execute the action programmed for it.

A demo image of the Baloon Type Menu Options displayed in Office Assistant is given below:

Click to Enlarge

Preparing for a Demo

Design a simple form with a TextBox and a Command Button as shown above to run the Demo Program.
Click on the Text Box and Display the Property Sheet (View - - > Properties) and change the Name property Value to OptTxt. This is used only for displaying the selected Option Text for Demo purposes.
Click on the Command Button and display its property sheet. Change the Name Property Value to cmdOpt and the Caption property Value to Get Option.
While the sample form is still in Design View, display the Form's VBA Module (View -> Code). Copy and paste the following Code into the Form's Module and save the Form with the name OptionCheck or with any name you prefer.

Private Sub cmdOpt_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"
OptionArray(4) = "CANCEL"

msg = "Please Select an Option"title = "
Report Options"

i = MsgGetOpt(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case 1
      'DoCmd.OpenForm "DataForm", acNormal
       msg = "Display Report Source Data"
    Case 2
       'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
       'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
    Case 4
        msg = "CANCEL"
End Select

    MsgOK "selected: " & msg

End Sub

The VBA Code

Let us look into what we are doing in the above Routine. First, we have defined a String Array Variable OptionArray with a maximum of 5 elements and loaded them with values in 4 elements out of 5 defined. A maximum of 5 elements or less are allowed in the main program MsgGetOpt() (the Code is given below). Even if you define more than 5 elements in the OptionArray it will be cut to the maximum of 5 elements by the statement k=iif(k>5,5,k) in the main program.

The msg & Title variables are initialized with Option Text and call the Program MsgGetOpt() with its parameters: msg, Title, and OptionArray variable, passing the OptionArray values by reference.

If you have already tried out the MsgBox with Office Assistant and Command Button Animation Topics earlier continue with step 3. But, Copy and Paste these VBA Codes on the same Project where you placed the Main Programs earlier. Or copy the Main Programs again from the following pages.

Copy and Paste the main programs from the Page MsgBox with Office Assistant into a Global Module in your Project and save it.
You must Link the 'Microsoft Office Library' File to your project as well. The procedure for doing that is explained on the Page Command Button Animation. A-List of other essential Library Files is also given there.
Copy and paste the following MsgGetOpt() Code into a VBA Global Module and save it.

Public Function MsgGetOpt(ByVal strText As String, ByVal strTitle As String, ByRef MaxArray5obj) As Integer
Dim intVal As Integer, X As Integer, Bal As Balloon, k As Integer
On Error GoTo MsgGetOpt_Err

k = UBound(MaxArray5obj)
k = IIf(k > 5, 5, k)

With Assistant
  If .On = False Then
    .On = True    
   '.FileName = "OFFCAT.acs"
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Visible = True
  End If
End With

Set Bal = Assistant.NewBalloon
With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To k
    If Len(MaxArray5obj(X)) > 0 Then
     .labels(X).Text = MaxArray5obj(X)
    End If
   Next
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

MsgGetOpt_Exit:
Exit Function

MsgGetOpt_Err:
Err.Clear
Resume MsgGetOpt_Exit
End Function

Open the Demo Form OptionCheck and click on the Command Button. You should see the Office Assistant-based MsgBox displaying the Options as shown in the sample image given on top of this page. Click on one of the Options. Another MsgBox will display the Option Text that you have selected. The selected option text with its corresponding sequence Number will appear in the TextBox on the Form as well. The User-selected option value is returned into the variable i in the cmdOpt_Click() Sub Routine. Performs a test on the variable i within the Select Case, End Select structure to find out which option the user has selected and executes the statements programmed there.

Next, we will look into the CheckBoxes with Office Assistant.

Download the Demo Database.

Download OfficeAssistant.zip

Who changed the Data

a.p.r. pillaiJanuary 18, 2008Accesstips, msaccess tips 2 comments

Introduction.

Who changed the data? This question comes up when something is found not in order with the information kept in the database and ended up with serious issues affecting many areas. Here, I didn't give out much to make it clearer to you, and wondering what this is all about. Let us look into a scenario to bring the story into a clear view.

Assume that there is a database on Credit Customers of the Company, which includes high profile Credit Parties from Ministries or Ministers themselves. All Credit Parties are grouped into different categories based on their status or other credibility criteria and assigned to different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the Credit parties as a regular follow-up measure, to remind them to make regular payments or to check and confirm that the statement is in order.

Since high-profile parties are kept under different Category Codes, their Statements are printed separately for internal record purposes only, but never forwarded to the Parties, as per strict instructions from the top.

Several individuals in the Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Codes by mistake and the Account landed in the common category and the monthly statement goes out to the VVIP party. As far as the common category is concerned, it is routine work to transport the statement to the concerned so that money can be recovered from them in time. But, the statements pertain to the VVIP categories, if delivered can put the Company into tight spots and can cause much embarrassment to the Management.

The investigating committee pointed their fingers at each one of those supposed to be responsible for the change, but nobody came forward hanging their head low. Finally, EDP Department has been called up and the poor Application developer has to find the answers quickly or his head is likely to roll. After all, he can change any data in his Application. But he was already prepared for such eventualities and had ready answers with him to save his skin. He caught the culprit red-handed having left his fingerprint on the record crystal clear, whether the change was intentional or not, is a different issue altogether.

If you are from the Accounts Department, I know you have so many questions to ask and you may even argue such a thing can never happen in your department. I already mentioned it as a story on mere assumptions, but you cannot rule out the possibility altogether.

User/Date/Time Stamps

Data Entry and Editing are two major functions that involve maintaining information up-to-date. Field-level validation checks are performed to maintain reasonable accuracy of the information fed into the System. Present-day inventions like Scanners, HHTs (Hand Held terminals), and other devices are also used. In these devices also the User IDs, Date and Time, etc., can be recorded as part of the information fed into computers.

When several Users are involved in using and updating information in MS-Access Databases, they should be installed in a Network, implementing strict MS-Access Security under Workgroups. Users must be organized into different Workgroups based on their activity with the database and allow them to Log-in with their own User ID and password limiting the activity within their own privileges.

We were discussing the fingerprinting (I coined this phrase here all by myself) of the data editing event on the record. When a new record is entered into a table or during the editing session we can record the date, time, and User ID into that record itself, which is very useful to sort the records or find the information entered or edited on a particular day or within a time period. Inadvertent change to the data can happen and the user may not remember which record, she has made the wrong change, even if she knows something went wrong somewhere. With the aid of the approximate time and date, the user herself can find the record involved and correct them, if she is given the facility to do that.

Add extra Time Stamp Fields to the Table.

While designing the Table add the following three fields at the end of the field list to record the Data Entry Date-Time, User ID, and record Edited Date-time:

Field Name	Data Type	Size
DEDate	Date/Time
EditedBy	Text	20
EditDate	Date/Time

Click on the DEDate Field. Enter the expression =Now() in the Default Value property of the field at the bottom. The EditDate and Edited By fields will update every time a new record is added or when changes are made to it on the Form.

While designing the Data Entry/Edit Forms place the above fields also on the Form, at a convenient location. Display the Property Sheet of the fields and set the Locked property of the above fields to Yes so that the user will not change the values manually. Set the Tab Stop property to No so that during normal data entry or editing time the cursor will not move into these fields.

Even if you set the Locked property to Yes the user can click on this field and sort the data and find the records she has edited recently and correct the mistakes, if she knows about it, but doesn't know how to find it.

If you don't want the user to use this field anyway but let her see the contents, then set the Enabled property of the field to No and reset the Locked Property to No. If you plan to keep these fields hidden from the user then set the Visible property of the fields to No.

Now, by setting up a simple Before Update Event Procedure we can start recording these values on the table. While the Form is still in design view, display the VBA Module of the Form (View - - > Code), copy and paste the following code into the module, and save the Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me!EditedBy = CurrentUser
     Me!EditDate = Now()
End Sub

The DEDate field will record the current date automatically when a new record is created and the above procedure will record the Current User ID and Current Date and Time as the final step of the edit action of each record.

To Avoid further Pitfalls

Even this method is not 100% fault-free. For example, if someone else edited that record by changing some other field after the error has happened, her name will be recorded overwriting the actual person who made the mistake. Then we will be catching an innocent person for somebody else wrongdoing. We may need to come up with more ideas, like adding more fields to update up to 5 editing events, if more editing then overwrites the oldest editing event, etc.

Consider keeping a history of changes made to the records and each edited record must be copied to a history file with the Date, Time, and User ID.

Earlier Post Link References:

Progress Counter

a.p.r. pillaiJanuary 11, 2008Forms, progressmeter 2 comments

Introduction.

This is the continuation of a series of examples and Demos tried earlier and uses Table and Form designed for earlier examples. If you have started on this Page please go through the earlier Topics and then continue. Click the Links Below or select them from the TRICKS Menu Tab above to open those Pages.

The new method that we are going to try out is better in look and feel and very active in nature. The progress control is a small form and it is updated at the record level, rather than based on the percentage of records processed, in the case of the progress bar indicator to advance forward.

This method cannot be used for Macros as we did with the Progress Bar Control in the earlier Article. When the processing time takes more than a few minutes and involves several files, you can easily incorporate the use of this new method with a few lines of Code in your Program. A Demo Run image of the Control is given below:

The display is self-explanatory. At the start of the Program, the ProgressCounter Form opens and initializes the fields with the Values of Program Name, Total Records to process, and the process Start Time. Subsequent calls to the controlling program will update the Processed number of records and Process Time. When the process is complete the End Time control is updated and the Form stays displayed for 4 seconds, giving enough time to view the Total Time taken for processing, and then closes automatically.

When the Program is active the Application is locked for processing and doesn't allow the user to close the Process Control Form or work with other objects in the database window.

Designing a New Form

We will design the above Form with Label Controls as shown below.

Open a New Form and add twelve Labels to the Form in the Detail Section.
Arrange the Labels, in size and shapes, as shown below, and fill the background Color of 3 Labels with dark Colors and 3 Labels at the Bottom with White Color.
Click on the Top left label on your design, display the Property Sheet (View Menu - - > Properties), and type Program: in the Caption Property.
Change the Captions of other labels with the descriptions as shown above following the same procedure.
Now, we are left with six Labels, 3 with dark backgrounds, and 3 Labels with white backgrounds arranged horizontally at the bottom. Display the Property Sheet of these controls and change their Name Property as given below:
- Name = lblProgram (label to the right of Program:)
- Name = TRecs ( -do- Total Records:)
- Name = PRecs ( -do- Processed:)
- Name = ST (label below Start Time)
- Name = PT ( -do- Process Time)
- Name = ET ( -do- End Time)
Draw a Box around the Labels as shown. If the box overlaps the labels when drawn, change its Back-Style Property = Transparent.
Next, we must change the Properties of the Form. Click on the Detail Section of the Form or on the horizontal bar above the detail Section with the description Detail.
Display the Property Sheet and change the Height Property Value to 1.2396". You may modify the design suitable to your own liking.
Click on the dark rectangle on the top left corner of the Form, to de-select other controls and select the Form, and display the Form's Property Sheet. Change the Properties as given below:
- Caption = Progress Counter
- Default View = Single Form
- Allow Edits = Yes
- Allow Deletions = No
- Allow Additions = No
- Scroll Bars = Neither
- Record Selectors = No
- Navigation Buttons = No
- Auto Resize = Yes
- Auto Center = Yes
- Pop Up = Yes
- Modal = Yes
- Border Style = Dialog
- Control Box = No
- Min Max Buttons = None
- Close Button = No
- Whats This Button = No
- Width = 2.3438"
- Allow Design Changes = Design View Only

Display the Code Module of the Form. Select Code from View Menu. Copy and paste the following Code into the Module, overwriting the existing lines:

Option Compare Database
Option Explicit
Dim i As Integer

Private Sub Form_Timer()
i = i + 1
Select Case i
  Case 1 To 16    'do nothing
  Case 17
    Me.TimerInterval = 0
    DoCmd.Close acForm, Me.Name
End Select
End Sub

Save the Form after changes with the Name: ProcessCounter. Give the name exactly as shown. No space between Process and Counter, because this name is referred to in the ProcCounter() Program.

We will use the same Demo Form ProgressMeter used in our earlier examples and the only change on that Form is the Command Button's On Click Property to call the Order Details Table updating the new Program.
Open the ProgressMeter Form. Click on the command button with the caption Process Orders and display its property sheet. Change the On Click property value to =ProcessOrders3() and don't forget the = sign. Save the Form.

I have made a copy of our earlier program ProcessOrders() and changed the three lines that call the ProgMeter2() routine for updating the Progress Bar in our earlier discussion. Since the Program is a Copy of the earlier code I changed the Procedure Name to ProcessOrders3(). The code is given below for your convenience and you may copy and paste the code into the Standard Module and save it.

Public Function ProcessOrders3()
'---------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 05/01/2008
'Remarks: Updates Extended Price on Order Detail Table
'---------------------------------------------------------
Dim db As Database, rst As Recordset
Dim recs As Long, qty As Integer
Dim unitval As Double, ExtendedPrice As Double
Dim Discount As Double, xtimer As Date

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLastr
ecs = rst.RecordCount
rst.MoveFirst

ProcCounter 1, 0, recs, "ProcessOrders()"

Do While Not rst.EOF
qty = rst![Quantity]
unitval = rst![UnitPrice]
Discount = rst![Discount]
ExtendedPrice = qty * (unitval * (1 - Discount))
rst.Editrst![ExtendedPrice] = ExtendedPrice
rst.Update

'Time delay loop for demo
'remove in real processing
xtimer = Timer
Do While Timer < xtimer + 0.02'do nothingLoop

ProcCounter 2, rst.AbsolutePosition + 1

rst.MoveNext
Loop

ProcCounter 3, rst.AbsolutePosition
rst.Close

Set rst = Nothing
Set db = Nothing

End Function

You need the Order Details Table, that we imported from the Northwind.mdb sample database earlier, for this example also. I hope that table is still available in your project. If not, download it again. If you forgot the location of the database visits the Page Saving Data on Form not in Table for location references.

The ProcCounter() Function

The ProcessCounter Form updating Program is given below. Copy and paste it into a Global Module in your Project and save the Module.

Public Function ProcCounter(ByVal intMode As Integer, ByVal lngPRecs As Long, Optional ByVal lngTRecs As Long, Optional ByVal strProgram As String)
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 5/01/2008
'Remarks: Process Counter Control
'--------------------------------------------------------------
Dim Stime As Double, ptime As Double, ETime As Double
Static m_lngTRecs As Long, m_strProgram As String, FRM As Form

On Error Resume Next

  If intMode = 1 Then
        DoCmd.OpenForm "ProcessCounter", acNormal
        GoSub initmeter
  ElseIf intMode = 2 Then
     GoSub updatemeter
  ElseIf intMode = 3 Then
     FRM.ET.Caption = Format(Now(), "hh:nn:ss")
     FRM.TimerInterval = 250
  End If

ProcMeter_Exit:
Exit Function

initmeter:
   Set FRM = Forms![ProcessCounter]
   m_strProgram = Nz(strProgram, "")
   Stime = Now()

    With FRM
        .lblProgram.Caption = m_strProgram
        m_lngTRecs = lngTRecs
       .TRecs.Caption = m_lngTRecs
       .PRecs.Caption = lngPRecs
       .ST.Caption = Format(Stime, "hh:nn:ss")
       DoEvents
    End With
Return

updatemeter:
   With FRM
        .PRecs.Caption = lngPRecs
        Stime = TimeValue(.ST.Caption)
        ETime = Now()
        ptime = Stime - TimeValue(Format(ETime, "hh:nn:ss"))
        .PT.Caption = Format(ptime, "hh:nn:ss")
        DoEvents
    End With
Return

End Function

The Demo Run

Open the ProgressMeter Form in Normal View. Click on the Process Orders Command Button. The ProcessCounter Form will appear updating the count of records already processed. Process Start Time and Process Time taken so far will be updated too. When the Processed records are equal to the total number of records in the Order Details file, the End Time will be updated. The control will stay visible for about 4 seconds displaying the details and after that, it will close itself.

A delay loop is built into the ProcessOrders3() Program to slow down the action. You may remove this when you implement the code in your own project.

Download the Demo Database.

Download ProgressMeter2007.zip

Download ProgressMeter.zip

Progress Bar on Form

a.p.r. pillaiJanuary 04, 2008Forms, progressmeter 8 comments

Introduction

Continued from the previous article: Progress Meter

We will try the Progress Bar Control on the sample Form that we have already designed earlier, for use with Macros.

NB: This method for Macros was found working in MS Access 2000 only. When tried in MS-Access 2003 it totally ignores updating the Progress Bar Control, even with the database Formats MS-Access 2000, 2002, and 2003. If any readers found a solution to this problem, please share it with me too.

However, you can use this method with our earlier Program replacing the sysCmd() lines for transaction-level processing, to view the Progress Bar on your own Form rather than on the Status Bar.

The Form Design

Open the ProgressMeter Form in Design View.
Select the ActiveX Control Option from Insert Menu.
Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.
Draw a Progress Bar on the Form as shown in the image below. You have to do some resizing to make it look like the sample below. You can make it in any size and shape you prefer.
Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View - - > Properties).
Change the following Property Values are shown below:
- Name = PB2
- Visible = False
Create a label on the left side of the ProgressBar. Display its Property Sheet and change the following values:
- Name = lblStatus
- Visible = False
Select the Command Button on the Form, display the Property Sheet, and change the On Click Property.
- On Click = Spl_Report
Spl_Report is a Macro in which we have sequenced our processing steps and the Progress Bar will show the current status of its activity.
Save the Form after the changes.
Select Options from Tools Menu. Uncheck the Status Bar under the Show Option group on View Tab. Click Apply and Click OK to close the control.
If you have a Macro running several steps of Queries, Macros, and Code, make a copy and rename it as Spl_Report and we will modify it for our demo. A sample Macro Image is given below for reference.

The Macro Action Lines
The first two steps on the Macro's Action Field have their values set as No.
The third line Calls the ProgMeter2(1, 5, "PB2") Function with the RunCode macro command with three parameters to initialize the Progress Meter. The Values of the parameters represent the following:
- 1 = indicating that the Progress Bar must be initialized.
- 5 = This is the maximum number of steps of Queries, other macros, or Functions that we are going to run.
- PB2 = The Name of the Progress Bar Control drawn on the Form.
The Function must be called after every Query running step to update the Progress Bar. But, subsequent calls to the function ProgMeter2(0) need only 0 (zero) as a Parameter indicating that it is the Progress Meter's updating step.
Insert one row each after every Query on the Macro, Copy and paste the RunCode macro line on the inserted rows calling the Function ProgMeter2(0)
At the end of the Macro steps add one more line to call the Function ProgMeter2(2), with the parameter value 2 indicating that the work is over and to turn off the ProgressBar.
If you have added more steps later in the Macro and forgot to modify the initializing value (second parameter 5) the program will ignore further updating calls and wait for the terminating parameter 2 for closing the Progress Bar.
Save the Spl_Report Macro after the above changes.

The Progress Meter2 Function

Copy and Paste the following VB Code into a Global Module in your Project and save it.

Public Function ProgMeter2(ByVal xswitch As Integer, Optional ByVal Maxval As Integer, Optional ByVal strCtrl As String)
'----------------------------------------------------------
'Program : Progress Bar Demo2
'Author  : a.p.r. pillai
'Date    : 02/01/2008
'----------------------------------------------------------
Static mtrCtrl As Control, i As Integer, xmax As Integer
Static lbl As Label, frm As Form
Dim position As Integer, xtime As Date

On Error GoTo ProgMeter2_Err

If xswitch = 1 Then
 'init control
    Set frm = Screen.ActiveForm
    Set mtrCtrl = frm.Controls(strCtrl)
    Set lbl = frm.Controls("lblstatus")
    mtrCtrl.Visible = True
    lbl.Visible = True
    xmax = Maxval
    i = 0
    DoEvents
ElseIf xswitch = 0 Then
    i = i + 1
    if i > xmax then
          goto ProgMeter2_Exit
    end if
    position = i * (100 / xmax)
    mtrCtrl.Value = position
    DoEvents
ElseIf  xswitch = 2 Then
    mtrCtrl.Visible = False
    lbl.Visible = False

    Set mtrCtrl = Nothing
    i = 0
    xmax = 0
    Exit Function
End If

ProgMeter2_Exit:
Exit Function

ProgMeter2_Err:
MsgBox Err.Description, , "ProgMeter2"
Resume ProgMeter2_Exit
End Function

The Program Logic is the same as of SysCmd() we have used for updating the Status Bar in our earlier example. The only difference is our program updates the Progress Bar on a Form.

Note: The built-in Function SysCmd() has other implementations too, with a different set of Parameters.

Open the ProgressMeter Form in Normal View. Click on the [Process Orders] Command Button to Run the process steps in Spl_Report Macro.

The Progress Bar will now show up on the Form with the message 'Working...', on the left side Label.

The image of the sample run is given below.

You can implement this method on any Form, like on your Control Screen or on a separate report running parameter Form. When you call the Function ProgMeter2(), use the Name that you have given to the Progress Bar Control as the third parameter to the Function in place of PB2, which we have used for our example: ProgMeter2(1, 5, "PB2"). The subsequent call to the Functions needs only one parameter value 0 for updating the control at each step and 2 to close the Progress Bar at the end.

Preventing Pitfalls

Further improvements to the design of the Form and the Code are required to prevent users from closing the Form by accident or straying away from the Form for doing something else making the Form inactive etc. In such situations, the program may run into Errors.

As I have mentioned at the beginning you can use this function for your VB Routines that update Table at the record level.