Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MsgBox with Options Menu

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:

  1. Design a simple Form with a Text Box and a Command Button as shown above to run the Demo Program.
  2. 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.
  3. 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.
  4. 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

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 and Title variables are initialized with Option Text and calls 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 have placed the Main Programs earlier. Or copy the Main Programs again from the following pages.

  1. Copy and Paste the main programs from the Page MsgBox with Office Assistant into a Global Module in your Project and save it.
  2. 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 are also given there.
  3. 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
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

Exit Function

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 Check Boxes with Office Assistant.


Who changed the Data

Who changed the data? This question comes up when something found not in order with the information kept in the database and ended up with serious issues reaching out to too many. 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 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 with different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the parties as a regular follow up measure, to remind them to make payments or to check and reconfirm the statement is in order or not.

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

Several individuals in Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Code 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 a routine work to transport the statement to the concerned so that money can be recovered from them in time. But, the statements pertains 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 on each one of the 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 finger print on the record crystal clear, whether the change was intentional or not, is a different issue altogether.

If you are from 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 is a story (assumption), but you cannot rule out the possibility altogether.

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 about the finger printing (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 on which record she has made the wrong change, even if she knows something went wrong somewhere. With the aide of approximate time and date the user herself can find the record involved and correct them, if she is given the facility to do that.

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 EditedBy fields we will update every time a new record is added or when changes 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 don't know how to find it.

If you don't want the user to use this field any way 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, with 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 VB 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.

Even this method is not 100% fault free. For example, if someone else edited that record changing some other field, after the error has happened, her name will be recorded overwriting the actual person made the mistake. Then we will be catching an innocent person for somebody else wrong doing.

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


Progress Counter

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.

  1. Progress Meter
  2. Progress Bar on Form

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 record level, rather than based on the percentage of records processed, in the case of progress bar's 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 few minutes and involves several files, you can easily incorporate the use of this new method with 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 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.

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

  1. Open a New Form and add twelve Labels on the Form at the Detail Section.
  2. Arrange the Labels, in size and shapes, as shown below and fill the background Color of 3 Labels with dark Color and 3 Labels at the Bottom with White Color.

  3. Click on the Top left label in your design, display the Property Sheet (View Menu - - > Properties) and type Program: in the Caption Property.
  4. Change the Captions of other labels with the descriptions as shown above following the same procedure.
  5. Now, we are left with six Labels 3 with dark back-ground and 3 Labels with white background 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)
  6. Draw a Box around the Labels as shown. If the box overlaps the labels when drawn, change its Back-Style Property = Transparent.
  7. 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.
  8. Display the Property Sheet and change the Height Property Value to 1.2396". If this size is not suitable for your present design then retain yours.
  9. Click on the dark rectangle on the top left corner of the Form, to de-select other controls and to 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
  10. Display the Code Module of the Form. Select Code from View Menu. Copy and paste the following Code into the Module:
    'Global declaraction
    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

    The first two lines of the above code should go at the top of the Module, below the

    Option Compare Database

    Option Explicit


  11. 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 in the ProcCounter() Program.

    We will use the same Demo Form ProgressMeter used for 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 new Program.

  12. 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 calls 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 a Global 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)
ecs = rst.RecordCount

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

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

ProcCounter 2, rst.AbsolutePosition + 1


ProcCounter 3, rst.AbsolutePosition

Set rst = Nothing
Set db = Nothing

End Function

You need the Order Details Table, that we have imported from 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 visit the Page Saving Data on Form not in Table for location references.

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

Exit Function

   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")
    End With

   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")
    End With

End Function

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


Progress Bar on Form

Continued from previous article: Progress Meter

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

NB: This method for Macros found working only on Microsoft Access 2000. 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.

  1. Open the ProgressMeter Form in Design View.
  2. Select ActiveX Control Option from Insert Menu.
  3. Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.
  4. 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 any size and shape you prefer.

  5. Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View - - > Properties).
  6. Change the following Property Values are shown below:
    • Name = PB2
    • Visible = False
  7. Create a label at the left side of the ProgressBar. Display its Property Sheet and change the following values:
    • Name = lblStatus
    • Visible = False
  8. 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.

  9. Save the Form after the changes.
  10. 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.
  11. 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 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. Values of the parameters represents 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.
  12. The Function must be called after every Query running step to update the Progress Bar. But, subsequent calls to the function ProgMeter2(0) needs only with 0 (zero) as Parameter indicating that it is the Progress Meter's updating step.
  13. 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)
  14. 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.
  15. 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.
  16. Save the Spl_Report Macro after the above changes.
  17. 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
    ElseIf xswitch = 0 Then
        i = i + 1
        if i > xmax then
              goto ProgMeter2_Exit
        end if
        position = i * (100 / xmax)
        mtrCtrl.Value = position
    ElseIf  xswitch = 2 Then
        mtrCtrl.Visible = False
        lbl.Visible = False
        Set mtrCtrl = Nothing
        i = 0
        xmax = 0
        Exit Function
    End If
    Exit Function
    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. The built-in Function SysCmd() have other implementations too, with different set of Parameters.

  18. 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. . . at the left side Label.

The image of a 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 third parameter to the Function in place of PB2, that we have used for our example: ProgMeter2(1,5,"PB2"). 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.

Further improvement on the design of the Form and the Code is 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 Error.

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



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...


Blog Archive

Recent Posts