LEARN MS-ACCESS TIPS AND TRICKS

Hiding Report Lines Conditionally-3

noreply@blogger.com (a.p.r. pillai) — Thu, 09 Jul 2009 07:26:00 +0000

This is the continuation of two Articles published earlier on this Subject. You may go through them by following the links given below before continuing from here.

Hiding Report Lines Conditionally

Hiding Report Lines Conditionally-2

We have learned how to hide Report items conditionally and calculate Sub-Totals and Report Footer Totals in Code by excluding values of those records we have removed from the Report Detail Section. We have updated the calculated values in Group Footer and Report Footer Section Controls.

We did this by checking for specific values in the Report Source Data and by Canceling the Format Event of the Report.

These exercises we have done on the Detail Section alone earlier on the Report by hiding specific record. But, here our focus is on hiding all records of a particular Customer Group including the Customer Group Header, Group Footer and Detail Sections.

If you understood the method we have used in the last two examples then you will have no difficulty in understanding it here. The only difference here is to compare the Group Value (CustomerID) in all the three Section's (i.e. Group Header, Detail and Group Footer Sections) Format Event Procedure to hide or show them rather than Report Detail Section alone as we did in the earlier example.

Make a copy of the earlier Report (Order_Details) and save it with the name Order_Details2.

Open the Report in Design View. We will make a small change on the Group Footer Section.

Click on the Label with the Caption Sub-Total to select it.

Select Change To from Format Menu and select Text Box from the displayed options.

Label

Text Box

CustomerID

Sub-Total

Display the Property Sheet (View- - >Properties) of the Text Box.

Write the following Expression in the Control Source Property of the Text Box.

=[CustomerID] & " Sub-Total: "

BSBEV Sub-Total: 123

CustomerID

Report

Display the Code Module of the Report (View - -> Code).

Delete the existing Code from the Module.

Copy and Paste the following Code into the Report Code Module and Save the Report.

 
Option Compare Database
Dim x_subtot As Long, x_gtot As Long

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
   Cancel = True
Else
   Cancel = False
End If

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
    Cancel = True
Else
    Cancel = False
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        x_subtot = x_subtot + [Quantity]
        x_gtot = x_gtot + [Quantity]
    End If
End Sub


Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [subtot] = x_subtot
        x_subtot = 0
    End If
End Sub

Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [gtot] = x_gtot
End If
End Sub

Open the Report in Print Preview and check whether these Customer Group Header, Detail and Group Footer Sections are hidden from the Report or not. Check the sample image given below.

From the VBA Code above we have purposely removed the Code for Group Footer Format Event Procedure so that the Group Footer alone for the CustomerID BSBEV and CENTC will appear. We can now see the position of these Group items appearing on the Report and from where the Group Header and Detail Sections are hidden from our view.

Another point to note is that since we have Canceled the Format Event of the Detail Section of these Customer Groups the Print Event has not taken place for Detail Section. Because of this the Group Summary Totals for these two Customer Codes are showing as zeroes.

Copy the following Code and Paste it at the bottom of the Code Module and save the Report.


Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
     If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
           Cancel = True
     Else
           Cancel = False
     End If
End Sub

Note: If you are wondering how to name the Sub-Routine and Parameters related to each Section of the Report correctly then stop worrying. This (the Subroutine header line and the End Sub lines) will be written by MS-Access. Click on the Section Header/Footer of the Report in design view, display the Property Sheet (View - -> Properties), set Event Procedure on the Format or Print Event Property, Click on the build (...) Button to the right edge of the Property Sheet. Both the Sub-Routine Name (pointing to the correct Section of the Report) with appropriate Parameter list and the End Sub lines will open up in the Code Module of the Report. You only need to write other lines between them manually.

Open the Report again in Print Preview and this time you will find the Group Footer of these Customers are also hidden.

We are checking for the CustomerlD Code BSBEV and CENTC in all the three Sections of the Report in the Format Event Procedure to hide these Sections. Once the Format Action is prevented from happening; the Print Event doesn't take place and the Quantity Value of those items are not added to the Total.

If you look at the Code structure carefully you can see that we are using the Format Event as well as Print Event Procedures for both Detail Section and Group Footer Sections. Format Action used for hiding or displaying the Report contents based on the criterion and Print Action for Calculations or Updating the Group Total Controls. We have used only Print Event Procedure for Report Footer Section.

Next we will learn how to open the same Report in two different Modes, i.e. as a Detail Report and as a Summary Report.

Change Form Modes On User Profile
Positioning POPUP Forms
Synchronized Floating Popup Form
Forms and Custom Properties
Control Tip Text and Time Delay

Hiding Report Lines Conditionally-2

noreply@blogger.com (a.p.r. pillai) — Fri, 03 Jul 2009 07:42:00 +0000

Last week, we have started the topic of hiding Report Detail Section data lines and worked through a simple example as well. But, we have not yet seen how this action affects the normal calculations on the Report, like calculating Group-wise Sub-Totals or Report Footer Totals.

We have seen that we can do this with simple VBA Code, if we know how MS-Access works on the data Formatting and Printing actions on the Report before it shows the results to us.

This week we will work with a sample report to see how Group-level Sub-Totals are calculated by MS-Access, when some of the group-level records or data lines are conditionally suppressed from showing on the Report with VBA Code.

For this example we need three data Tables from C:\Program Files\Microsoft Office\Office11\Samples\NorthWind.mdb sample Database.

Import the following Tables into your database:

Orders

Order Details

Products

Open a new Query and display its SQL Window.

Copy and Paste the following SQL String into the SQL Editing Window and Save the Query with the Name Order_DetailsQ:


SELECT Orders.CustomerID,
   [Order Details].OrderID,
   Products.ProductName,
 [Order Details].Quantity
FROM Orders INNER JOIN ([Order Details] INNER JOIN Products ON [Order     Details].ProductID = Products.ProductID) ON Orders.OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID) Between 10248 And 10300) AND ((Left([CustomerID],1)) Between "A" And "L"));

Query

Click on the Order_DetailsQ query to select it and select Report from Insert Menu.

Select Report Wizard from the displayed list of options and click OK.

Select all fields from the Available Fields List and move them to the Selected Fields List and click Next Command Button.

The CustomerID field is already selected for Grouping Levels. If any other Field is appearing on the Report sample View then click the < Button to remove it and select CustomerID Field from the Field List and click > Button to use it as Group Level and click Next.

Click on the Summary Options Command Button and put a Check Mark under the Sum option for Quantity Field, click OK and then click Finish to create the Report.

Save the Report with the name Order_Details.

Sub-Totals

Report Footer Total

Printer

OrderID

10280

10297

BERGS

BLONP

Sub-Totals

Report Totals

Open the Report in Design View.

Display the Code Module (View - -> Code) of the Report.

Copy and Paste the following Code into the Module, Save and close the Report.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
      Cancel = True
Else
      Cancel = False
End If
End Sub

hiding-report-lines-Conditionally

parameter table

If…Then

Open the Report in Print Preview.

Check the Sub-Totals of CustomerIDs BERGS and BLONP with the Print out you have taken earlier.

Sub-Totals

Report Footer Total

Summary for CustomerID = BERGS (7 records)

Open the Report in Design View and delete the Text Box with the expression that shows the above Summary message.

Before we proceed further with corrective actions for showing Sub-Totals and Report Total I want to draw your attention to the above Code that we have used for this example.

If you have tried last week's example you will see some difference in both VBA Codes. Last Week we have asked MS-Access to hide the Detail Section with the statement Report.Section(acDetail).Visible = False during the Report Format Event when the OrderIDs in the criterion are encountered.

In this example we have asked MS-Access to Cancel the Format action. As far as the output is concerned both these actions have the same result.

You can run another experiment by running the same Code under the Print Event Procedure and see what difference it makes. I will give the Code below. You may Copy and Paste it in the Report Module overwriting the earlier Code.


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
   Cancel = True
Else
      Cancel = False
End If
End Sub

Open the Report in Print Preview and inspect the area of the records of OrderIDs we have used in the criterion. Even though the data lines are suppressed from appearing on the Report the empty space of those Report Lines are left on the Report.

We will now proceed with the Sub-Total and Report Footer Total calculation part. That's right; we have to Calculate the Quantity Totals excluding the Values of Orders 10280 and 10297 and write it directly in the Sub-Totals and Report Footer Total Text Boxes. Let us see how we do that.

Open the Report in Design View.

Remove the expression =Sum([Quantity]) from the Text Box Control Source Property in the CustomerID Group Footer and in the Report Footer Sections.

Click on the Group Footer Sub-Total Text Box and display its Property Sheet (View - -> Properties).

Change the Name Property Value to SubTotal.

Similarly change the Name Property Value of Page Footer Text Box to GTotal.

Copy and Paste the following Code into the Report Module replacing the earlier Code.


Dim x_SubTotal As Long, x_GTotal As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
    Cancel = True
Else
    Cancel = False
End If

End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    x_SubTotal = x_SubTotal + [Quantity]
    x_GTotal = x_GTotal + [Quantity]
End If
End Sub

Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [SubTotal] = x_SubTotal
    x_SubTotal = 0
End If
End Sub

Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [GTotal] = x_GTotal
End If
End Sub

Save the Report, open it in Print Preview and check whether the Sub-Totals and Report Footer Totals are appearing now correctly or not.

In the above code we have prevented the Report from showing the record lines in the Format Event and the Calculation is done in the Print Event of the Report. We are checking whether the PrintCount parameter Value is 1 or not, to correctly calculate the Totals. The Print Event can take place more than once if you move from a Page to an earlier Page in the Retreat Event.

So far everything went on well, but there is a major problem with this method and it is important that the User must be aware of this to avoid undesirable results.

If you jump directly to a page (say from page 2 to the Last Page by typing the Page Number in the Report Page control below in Preview) then our line by line Calculation on every Page fails and the Report Footer Totals will be wrong. So, if the User is not aware of this issue and jumps through the Report Pages in Print Preview, before printing the Report, then we are in soup.

To work around this problem we can use any of the following methods:

Use Custom Menus and Tool Bars in your application and use separate options for Printing and Previewing the Report.

Calendar and Toolbars

Custom Menus and Tool Bars

Custom Menus and Tool Bars-2

Disable Print… Command Buttons from File Menu and from Report Preview Menu while previewing this particular Report and enable them again when Preview is closed. To do this add the following Code into the Module of the Report:


Private Sub Report_Close()
   CommandBars("File").Controls("Print...").Enabled = True
   CommandBars("Print Preview").Controls("Print").Enabled = True
End Sub

Private Sub Report_Open(Cancel As Integer)
   CommandBars("File").Controls("Print...").Enabled = False
   CommandBars("Print Preview").Controls("Print").Enabled = False
End Sub

Print…

Command Button

List Box

DoCmd.OpenReport "myReport", acViewNormal

To be on the safest side use this method on Reports having only 2 pages.

Next we will examine how to hide Group level information like Group-Header, Detail and Group-Footer Sections with Code.

Positioning POPUP Forms
Synchronized Floating Popup Form
Forms and Custom Properties
Control Tip Text and Time Delay
External Files List in Hyperlinks

Hiding Report Lines Conditionally

noreply@blogger.com (a.p.r. pillai) — Thu, 25 Jun 2009 20:34:00 +0000

Hiding Report lines or Report Sections conditionally at run time may not be an every day requirement. But, it is interesting to know how to do it, if it becomes necessary, with the help of Programs. One thing I like very much about MS-Access Report is its ability to write programs on the Report itself, especially for highlighting very critical information on the Report so that the attention of Users can be drawn to specific information on it. You can see an interesting and useful example in one of the earlier Posts with the Title: Highlighting Reports.

To suppress something from appearing on the Report we normally think about using a Query to filter out unwanted items from Source Data before Previewing or Printing the Report. Frankly, Query is the best method compared to what I am trying to show you here. To get the same result on the Report without filtering the source data we have to play around with few tricks (after all, this is all about Tips & Tricks, right?).

If you really enjoy working with MS-Access programs then read on. As far as the program code is concerned there is nothing complicated to deal with, all we need is few lines of simple code and even a VBA beginner can easily understand them.

It is interesting to do something different, which not only kills the boredom of doing the same thing again and again but also gives you better insight in doing things better next time.

We will create a sample Report quickly to try out this Trick.

Import Orders and Employees Tables from C:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb sample Database.

Employees

EmployeeID

Combo Box

EmployeeID

Orders

Click on the Orders Table and select Report from Insert Menu.

Select Report Wizard from the displayed list and click OK.

Select the following Data Fields from the Available Fields List and move them one by one to the Selected Fields List:

OrderID

CustomerID

EmployeeID

OrderDate

RequiredDate

Click Next and select CustomerID as Grouping Level and move it to the right. Click Next Command Button.

Select OrderID for Sorting in the first Sort Control and Click Finish to create the sample Report.

Open the Report in Print Preview and check the Order Numbers appearing under the first three Customer Groups on the Report.

Orders

10702

10625 & 10573

ALFKI, ANATR & ANTON


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10702 Or [OrderID] = 10625 Or [OrderID] = 10573 Then
      Report.Section(acDetail).Visible = False
Else
   Report.Section(acDetail).Visible = True
End If
End Sub

On Format

Detail Section

Open your Report in Design View and display the Code Module (View - ->Code).

Copy and Paste the above Code into the Report Module.

Select Close and Return to Microsoft Office Access from File Menu to close the VBA Window and return to the Design View of the Report.

Alt+F11

Keyboard Shortcuts

Click on the Detail Section of the Report and display the Property Sheet (View - ->Properties).

Check the On Format Event Property and you can see that the entry [EventProcedure] is appearing there indicating that the Code that we have pasted Runs on the Format Event of the Report.

Events

Print

Retreat

Format

Report Section

Print

Retreat

Format

Retreated

FormatCount

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Save the Report and open it in Print Preview.

Check whether the Report Lines associated with OrderIDs 10702, 10625 & 10573 are still appearing on the Report or not.

We were using Constant values for OrderIDs in the Program to check and suppress the Report lines from appearing on the Report. We cannot expect the User to open the Report in Design View and change the Program every time to change the OrderIDs, if it become necessary, before printing the Report. Besides, there may be more than two or three Orders to hide this way. We must provide a much simpler method to the User to input the OrderIDs as parameters to the Program and Run the Report based on that.

We will create a parameter table to key in OrderIDs, read them in the program and compare with the Report OrderIDs and skip those lines on the Report for matching cases.

Create a Table with the name OrderParam with a single Field with the name OID with Data Type Number and Field Size Long Integer.

Select Primary Key from Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values going into the Parameter Table and makes it easier to cross-check OrderIDs from Report.

Save the Table and open it in Data Sheet View.

Key in the OrderIDs 10702, 10625 and 10573 (or any other OrderIDs you would like to hide) in the OrderParam Table.

Orders

Copy and Paste the following Code in the Report's Code Module replacing the earlier Code:


Dim cdb As Database, rst As Recordset

Private Sub Report_Open(Cancel As Integer)
     Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("OrderParam")
    rst.Index = "PrimaryKey"
End Sub


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
rst.Seek "=", [OrderID]
If Not rst.NoMatch Then
    Report.Section(acDetail).Visible = False
Else
    Report.Section(acDetail).Visible = True
End If
End Sub

Private Sub Report_Close()
   rst.Close
   Set rst = Nothing
   Set cdb = Nothing
End Sub

Save the Report with the new Code and open it in Print Preview.

Look for the OrderIDs given in the OrderParam Table to check whether they are really suppressed from the Report or not.

Let us examine what we did in the above Code.

We have declared the Database and Recordset Objects at Global level of the Report Module so that we can refer to these objects in all other Sub-Routines.

Opening the OrderParam Table and activate the PrimaryKey Index on the Report's Open Event Procedure and keep it memory.

We cross-check each OrderID with the OrderParam Table contents on the Report's Detail Section Format Event Procedure and hide the Report line for the OrderId that matches with the OrderParam value. This process continues till it reaches the end of the Report.

The OrderParam Recordset is closed in the Close Event Procedure of the Report.

Since, the checking process need referencing an external table for each record of the Report Source Data there will be slight delay before the Previewing or Printing action takes place.

Next we will explore how this method works with Customer Level Summary Totals in the Customer Footer Section of the Report.

Synchronized Floating Popup Form
Forms and Custom Properties
Control Tip Text and Time Delay
External Files List in Hyperlinks
Combo Box Column Values

Network And Print Page Setup-3

noreply@blogger.com (a.p.r. pillai) — Fri, 19 Jun 2009 08:33:00 +0000

Shared MS-Access Reports are normally designed for a particular Printer on the Network. When Users attempt to print this Report on a different Printer the Report will change to the default Page Settings of that Printer, like Paper Size, Page Orientation and others and the Report may not print correctly.

We have familiarized how to change Page Setup Values (Paper Size, Page Orientation and Margin Settings) of Reports through VBA Programs before printing the Report on Network based Printers in the earlier Posts.

This subject is not complete unless we touch the topic of Columns Setting changes (File - -> Page Setup - -> Columns) through Program for multi-column Reports.

The Columns Tab values of Page Setup Dialog Control are also modified through the PrtMip Property Values of Report. If you have gone through the earlier Post on Margin Settings then you have already created the User Defined data types to copy PrtMip Property Values into memory for modification. In that case you need only a new Sub-Routine to modify the Report Column settings of a multi-column Report and open it in Print Preview.

For the benefit of the Readers who have landed on this Page directly; I will reproduce the first part of the Code (declaring two User-defined data types segment) below.

Open the VBA Editing window (Alt+F11); insert a new Standard Module (Global Module) - (Insert - -> Module), copy and paste the following Code (from both Code windows) into the Module immediately below the global declaration line Option Compare Database.

Those who have already copied the first part for Margin Setting example take the code from the second Code window and paste it into the same Module where you have placed the data type declarations for PrtMip Property earlier. You can paste the Code into a different Global Module, if you prefer to do so.


Private Type str_PRTMIP
    strRGB As String * 28
End Type

Private Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type


Public Sub PageColumns(ByVal strName As String)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rpt As Report
    Const PM_HORIZONTALCOLS = 1953
    Const PM_VERTICALCOLS = 1954
    Const TWIPS = 1440
    
    ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString
    
    ' Create two columns.
    PM.cxColumns = 2
    
    ' Set 0.25 inch between rows.
    PM.xRowSpacing = 0.25 * TWIPS
    
    ' Set 0.5 inch between columns.
    PM.yColumnSpacing = 0.5 * TWIPS
    PM.rItemLayout = PM_VERTICALCOLS
    
    ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB
    
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    
    Set rpt = Nothing
    
End Sub

Next step is to create a multi-column sample Report to test our Program. We can design a Report for Address Labels with the Imported Employees Table from MS-Access sample database Northwind.mdb.

Import (File - ->Get External Data - - >Import) the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Sample Database.

Click on the Employees Table and select Report from Insert Menu.

Select Design View from the displayed list of Options.

Label Wizard

Remove the check mark from Page Header/Footer Option of View Menu to remove the Page Header/Footer Sections from the Report Design.

Design a sample Label in the Detail Section of the Report as shown in the Image given below.

Create four Text Boxes about the size of 2.75 Inches long and 0.25 Inches high each and arrange them close by as shown on the design.

Display the Property Sheet of each Text Box (View - ->Properties) and write the expressions in the top and third Text Boxes and insert the Field names Address and Country in second and fourth Text Boxes respectively.

Click outside the Text boxes and drag the mouse over them to select all the four Text Boxes together.

Display the Property Sheet of the Text Boxes (View - -> Properties).

Change the Border Color Property value to 0.

Open the Page Setup Dialog Box from File Menu and select Columns Tab.

Change the values on the controls as shown in the image above.

Columns

Width

3.25 Inches

1.4 Inches high

Across, then Down

Down, then Across

You may set the Margin Values to 0.5 Inches on all four sides on the Margins Tab.

Select A4 Paper Size and Orientation to Portrait on the Page Tab and close the Page Setup Dialog Box.

Click on the Sorting and Grouping Toolbar Button above. You can find this icon at the top right end (next to the Tool Box Button icon) on the above image.

Select FirstName in the Field/Expressions Column and Ascending in the Sort Order Column.

Save the Report with the name MyLabels or any other name you prefer.

Open the Report in Print Preview and check the arrangement of Labels and values in them. The Employee Names were sorted in First Name Order on the Report. The Label arrangement is now Across, then Down order.

Close the Report Preview and open it in Design View.

Open the Page Setup Dialog Box and select the Columns Tab.

Change the Row Spacing Value to 0 Inch and the Column Spacing value to 0.1 Inch.

Leave the Column Layout Value (Across, then Down) setting as it is.

Column Layout

Column & Row Spacing

You can run the Program PageColumns() directly from the Debug Window (Immediate Window) for testing. Press Alt+F11 Keyboard Shortcut to display the VBA Editing Window and press Ctrl+G to bring up the Debug window.

Type PageColumns "MyLabels" in the Debug Window and press Enter Key.

Through the PageColumns() Program we have now changed the Report Column Layout to Down, then Across and the Column, Row spacing of Labels to the original settings we have initially specified on the Page Setup Dialog Box.

You can run the Program through a Button Click Event Procedure from your Main Switchboard by adding the following lines of sample Code:


Private Sub cmdPreview_Click()
 PageColumns "YourReportName"
End Sub

You can call PageColumns() Program and pass the Report Name as parameter when the User attempts to print Address Labels. This will ensure that the Address Labels are printed with correct settings on any Printer on the Network.

NB: The User must select the printer (if she has more than one Printer installed on her machine) and set it up as Default Printer before attempting to print the Report.

Forms and Custom Properties
Control Tip Text and Time Delay
External Files List in Hyperlinks
Combo Box Column Values
Drill-Down Inquiry Screen-2

Network And Print Page Setup-2

noreply@blogger.com (a.p.r. pillai) — Fri, 12 Jun 2009 13:06:00 +0000

We have seen in the earlier Article how to change Paper Size and Page Orientation of MS-Access Reports automatically through program for any Printer on the Network. We have copied PrtDevMode Property Values into Memory; modified them to match the Paper Size and Page Orientation of the Report and updated them back into the Report Page Settings before printing it on the default Printer installed on the User's Machine.

We are going to perform a similar exercise to change the Margin Settings of MS-Access Report through program. This time we have to work with PrtMip Property of the Report to change the Margin Values.

The procedure is almost same as of the previous example. The steps taken in the Program is as follows:

Open the Report in Design View.

Copy the PrtMip Property Values of the Report into a 28 Bytes long String Variable and move it into a redefined structured data area for modification.

Change the required Margin Values in Memory.

Update them back into the Report’s PrtMip Property.

Save the Report with the changes and open it in Print Preview.

So let us start.

Open one of your Databases with Reports in it.

Display the Visual Basic Editing Window (Alt+Fll).

Insert (Insert - - > Module) a new Standard Module (Global Module).

Copy and paste the following Code into the new Module and save it.


Private Type str_PRTMIP
    strRGB As String * 28
End Type

Private Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type

Public Sub SetMargins(ByVal strName As String)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rpt As Report
    Const TWIPS As Long = 1440
    ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString
    
    ' Set margins.
    PM.xLeftMargin = 0.75 * TWIPS
    PM.yTopMargin = 0.5 * TWIPS
    PM.xRightMargin = 0.5 * TWIPS
    PM.yBotMargin = 0.5 * TWIPS
    
    ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB
    
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    
    Set rpt = Nothing
    
End Sub

Open one of your existing Reports in Design View.

Select File - ->Page Setup- - > Margins.

Change all four sides (Left, Right, Top and Bottom) of the Margin settings to 1 Inch.

Save and Close the Report.

Open the Main Switchboard Form of your Application or create a new Form.

Create a new Command Button on the Form.

While the Command Button is in selected state, display the Property Sheet (View - ->Properties).

Change the Name Property Value to cmdPreview and change the Caption Property Value to Print Preview.

Display the Code Module of the Form (View - -> Code).

Copy and paste the following lines into the Code Module of the Form.


 Private Sub cmdPreview_Click()
  SetMargins "MyReport"
 End Sub

Replace the name MyReport with your own Report Name.

Save and close the Form.

Open the Form in normal view and Click on the Command Button to run the Program and change all four margins of the Report to new values and to open the Report in Print Preview.

Close the Report and open it again in Design View and check whether the margin settings have been really changed through program or not.

Note:If any value is less than the allowable range, based on the printer driver settings, the printer may fix it to the allowable range automatically. In that case you may find some value is greater than what you have specified.

You can do a sample run of the Program by typing SetMargins "YourReportName" in the Debug Window directly, without the use of Form or Command Button.

You can run this Program from within the earlier PaperAndOrient() Program to change the Margins also along with the Paper Size and Page Orientation. All the three set of values can be changed by calling the PaperAndOrient() Program alone.

The modified PaperAndOrient() Program is given below:


Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report
    
    ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    
    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation

        ' Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If
        
        ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    SetMargins strName
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing
    
End Sub


Public Sub SetMargins(ByVal strName As String)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rprt As Report
    Const TWIPS As Long = 1440

    Set rprt = Reports(strName)
    PrtMipString.strRGB = rprt.PrtMip
    LSet PM = PrtMipString
    
    ' Set margins.
    PM.xLeftMargin = 0.75 * TWIPS
    PM.yTopMargin = 0.5 * TWIPS
    PM.xRightMargin = 0.5 * TWIPS
    PM.yBotMargin = 0.5 * TWIPS
    
    ' Update property.
    LSet PrtMipString = PM
    rprt.PrtMip = PrtMipString.strRGB
 
    Set rprt = Nothing
    
End Sub

The Dimensions of the Reports, Forms and other Objects are measured internally in Twips rather than in Inches or Millimeters. We are allowed to change the measurements on the Property Sheets of Report, Form or other Objects in standard measurements manually, like Inches or Centimeters or in any other regional values. They are automatically converted into Twips internally. But, in programs we have to do that work before changing the values of the Property of objects.


1 Inch = 1440 Twips
1 Inch   = 72 Points
1 Point = 20 Twips
OR 
1 Twip = 1/20 Point

We have used Constant values in the Program for Page Size, Orientation and Margins for simplicity. You can modify the Code to pass these values as parameters along with the Report Name, when the Program is called for each Report. This will give more flexibility and the Program can be called for Reports with different Page Settings.

Next we will see how to change the values on the Columns Tab of the Page Setup Dialog Box of File Menu.

Control Tip Text and Time Delay
External Files List in Hyperlinks
Combo Box Column Values
Drill-Down Inquiry Screen-2
Drill-Down Inquiry Screen

Network And Report Page Setup

noreply@blogger.com (a.p.r. pillai) — Fri, 05 Jun 2009 05:16:00 +0000

When MS-Access Application is installed on a Network; Security is one of the major issues that the Database Developer has to tackle. This includes security of data and objects within the Database and the Database file itself. To learn more about securing a Database on Network click here.

We are going to look into another issue, most often faced by Users and solved temporarily by alternative methods. When Ms-Access Reports are designed for a particular Printer on the Network and when all Users share the same Printer then there are no issues. But, any of the Users tries to print the Report on a different Printer then it is likely that the Report may not print correctly. The User may have access to different Printers on the Network or Local Printer. The default Paper Size, Page Orientation or Margin Settings on these Printers can be different and the Report may not print correctly when printed.

To overcome this issue Users have to preview the Report, if necessary, open the Page Setup Menu and change Paper Size, Page orientation (Portrait or Landscape) and Margins before sending the Report to the Printer. This can be done only if the Report Page Setup Option is provided to the User. If Customized Menus and Toolbars are created for the Application this option probably may not appear in them.

For more details on Customized Menus and Toolbars visit the following Links:
Calendar and Toolbars
Custom Menus and Toolbars
Custom Menus and Toolbars-2

To make life easier for the User we can modify the PrtDevMode Property of the Report through Program to change some of the critical parameters automatically, like Paper Size, Page Orientation (Portrait or Landscape) and Margins before the Report is sent to the Printer. This ensures that the Report will print correctly on any printer.

The PrtDevMode Property of the Report is a 94 Byte long structure with several parameters that can be modified through Program to make the Printer behave the way we want.

To try out an example we will concentrate on two simple parameters for our Report. Our sample Report is designed in Landscape Mode and need to print on A4 (210 x 297 mm) size Pager. We must change the following member parameters of the PrtDevMode Property of the default printer:

Orientation - Valid Values: 1 = Portrait, 2 = Landscape

PaperSize - 9 = A4 (210 x 297 mm)

The above options (Orientation and Paper size) are appearing on the Page Tab of the Page Setup Dialog Box of File Menu. We are trying to change these values at runtime through Program.

Open a new Standard Module (Global Module) in your Database and copy the following Code into the module and save it.


Private Type str_DEVMODE
    RGB As String * 94
End Type

Private Type type_DEVMODE
    strDeviceName As String * 16
    intSpecVersion As Integer
    intDriverVersion As Integer
    intSize As Integer
    intDriverExtra As Integer
    lngFields As Long
    intOrientation As Integer
    intPaperSize As Integer
    intPaperLength As Integer
    intPaperWidth As Integer
    intScale As Integer
    intCopies As Integer
    intDefaultSource As Integer
    intPrintQuality As Integer
    intColor As Integer
    intDuplex As Integer
    intResolution As Integer
    intTTOption As Integer
    intCollate As Integer
    strFormName As String * 16
    lngPad As Long
    lngBits As Long
    lngPW As Long
    lngPH As Long
    lngDFI As Long
    lngDFr As Long
End Type

Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report
    
    ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    
    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation
        ' Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If
        
        ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing
    
End Sub

At the beginning of the Code two new User Defined Data Type str_DEVMODE and type_DEVMODE are declared. The Report PrtDevMode Property Value is moved into this structured data area so that we can modify the required element's value and update them back into the Report before printing.

RGB is defined as a member of the str_DEVMODE with 94 Bytes long String data type. This 94 Byte data area consists of 26 different parameter values of various data types and sizes and defined accordingly under type_DEVMODE data structure. When we move the data from str_DEVMODE (a single block of 94 characters) into type_DEVMODE we can individually change the required value before updating it back into the Report's Page Setup.

NB: If the Database is implemented with Microsoft Access Security then all Users must have Report Design Change Authority to run this procedure.

To try out our Program open one of your Reports with Landscape Page Orientation in Design View.

Select Page Setup from File Menu.

Select the Page Tab on the Dialog Box.

Change Orientation to Portrait.

Change Paper Size to A6.

Save the Report and open it in Print Preview to check how it looks with the change.

Close the Report after viewing.

Create a Command Button on an existing Form or on a new Form and keep the Form in Design View.

Display the Property Sheet of the Command Button (Alt+Enter or View- - > Properties).

Change the Name Property Value to cmdPreview.

Copy and paste the following Code into the Code Module (View - -> Code) of the Form.


Private Sub cmdPreview_Click()
     PaperAndOrient "MyReport"
End Sub

Replace "MyReport" with your own Report Name.

Save the Form and open it in normal view.

Click on the cmdPreview button to run the program to change the Page setup
correctly and open it in Print Preview.

Type PaperAndOrient "MyReport" in Debug Window (Ctrl+G) and press Enter to run the Program directly without the Form and Command Button.

Open the Report again in design view and check whether the wrong changes that you have made manually in the Page Setup Dialog Box, to test the program, have now corrected through the program or not.

Next we will see how to change the values on the Margins Tab of the Page Setup Dialog Box through Program.

External Files List in Hyperlinks
Combo Box Column Values
Drill-Down Inquiry Screen-2
Drill-Down Inquiry Screen
Command Button Animation-2

Filter by Character and Sort

noreply@blogger.com (a.p.r. pillai) — Sat, 18 Apr 2009 07:57:00 +0000

Search, Find, Filter and Sort operations are necessary to organize data into manageable form so that finding required information becomes easy. We have seen some of these actions through the following earlier Posts Titled:

Find or Filter Data on Form
Filtering Data for different Users
Animating Label on Search Success

Now, we will try a different and easy method (for the User) for filtering data by keying in one or more characters into a Text Box and obtain instant result, rather than Typing Search Text on Controls and Clicking on different Buttons or Tool Bars.

When the first character is entered into a Text Box (say the letter F) all Records with names starting with that letter will be filtered instantly. If the filtered list is large then the next character that matches can be entered to further narrow down the list and this method can be repeated.

If Backspace key is pressed to delete the last character or more characters entered in the Text Box; the filtered list will expand progressively and reverse this process. Since, this action is instantaneous the User can get the result the moment she touches a key.

If we need values from one of these filtered records transferred to some other open form then it can be done by writing a routine on the Double-Click Action at Form level as well. Double-Clicking on the left border of a particular record (Record Selector) can trigger this action and can transfer required values into a different open Form. We will see sample Code for this action at the end of this Article.

A sample image of a Form is given below.

The only limitation is that we have created this method for use on a Tabular type Form so that several records can be viewed at one time. The earlier methods (see the references given above) were created for using on Forms with single record view.

Normally, the search or filter operation of this type will be concentrated on a single field, like the Employee Code, Company Code or Company Name; that has more relevance while looking for information.

But, here we will try to go one step further by providing a list of Field Names of the Source Object (Table or Query) attached to the Form (in a Combo Box), so that the User can select the Field that she likes to filter the data on. This will provide more choices and flexibility.

To try this example we need the Customers Table from the Northwind.mdb sample database. If you don't know the location of this file then you can find this in last week's Post, the third item out of the three links given above.

Import the Customers Table.

Design a Tabular Form as shown in the Image given above. I have selected only the first three fields from the Table for this example. If you would like to use all the Fields in the Table you may do so.

Expand the Form Footer Section, if it is not visible (View - ->Form Header/Footer).

If the Tool Box is not visible display it by selecting Toolbox from View Menu.

De-select the Control Wizards Button (the top right one); if it is already in selected state on the ToolBox, so that the Wizard will not start when we select the Combo Box Tool.

Select the Combo Box Tool from the Toolbox and draw a Combo Box on the Footer Section of the Form as shown in the design above.

Change the Caption value of the Child Label of the Combo Box to Filter/Sort Field: .

Click on the Combo Box to select it and display its Property Sheet (View- ->Properties).

Change the following Property Values as given below:

Name = cboFields

Row Source Type = Field List

Row Source = Customers

Column Width = 1"

Create a Text Box below the Combo Box.

Change the Caption Value of the Child Label to FilterText: .

Change the Name Property Value of the Text Box to FilterText.

Create a Command Button to the right of the Text Box and change the following Property Values as shown:

Name = cmdClose

Caption = Close

Display the VBA Code Module of the Form (View - ->Code or Alt+F11).

Copy and Paste the following Code into the Module and Save the Form with the name Customers or any other name you prefer.



Dim x, rst As Recordset

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub FilterText_KeyUp(KeyCode As Integer, Shift As Integer)
Dim i As Integer, tmp

On Error GoTo FilterText_KeyUp_Err
i = KeyCode

Select Case i
    Case 8 'backspace key
        Me.Refresh
        If Len(x) = 1 Or Len(x) = 0 Then
            x = ""
        Else
            x = Left(x, Len(x) - 1)
        End If
        GoSub setfilter
    Case 37, 39 'left and right arrow keys
        SendKeys "{END}" 'ignore action
    Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
        x = x & Chr$(i)
        Me![FilterText] = x
        GoSub setfilter
End Select

FilterText_KeyUp_Exit:
Exit Sub

setfilter:
  Me.Refresh
  tmp = Nz(Me!cboFields, "")
  If Len(Nz(x, "")) = 0 Then
        Me.FilterOn = False
  Else
        Me.Filter = Me![cboFields] & " like '" & x & "*'"
        Me.FilterOn = True
  End If
  Me.OrderBy = Me.RecordSource & ". " & Me!cboFields
  Me.OrderByOn = True
  Me![cboFields] = tmp
  Me.FilterText.SetFocus
  SendKeys "{END}"
Return

FilterText_KeyUp_Err:
MsgBox Err.Description, , "FilterText_KeyUp()"
Resume FilterText_KeyUp_Exit

End Sub

Private Sub Form_Close()
    Application.SetOption "Behavior Entering Field", 0
    Me.FilterOn = False
    Me.OrderByOn = False
End Sub

Private Sub Form_Load()
    Application.SetOption "Behavior Entering Field", 2
    Set rst = Me.RecordsetClone
    Me!cboFields = rst.Fields(0).Name
    rst.Close
End Sub

Open the Form in normal View.

The CustomerID Field will appear as default value in the Combo Box control.

Click on the Text Box below the Combo Box to set the focus on it.

Type the Character F and you will see that all the records with CustomerID values starting with the letter F is filtered.

Type the Character R next to the character F in the FilterText control and the list narrows with starting CustomerID characters FR. You can further filter and narrow down the list progressively this way, if needed.

Press Backspace Key to delete the character R and to leave F character alone in the Text Box. The list will expand and all items starting with the letter F are back in the List.

Press Backspace Key again to delete the character F from the Text Box. This time the Filter action is removed and all the records are back in the Form.

NB: The Filter Criteria Text Values are limited to the Characters 0 to 9 and A to Z only.

If you want to try the Filter action on one of the other two fields you may select that Field's name from the Combo Box above before trying the filter action explained from Step-19 onwards.

Since the CustomerIDs are created based on the CompanyName Field; trying on both those fields will give almost same result.

You may try it on the third Field ContactName for different results.

The sample code given below assumes that you have Double-Clicked on the left border of a record (Record Selector) to transfer the CustomerID code into CusID field of another open Form; Orders.


Private Sub Form_DblClick(Cancel As Integer)
Forms!Orders![CusID] = Me![CustomerID]
End Sub

Combo Box Column Values
Drill-Down Inquiry Screen-2
Drill-Down Inquiry Screen
Command Button Animation-2
Cardinal Text Format in Access

Animating Label on Search Success

noreply@blogger.com (a.p.r. pillai) — Sat, 11 Apr 2009 07:07:00 +0000

We have seen how to find or filter records using values entered into a text box. We have used three different methods to find or filter data after entering text or numeric search values into a text box. But, we have not used any visual indicator to announce that the search operation was successful or not. If the search operation was successful then the record that matches the criteria will become current or filtered, that was the only clue to know that the search was successful.

Here, we will see how to animate a Label few times with an indicative message; announcing the search operation was successful or not..

To try an example with the sample VBA Code given below; import the Customers Table and Customers Form from Northwind.mdb sample database. If you are not sure, where to find this database, check the location C:\Program Files\Microsoft Office\Office11\Samples Folder (in Office2003).

Open the Customers form in design view.

Expand the Form Footer Section to create few controls for Quick Search operation and for our magic label animation. Check the sample image of the Form given below with controls added at the Footer of the Form:

Draw a Label on the Form Footer Section and change its Caption to Customer ID to Find: .

Draw a Text Box to the right of the Label and change its Name Property (View - -> Properties or Alt+Enter) Value to xFind.

Create a Command Button to the right of the Text Box and change its Name Property Value to cmdFind. Change the Caption of the Command Button to << Find.

Create a Label below the Text Box and change the following Property Values shown against each Property:

Name = lblMsg

Caption = x

Visible = False

Display the Code Module of the Form (View - -> Code or Alt+F11).

Copy and Paste the following Code into the Form Module and save the Form:



Dim backcolor As Long, forecolor As Long
Dim L As Integer

Private Sub cmdFind_Click()
'---------------------------------------------------------------
'Author : a.p.r. pillai
'Date    : April-2009
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------
Dim m_Find, rst As Recordset

On Error GoTo cmdFind_Click_Err


backcolor = -2147483633

m_Find = Me![xFind]
If IsNull(m_Find) Then
    Exit Sub
End If
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID = '" & [m_Find] & "'"
If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    Me.lblMsg.Caption = "** Successful ***"
    forecolor = 16711680
    Me.lblMsg.forecolor = forecolor
Else
    Me.lblMsg.Caption = "Sorry, Not found...!"
    forecolor = 255
    Me.lblMsg.forecolor = forecolor
End If
L = 0
Me.lblMsg.Visible = True
Me.TimerInterval = 250

cmdFind_Click_Exit:
Exit Sub

cmdFind_Click_Err:
MsgBox Err.Description, , "cmdFind_Click()"
Resume cmdFind_Click_Exit
End Sub

Private Sub Form_Timer()
L = L + 1
Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        Me.lblMsg.Visible = True
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        Me.lblMsg.Visible = False
    Case 19
       Me.lblMsg.forecolor = forecolor
       Me.lblMsg.Visible = True
       Me.TimerInterval = 0
End Select
End Sub

Private Sub xFind_GotFocus()
     Me.lblMsg.Visible = False
End Sub

Open the Customers form in normal View to try out our creation. When you open the form the Label that we have created below the Text Box will not be visible.

Type record number 55 in the Record Navigation control below.

Highlight the CustomerID code and press Ctrl+C to copy the Customer Code into Clipboard.

Type 1 in the record navigation control to make the first record as current.

Click on the Text Search Control at the Form Footer to select it and press Ctrl+V to Paste the Customer Code from Clipboard.

Click on the Command Button to find the first record that matches the Customer Code.

CustomerID

** Successful **

Make the first record as current again and make some change in the CustomerID in the search Text Box so that the search operation will fail with the modified Value.

Click on the Command Button to search for the wrong CustomerID Code.

This time we will get the Sorry, Not Found…! Message flashing nine times and the message stays on the screen.

In this example we have made the label visible and hidden intermittently within an interval time of 250 Milliseconds. This method is ideal on all type of Forms with different backgrounds, like the one we have used with a Background picture.

We can make the Label flash by changing the text Color (rather than hiding and displaying the label as we did in the above example) with the same timing mechanism, if the Form background have a particular Color.

Create a copy of the Customers Form with the name Customers2.

Open the Form in Design View and display the Form’s Property Sheet.

Find the Picture Property and delete the .WMF image file path name. This action will display a message asking to reconfirm the delete action and respond to remove the entry.

Remove the After Update property value also. There is a Macro attached here to run on the after update event of the form.

Without closing the Property Sheet Click on the Footer of the Form and change Back Color Property value to -2147483633. This is the normal Form background color, when you open a new form in Design View.

Click on the label below the Text Box and change the following Property Values:

Back Style = Transparent

Special Effect = Flat

Border Style = Transparent

Replace the following Select Case …. End Select code segment with the Code given below, in the Sub Form_Timer() Event Procedure:


Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        Me.lblMsg.forecolor = forecolor
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        Me.lblMsg.forecolor = backcolor
    Case 19
       Me.lblMsg.forecolor = forecolor
       Me.lblMsg.Visible = True
       Me.TimerInterval = 0
End Select

Case…

Color

Font

Blue Color

Red Color

Case…

Font Color

Background Color

Case…

L = 19

Interval Timer

Font Color

Blue

Red

Save the Form and open it in normal view.

Repeat the procedure explained under Steps-10 to 17 above.

This time the Label will flash with Blue Color when the search result is successful and with Red Color when the search fails. In both situations the colors are exchanged with the background color intermittently with the Form Background Color Value -2147483633.

If you want to slow down the action then increase the interval time value from 250 Milliseconds to a higher Value or reduce it to flash the Label faster.

Drill-Down Inquiry Screen-2
Drill-Down Inquiry Screen
Command Button Animation-2
Cardinal Text Format in Access
Custom Report Wizard

Form Background with Gradient Color

noreply@blogger.com (a.p.r. pillai) — Sat, 04 Apr 2009 13:23:00 +0000

Form background pictures are not only used for giving the Form a different look but also for other useful purposes as well.

For example, if the user is keying in data from a pre-printed Document like a Telephone Bill or Electricity Bill etc.; it is a good idea to scan an image of this Document and use it as the Data Entry Form’s background picture. The Data Fields can be positioned in appropriate locations on the image matching the physical Document so that it will be easier for the user to find information on the Document and key-in exactly on the same locations on the Form.

Microsoft Access Form Wizard have several images to use as background picture, but I didn’t find the one that I need; the one with a gradient background. So, I thought I will make one of my own and use it.

The steps that I have taken are given below to create the image for the Form Background picture.

Opened a new Microsoft Word Document and gone through the following steps to create a Gradient Bit Map Image:

Selected Format - ->Background - -> Fill Effects.

Under Colors options selected One Color.

Clicked on the Color 1 drop down control and selected a light color.

Used the Darker/Lighter slider control to adjust the color tone.

Selected the Horizontal Radio Button under Shading Styles.

Clicked on one of the Style Variants.

Clicked OK to apply the selected shade as Background of the Word Document.

Maximized the Word Document Window.

Pushed the Prt Scrn key (at the top row right side). The full screen image is now captured and copied to Clipboard.

Opened MS-Paint Program. You can find this in Start - - > Programs - - > Accessories.

Displayed the Toolbox (View - ->Toolbox or press Ctrl+T).

Selected the Select Tool (the one at Top right with a rectangle picture).

Drawn a rectangle from left top corner of the gradient image area to right bottom corner to select the image large enough for my Form's width and height. If you find the width is not enough to cover your form then change the Page Orientation of the Word Document into Landscape before capturing it in MS-Paint.

Used Ctrl+C to copy the selected area into Clipboard.

Clicked outside the selected area to deselect the earlier selection.

Selected Clear Image from Image Menu (Ctrl+Shift+N) to clear the Canvas. Inspected the right Scroll Bar to check whether it is positioned at the top or not. It was positioned slightly down so I dragged it up to the top. Similarly adjusted the Scroll bar at the bottom to position at the left end.

Pressed Ctrl+V to paste the copied image from the Clipboard.

Clicked outside the pasted image to de-select it.

Dragged the Sizing control at the bottom of the Canvas up and stopped at the edge of the pasted image to eliminate the white area of the canvas.

Similarly adjusted the right side of the image.

Selected Save from File Menu to save the image at a convenient location with a name.

If you were with me so far then we will enter into the next stage of implementing the gradient image as background picture. But, there are some limitations to use images like this one as background picture, but we can overcome this with a little trick. If you are thinking about the increase in size of the database then you are in the right direction. If you are considering the idea of saving the image in .jpeg or .GIF format; then you are far ahead of me. Saving the image in .GIF format in MS-Paint will not give you a good quality image. If you have the software like Adobe Photoshop then you can make a better quality .GIF image.

We will do another trick to use the .bmp image itself to maintain the background image quality without affecting the database size.

But, first let us see how to apply the gradient image as the Form's Background picture.

Before opening your database if you would like to check the present size of your database you may open Windows Explorer, check and note down the current size of the database. I suggest you better Compact the Database before you take its file size. You may check it again after the background picture is added to a Form to find the difference.

Open your Database and open a Form in Design View or create a new one.

Display the Property Sheet of the Form (View - -> Properties).

Find the Picture Property and click on it.

Click on the build button (…) at the right side of the Property Sheet and click on it to browse to the location of the image you have saved and select the image.

The next four Properties are used for formatting and displaying the Background Picture.

Picture Type

Picture Size Mode

Picture Alignment

Picture Tiling

The Picture Type Property has two choices Embedded or Linked to select from and the default value is embedded. The embedded option is provided for invoking the default Image Editor Program from within MS-Access by double-clicking on the image and to make changes to the image, if needed. To facilitate this; the necessary image editor program invocation information is also stored along with the image in the database, resulting a substantial increase of the database size for each image you add this way on your Form or Report. But, the background image inserted this way cannot be edited by double-clicking on the background as we do on the inserted image as an OLE object, on the Form.

A better choice is Linked option that will keep the image outside and the database size is not affected. In either case the image should go along with your database in the same location of the image wherever you take your database.

The Picture Size Mode have three options Clip (default) displays the picture in actual size wherever you position it by setting one of the Picture Alignment property Values (Top Left, Top Right, Bottom Right, Bottom Left, Center and Form Center). The Center option will position the image vertically and horizontally centered on the Form and the Form Center option will position the image vertically centered at the left side.

The Stretch Picture Size Mode option will stretch the image to fit the dimension of the Form and result in distortion of the image, if it is a picture of something.

The Zoom Picture Size Mode option will attempt to maintain the right proportions of the image when stretched to match the dimensions of the Form. But both the Stretch and Zoom options will show stretch marks when the Form is restored to its original size. This is very much evident when a .jpeg or .GIF image is used.

We can modify our earlier .bmp file to a one pixel width image and Tile it across the Form that will give a good quality gradient picture effect and the image size also will be small.

Open the earlier saved .bmp image in MS-Paint Program.

Select Attributes… from Image Menu.

Change the Width Value to 1 and let the Height value remain without change.

Check the Units selected is Pixels.

Save the image with a different name.

Change the name of the background image to the new image in the Picture property of the Form.

Caution:

Picture Size Mode

Stretch

Zoom

Set the Picture Tiling Property Value to Yes and save the Form.

Open the Form in Normal View and enjoy your creation.

Drill-Down Inquiry Screen
Command Button Animation-2
Cardinal Text Format in Access
Custom Report Wizard
Custom Made Form Wizard

MS-Access and Reference Library

noreply@blogger.com (a.p.r. pillai) — Fri, 27 Mar 2009 17:30:00 +0000

We are using several Object Libraries besides the default References Library during the course of developing an Access Database. Microsoft Data Access Objects (DAO), Microsoft ActiveX Data Objects (ADODB), Visual Basic for Application (VBA) and so on. These are essential to create Data Processing Routines in VBA.

I have briefly touched this subject in my first Article in this site: Command Button Animation and advised to link these files to your Database manually before attempting to run the Program given in there. A List of essential Library Files and the procedures to attach them to the Database manually also explained there.

Some of these Library References like: Microsoft Access 9.0 Object Library, Visual Basic for Applications, OLE Automation are attached automatically when a new database is created and others are added manually. We add new reference Libraries to use interesting features extended by them, which are not available in Microsoft Access.

We have used Microsoft Office Library (Office) to create customized Message Boxes, Option Baloons and Check-Boxes etc. with Office Assistant to capture user responses in an interesting way. We have created a Wizard to select Office Assistant of your choice in Access, without using the Option provided by Office Assistant.

Databases and Tables can be accessed in VBA only when we add Microsoft Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADODB) Library Files to Access Database.

If you have written some common Functions of your own then you can save them in a separate database on Server and attach it as a Library Database so that you don't have to copy and paste those Functions in other Databases.

You can use Object Browser of VBA (View - -> Object Browser) to browse the Properties and Methods extended by a particular Library.

Most of the time we use a fixed set of Library References in our Database and these are attached manually (Tools - ->References) one by one after opening the VBA Editing Window. Even though this is only a one-time exercise we can automate this and it is useful to quickly restore the links if they are lost as well.

First let us look at the few steps that we need to take to accomplish this.

A.     Prepare a list of Library References and save it in a Text File, on Server if you are developing databases to use on a Network.

B.     Write a VBA routine to read this list from the Text File and attach them to the Project.

A.     To prepare the required Object Library References List we must know what are these files and where on the Disk they can be located.
Assume that we need to know what is the physical file name and location representing the Reference Library Description Microsoft DAO 3.6 Object Library in the Available References list.

To find out this manually, open VBA Module Window (View - -> Code or Alt+F11), select Tools- - >References and find the item with the description Microsoft DAO 3.6 Object Library among the list of other Microsoft Reference Library Files, see the image given below.

When you select the file; its Description, the physical File Pathname and Language are displayed. You can write down the Pathname to prepare the list, if you want to. But, we will do half of the task differently. We can attach the required files manually and prepare the list with a small VBA Routine.

Following is a list of References that I use regularly to start with my Projects and we will use them as an example:

The first three items will be automatically selected by MS-Access when you create a new Database. Others must be added manually.

Attach the above list of Library References manually.

Copy and Paste the following lines of Code into a Standard Module of your Project and save it.


Public Sub ReferenceList()
Dim Ref As Reference
For Each Ref In Application.References
   Debug.Print Ref.FullPath
Next
End Sub

Press Ctrl+G to display the Debug Window (Immediate Window), if it is not already visible.

Click in the middle of the Code and press F5 to Run it and print the following Path Names of the selected Reference Library Files in the Debug Window.

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB

C:\WINDOWS\system32\stdole2.tlb

C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

C:\Program Files\Common Files\system\ado\msado15.dll

C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

D:\MDBS\aprRefLib.mde

The internal names (Project Names) of the above Library Files are different and it is important to know about it because if we need to remove any of this library items from the Current Project then you must use the Project Name in programs rather than using the above file names.

When you create Ms-Access database with the name abcd.mdb; by default the same name abcd will be inserted into the Project Name control in the database. You can check the Project Name of your database selecting Tools Menu in the VBA Editing Window. You will find a Menu Option like abcd Properties. You can open this option and set a different name in the Project Name control, if needed.

Note: But, it is important that you should not use any names of your Standard Modules for the Project Name.

You can read this Property Value in programs or in Debug Window using the statement x = Application.GetOption("Project Name").

Or, modify the Project Name with the statement like Application.SetOption "Project Name", "Myabcd"

The List of Library Files given above; also have unique Project Names and these are the Internal Object Library Reference names that will appear in our database, when linked. You can check their names by opening the Object Browser (View - -> Object Browser or F2) and clicking on the drop down control. Among the list you can see that your own current database Project Name abcd is also appearing in the list.

Let us inspect the Project Names of the first three files in the list given above.

Access

Stdole

Project Name

abcd (current database project name)

aprRefLib (you can read this as your own Function Library Project Name and I will come to that later in this Article.)

Leaving aside the above five items we are left with the following four Reference Libraries selected out of the eight items listed above to attach to our new Projects automatically:


C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll 
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
C:\Program Files\Common Files\system\ado\msado15.dll
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Open NotePad.Exe, Copy and Paste the above File Names and save it on your Server common location where all your MS-Access Projects are installed.

Let us call the Server PathName (the Location and File Name) of the target text file as

\\hosfs03\InhouseSys\CommonLib\RefLib.txt

B. The first stage of our preparation is over. The next thing what we need is a VBA Program that can read the above Text File contents and link the Reference Library files shown in the second image above automatically to your new Project.

I will give the VBA Routines below, but you have two choices to decide where to place the code so that it is easier to use in all of your Projects without duplicating the code. The second choice is to copy the Code into your new Projects and run it from there, which I don't advise you to do. Instead take the first option and do little ground work now by following the few steps given below. This will make your future development processes easier without duplicating common Programs in all your other Projects.

If you have not started to build a Reference Library Database of your own so far then this is the time to start doing it and you will find how useful it is. It is not that hard to do it either.

Create a new Database with the name MyLib.mdb and save it to your Server Common Location. Let us take this location address same as the one we have saved our Text File with the list.

Open the VBA Editing Window (Alt+F11).

Create a Standard Module (Insert - ->Module) to create an empty Code Module with the name Module1. You may change the name of the Module after displaying its Property Sheet (View - ->Properties Window).

Copy and Paste the following Code into the Module; save and close the VBA Editing Window.


Public Function AddReferences()
Dim j As Integer, i As Integer, msg As String
Dim Ref As Reference, RefObj As Object
Dim RefPath As String, LibName As String
Dim LibPath As String, libAttached() As String
Dim refcount As Integer, chk_flag As Boolean
Dim lib_Attached As String, validate As Boolean

Const LibraryList As String = "\\hosfs03\InhouseSys\CommonLib\RefLib.txt"

On Error GoTo AddReferences_Err

validate = Ref_Retain_Remove()
If validate = False Then
   msg = "Errors Encountered in Validation check, Program aborted. "
   Exit Function
End If

Set RefObj = Application.References
refcount = Application.References.Count
ReDim libAttached(1 To refcount) As String
i = 0
'Prepare list of exiting attached Library Files
For Each Ref In RefObj
    i = i + 1
    libAttached(i) = Ref.FullPath
Next
'Open text file with List of required Reference Library files
Open LibraryList For Input As #1
msg = ""
Do While Not EOF(1)
    Input #1, LibPath
    chk_flag = False
    For j = 1 To i
'check for missing cases
        lib_Attached = libAttached(j)
        If libAttached(j) = LibPath Then
            chk_flag = True
            Exit For
        End If
    Next
    If chk_flag = False Then
'Reference found missing, add to the Project
        Set RefObj = Application.References.AddFromFile(LibPath)
        msg = msg & LibPath & vbCr
    End If
Loop
Close #1

If Len(msg) <> 0 Then
    msg = "Following References Attached: " & vbCr & vbCr & msg
End If

MsgBox msg, , " AddReferences()"

AddReferences_Exit:
Exit Function

AddReferences_Err:
MsgBox Err.Description, , "AddReferences()"
Resume AddReferences_Exit
End Function



Public Function Ref_Retain_Remove() As Boolean
Dim Ref As Reference, Reflist(), exclusion(1 To 5) As String
Dim ref_count As Integer, strRefName As String
Dim i As Integer, j As Integer, chk_flag As Boolean

On Error GoTo Ref_Retain_Remove_Err

exclusion(1) = "VBA"
exclusion(2) = "Access"
exclusion(3) = "stdole"
exclusion(4) = Application.GetOption("Project Name")
‘Replace this line with your own Reference Library Name
exclusion(5) = "aprRefLib"

ref_count = Application.References.Count

If ref_count > 4 Then
    ReDim Reflist(1 To ref_count)
    i = 0
    For Each Ref In Application.References
       strRefName = Ref.Name
       chk_flag = False
       For j = 1 To 5
          If strRefName = exclusion(j) Then
              chk_flag = True
              Exit For
           End If
        Next
        If chk_flag = False Then
            i = i + 1
        'Collect the Reference Library Project Names, if any, other than
        'the Names in the exclusion list to remove them
        'before attaching the new ones, to avoid Project Name conflict.
            Reflist(i) = Ref.Name
        End If
    Next
    ReDim Preserve Reflist(1 To i)
'Remove the collected Reference Libraries
    For j = 1 To i
        Set Ref = References(Reflist(j))
        References.Remove Ref
    Next
End If

Ref_Retain_Remove = True

Ref_Retain_Remove_Exit:
Exit Function

Ref_Retain_Remove_Err:
MsgBox Err.Description, , "Ref_Retain_Remove()"
Ref_Retain_Remove = False
Resume Ref_Retain_Remove_Exit
End Function

Note: Before closing the Database, if you would like to save a copy of the Library Database in a Compiled state you can create it by running Tools- ->Database Utilities - -> Make MDE File… option and name the database as MyLib.MDE. You may save it at the same location of the .mdb file and move the .mdb file to your local drive or in your private server location where access to others are forbidden. When you add more common routines into this Library file you can recompile it and replace the earlier .MDE file so that the new programs can be made available to all your Projects without making any changes in them. You can attach your Library Database MyLib.mde along with others to all of your future and current running Projects.

We have two programs; the second one is to validate the existing attached References and remove them except the essential ones mentioned above to avoid conflicts.

The preparations are in place and we are going to do a Trial Run.

Create a new Database.

Open the VBA Editing Window (Alt+F11).

Open the Debug Window (Immediate Window) – Ctrl+G.

Type the following line in the Debug Window and press Enter so that we can attach your own Common Library File MyLib.mde and Run the main program AddReferences() from there:

Note: Change the Server Location address to match with your own.

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

If you select Tools- ->Reference; you can see that your own Library File is now attached to your Project.

We can now call the AddReferences() program from your Function Library from the Debug Window itself and attach other Library Files List we have created in the Text File.

Type the following line in the Debug Window and press Enter key to do that:

AddReferences

You can now check the Reference Library List to re-confirm that all the required files are in place. In your next Project all you need to do after creating a new Database is to type the following two lines in the Debug Window and press Enter key to add all required Reference Libraries to your Project at once:

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

AddReferences

Command Button Animation-2
Cardinal Text Format in Access
Custom Report Wizard
Custom Made Form Wizard
Rounding Function MRound() of Excel

Inputbox and Simple Menus

noreply@blogger.com (a.p.r. pillai) — Fri, 20 Mar 2009 13:57:00 +0000

We have already learned how to create Menus and Toolbars through the following Articles:

Calendar and Toolbars
Custom Menus and Toolbars
Custom Menus and Toolbars-2

I think InputBox() function is one of the few things that we come across when start learning VBA and discard it later altogether when we see more exiting objects like MsgBox(). Probably its usage is not properly understood and it doesn't deserve this neglect. The usage of Inputbox() function is very simple like MsgBox() but seldom used in programs to gather user responses. Instead, MsgBox() is the most preferred control, which has a variety of options available to configure it with different options of Command Buttons. But these options are like OK, Cancel, Retry, Yes, No etc. MsgBox cannot be programmed to make selections from user defined Options like the facility provides by InputBox().

We have learned how to use Office Assistant with preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using Office Assistant with Check-Boxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and needs some time to understand the intricacies. But, once the program is in place the usage is very simple and can be inserted freely in your Code across projects, when placed in a library database and when linked to your other projects.

Coming back to the Inputbox() function; let us look into a simple example in a sub-routine. A Report Command Button on the Main Switchboard when clicked; a small menu is displayed asking the User whether she would like to get (1) Report Preview or (2) Print the Report directly to the Printer or (3) Exit, if there is a change of mind.

The simple usage of InputBox() function Syntax is given below:

X = InputBox("Message Text","Title",Default Value)

The Title and Default Values parameters are Optional. There are other optional values as well. You may check for them in the Microsoft Access Help Documents.

The Message Text is displayed on the body of the Form and the Title text appears on the Title Bar of the Control. We have used 1 as third Parameter (Default Value) and that value is inserted in the Text Box as shown in the following example image:

The Code that brings this Form up on a Command Button Click EventProcedure is given below.


Private Sub cmdRpt_Click()
Dim varResponse as Variant

varResponse = InputBox("(1) Print Preview (2) Print (3) Exit", "Select 1/2/3",1)

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

When the User makes a selection as per the Options provided, the selected value is tested and executes the actions programmed in the Select Case….End Select statements.

Note: The Inputbox() function brings up a Form with its Border Style Property Value set as Dialog so that the Minimize/Maximize & Restore Buttons removed from the Title Bar except the Window Close control. You are not allowed to click on any other Form or Objects in the Application window before you are done with the InputBox() Function, indicating that this Form's Modal Property is set to Yes. We have familiarized the usage of Pop up and other Property settings of the Form in our earlier discussions on Form Properties in the following Articles:

Forms and Custom Properties
Synchronized Floating Pop-up Form
Positioning Pop-up Forms
Change Form Modes on User Profile

While using Inputbox() this way in programs we must take care of few things to avoid some undesirable side effects and pay attention to give the Menu a better look as well.

First, we will take the side effects part. We have provided Option 3 to close the Dialog Box normally, if the User has a change of mind after bringing up the Menu. But, she may ignore option 3 in the Menu and may use the Window Close control on the Title bar or click on the Cancel Command Button instead; that can invite trouble in the Code.

Since we have defined the Option Values in the Menu as Integer (1,2 and 3) naturally we will think about using an Integer Type Variable to record the response of the User. But, the User may click on the Cancel Command Button or Window Close Title Bar control, without selecting any of the options displayed, to close the Window. In that case the InputBox() Function will return an Empty String ("") as response. If we have defined an Integer Variable to accept the returned value, then this will end up with a Type Mismatch Error and the program will crash.

We can use a string variable to accept the Empty string result to avoid this problem and the Select Case…. statement will work without modification even though we are testing for Integer values rather than in Case "1" style. A reasonable Variable Type selection to record the user response in these circumstances is a Variant Type that can accept any data type.

With a little trick we can make the program to ignore the Cancel Button and Window Close Title Bar control Clicks and force the user to make a selection from the Menu Options only, if she want to get out of the Menu normally.

We have inserted 1 as third Optional Parameter Value to take care of the OK Command Button Click by the User just in case she doesn't type a selection value from the Menu Options and simply hits the OK Button. If she types one of the Option values in the Text Box and hits the Enter key, as we expect her to do, that will return the value selected into the varResponse Variable and the InputBox control will close normally.

We will modify the above Code:

to make it a better looking real Menu

to ignore the Cancel Command Button and Window Close Title Bar control clicks.

To prepare the Menu we will define a string Variable and write the following expression before inserting it into the InputBox() Function:

Dim strMenu as String

strMenu = "1. Report Preview" & vbCr & vbCr
strMenu = strMenu & "2. Print Report" & vbCr & vbCr
strMenu = strMenu & "3. Exit"

We will put the InputBox() Function within a Do While…Loop with a condition so that the Function will keep repeatedly executing if the user doesn't make a valid selection from the Menu and discourage her from Clicking Window Close or Cancel Buttons to get out of the Dialog Box.

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3
varResponse = InputBox(strMenu, "Select 1/2/3",1)
Loop

After the above changes the Menu will look like the following image:

The modified Code is given below:



Private Sub cmdRpt_Click()
‘------------------------------------------------------------
‘Author : a.p.r. pillai
‘Date : March-2009
‘URL  : www.msaccesstips.com
‘All Rights Reserved by www.msaccesstips.com
‘------------------------------------------------------------
Dim varResponse As Variant
Dim strMenu As String

strMenu = "1. Report Preview" & vbCr & vbCr
strMenu = strMenu & "2. Print Report" & vbCr & vbCr
strMenu = strMenu & "3. Exit"

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3
    varResponse = InputBox(strMenu, "Select 1/2/3", 1)
Loop

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

Probably the next question comes into one's mind is, do we have to use the Menu Options always in the form 1,2,3 etc., can we use alphabets instead, like

R. Report Preview
P. Print Report
E. Exit ?

Yes, we can with few changes in the Code as given below:

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr
strMenu = strMenu & "E. Exit"

varResponse= ""

Do While instr(1, "RPE",varResponse)=0 or len(varResponse)=0
   varResponse = InputBox(strMenu, "Select R/P/E", "R")
Loop

The Select Case… statement must be changed to:

Select Case varResponse
Case "R"
   DoCmd.OpenReport "Orders", acViewPreview
Case "P"
   DoCmd.OpenReport "Orders", acViewNormal
Case "E"
   Exit Sub
End Select

The changed Code with alphabets as Options is given below:


Private Sub cmdRpt_Click()
‘------------------------------------------------------------
‘Author : a.p.r. pillai
‘Date : March-2009
‘URL  : www.msaccesstips.com
‘All Rights Reserved by www.msaccesstips.com
‘------------------------------------------------------------
Dim varResponse As Variant
Dim strMenu As String

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr
strMenu = strMenu & "E. Exit"

varResponse = ""

Do While InStr(1, "RPE", varResponse) = 0 Or Len(varResponse) = 0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")
Loop

Select Case varResponse
    Case "R"
        DoCmd.OpenReport "Orders", acViewPreview
    Case "P"
        DoCmd.OpenReport "Orders", acViewNormal
    Case "E"
        Exit Sub
End Select

End Sub

Cardinal Text Format in Access
Custom Report Wizard
Custom Made Form Wizard
Rounding Function MRound() of Excel
Custom Calculator and Eval() Function

Change Form Modes on User Profile

noreply@blogger.com (a.p.r. pillai) — Fri, 13 Mar 2009 09:30:00 +0000

We have seen how to define a Pop up Form and how it can be made to appear on a particular position on the Application window, when it is opened. We have also seen that the Form behavior is controlled by setting or modifying the Property values of the Form. In those cases we have changed the Form's Pop up, Border Style and Auto Center Property Values manually during design time.

Here, we will try something different and will attempt to change the Form opening Modes (Data Entry Mode or Edit/Search Mode) at run time automatically based on the identity of the Current User of the Form. When the Database is shared on a Network the same Form can be opened by different Users and the Form should behave differently for each User or Users Group.

For Example, a single Form can be used for Data Entry by one group of Users, who are allowed only to key-in new records and others are allowed to View, Edit and Search but not allowed to add new records. Data Entry User can also edit data that he/she keys-in during the current data entry session.

The Open Mode change can be done by checking the Security User Account or Group Account name of the Current User and by changing the Data Entry, Allow Additions Property Values.

We need a small VBA program to check the Current User's Group Account information to determine whether he/she belongs to the DATAENTRY Group Account or not (the User Workgroup name DATAENTRY is used here as an example only). Accordingly the program will set the Mode of the Form for the current session.

The VBA Routine is given below:


Public Function ChangeFormMode(ByVal strFormName As String, ByVal UserGroupName As String)
'----------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2009
'All Right Reserved by www.msaccesstips.com
'----------------------------------------------
Dim wsp As Workspace, strUser As String
Dim j As Integer, frm As Form
Dim chkFlag As Boolean, GrpCount As Integer

On Error GoTo ChangeFormMode_Err

Set frm = Forms(strFormName)
strUser = CurrentUser
Set wsp = DBEngine.Workspaces(0)
GrpCount = wsp.Users(strUser).Groups.Count
'checks whether 'DATAENTRY' Group Name is present
'within the User's Group Account Names.
chkFlag = False
For j = 0 To GrpCount - 1
    If wsp.Users(strUser).Groups(j).Name = UserGroupName Then
        chkFlag = True
        Exit For
    End If
Next
If chkFlag Then
    frm.DataEntry = True
    frm.AllowAdditions = True
Else
    frm.DataEntry = False
    frm.AllowAdditions = False
End If

ChangeFormMode_Exit:
Exit Function

ChangeFormMode_Err:
MsgBox Err.Description, , "ChangeFormMode"
Resume ChangeFormMode_Exit
End Function

Copy and Paste the above program into a Global Module (Standard Module) in your database and save it. The program is called from the Form_Load() EventProcedure of the Form with the Form Name and the User Group Account name as parameters as follows:


Private Sub Form_Load()
   ChangeFormMode Me.Name, "DataEntry"
End Sub

A security User Account may belong to one or more security Group Accounts besides the default Users Group Account, which all the Users belong. Therefore, we need to check through the User's Security Group Account list, whether he/she belongs to the DataEntry Group or not and if found then change the Form's open Mode accordingly.

Note: This method to work, the Database must be secured by implementing MS-Access Security and assume that the Users are grouped under different Work Groups like Data Entry Group, Team Leaders Groupb or Managers Group and so on. To learn more about Microsoft Access Security visit the pages under Security in the Main Menu.

If your machine is not configured to use a common MS-Access Work Group Information file or if you have not implemented MS-Access Security using the local Work Group Information File then you are automatically log-in using the default User Account Admin, a member of the Admins and Users Group. This is happening silently and you are not asked to enter a UserID or Password. In that case you can try this procedure by passing the Admins or Users work group name as parameter to the above Program.

But, if all Users need both features inter-changeably at their will then this can be implemented with the use of a Command Button on the Form. Since, this procedure is manually controlled, requirement of checking the Security User Account or Group Accounts doesn't arise.

All we need is a Command Button at the Footer of the Form, set with specific Caption Value which can be checked and changed on every Click on the Command Button and switch the Form Mode to Data Entry or Normal.

Assume that the Form Opens in Edit/Search Mode by setting the following Property Values at design time:

Data Entry = False

Allow Additions = False

The Caption of the Command Button will be set as Data Entry indicating that if the User wishes to change the Form into Data Entry Mode then she may click on the Command Button. Since, the same Command Button click is needed to switch the Form into one of these two Modes we must check the Caption of the Command Button to determine what is the User's intention. She may click on the Command Button repeatedly too.

On every click we must check the Caption Value and change the Mode of the Form as well as the Caption of the Command Button to Edit/Search or Data Entry interchangeably.

We can implement this with few lines of Code on the Form's Class Module itself. The Command Button's Name Property Value is cmdEdit.


Private Sub cmdEdit_Click()
If Me.cmdEdit.Caption = "Data Entry" Then
     Me.cmdEdit.Caption = "Edit/Search"
     Me.DataEntry = True
     Me.AllowAdditions = True 
Else
     Me.cmdEdit.Caption = "Data Entry"
     Me.DataEntry = False
     Me.AllowAdditions = False
End If
End Sub

The above technique we have used for a different function on a Form and you may take a look at that Article with the Title: Double Action Command Button.

Custom Report Wizard
Custom Made Form Wizard
Rounding Function MRound() of Excel
Custom Calculator and Eval() Function
Data Editing and Security Issues

Positioning Pop Up Forms

noreply@blogger.com (a.p.r. pillai) — Fri, 06 Mar 2009 14:44:00 +0000

We have seen the Pop up form in action last week in an Article with the Title: Synchronized Floating Pop up Form and I hope you have downloaded the Demo Database as well.

I know, I don't have to talk at length about Forms and their importance in a database, because that is the first piece of object we would like to lay our hands on, the moment we decide to start learning MS-Access and wants to put all kinds of fancy objects all over the Form. MS-Access Forms are far better than Forms designed in Visual Basic.

There was an interesting incident in our office long back. A Visual Basic Application developed by our Computer Department was installed in one of my colleague's machine. My friend got a brief introduction from the developer before he left the scene. My friend started exploring the new Application.

I was sitting few workstations away from him and after about half an hour he called me and asked for some help. I was also very curious to have a look at the new Application. When I set my eyes on the screen I realized why he called me and he confirmed that too. I couldn't resist a smile on my face and pat myself on my back for working with MS-Access Forms, even though I knew VB can do many things, which you cannot do in Access.

Few years ago most of the Computer Monitors were used with 640 x 480 Resolution and the Users preferred to maintain this way because dBase and Foxprow 2.5/3.0 based Applications were also running on their machines.

The VB Application's Main Switchboard Form overlaps on all four sides of the Screen on my colleague's machine and some of the Objects like Command Buttons and Option Group etc. at the edges of the Form are only partially visible on the screen. The Form was designed on a machine with a higher resolution without knowing where he is going to install it and ended up this way.

As far as I know, VB Form doesn't have any Control Buttons (Min/Max, Restore and Close) on the Title Bar; no sizing handles, no Header/Footer Sections or able to define Pages and it looked like an oversize wooden board placed behind the screen frame.

I really felt few inches taller when positive remarks came from the Users of my MS-Access Databases.

Coming back to the point, we can change the behavior of the Form by setting its Property Values and we have seen several of them through earlier examples. We have set the Pop up Property Value to Yes in the previous Article to position the EmployeeSub Form above all other Forms.

When the Database Window is in maximized state if you open a normal Form it will also adjust itself to the maximized state. But, if the Pop up Property value is set to Yes then the form will open in its original designed size. This is true if you set the Border Style Property value to Dialog or Thin instead of setting the Pop up Property value. But, this will remove the Minimize/Maximize; Restore Window Buttons on the Title Bar. Setting the Border Style property value to None will remove the Title Bar totally.

It is a well known fact that all MS-Access Objects like Forms/Reports opens and stays within the MS-Access Application Window. You cannot take any of them out of the Application Window boundaries, right?

Wrong, you can, try this to find out:

If the Access Application Window is in maximized state then click the Restore Down control button (the middle one between minimize/maximize and close buttons) on the Title bar and you will find a Window sizing control at the right bottom corner of the Application window. When you place the mouse pointer there it will change into a double-headed arrow. Click and drag the corner up, moving the mouse towards left top corner direction of the screen, so that the MS-Access Application Window reduces to about half of its size or make it even smaller. But you must be able to see one of your Pop up Forms in the database window and you are going to open it next.

Open one of your Pop up Forms and you will see that it jumps out of the Application Window.

If you didn't make the Application window small enough then click on the Title Bar of the Pop up Form and drag it out of the Application Window. You can place both the Pop up Form and Application Window side by side or place the Pop up Form over the small Application window to hide it completely.

Don't attempt to minimize the MS-Access Application Window, if you do then the Pop up Form will jump in and hide. If the Application window is very small then the Pop up Form may open over the Application window hiding it completely.

Forms have another Property Auto Center. Usually this property is set to Yes along with the Pop up Property settings to make the Form to appear in the middle of the Application Window.

But, what do we do if we want the Pop up Form to appear in the top right corner or right bottom corner or any place other than the center of the Screen? There are no Property settings to make this happen. But there is a simple way to get this done, try the following method:

Maximize the Application Window.

Open a Pop up Form in Design View.

Display the Property Sheet of the Form (View - ->Properties) and see that the Auto Center property is set to No.

If the Form's design frame is larger than the actual design size then reduce the design frame size by dragging the right bottom corner to the desired size.

Click on the Title Bar of the Form and drag it to the right top corner (or any other position you prefer) and place it there.

Click on the Save Toolbar Button or select Save from File Menu.

Close the Design view of the Form

Now, Open the Form in Normal View. The Form will appear where you have placed and saved it in design view.

Tip: This works not only for Pop up Forms, works on any Form.

Custom Made Form Wizard
Rounding Function MRound() of Excel
Custom Calculator and Eval() Function
Data Editing and Security Issues
Event Trapping & Summary on Datasheet

Synchronized Floating popup Form

noreply@blogger.com (a.p.r. pillai) — Fri, 27 Feb 2009 16:52:00 +0000

This Form is designed specifically for inquiry purposes (not for Data Entry). The Source Data Table has several fields and the information in them can be categorized into different groups for viewing.

For example, the Employees Form, in Northwind.mdb sample database, has been divided into two parts, viz. Company Information and Personal Information. The Company Info part is designed on the first Page of a Tab Control and the second part Personal Info category of fields are placed on second page of the Tab Control and stays hidden till it receives a click on the second page of the Tab Control, to bring the data into view. Let us assume that the Company Info is the most frequently viewed or updated information and it is kept in full view and Personal Info kept behind because it is not so often viewed or updated.

We will design them differently with an interesting trick involving two separate stand alone Forms without linking them as Main Form and Sub-Form. Company Info on one Form and Personal Info on a separate Form but both will have source data from Employees Table.

Let us name them as EmployeeMain and EmployeeSub Forms. They will remain as two independent Forms. A sample image of both Forms in running mode is given below:

The trick is that when we open the EmployeeMain Form it will show only the Company Info alone (no trick here). We will move few records forward using the Record Navigation Button and at this point we would like to see the Personal Info part of the current record. We will click on a Command Button to open the EmployeeSub Form with the Personal Info of the current record on the EmployeeMain Form. From this point onwards the records on both Forms move forward/back synchronized, when you move to Next or Previous records on the EmployeeMain Form, even though they are two separate Forms.

The EmployeeSub Form is defined as a Pop Up Form (or its Pop up Property value is set to Yes) to float it above the EmployeeMain Form or any other Form open in the Application Window, provided if you don't open another Pop Up Form. If you don't want the EmployeeSub Form to stay on you may close it and open again when needed using the Personal Info Command Button on the EmployeeMain Form. When you close the EmployeeMain Form this action will close the EmployeeSub Form too, if it is running.

We can very easily design these two Forms by importing the Employees Table and Employees Form from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Database. If you have not used these Objects so far for any other examples given on this site you may import them into your database now.

Open the Employees Form in Design View.

Click on the Form and drag the mouse over all Controls on the Company Info Tab except the Photo (if you want it you may select that too) and the Command Button controls.

Select Copy from Edit Menu.

Click on the Employees Table and select Form from Insert menu and select Design View from the list of options displayed, to create a new Form.

Click on the Detail Section of the Form and select Paste from Edit Menu to place the copied fields of Employees Form on to the new Form.

If the Form Header/Footer Sections of the Form are not visible then select Form Header/Footer from View Menu.

Copy and Paste the Text Control with the employee name expression from the Header Section of the Employees Form to the Header Section of your Form. Change the Font Color to Red or some other Color you like.

Create a Command Button in the Detail Section below the data fields.

Display its Property Sheet (View- -> Properties).

Change the Name Property Value to cmdPersonalInfo and the Caption Property Value to Personal Info.

NB:

Create a Command Button at the Footer Section of the Form.

Change the Name Property value to cmdClose and the Caption Property Value to Close.

Display the Form's Property Sheet. Click on the left top corner of the Form where a black rectangle is shown, at the intersection of the horizontal and vertical design guide (scales) meet, to select the Form's Property Sheet, if it is not the current one.

Change the Caption Property Value to Company Info.

Display the VBA Code Module of the Form (Alt+F11). Copy and Paste the following Code into the Code Module.


Option Compare Database
Option Explicit
Dim strSQL As String


Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub cmdPersonalInfo_Click()
If IsLoaded("EmployeesSub") Then
   strSQL = "SELECT Employees.* FROM Employees "
   strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
   Forms("EmployeeSub").RecordSource = strSQL
   DoCmd.SelectObject acForm, "EmployeeSub", False
Else
   DoCmd.OpenForm "EmployeeSub", acNormal, , "[Employeeid] = " & Me![EmployeeID], acFormReadOnly, acWindowNormal
End If

Forms("EmployeeMain").ActiveControl.SetFocus

End Sub

Private Sub Form_Close()
    DoCmd.Close acForm, "EmployeeSub"
End Sub

Private Sub Form_Current()
If IsLoaded("EmployeeSub") Then
   strSQL = "SELECT Employees.* FROM Employees "
   strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
   Forms("EmployeeSub").RecordSource = strSQL
   DoCmd.SelectObject acForm, "EmployeeSub", False
   Forms("EmployeeMain").SetFocus
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
End Sub

Save the Form with the name EmployeeMain.

Repeat the process from Step-3 to Step-8 for transferring information from the Personal Info Tab of the Employees Form to a new Form.

Display the Form Header/Footer Section of the Form (View - ->Form Header/Footer).

Create a Command Button and change the Name Property Value to cmdClose and the Caption Property Value to Close.

Display the Form's Property Sheet.

Change the following Property Values as given below:

Caption = Personal Info

Default View = Single Form

Allow Additions = No

Allow Deletions = No

Data Entry = No

Record Selectors = No

Navigation Buttons = No

Dividing Lines = No

Auto Resize = Yes

Pop Up = Yes

Border Style = Dialog

Allow Design Changes = Design View Only.

Display the Code Module of the Form (Alt+F11).

Copy and paste the following Code into the Code Module.


Private Sub cmdClose_Click()
     DoCmd.Close acForm, Me.Name
End Sub

Save the Form with the name EmployeeSub.

EmployeeSub

Open

Copy and Paste the following Code into a Global Module (Standard Module) and save the Module:


Public Function IsLoaded(ByVal strFormName As String) As Boolean
Dim j As Integer

On Error GoTo IsLoaded_Err

IsLoaded = False
For j = 0 To Forms.Count - 1
    If Forms(j).Name = strFormName Then
       IsLoaded = True
       Exit For
    End If
Next

IsLoaded_Exit:
Exit Function

IsLoaded_Err:
IsLoaded = False
Resume IsLoaded_Exit

End Function

To try out your creation, open the EmployeeMain Form, click on the Record Navigation Control to advance few records forward.

Click on the Personal Info Command Button. The EmployeeSub Form will open up showing Personal Information pertains to the same employee on the main Form. Check the Names of the Employee appearing on top of both forms.

Now, try advancing records on the main from forward or back with the record navigation control. You will see that the corresponding personal information on the EmployeeSub Form is also moving along with the records on the main form.

If you close the EmployeeMain Form while the EmployeeSub Form is open both will be closed.

You may download the sample database from the Download Link given below and give it a try before you design one of your own to understand the trick:

Download - File: SynchronizedForms2K.zip (Size:54.6K)

Rounding Function MRound() of Excel
Custom Calculator and Eval() Function
Data Editing and Security Issues
Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray

Forms and Custom Properties

noreply@blogger.com (a.p.r. pillai) — Fri, 20 Feb 2009 18:23:00 +0000

Searching and finding a record in a Form is easy with Edit - -> Find (Ctrl + F) Option on a particular field value. But, this will fetch only the first record even if there are more records matching the same search text. Most of the time we need to find records that matches values in more than one field, like records of Sales Representatives of Northwind Traders located in the City of London.

We will create a simple method to find all records of Employees Table that matches both fields (City and Title) and display them. If you have not imported the Employees sample Table from C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb before then you may do it now.

Create a temporary table with the name temp_param with two text fields; City and Title.

Add a single record with City field value as London and Title field value as Sales Representative.

Design a Main Form with this Table and place both Fields on the Header Section of the Form.

City

Title

Query Name: cboCityQ


SELECT Employees.City
FROM Employees
GROUP BY Employees.City;

Query

cboCityQ

Query

cboTitleQ

Query Name: cboTitleQ


SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title;

Create a Combo-Box in the Header Section of the Main Form using cboCityQ as source data and select City as Control Source.

Create another Combo-Box using cboTitleQ as source data and select Title as Control Source.

Name the City field Combo-Box as cboCity and Title field Combo-Box as cboTitle.

Design a Datasheet Form on Employees Table and save the Form with the name Employees_Sub.

Insert the Employees_Sub Form as Sub-Form in the Detail Section of the Main Form.

Click on the Sub-Form, display the Property Sheet (View- ->Properties) and set the following Property values as shown below:

Link Child Fields = City;Title

Link Master Fields = cboCity;cboTitle

Now you can select City and Title values from the Combo-Boxes and all the matching records will immediately show up in the Datasheet Sub-Form.

Database Sharing Issues

If it is a single User database (or given Exclusive Access to a single User on Network) then the above method works fine and will have no issues. But, it is different when the database is shared on a Network. Even though different instance of the Main Form is used by individual User they are sharing the same Table to set different search criteria at the same time and likely to clash each other when the Main Form is refreshed and the temp_param table record is updated.

A work-around to this problem is to use Unbound Text Boxes/Combo-Boxes on the Main Form and not to use the temp_param Table at all to store the values selected from cboCity and cboTitle Combo-Boxes.

This will enable the Users to set the Values on the Unbound Combo-Boxes on their own instance of the Form without conflicts.

This method also has some minor side effects. When the User opens the Main Form it will be empty till they enter values into the unbound Combo-Boxes. This we can rectify to certain extend by creating two Custom Properties on the Main Form. Save the values from the Combo-Box controls into these custom properties when the Form closes and restore them back when the Form opens again.

Why I said 'to certain extend' because if several Users are sharing the Main Form then the Custom Property values are saved by each User of the Main Form (when he/she closes their instance of the Form) and will retain only the value saved last. But all the Users who open the Main form next time will be presented with the records related to the Custom Property Values saved last. But this is not a big issue because most probably next time when they open the Form they may require records for different criteria and can change it too.

To implement this method, first we must create the Custom Properties: prpCity and prpTitle on the Main Form and save some initial values into them. We can do this only with VBA Code. Sample Code is given below:


Public Function CustomProperty()
Dim db As DAO.Database, doc As Document, prp As Property
Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")
'creates the Custom Property with Name, data type and initial value
Set prp = doc.CreateProperty("prpCity", dbText, "London")

'add the new Custom Property to the Properties collection of the Form Main
'NB: This will not appear in the Property Sheet of the Form

doc.Properties.Append prp
Set prp = doc.CreateProperty("prpTitle", dbText, "Manager") 

doc.Properties.Append prp
doc.Properties.Refresh

End Function

Here, you can see that the reference to the Main Form is addressed differently than the usual method of Forms_Main or Forms![Main] or Forms("Main") etc.. The Forms group is addressed as Container; a member of the Containers (Tables, Forms, Reports etc.) Group and the Main Form is addressed as a Document, a member of the Documents Group. To learn more about Containers and Documents visit the page with the Title: Saving Data on Forms not in Table.

Since, this is a one time exercise you can Copy the above Code into a Global Module (Standard Module) and Run the Code directly by placing the Cursor in the middle of the Code and pressing F5 (Run).

If you attempt to run the Code a second time it will show Errors indicating that the Custom Properties with the given name are already present on the Form.

The next step is to use these Properties on the Form_Close() and Form_Load() EventProcedures to save values from the Combo-Box Controls into the Custom Properties and restore them back into the Combo-Box Controls when the Form is open.

The following Code saves the Combo-Box contents into prpCity and prpTitle custom properties on the Main Form when the Form is closed:


Private Sub Form_Close()
Dim db As Database, doc As Document, prp As Property

On Error GoTo Form_Close_Err

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")

'Save the current values from the combo boxes into the custom properties
doc.Properties("prpCity").Value = Me![cboCity]
doc.Properties("prpTitle").Value = Me![cboTitle]

Form_Close_Exit:
Exit Sub

Form_Close_Err:
MsgBox Err.Description, , "Form_Close()"
Resume Form_Close_Exit
End Sub

The following Code restores the cboCity and cboTitle values when the Main Form opens by Users again:


Private Sub Form_Load()
Dim db As Database, doc As Document, prp As Property

On Error GoTo Form_Load_Err

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")

'Set the Combobox values from the Custom Property values saved earlier

Me![cboCity] = doc.Properties("prpCity").Value
Me![cboTitle] = doc.Properties("prpTitle").Value

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

How about positioning a particular record on the Form, which you have worked on last time, as current record when the Form opens? Click here to find out.

Custom Calculator and Eval() Function
Data Editing and Security Issues
Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray
Text Box and Label Inner Margins

ControlTip Text and Time Delay

noreply@blogger.com (a.p.r. pillai) — Fri, 13 Feb 2009 11:59:00 +0000

Most of the Controls on a Form like Command Buttons, Labels, Text-Boxes and others have the Property ControlTip Text. This can be set with a Value of up to a maximum length of 255 characters, either manually during design time or through Visual Basic. The text is displayed when the Mouse Pointer rests on the Control for a few seconds. This is useful to inform the User to do certain action, like Click or Double-Click, on the Control to run a Program or Macro attached to it.

The Toolbar Buttons above also have this feature programmed with the Screen Tip Property Value (another name for ControlTip Text) to give clues to the User as to what the control does or what to do to use it. For example when you point the Mouse pointer on the Copy Toolbar Button it will show Copy (Ctrl+C) indicating that either Click on the Button to Copy the selected Text/Control or use Ctrl+C to get the same result.

The time delay is programmed into this action assuming that the Mouse Pointer rests on the Control because the user probably doesn't know what it does and needs some help to indicate what he/she should do to use it or what result one can get by using it.

Here, we will concentrate on Controls on a Main Switchboard (Control Screen), like Command Buttons, List-Boxes and how to achieve the same result without the time delay that needs for the ControlTip Text Property.

There is another Property named Tag placed near to the ControlTip Property; its function is not clearly defined and free to use, as you wish. The value in this Property will not affect the Control in any way and will not have any side effects to other Properties either. You can write a short story into the Tag Property, if you can limit your story to 2048 characters.

We are going to use this Property of different Controls on the Main Switchboard of a sample database to give clues to the User instantly what each control does or what to do (like Click or Double-Click) to run the Program or Macro attached to them.

You must select each control and display their Property Sheet(View - -> Properties) and type the Value, you would like to display, into the Tag Property. This text will appear instantly in the Label with dark background (you can use any color you like) placed at the bottom of the Form when the Mouse is moved over a Control with Tag value set and programmed.

An image of a sample Main SwitchBoard with the above trick is given below:

A Label with dark background (let us call it LBLTIP) is placed at the bottom of the design to display the clues when the Mouse moves over the controls above. The example text displayed on the image is attached to the Tag Property of the List-Box with Forms' Names.

To display the clue and to remove it when the Mouse moves out of the Control we need to run few lines of VBA Code at two places. On Mouse Move Event Procedure of the Control as well as at the Detail Section of the Form to remove the text and replace it with some general message text.

The sample Code in the On Mouse Move Event Procedure of the Reports Command Button is given below that displays: Open Report SwitchBoard in the LBLTIP label below, when the Mouse is moved over the Command Button.


Private Sub cmdRpt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Me.LBLTIP.Caption <> Me.cmdRpt.Tag Then
    Me.LBLTIP.Caption = Me.cmdRpt.Tag
End If
End Sub

When the Mouse moves out of the Command Button and touches the empty area of the Detail Section of the Form the LBLTIP Caption changes to Welcome User: Admin (the current MS-Access User Account name) appears and stays there till the mouse moves over another Control on the Form.

The VBA Code in the Detail Section On Mouse Move Event Procedure is given below:


Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strtag As String
strtag = "Welcom User: " & CurrentUser
If Me.LBLTIP.Caption <> strtag Then
    Me.LBLTIP.Caption = strtag
End If
End Sub

Even though the Detail Section Mouse Move Event Procedure Code has no change; repeating all three lines of code for each control is excess work. The IF…Then statement is used to test and prevent setting the LBLTIP Caption value repeatedly.

We can implement this with a single line of code, if we write a common routine and place it in a Global Module (standard Module) and call it with a single line of code with necessary Parameters. Such a Function is given below

Copy the following VBA Code and save it in a Standard Module:


Public Function ControlTip(ByVal frmName As String, Optional strtext As String = "Welcome User: ", Optional xswitch As Integer = 0)
On Error GoTo ControlTip_Err
With Forms(frmName).Controls("lblTip")
    Select Case xswitch
        Case 1
            If .Caption <> strtext Then
                .Caption = strtext
            End If
        Case Else
            If .Caption <> strtext Then
                .Caption = strtext & CurrentUser
            End If
    End Select
End With

ControlTip_Exit:
Exit Function

ControlTip_Err:
MsgBox Err.Description, , "ControlTip()"
Resume ControlTip_Exit
End Function

The above Function has three Parameters:

Name of the Form from where the Function is called.

The LBLTIP.Caption text to display. This will be taken from the Tag Property Value of the Control. This parameter is defined as Optional so that this can be called without a value at Form-level (in our example from Detail Section) to display the welcome message after removing the earlier contents.

The Optional xswitch parameter is used for display control. Value of 1 will display the Text value passed through the Second Parameter or else it will display a welcome message to the active User replacing earlier text in the LBLTIP control.

With the above Function we can simplify the first two Sub-Routines as given below:


Private Sub cmdRpt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
     ControlTip Me.Name, Me. cmdRpt.Tag, 1
End Sub



Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ControlTip Me.Name
End Sub

You can download the sample database (Access2000 Version) from the Link below and try it out.

Download - File: ControlTip2K.zip (Size:82.4K)

Data Editing and Security Issues
Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray
Text Box and Label Inner Margins
Multiple Parameters for Query

External Files List in Hyperlinks

noreply@blogger.com (a.p.r. pillai) — Fri, 06 Feb 2009 19:42:00 +0000

Is it possible to display a directory listing of External Files on a Form and open them in their parent applications? Several queries of different kind received through E-mails pointing towards this objective, from Readers of the earlier Article; File Brower in MS-Access (Common Dialog Control).

We have seen that we can open and work with external data sources like dBase Tables, Excel databases and AS400(iSeries) Tables directly without linking them permanently with MS-Access and familiarized these procedures through the following Articles:

Opening External Data Sources
Opening dBase Files Directly
Display Excel Value Directly on Form
Opening Excel Database Directly
Database Connection String Properties
Access Live Data in Excel
Access Live Data in Excel-2
Source ConnectStr Property and ODBC

But, all of them fall into only one category, data files.

To answer the above query we will create a Form with a Datasheet Sub-Form and with few simple controls to take a listing of a particular category of Files of your choice (Text Files, Word Files, Excel Files or All Files) from a Folder (including sub-folders, if needed) and display them in a list. If you would like to open and work with them in their parent Applications, you are welcome to do so by clicking and opening it in their parent Application. An Image of a sample run is given below:

All you have to do is to type the File Path like C:\Documents and Settings\UserName\My Documents\*.xls into a Text Box and click a Command Button and there it is; all excel files from the selected folder is presented on the Form as Hyperlinks on the Datasheet.

I know you are not impressed, because you could memorize and keep entering the above Path or some other Path without difficulty. Then what about other numerous locations and sub-folders on several Disk Drives? Opening Windows Explorer is out of the question; to find the location and enter it into the Text Box Control manually. Well, if there is no other way then that also can be done.

No, you will click on the Path Lookup Command Button to invoke something like the Common Dialog Control and browse to the location of the files you are interested in, select one of them and Click the OK Button. The Files' location address with the File you have selected will be placed in the Text Control.

That saves a lot of trouble in finding the location of the files. It takes only few clicks of the mouse and we are doing it plenty of times a day.

You may bring in the selected file into the list in Datasheet View by clicking the Get Files Command Button or modify the file name part with Wild Card characters (like *.xls, or Acc*.jpg) to bring in a group of files into the list.

The Form has a simple design as you can see from the image given above. Following are the main elements of the design.

A Table: DirectoryList with a single Field FileLinks with data type Hyperlink.

A Datasheet Form created on the above Table with the name FilesListing_Sub.

The Main Form FilesListing with the Datasheet Form inserted as Sub-Form that occupies the major part of the design.

A Command Button (Name: cmdPathLookup) with the Caption Path Lookup runs the Sub-Routine and works like Common Dialog Control to browse and select a file from required location and insert the Path Name in the Text Box below.

A Text Box with the name PathName to hold the location address (or Path Name of the Files) and to modify as per your requirement before you Click the Get Files Command Button. If you know the Path Name correctly then you may type it in the Text Box directly, whichever is easier to you.

The Command Button with the name cmdGo and with the Caption Get Files, when clicked brings in all the files matching the Path Name specified and displays them in the Data Sheet View above.

A Combo-Box with the name cboYN with Yes, No Values. If you set the value to Yes before clicking the Get Files Command Button; all files from the Sub-Folders of the selected folder, if any, also included in the output.

The Command Button with the name cmdClose and with the Caption Close when clicked closes the FilesListing Main Form.

Display the Property Sheet(View- ->Properties) of the Main Form FilesListing and change the Property Values as given below to modify its behavior when opened:

Caption = External Files List

Default View = Single Form

Allow Edits = Yes

Allow Deletions = Yes

Allow Additions = No

Scroll Bars = Neither

Record Selectors = No

Navigation Buttons = No

Dividing Lines = No

Auto Re-Size = Yes

Auto Center = Yes

Pop up = Yes

Modal = No

Border Style = Dialog

Control Box = Yes

Min Max Buttons = None

Close Button = Yes

Allow Design Changes = Design View Only

The image of the Main Form in design view is given below:

The heading Labels External Files List is created with the 3D Text Creation Program and modified to look like the above design. You can download this 3D Text Creation Wizard from any of the following links:

Create 3D Headings on Forms
Border 2D Heading Text
Border 3D Heading
Shadow 3D Heading Style.

The Files' List is created as Hyperlinks in the target table: DirectoryList, every time, replacing the existing list of files, and appears in Datasheet View. You may click on the HyperLink to open that file in its parent Application to work with it.

You can delete unwanted files from the list. Select a record by clicking on the left border of the list and press Delete Key or press Ctrl+- (press and hold Ctrl Key and press - Key). You cannot add a record manually or edit a record on the Datasheet (you may do so directly on the Table, if you like to mess around with it) so that the HyperLink Value Segments are not altered.

If you would like to know more about the Hyperlink value Segments (four of them) and what they do; go to the link Open Forms with Hyperlinks in ListBox.

You may download the sample database from the bottom of this Post and try it out before you design one of your own to understand it better.

You can easily implement this in your various Projects by simply importing the Forms and the Table into them.

It is Access2000 Version file and Access97 users cannot use it. But, you can design the above Forms and Controls with their specific names as explained above by following Step-1 to 9.

The Sub-Routines and Function which runs behind the Main Form FilesListing is given below. You may create the above design with the correct names explained under Steps-1 to 9, copy and paste the complete Code in the Main Form Class Module and save the Form.


Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdGo_Click()
Dim strtxt As String
On Error GoTo cmdGo_Click_Err
strtxt = Nz(Me![PathName], "")
New_Search strtxt, Me!cboYN
Me.FilesListing_sub.Form.Requery

cmdGo_Click_Exit:
Exit Sub

cmdGo_Click_Err:
MsgBox Err.Description, , "cmdGo_Click"
Resume cmdGo_Click_Exit
End Sub

Private Sub cmdPathLookup_Click()
'=============================================
'Source Code : Northwind.mdb sample database
'=============================================
    ' Displays the Office File Open dialog to choose a file name
    ' If the user selects a file
    ' display it in the Pathname control.
    Dim result As Integer
    On Error GoTo cmdPathLookup_Click_Err
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select a File from Target Folder"
        .Filters.Add "All Files", "*.*"
        .FilterIndex = 1
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.path
        result = .Show
        If (result <> 0) Then
            Me!PathName = Trim(.SelectedItems.Item(1))
        End If
    End With
    
cmdPathLookup_Click_Exit:
Exit Sub

cmdPathLookup_Click_Err:
MsgBox Err.Description, , "cmdPathLookup_Click"
Resume cmdPathLookup_Click_Exit
End Sub

Private Sub Form_Load()
Me!PathName = CurrentProject.path & "\*.*"
End Sub


Private Function New_Search(ByVal strFilePathName As String, boolsubfolders As Boolean)
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb. 2009
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------
Dim i, strFolder As String, strFiles As String, locn As Long
Dim db As Database, rst As Recordset, xtn As Integer

'On Error GoTo New_Search_Err

If Len(Trim(strFilePathName)) = 0 Then
   MsgBox "File Path Name required."
   Exit Function
End If
xtn = InStr(1, strFilePathName, ".")
locn = InStrRev(strFilePathName, "\")

If xtn > 0 And locn > 0 Then
   strFiles = Trim(Mid(strFilePathName, locn + 1))
   strFolder = Trim(Left(strFilePathName, locn - 1))
ElseIf xtn > 0 And locn = 0 Then
   strFiles = Trim(strFilePathName)
   If Len(Left(strFiles, xtn - 1)) = 0 Then
      MsgBox "Invalid File specification."
      Exit Function
   End If
   strFolder = CurrentProject.path & "\*.*"
ElseIf xtn = 0 And locn > 0 Then
   strFiles = "*.*"
   strFolder = Trim(strFilePathName)
End If

With Application.FileSearch
'the .NewSearch resets earlier search property values to defaults
    .NewSearch
    .LookIn = strFolder
    .SearchSubFolders = boolsubfolders
    .fileName = strFiles
If .Execute() > 0 Then
    i = .FoundFiles.Count
    MsgBox "File found = " & .FoundFiles.Count
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE DirectoryList.* FROM DirectoryList;"
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
    For i = 1 To .FoundFiles.Count
        rst.AddNew
        strFiles = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
        strFiles = strFiles & "#" & .FoundFiles(i) & "##Click"
        rst![FileLinks] = strFiles
        rst.Update
    Next
Else
    MsgBox "No matching File Names found!"
End If
End With

New_Search_Exit:
Exit Function

New_Search_Err:
MsgBox Err.Description, , "New_Search"
Resume New_Search_Exit
End Function

If there is any difficulty in running the On Click Event Procedure of the Path Lookup Command Button then replace that with the File Browser method explained in the Article: File Browser in MS-Access (Common Dialog Control).

Download - File: DirListing2K.zip (Size:36.6K)

Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray
Text Box and Label Inner Margins
Multiple Parameters for Query
Form Menu Bars and Toolbars

Combo-Box Column Values

noreply@blogger.com (a.p.r. pillai) — Fri, 30 Jan 2009 11:52:00 +0000

Command Buttons are the most commonly used control on a Form. We have seen them in action in different ways and with different designs. Those who have not come across those Articles before then links to them are given below; you may take a look at them.

Command Button Animation

Command Button Animation-2

Double-Action Command Button

Colorful Command Buttons

Transparent Command Button

Next in line is the most preferred and familiar control on Forms; the Combo-Box Control. This can be created not only on Forms but also on Tables as well.

If you would like to see few examples for the usage of Combo-Boxes in Tables then you have them on your PC itself. Open Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MS-Office2003 pathname) and open Employees Table or Orders Table or Order Details Table in Design View and look at the Fields and Properties given under each Table listed below:

Employees Table

Field: TitleofCourtesy

Note

Lookup

Field Properties

Row Source Type

Row Source

Property

Row Source

Row Source Type

Value List

Column Count

Row Source

Dr.;Mr.;Miss;Mrs.;Ms.

Limit To List

Field: ReportsTo

Note:

Row Source

EmployeelD

ReportsTo

Employees

Row Source Type

Table/Query

Row Source

Limit To List

Yes

Combo-Box

EmployeelD

Orders Table

CustornerlD

EmployeelD

Shipvia

Orders Qry

Customer

Order Details Table

ProductID

Normally, Combo-Box will have one or more columns of information like EmployeelD and Employee Name. When clicked the first column value is inserted (this depends on the Bound Column Property Value setting) into the target field and the description appearing in the second Column is shown for information purpose only.

The Column Width Property must be set with Values like 0.5"; 1.5" for each column and the List Width Property Value is equal to the value of all Column Widths added together.

In our above example the EmployeelD is the value inserted into the ReportsTo field when clicked. The EmployeelD is a number but the descriptive name is important to the User because it is more meaningful and easy to remember. So in the Combo-Box the EmployeelD number is kept hidden by setting the first Column Width Value as 0", thereby showing only the name of the Employee in the Combo-Box.

We will go back to the first sentence of the above Paragraph and proceed further on that point. There are times that we need information from other Columns also to insert into other target fields with one click.

Assume that our Employee Combo-Box have one more column for Designation (besides EmployeelD and Name) and when clicked this information also must be inserted into another control on the Form. You can do this with a one-line VBA Code in the On Click Event Procedure in the Form Module like:


Private Sub EmployeeCombo_Click()
     Me![Designation] = Me!EmployeeCombo.Column(2) 
End Sub

The column index numbers of Combo-Boxes are 0 based and the third column has an index value of 2.

Sum() Min() Max() ParamArray
Text Box and Label Inner Margins
Multiple Parameters for Query
Form Menu Bars and Toolbars
Seriality Control - Missing Numbers

Drill-Down Inquiry Screen-2

noreply@blogger.com (a.p.r. pillai) — Fri, 23 Jan 2009 17:18:00 +0000

This is the continuation of earlier Article: Drill-Down Inquiry Screen. If you have landed on this Page please read the first part of this Article by following this Link: Drill-Down Inquiry Screen and continue...

I hope you have downloaded the sample database from the bottom of the first part of this Article and tried it out. Then you have a general idea of the shape of things to come.

Here, we will design three small Forms, assemble them on the Main Form on a Tab Control and use few lines of Code to refresh the Main Form to update the contents of the Sub-forms before bringing them into view.

First, we will design the top layer of the Form for the Employee-wise Order Summary Information.

03_Employee_Summary Form

Select the Query 03_Employee_Summary.

Design a Tabular Form (continuous form) like the sample given below. You may use the Form Wizard from Insert Menu to create the Form quickly but it may insert background images and the controls may be created with shadows etc. In that case you have to modify the design to look like the sample given below.

Select all the Data Fields together by clicking on the left side border of Detail Section or click in Detail Section somewhere and drag the mouse over all the controls to select them together.

Display the Property Sheet (View - ->Properties).

Change the following Property Values:

Enabled = No

Locked = Yes

Tab Stop = No

Back Color = 12632256

Special Effect = Raised

Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.

Draw a Command Button as wide as the full length of all the Fields above, so that we can cover the fields by placing it over them.

Display the Property Sheet of the Command Button.

Change the following Property Values:

Name = cmd1

Transparent = Yes

Hyperlink SubAddress = #

We must make the Command Button's height same as of the Fields above. Click on the Command Button to select it, hold the Shift Key and click on any one of the Fields above to select it along with the Command Button. Alternatively you can click and drag over the Command Button and any one of the fields above.

Select Format - -> Size and select Shortest, if Command Button's height is more than the field, or select Tallest to make the Command Button as tall as the fields above.

Drag the Transparent Command Button and place it over the Fields above.

To make sure that the Transparent Command Button stays above all the data fields, select Format - - > Bring to Front.

Now, reduce the Detail Section height, but there must be little gap above and below the Data Fields.

Draw a Text Box in the Form Footer Section below in the same position of TORDERS field in the Detail Section and write the following expression in the Control Source Property:

=Sum([TORDERS])

Change the Caption of the child label to Total Orders.

Create a Label at the Header of the Form and change the Caption value to EMPLOYEE-WISE ORDERS SUMMARY. Change the font size to 12.

Display the Code Module of the Form ( View - -> Code), Copy and paste the following VBA lines into the Module:

 
Private Sub cmd1_Click()
     Me.Parent.Refresh
      Me.Parent.Tabctl0.Pages(1).SetFocus
End Sub

Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.

Change the design to look like the sample Image given below:

Select all the fields as we did earlier and change the following Property Values:

Enabled = No

Locked = Yes

Tab Stop = No

Back Color = 16777215

Special Effect = Flat

Follow Step-6 to 8 given above.

Change the following Properties of the Command Button:

Name = cmdOrder

Transparent = Yes

Hyperlink SubAddress = #

Follow Step-10 to 13 explained above. Reduce the height of the Detail Section without leaving gap above and below the fields.

Create a Command Button at the Footer Section of the Form.

Display the Property Sheet of the Command Button and change the following Property Values:

Name = cmdMain

Caption = Goto Main

Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading to the Form.

Add a Text Box above the Field Headings and write the following expression in the Control Source Property of the Text Box:

= "Order List of " & [EmpName]

Display the Code Module of the Form (View - - >Code), Copy and paste the VBA Code given below into the Module and save the Form with the name 04_Order_ListQ.


Private Sub cmdMain_Click()
Me.Parent.Tabctl0.Pages(0).SetFocus
End Sub


Private Sub cmdOrder_Click()
Me.Parent.Refresh
Me.Parent.Tabctl0.Pages(2).SetFocus
End Sub

05_Order_DetailQ Form.

Select the Query 05_Order_DetailQ and Create a Tabular Form.

Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:

=Sum( [Quantity])

Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:

=Sum([ExtendedPrice])

Create a Command Button in the Form Footer below the Text Boxes.

Change the following Property Values of the Command Button.

Name = cmdBack

Caption = << Back

Display the Code Module of the Form (View - -> Code), copy and paste the following lines into the Module:


Private Sub cmdBack_Click()
Me.Parent.TabCtl0.Pages(1).SetFocus
End Sub

Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready for the Main Form Inquiry_Main to assemble all the three Sub-Forms on a Table Control and make the Tab Control invisible.

Inquiry_Main Form

Select the Parameter Table Date_Param, select Form from Insert Menu and select Design View from the displayed List.

Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.

Check the sample image given below. The Tab Control will have only two pages when created but we need one more page.

Click on the Tab Control to select it and select Tab Control Page from Insert Menu.

Page

Caption

Page1, Page2

Page3

Summary, Orders

Order Detail

Sub-Forms

03_Employees_Summary, 04_Order_ListQ

05_Order_DetailQ

Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.

Drag and drop the 03_Employee_Summary Form on the Tab Control Page1.

You may delete the child label attached to the Sub-Form. Re-size the Sub-Form and Tab Control to display the contents properly on the Screen. You may save the Form with the name Inquiry_Main and open it in Normal View to check as how the Information is displayed on the Sub-Form. If necessary increase or decrease the size of the Form and Tab Control. Leave some gap between the Tab control and the top of the Detail Section of the Form to insert few Text Boxes for StartDate and EndDate fields and two more Text Boxes for control purposes. We need space for a heading above these controls as well.

When you are satisfied with the Sub-Form dimension for the design, click on the Sub-Form and display the Property Sheet. Take a piece of paper and note down the following Property Values for resizing and positioning the other two forms we are going to insert into Page2 and Page3 of the Tab Control.

Left

Width

Height

Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.

Select Tab Control Page2, press and hold Shift Key, right-click on the Tab Control Page2 and select Paste from the displayed menu.
The pasted control will be an empty form displaying the Source Object Name of the copied Form with white background.

Display the Property sheet of the Form and change the Source Object Property value to 04_Order_ListQ. After this change the Form will appear on the Tab Control Page2.

Change the dimension property values to the same value you have noted down. Since, you have copied the Form (no drag and drop from Database Window); you need to change only the Top and Left Property Values, Width and Height values will be same. If not then change it.

Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form into the third Page of the Tab Control.

Display the Field List (View - ->Field List), if it is not visible.

Drag and drop StartDate and EndDate field above the Tab Control, create labels above and left and change their Caption values as shown on the design above.

Create a Command Button to the right of EndDate field and change its property values as given below:

Name = cmdRefresh

Caption = <<Refresh Screen

Create a Text Box to the right of the Command Button and display its property sheet and change the following Property Values:

Name = EID

Control Source = =[03_Employee_Summary].Form!EmployeeID

Visible = No

Create another Text Box below the earlier one and change the property values as given below.

Name = OID

Control Source = =[04_Order_ListQ].Form!OrderID

Visible = No

Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.

Create a Command Button below the Tab Control and change the following property values:

Name = cmdQuit

Caption = Quit

Tab Control

Click on the Tab Control, display the Property sheet and change the following property values:

Caution:

Save

File

Back Style = Transparent

Multirow = No

Style = None

Display the Code Module of the Form, copy and paste the following few lines of Code into the Module:


Private Sub cmdQuit_Click()
      DoCmd.Close acForm, Me.Name
End Sub


Private Sub cmdRefresh_Click()
     Me.Refresh
End Sub


Private Sub EndDate_LostFocus()
    Me.Refresh
End Sub


Private Sub Form_Load()
     Me.TabCtl0.Pages(0).SetFocus
End Sub

We will finish the design by setting the Property Values of the Inquiry_Main Form. Display the Property Sheet of the Form and change the following Property Values:

Allow Edits = Yes

Allow Deletiions = No

Allow Additions = No

Data Entry = No

Scroll Bars = Neither

Record Selectors = No

Navigation Buttons = No

Dividing Lines = No

Auto Re-size = Yes

Auto Center = Yes

Pop up = Yes

Modal = No

Border Style = Dialog

Control Box = Yes

Min Max Buttons = None

Close Button = Yes

Allow Design Changes = Design View Only

Save the Form, open it in Normal View and try out your creation.

Note: If you face any difficulty in running your design normally, use the downloaded database as reference, find the mistake and correct it.

Text Box and Label Inner Margins
Multiple Parameters for Query
Form Menu Bars and Toolbars
Seriality Control - Missing Numbers
Wave Shaped Reminder Ticker

Drill-Down Inquiry Screen

noreply@blogger.com (a.p.r. pillai) — Sat, 17 Jan 2009 12:28:00 +0000

Following is an image of a three layer Inquiry Screen, designed for the Management, to keep track of Northwind Traders staff-level Sales Performance:

The top layer of the Form shows Sales-Person level summary of Orders and the Percentage on Total Orders. At the Footer of the Form shows the Total of all Orders put together. When clicked on a particular Employee’s record the individual Order-level Summary information will be visible on the main screen overlaying the earlier view. Check the image given below.

Several items can be ordered under a particular Order and details of all items can be displayed by Clicking on one of the Order record. Check the image given below.

The above Form shows order-level details and summary information at the Footer of the form with Total Quantity of all Orders and the Total Net-Value of all items after discount. The Command Buttons at the footer of each form is used to switch the view back to the upper layer of the form.

The Date Parameter values at the top can be changed to display information for a different period.

First of all, I would like to assure you that there is no complicated VBA Code to drive this design except one or two lines of them here and there to refresh the controls and for switching from one layer of the form to the other. Other than that the entire Screen works on Queries and Forms.

We need few Tables from Northwind.mdb sample database, to create six simple Queries, three Sub-Forms and a Main Form to organize all of them together to present the information to the User.

If you would like to see the Inquiry Screen in action before you design it, you can download it from the bottom of this post and take a look at it. If you find it difficult to figure it out how it works or how to put all of them together then come back and go through the design task step by step so that you will know how simple or difficult it is and how each element of this trick works together with others.

Here, we will see the usage of Transparent Command Buttons again, which we have already seen in an earlier Article with the same Title.

We will have to split this topic into more than one Blog-Post so that we can do the design task with ease. Even though I can show you how to do it in few minutes, explaining that on paper with images and Property settings takes lot more than that.

I know, if you download the database and take a look at it first, you will be better prepared to find out more about it and ready to go through the design task with more interest.

So, let us start the first part.

Download the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MSOffice2003 address, you can replace Office in place of Office11 if your Version of Access is 2000):

Employees

Orders

Order_Details

Products

Create a Parameter Table with the following name and Structure:

Field Name	Data Type
StartDate	Date
EndDate	Date

Open the Table and create a single record with the following StartDate and EndDate values and save the record:

StartDate = 01-01-1996

EndDate = 31-12-1996

Open new Queries and Copy the following SQL Strings into Query's SQL Editing Window and save them with exact Name given for each of them. Create the Queries in the same order as given here as they have dependencies on Queries created first.

Query Name: 00_Orders_MainQ


SELECT Orders.*
FROM Orders, Date_Param
WHERE (((Orders.OrderDate) Between [StartDate] And [EndDate]));

Query Name: 01_OrderCount1


SELECT Employees.EmployeeID, 
        [FirstName] & " " & [LastName] AS EmpName, 
         Count([00_Orders_MainQ].OrderID) AS TORDERS
FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
GROUP BY Employees.EmployeeID, [FirstName] & " " & [LastName];

Query Name: 02_OrderCount2


SELECT Count([00_Orders_MainQ].OrderID) AS TOTALORDERS
FROM 00_Orders_MainQ;

Query Name: 03_Employee_Summary


SELECT [01_OrderCount1].*,
      [TORDERS]/[TOTALORDERS] AS PCNT
FROM 01_OrderCount1, 02_OrderCount2;

Query Name: 04_Order_ListQ


SELECT [00_Orders_MainQ].OrderID, 
       UCase([FirstName] & " " & [Lastname]) AS EmpName,
       [00_Orders_MainQ].OrderDate, 
       [00_Orders_MainQ].RequiredDate
FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
WHERE ((([00_Orders_MainQ].EmployeeID)=[Forms]![Inquiry_Main]![EID]));

Query Name: 05_Order_DetailQ


SELECT [FirstName] & " " & [LastName] AS EmpName, 
        [Order Details].OrderID,
        [Order Details].ProductID, 
        [Order Details].Quantity,
        [Order Details].UnitPrice, 
        [Order Details].Discount, 
        (1-[Discount])*[UnitPrice]*[Quantity] AS ExtendedPrice
FROM (Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID) INNER JOIN [Order Details] ON [00_Orders_MainQ].OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID)=[Forms]![Inquiry_Main]![OID]));

Contd/-...

Download Link :
Download - File: inquirydemo00.zip (Size:137K)

Multiple Parameters for Query
Form Menu Bars and Toolbars
Seriality Control - Missing Numbers
Wave Shaped Reminder Ticker
No Data and Report Error

Command Button Animation-2

noreply@blogger.com (a.p.r. pillai) — Sat, 10 Jan 2009 08:58:00 +0000

A Screen with Animated Text or Controls gives the application a lively look and feel than a rigid screen to the user as well as to the onlooker. Command Button Animation was the first Blog-Post that I have published on this site.

Another Screen design improvement was creating 3D headings on Form and Report with different Fonts, Sizes and Styles. Initially, created them manually and this lead to the creation of a 3D-Text Wizard. You can download this Wizard by following the highlighted link or from Downloads Menu.

You can find the details of 3D Text Styles in the following Posts and download the Wizard from any one of the Posts:

Create 3D Headings on Forms

Border 2D Heading Text

Border 3D Heading

Shadow 3D Heading Style

IBM AS400 (iSeries) Screens influenced me to go along designing MS-Access Screens with dark background, data labels with light shades and information in Green text. Even though these are old text based screens better visibility of information is the main attraction of these screens.

But, when I started designing them I faced little problem with the Command Button Animation that was in use till that time because it was not designed for dark backgrounds. I had to invent a different animation method around the original command button. It is simple to design besides easy to implement without the use of too complex VBA code.

So, here it is for you and I hope you like it too. First, we will implement it with a simple and easy to understand method. After that we will write a common routine that can drive the animation on any form with one or two lines of Code.

Open a new Form or an existing one.

Select the Footer of the Form. If it is not visible select Form Header/Footer from View Menu.

Display the Property Sheet (View - ->Properties).

Change the Back Color Property Value to 0.

Select the Command Button Tool from Toolbox and draw a Command Button in the Footer Section of the Form.

Display the Property Sheet of the Command Button.

Change the Name property Value to cmdExit and Caption property value to Exit.

Select the Rectangle Tool from Toolbox and draw a rectangle around the Command Button as shown in the sample design below:

Give a little more gap, between the button and the rectangle at the bottom and the right side than above and left, giving it a feel that the Command Button is in a raised state.

Click on the Rectangle and display its Property Sheet.

Change the Name Property Value to ExitBox and the Visible Property Value to No.

On Mouse Move

Select the Command Button.

Display the Property Sheet of the Command Button (View - ->Properties).

Select [Event Procedure] in the On Mouse Move property and click on the build button (…).

Copy and paste the Code given below, between the sub-routine skeleton. You can ignore the first and last lines while copying as these will be present in the Module.


Private Sub cmdExit_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
     If Me.ExitBox.Visible = False Then
           Me.ExitBox.Visible = True
     End If
End Sub

Click anywhere within the Form Footer area and display the Property Sheet and repeat Step-14 above.

Copy and paste the following Code into the empty skeleton of the sub-routine, as you did above:


Private Sub FormFooter_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
   If Me.ExitBox.Visible = True Then
      Me.ExitBox.Visible = False
   End If
End Sub

IF…Then

Save the Form and open it in Normal view.

Move the Mouse over and out of the Command Button repeatedly which will give the button a sense of going flat and raised state every time.

Copy and paste the Code given below into a Global Module of your database and save it.


Public Function AnimateFrame(ByVal OnOff As Boolean, ByVal x_Box As String)
Dim frm As Form, ctrl As Control
On Error GoTo AnimateFrame_Err

Set frm = Application.Screen.ActiveForm
Set ctrl = frm.Controls(x_Box)

Select Case OnOff
    Case False
        If ctrl.Visible = False Then Exit Function
        frm.Controls(x_Box).Visible = False
    Case True
        If ctrl.Visible = True Then Exit Function
        frm.Controls(x_Box).Visible = True
End Select

AnimateFrame_Exit:
Exit Function

AnimateFrame_Err:
Resume AnimateFrame_Exit
End Function

Now, we can replace the code we have written earlier with a single line Code each to display and hide the rectangle.

If Me.ExitBox.Visible = False Then
Me.ExitBox.Visible = True
End If

The above Code can be replaced with the statement

AnimateFrame True, "ExitBox"

in the On Mouse Move of Command Button and

If Me.ExitBox.Visible = True Then
Me.ExitBox.Visible = False
End If

can be replaced with the statement

AnimateFrame False, "ExitBox"

in the FormFooter_MouseMove event procedure.

Form Menu Bars and Toolbars
Seriality Control - Missing Numbers
Wave Shaped Reminder Ticker
No Data and Report Error
Lost Links of External Tables

Cardinal Text Format In Access

noreply@blogger.com (a.p.r. pillai) — Fri, 02 Jan 2009 12:31:00 +0000

The other day one of my colleagues asked me to open MS-Word and type the expression =Rand() on a separate line and press Enter key. It was a magic that I didn't know till that time. The following sentence appears fifteen times repeatedly (in 3 rows and 5 columns) overwriting the expression itself:

The quick brown fox jumps over the lazy dog.

Open a Document in MS-Word and try it out yourself. The above sentence has all the letters of Alphabet in it. You can control the printing by inputting parameters to the Function like =Rand(5,1) will print the same sentence in 5 lines in one Column. It is a built-in Function with different constructs that can accept different set of parameters and looks like created for fun, I think! It works only when you type it on a separate line. Even though it looks like a Random Function it has nothing to do with it.

There is another feature in MS-Word that, I like to see in MS-Access, formats numeric values in Cardinal Text.

For example, result of the Mail Merge formula { = 9.20 + 5.35 \* CardText } outputs fourteen and 55/100, when the document is merged into another Document or to Printer. Format switches can be either \* DollarText or \* CardText to get the above output. When the \* Caps switch is added to it, like { = 9.20 + 5.35 \* DollarText \* Caps} , then it changes the first letter of each word into upper case.

This is very useful for printing Invoice values in MS-Access. I have written a Function to achieve this in MS-Access and here it is for you to try it out.

Copy and Paste the following Code into a Global Module of your Database and save it:


Public Function CardText(ByVal inNumber As Double, Optional ByVal precision As Integer = 2) As String
'------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2008
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim ctu(0 To 19) As String, ctt(0 To 9) As String, bmth(0 To 4) As String
Dim strNum As String, j As Integer, k As Integer, fmt As String
Dim h As Integer, xten As Integer, yten As Integer
Dim cardseg(1 To 4) As String, txt As String, d As String, txt2 As String
Dim locn As Integer, xfract As String, xhundred As String

On Error GoTo CardText_Err

strNum = Trim(Str(inNumber))
locn = InStr(1, strNum, ".")
'Check Decimal Places and rounding
If locn > 0 Then
  xfract = Mid(strNum, locn + 1)
 strNum = Left(strNum, locn - 1)
    If precision > 0 Then
        If Len(xfract) < precision Then
            xfract = xfract & String$(precision - Len(xfract), "0")
        ElseIf Len(xfract) > precision Then
            xfract = Format(Int(Val(Left(xfract, precision + 1)) / 10 + 0.5), _
            String$(precision, "0"))
        End If
        xfract = IIf(Val(xfract) > 0, xfract & "/" & 10 ^ precision, "")
    Else
        strNum = Val(strNum) + Int(Val("." & xfract) + 0.5)
        xfract = ""
    End If
End If

h = Len(strNum)
If h > 12 Then
   'if more than 12 digits take only 12 (max. 999 Billion)
   'extra value will get truncated from left.
   strNum = Right(strNum, 12)
Else
   strNum = String$(12 - h, "0") & strNum
End If

GoSub initSection

txt2 = ""
For j = 1 To 4
    If Val(cardseg(j)) = 0 Then GoTo NextStep
    txt = ""
    For k = 3 To 1 Step -1
      Select Case k
        Case 3
            xten = Val(Mid(cardseg(j), k - 1, 1))
            If xten = 1 Then
                txt = ctu(10 + Val(Mid(cardseg(j), k, 1)))
            Else
                txt = ctt(xten) & ctu(Val(Mid(cardseg(j), k, 1)))
            End If
        Case 1
            yten = Val(Mid(cardseg(j), k, 1))
            xhundred = ctu(yten) & IIf(yten > 0, bmth(1), "") & txt
            Select Case j
                Case 2
                    d = bmth(2)
                Case 3
                    d = bmth(3)
                Case 4
                    d = bmth(4)
            End Select
            txt2 = xhundred & d & txt2
    End Select
   Next
NextStep:
Next

If Len(txt2) = 0 And Len(xfract) > 0 Then
    txt2 = xfract & " only. "
ElseIf Len(txt2) = 0 And Len(xfract) = 0 Then
    txt2 = ""
Else
  txt2 = txt2 & IIf(Len(xfract) > 0, " and" & xfract, "") & " only."
End If

CardText = txt2

CardText_Exit:
Exit Function

initSection:
ctu(0) = ""
ctu(1) = " One"
ctu(2) = " Two"
ctu(3) = " Three"
ctu(4) = " Four"
ctu(5) = " Five"
ctu(6) = " Six"
ctu(7) = " Seven"
ctu(8) = " Eight"
ctu(9) = " Nine"
ctu(10) = " Ten"
ctu(11) = " Eleven"
ctu(12) = " Twelve"
ctu(13) = " Thirteen"
ctu(14) = " Fourteen"
ctu(15) = " Fifteen"
ctu(16) = " Sixteen"
ctu(17) = " Seventeen"
ctu(18) = " Eighteen"
ctu(19) = " Nineteen"

ctt(0) = ""
ctt(1) = " Ten"
ctt(2) = " Twenty"
ctt(3) = " Thirty"
ctt(4) = " Fourty"
ctt(5) = " Fifty"
ctt(6) = " Sixty"
ctt(7) = " Seventy"
ctt(8) = " Eighty"
ctt(9) = " Ninety"

bmth(0) = ""
bmth(1) = " Hundred"
bmth(2) = " Thousand"
bmth(3) = " Million"
bmth(4) = " Billion"

cardseg(4) = Mid(strNum, 1, 3)
cardseg(3) = Mid(strNum, 4, 3)
cardseg(2) = Mid(strNum, 7, 3)
cardseg(1) = Mid(strNum, 10, 3)
Return

CardText_Err:
CardText = ""
MsgBox Err.Description, , "CardText()"
Resume CardText_Exit
End Function

The Function name CardText() is derived from MS-Word Number Format Switch \* CardText. The CardText() Function can accept a maximum value of 10^12-1 or up to 999 Billion. For most applications this will be sufficient. Passing a Value greater than this will get truncated from left.

The CardText() Function accepts two parameters and the second one is Optional. The second parameter controls the number of digits after decimal places.

By default the CardText() Function will round-off fractional part, if present, to two decimal places when second parameter is omitted.

To try out the Code you may open VBA Editing Window (Alt+F11) and open Immediate Window (Ctrl+G) and type the following statement or similar one with different Value or Expression:

Example: ? CardText(1234.5678,3) will produce the result shown below.

Result: One Thousand Two Hundred Thirty Four and 568/1000 only.

The first parameter can be a Number or an Expression that evaluates to a Numeric Value.

If the second parameter is zero then the Number is rounded to the next highest Integer.

Example: ? CardText(1234.5678,0)

Result: Thousand Two Hundred Thirty Five only.

To change the output to upper-case or lower-cases letters enclose the CardText() Function in UCase() or LCase() built-in function respectively.

Example: ? UCase(CardText(1234.5678))

Result: ONE THOUSAND TWO HUNDRED THIRTY FOUR AND 57/100 ONLY.

To prefix a Currency Description use the following example:

Example: ? "Dollars" & CardText(1234.5678)
Or
="Dollars" & CardText([UnitPrice]) on Forms or Reports.

Result: Dollars One Thousand Two Hundred Thirty Four and 57/100 only.

You may try the Function on Form or Report with data field Value as input.

The CardText() Function is not extensively field tested and if you find bugs please let me know. Use it at your own risk.

Any suggestions for improvement are welcome.

Seriality Control - Missing Numbers
Wave Shaped Reminder Ticker
No Data and Report Error
Lost Links of External Tables
Link External Tables with VBA

Custom Report Wizard

noreply@blogger.com (a.p.r. pillai) — Thu, 25 Dec 2008 12:40:00 +0000

After designing and working with a Form Wizard it is natural to think about designing a Report Wizard too. Because of the designing task of a Form and Report is almost same, except Page Footer with Page Number and date.

If you have gone through the designing task of the Form Wizard then you don't have to do it again. Please go through the earlier Post: Custom made Form Wizard to understand the designing task of the FormWizard or to download it from there.

Do the following few simple steps and the ReportWizard is ready:

Make a copy of the FormWizard and rename it as ReportWizard.

Open the ReportWizard in Design View.

Change the List Box and Combo Box headings to read as Report Format and Select Table/Query for Report respectively.

Change the word Form to Report in the left side labels.

Display the Code Module of the ReportWizard by selecting View - ->Code (or Alt+F11).

Press Ctrl+A to select the entire Code in the Form Module and press Delete Key to delete the Code.

Copy and Paste the Code given below into the ReportWizard Form Module and save the Form:


Option Compare Database
Option Explicit
Dim DarkBlue As Long, twips As Long, xtyp As Integer, strFile As String
Dim MaxSeq As Integer
Private Sub cmdBack_Click()
   Me.Page1.Visible = True
   Me.Page1.SetFocus
   Me.Page2.Visible = False

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.NAME
End Sub

Private Sub cmdCancel2_Click()
DoCmd.Close acForm, Me.NAME
End Sub

Private Sub cmdForm_Click()
If xtyp = 1 Then
   Columns
Else
   Tabular
End If
DoCmd.Close acForm, Me.NAME

cmdForm_Click_Exit:
Exit Sub

cmdForm_Click_Err:
MsgBox Err.Description, , "cmdForm_Click"
Resume cmdForm_Click_Exit
End Sub

Private Sub cmdNext_Click()
Dim vizlist As ListBox, lcount As Integer, chkflag As Boolean
Dim FildList As ListBox, strName As String, strRSource As String
Dim cdb As Database, doc As Document
Dim Tbl As TableDef, Qry As QueryDef, QryTyp As Integer
Dim flag As Byte, FieldCount As Integer, j As Integer

On Error GoTo cmdNext_Click_Err

Set vizlist = Me.WizList
lcount = vizlist.listcount - 1

chkflag = False
For j = 0 To lcount
  If vizlist.Selected(j) = True Then
    xtyp = j + 1
    chkflag = True
  End If
Next

If IsNull(Me![FilesList]) = True Then
   MsgBox "Select a File from Table/Query List. ", vbOKOnly + vbExclamation, "cmdNext"
   Me.WizList.Selected(0) = True
Else
    strFile = Me!FilesList

   Me.Page2.Visible = True
   Me.Page2.SetFocus
   Me.Page1.Visible = False
   
Set cdb = CurrentDb
flag = 0
For Each Tbl In cdb.TableDefs
    If Tbl.NAME = strFile Then
       flag = 1
    End If
Next
For Each Qry In cdb.QueryDefs
    If Qry.NAME = strFile Then
       flag = 2
    End If
Next
If flag = 1 Then
    Set Tbl = cdb.TableDefs(strFile)
    Set FildList = Me.FldList
    strRSource = ""
    FieldCount = Tbl.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Tbl.Fields(j).NAME
        Else
            strRSource = strRSource & ";" & Tbl.Fields(j).NAME
        End If
    Next
ElseIf flag = 2 Then
    Set Qry = cdb.QueryDefs(strFile)
    strRSource = ""
    FieldCount = Qry.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Qry.Fields(j).NAME
        Else
            strRSource = strRSource & ";" & Qry.Fields(j).NAME
        End If
    Next
End If

Me.FldList.RowSource = strRSource
End If

cmdNext_Click_Exit:
Exit Sub

cmdNext_Click_Err:
MsgBox Err & ": " & Err.Description, , "cmdNext_Click"
Resume cmdNext_Click_Exit
End Sub



Private Sub FilesList_NotInList(NewData As String, Response As Integer)
'Not in List
End Sub

Private Sub Form_Load()
Dim strRSource As String, FList As ComboBox
Dim cdb As Database, MaxTables As Integer, rst As Recordset
Dim Tbl As TableDef, Qry As QueryDef, fld As Field
Dim j As Integer, strSQL1 As String, rstcount As Integer
Dim MaxSeq As Integer, mMax

On Error Resume Next

strSQL1 = "SELECT MSysObjects.Name " _
& "FROM MSysObjects  " _
& "WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) " _
& "AND ((Left([Name],4))<>'WizQ') AND ((Left([Name],1))<>'~') " _
& "AND ((MSysObjects.Flags)=0)) " _
& "ORDER BY MSysObjects.Type, MSysObjects.Name; "

mMax = 100
DoCmd.Restore

DarkBlue = 8388608
twips = 1440

Set cdb = CurrentDb
Set Qry = cdb.QueryDefs("WizQuery")
If Err = 3265 Then
  Set Qry = cdb.CreateQueryDef("WizQuery")
  Qry.sql = strSQL1
  cdb.QueryDefs.Append Qry
  cdb.QueryDefs.Refresh
  Err.Clear
End If

Me.FilesList.RowSource = "WizQuery"
Me.FilesList.Requery

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox Err & ": " & Err.Description, , "Form_Open"
Resume Form_Open_Exit
End Sub

Private Sub cmdLeft_Click()
LeftAll 1
End Sub

Private Sub cmdLeftAll_Click()
LeftAll 2
End Sub
Private Sub cmdright_Click()
RightAll 1
End Sub

Private Sub cmdRightAll_Click()
RightAll 2
End Sub

Public Function Tabular()
Dim cdb As Database, FldList() As String, Ctrl As Control
Dim Rpt As Report, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
Dim Rpttemp As Report, lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
Dim strTblQry As String, intflds As Integer, lstcount As Long
Dim RptFields As ListBox, j As Integer, mMax
Dim PgSection As Section, DetSection As Section

'Create Report with Selected Fields

On Error Resume Next

Set RptFields = Me.SelList
lstcount = RptFields.listcount

If lstcount = 0 Then
   MsgBox "Fields Not Selected for Report! "
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
Set Rpt = CreateReport
Set PgSection = Rpt.Section(acPageHeader)
    PgSection.Height = 0.6667 * twips

Set DetSection = Rpt.Section(acDetail)
    DetSection.Height = 0.1667 * twips

For j = 0 To lstcount
  FldList(j) = RptFields.ItemData(j)
Next

With Rpt
    .Caption = strFile
    .RecordSource = strFile
    
    lngtxtwidth = 0.5 * twips
    lngTxtLeft = 0.073 * twips
    lngTxtTop = 0
    lngTxtHeight = 0.1668 * twips

    lnglblwidth = lngtxtwidth
    lngLblleft = lngTxtLeft
    lngLblTop = 0.5 * twips
    lngLblheight = lngTxtHeight
End With

For j = 0 To lstcount
   Set Ctrl = CreateReportControl(Rpt.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .BorderStyle = 1
       .NAME = FldList(j)
       lngTxtLeft = lngTxtLeft + (0.5 * twips)
    End With
   
   Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
    With Ctrl
       .Caption = FldList(j)
       .NAME = FldList(j) & " Label"
       .Width = (0.5 * twips)
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .BorderColor = 0
       .BorderStyle = 1
       .FontWeight = 700 ' Bold
       lngLblleft = lngLblleft + (0.5 * twips)
    End With
Next

lnglblwidth = 4.5 * twips
lngLblleft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblheight = 0.323 & twips
lnglblwidth = 4.5 & twips
 Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 16
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With
On Error GoTo Tabular_Err

Page_Footer Rpt

DoCmd.OpenReport Rpt.NAME, acViewPreview

Tabular_Exit:
Exit Function

Tabular_Err:
MsgBox Err.Description, , "Tabular"
Resume Tabular_Exit
End Function

Public Function Columns()
Dim cdb As Database, FldList() As String, Ctrl As Control
Dim Rpt As Report, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
Dim lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
Dim strTblQry As String, intflds As Integer, lstcount As Long
Dim FrmFields As ListBox, j As Integer, mMax
Dim HdSection As Section, DetSection As Section

'Create Report with Selected Fields

On Error Resume Next

Set FrmFields = Me.SelList
lstcount = FrmFields.listcount

If lstcount = 0 Then
   MsgBox "Fields Not Selected for Report! "
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
Set Rpt = CreateReport

Set HdSection = Rpt.Section(acPageHeader)
    HdSection.Height = 0.6667 * twips

Set DetSection = Rpt.Section(acDetail)
    DetSection.Height = 0.166 * twips

For j = 0 To lstcount
  FldList(j) = FrmFields.ItemData(j)
Next

With Rpt
    .Caption = strFile
    .RecordSource = strFile
    lngtxtwidth = 1.5 * twips
    lngTxtLeft = 1.1 * twips
    lngTxtTop = 0.0417 * twips
    lngTxtHeight = 0.2181 * twips

    lnglblwidth = lngtxtwidth
    lngLblleft = 0.073 * twips
    lngLblTop = 0.0417 * twips
    lngLblheight = 0.2181 * twips
End With

For j = 0 To lstcount

   Set Ctrl = CreateReportControl(Rpt.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .FontName = "Verdana"
       .FontSize = 8
       .FontWeight = 700
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .NAME = FldList(j)
       .BackColor = RGB(255, 255, 255)
       .BorderStyle = 1
       .SpecialEffect = 0
       If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
        lngTxtTop = (0.0417 * twips)
        lngTxtLeft = lngTxtLeft + (2.7084 * twips)
       Else
        lngTxtTop = lngTxtTop + .Height + (0.1 * twips)
       End If
    End With

   Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acDetail, FldList(j), FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
    With Ctrl
       .Caption = FldList(j)
       .Height = (0.2181 * twips)
       .NAME = FldList(j) & " Label"
       .Width = twips
       .ForeColor = 0
       .BorderStyle = 0
       .FontWeight = 400
       If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
        lngLblTop = (0.0417 * twips)
        lngLblleft = lngLblleft + (2.7083 * twips)
       Else
        lngLblTop = lngLblTop + .Height + (0.1 * twips)
       End If
    End With
Next

lnglblwidth = 4.5 * twips
lngLblleft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblheight = 0.323 & twips
lnglblwidth = 4.5 & twips
 Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 20
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With
   
DoCmd.OpenReport Rpt.NAME, acViewPreview

Columns_Exit:
Exit Function

Columns_Err:
MsgBox Err.Description, , "Columns"
Resume Columns_Exit
End Function

Public Function Page_Footer(ByRef obj)
Dim lngWidth As Long, ctrwidth As Long, ctrlCount As Long
Dim j As Long, cdb As Database
Dim lngleft As Long, lngtop As Long, LineCtrl As Control, Ctrl As Control
Dim rptSection As Section, leftmost As Long, lngheight As Long
Dim rightmost As Long, RightIndx As Integer
'
'Note : The Controls appearing in Detail Section from left to Right
'       is not indexed 0 to nn in the order of placing,
'       instead 1st control placed in the Section has index value 0
'       irrespective of its current position.
'
'On Error GoTo Page_Footer_Err

Set cdb = CurrentDb
Set rptSection = obj.Section(acDetail)

ctrlCount = rptSection.Controls.Count - 1

lngleft = rptSection.Controls(0).Left
rightmost = rptSection.Controls(0).Left
'indexed 0 control may not be the leftmost control on the Form/Report
'so find the leftmost control's left value
For j = 0 To ctrlCount
 leftmost = rptSection.Controls(j).Left
 
 If leftmost < lngleft Then
   lngleft = leftmost
 End If
 If leftmost > rightmost Then
   rightmost = leftmost
   RightIndx = j
 End If
Next
 
lngtop = 0.0208 * 1440
lngWidth = 0: ctrwidth = 0

   lngWidth = rightmost + rptSection.Controls(RightIndx).Width
   lngWidth = lngWidth - lngleft
   
  Set LineCtrl = CreateReportControl(obj.NAME, acLine, acPageFooter, "", "", lngleft, lngtop, lngWidth, 0)
  Set Ctrl = LineCtrl
  LineCtrl.BorderColor = 12632256
  LineCtrl.BorderWidth = 2
  LineCtrl.NAME = "ULINE"
  
lngtop = 0.0418 * 1440
lngleft = LineCtrl.Left
lngWidth = 2 * 1440
lngheight = 0.229 * 1440

'draw PageNo control at the Report footer
Set LineCtrl = CreateReportControl(obj.NAME, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Page : ' & [page] & ' / ' & [pages] "
   .NAME = "PageNo"
   .FontName = "Verdana"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 1
End With
'draw Date Control at the right edge of the Line Control
'calculate left position of Date control

lngleft = (LineCtrl.Left + Ctrl.Width) - lngWidth
Set LineCtrl = CreateReportControl(obj.NAME, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
With LineCtrl
   .ControlSource = "='Date : ' & Format(Date(),'dd/mm/yyyy') "
   .NAME = "Dated"
   .FontName = "Verdana"
   .FontSize = 10
   .FontWeight = 700
   .TextAlign = 3
End With

Page_Footer_Exit:
Exit Function

Page_Footer_Err:
MsgBox Err.Description, "Page_Footer"
Resume Page_Footer_Exit
End Function

Private Function RightAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

On Error GoTo RightAll_Err
If SelectionType = 0 Then
   Exit Function
End If
Set FldList = Me.FldList
Set SelctList = Me.SelList

listcount = FldList.listcount - 1
strRSource = SelctList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If FldList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = FldList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & FldList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = FldList.ItemData(j)
                Else
                    strRS2 = strRS2 & ";" & FldList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = strRS2
        SelctList.Requery
        FldList.Requery
    Case 2

        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = FldList.ItemData(j)
            Else
                strRSource = strRSource & ";" & FldList.ItemData(j)
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = ""
        SelctList.Requery
        FldList.Requery
End Select

RightAll_Exit:
Exit Function

RightAll_Err:
MsgBox Err.Description, , "RightAll"
Resume RightAll_Exit
End Function


Private Function LeftAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

On Error GoTo LeftAll_Err

If SelectionType = 0 Then
   Exit Function
End If

Set FldList = Me.FldList
Set SelctList = Me.SelList

listcount = SelctList.listcount - 1
strRSource = FldList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If SelctList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = SelctList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & SelctList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = SelctList.ItemData(j)
                Else
                    strRS2 = strRS2 & ";" & SelctList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRS2
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
    Case 2
        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = SelctList.ItemData(j)
            Else
                strRSource = strRSource & ";" & SelctList.ItemData(j)
            End If
        Next
        SelctList.RowSource = ""
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
End Select

LeftAll_Exit:
Exit Function

LeftAll_Err:
MsgBox Err.Description, , "LeftAll"
Resume LeftAll_Exit

End Function

Open the ReportWizard in Normal View.

Select a Table or Query from the Combo Box.

Select Tabular Wizard option from above.

Click OK to load the selected Table/Query Field List and open the List of Fields.

Select the Fields for the Report from the List Box.

Click Finish to create the Report.

Normally, after creating the Report we need to modify the Detail Section Controls to make their sizes according to the data type and field sizes. After these changes the Report Footer created by the Wizard may not match with the modification that we have made. But, we already have a solution for this in an earlier Post with the Title: Draw Page Border. One of the following two Programs presented there can be used for drawing a new Page Footer (after deleting the existing Page Footer) or to resize it after changes made to the Detail Section Controls.

DrawPageFooter()

ReSizePageFooter()

There are other Reports related Functions also presented there to make Report Designing tasks easier. You may take a look at them as well.

You can create beautiful 3D Headings on the Report or Form with Labels or Text Boxes (Text Box values can be drawn from data Fields). Take a look at the sample Report Headings created with a 3D-Text Creation Wizard:

The following four Posts are dedicated for 3D Text Styles and you can download the 3D-Text Wizard from any of them:

Create 3D Headings on Forms

Border 2D Heading Text

Border 3D Heading

Shadow 3D Heading Style

After creating the 3D-Text you can customize it by changing the Fonts, Fore-Color and Styles like Bold, Italics or Underline.

Download - File: ReportWizard.zip (Size:105K)

Wave Shaped Reminder Ticker
No Data and Report Error
Lost Links of External Tables
Link External Tables with VBA
Source Connect Str Property and ODBC

A Custom Made Form Wizard

noreply@blogger.com (a.p.r. pillai) — Fri, 12 Dec 2008 21:29:00 +0000

Want to know how Form Wizards work? We will build one of our own and try it out. Perhaps, you may ask why we should do this when MS-Access already has a Form Wizard. Very true and I thought on those lines for some time, before I decided to give it a try and create the one that I need. Mainly for two reasons:

Even though MS-Access creates a Form on ready to use basis, most of the time we must modify this to make it a better looking one. Needs more time for resizing, shaping and arranging the fields and labels.

It creates Text Boxes of various sizes and shapes depending on the data type and needs more time to shape it up.

If you create a Tabular Form using Employee Table from Northwind.mdb sample database, you will know what I am talking about.

Up to Access2000, when the Table or Query have more fields then all of them cannot be placed on the 22cm width of the Form and ends up in error. Later Versions squeezes the controls to accommodate all the fields on the Form.

This is where I thought I need a Wizard to create a Tabular Form with equal sized fields (all fields with half an inch in size) so that we can accommodate more fields on the Form. It will be easier to select all of the fields together, re-size quickly and space them out at one step. Fields which needs more width can be re-sized on a case to case basis later. This way we can save some design time. A Tabular Form image created by the Custom Form Wizard (it looks almost like a Datasheet) is given below:

The Form Wizard has a simple design and easy to make, except the VBA Programs. You can download this FormWizard from the bottom of this Post and check the design, Property settings, VBA Routines etc. An image of the FormWizard in running mode is given below. The Wizard can create a simple Form in Column Format or Tabular Format. You can select a Table or Query for the Form from a Combo Box.

Open a new Form.

Insert a Tab Control on the Detail Section of the Form.

Select the First Page of the Tab Control and display the Property Sheet (View - -> Properties).

Change the Caption Property Value to Select Table. This description now appears on the First Page of the Tab Control.

Create a List Box as shown on the design and position its child label on the top and give the Caption value Form Type.

Create a Label at the left side of the List Box and enter the Caption Text as shown.

Click on the List Box and display the Property Sheet.

Change the following Property Values as shown below:



Name                :  wizlist

Row Source Type     :  Value List

Row Source          :  1;"Form Wizard : Columns";2;"Form Wizard : Tabular"

Column Count        :  2

Column Head         :  No

Column Width        :  0";1"

Bound Column        :  1

Turn off the Wizard Tool on the Toolbox. Select the Combo Box Tool and draw a Combo Box as shown, below the List Box, and change the Property Values as given below:



Name                    :  FilesList

Row Source Type         :  Table/Query

Row Source              :  WizQuery

Column Count            :  1

Column Width            :  1"

Bound Column            :  1

List Width              :  1"

Limit to List           :  Yes

Create a Label to the left of the Combo Box and change the Caption value as shown.

Create a Command Button below and change the Caption to OK.

Create a second Command Button to the right and change the Caption to Cancel.

Select the second Tab Control and change its Caption property Value to Select Fields.

Create a List Box for Field List and a Second List Box for Selected Fields side by side as shown on the above design.

Select the first List Box at the left, display the Property Sheet and change the Property Values as shown below:



Name                :  FldList

Column Count        :  1

Column Head         :  No

Column Widths       :  2"

Bound Column        :  1

Select the second List Box and change the Name Property to SelList and change other Properties to the same Values as given above.

Create four small Command Buttons between the List Boxes as shown on the design.

Change their Name property Values as cmdRight, cmdRightAll, cmdLeft., cmdLeftAll from first Command Button on the top to the fourth one at the bottom respectively.

Change their Caption Property Values with >, >>, < and << symbols as shown.

Create three Command Buttons below the List Boxes.

Change the Name Property Value of the left Command Button to cmdBack and the Caption Property Value to <<Back.

Change the Name Property Value of the Command Button in the middle to cmdForm and the Caption Property Value to Finish.

Change the Name Property Value of the right side Command Button to cmdCancel2 and the Caption Property Value to Cancel.

Click to the right side of the second page of the Tab Control to select the Tab Control and display the Property Sheet.

Change the following Property Values:



Name            :  TabCtl0

Back Style      :  Transparent

Style           :  None

Save the Form (File - -> Save) with the name FormWizard.

Display the Code Module of the Form (View - ->Code), while the Form is still in design View, Copy the entire Code given below and Paste it into the Code Module of the Form and save the Form.


Option Compare Database
Option Explicit
Dim DarkBlue As Long, twips As Long, xtyp As Integer, strFile As String

Private Sub cmdBack_Click()
   Me!FileList = Null

   Me.Page1.Visible = True
   Me.Page1.SetFocus
   Me.Page2.Visible = False

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.NAME
End Sub

Private Sub cmdCancel2_Click()
DoCmd.Close acForm, Me.NAME
End Sub

Private Sub cmdForm_Click()
If xtyp = 1 Then
   Columns
Else
   Tabular
End If

DoCmd.Close acForm, Me.NAME

cmdForm_Click_Exit:
Exit Sub

cmdForm_Click_Err:
MsgBox Err.Description, , "cmdForm_Click"
Resume cmdForm_Click_Exit
End Sub

Private Sub cmdNext_Click()
Dim vizlist As ListBox, lcount As Integer, chkflag As Boolean
Dim FildList As ListBox, strName As String, strRSource As String
Dim cdb As Database, doc As Document
Dim Tbl As TableDef, Qry As QueryDef, QryTyp As Integer
Dim flag As Byte, FieldCount As Integer, j As Integer

On Error GoTo cmdNext_Click_Err

Set vizlist = Me.WizList
lcount = vizlist.listcount - 1

chkflag = False
For j = 0 To lcount
  If vizlist.Selected(j) = True Then
    xtyp = j + 1
    chkflag = True
  End If
Next

If IsNull(Me![FilesList]) = True Then
   MsgBox "Select a File from Table/Query List. ", vbOKOnly + vbExclamation, "cmdNext"
   Me.WizList.Selected(0) = True
Else
   strFile = Me!FilesList
   Me.Page2.Visible = True
   Me.Page2.SetFocus
   Me.Page1.Visible = False
   
Set cdb = CurrentDb
flag = 0
For Each Tbl In cdb.TableDefs
    If Tbl.NAME = strFile Then
       flag = 1
    End If
Next
For Each Qry In cdb.QueryDefs
    If Qry.NAME = strFile Then
       flag = 2
    End If
Next
If flag = 1 Then
    Set Tbl = cdb.TableDefs(strFile)
    Set FildList = Me.FldList
    strRSource = ""
    FieldCount = Tbl.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Tbl.Fields(j).NAME
        Else
            strRSource = strRSource & ";" & Tbl.Fields(j).NAME
        End If
    Next
ElseIf flag = 2 Then
    Set Qry = cdb.QueryDefs(strFile)
    strRSource = ""
    FieldCount = Qry.Fields.Count - 1
    For j = 0 To FieldCount
        If Len(strRSource) = 0 Then
            strRSource = Qry.Fields(j).NAME
        Else
            strRSource = strRSource & ";" & Qry.Fields(j).NAME
        End If
    Next
End If

Me.FldList.RowSource = strRSource
End If

cmdNext_Click_Exit:
Exit Sub

cmdNext_Click_Err:
MsgBox Err & ":" & Err.Description, , "cmdNext_Click"
Resume cmdNext_Click_Exit
End Sub

Private Sub FilesList_NotInList(NewData As String, Response As Integer)
'Add item
End Sub

Private Sub Form_Load()
Dim strRSource As String, FList As ComboBox
Dim cdb As Database, MaxTables As Integer, rst As Recordset
Dim Tbl As TableDef, Qry As QueryDef
Dim j As Integer, strSQL1 As String, rstcount As Integer

On Error Resume Next
DoCmd.Restore

strSQL1 = "SELECT MSysObjects.Name " _
& "FROM MSysObjects " _
& "WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) " _
& "AND ((Left([Name],4))<>'WizQ') AND ((Left([Name],1))<>'~') " _
& "AND ((MSysObjects.Flags)=0)) " _
& "ORDER BY MSysObjects.Type, MSysObjects.Name; "


DarkBlue = 8388608
twips = 1440

Set cdb = CurrentDb
Set Qry = cdb.QueryDefs("WizQuery")
If Err = 3265 Then
  Set Qry = cdb.CreateQueryDef("WizQuery")
  Qry.sql = strSQL1
  cdb.QueryDefs.Append Qry
  cdb.QueryDefs.Refresh
  Err.Clear
End If

Set FList = Me.FilesList
Me.FilesList.RowSource = "WizQuery"
Me.FilesList.Requery

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & ": " & Err.Description, , "Form_Load"
Resume Form_Load_Exit
End Sub

Private Sub cmdLeft_Click()
LeftAll 1
End Sub

Private Sub cmdLeftAll_Click()
LeftAll 2
End Sub
Private Sub cmdright_Click()
RightAll 1
End Sub

Private Sub cmdRightAll_Click()
RightAll 2
End Sub

Public Function Tabular()
'-------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Sept-2000
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
Dim cdb As Database, FldList() As String, Ctrl As Control
Dim frm As Form, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
Dim lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
Dim strTblQry As String, intflds As Integer, lstcount As Long
Dim FrmFields As ListBox, j As Integer
Dim HdSection As Section, DetSection As Section

'Create Form with Selected Fields

On Error GoTo Tabular_Err

Set FrmFields = Me.SelList
lstcount = FrmFields.listcount

If lstcount = 0 Then
   MsgBox "Fields Not Selected for the Form"
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
Set frm = CreateForm
Application.RunCommand acCmdFormHdrFtr

With frm
    .DefaultView = 1
    .ViewsAllowed = 0
    .DividingLines = False
    .Section(acFooter).Visible = True
    .Section(acHeader).DisplayWhen = 0
    .Section(acHeader).Height = 0.5 * 1440
    .Section(acFooter).Height = 0.1667 * 1440
End With

Set HdSection = frm.Section(acHeader)
    HdSection.Height = 0.6667 * twips

Set DetSection = frm.Section(acDetail)
    DetSection.Height = 0.166 * twips


For j = 0 To lstcount
  FldList(j) = FrmFields.ItemData(j)
Next

With frm
    .Caption = strFile
    .RecordSource = strFile
    lngtxtwidth = 0.5 * twips
    lngTxtLeft = 0.073 * twips
    lngTxtTop = 0
    lngTxtHeight = 0.166 * twips

    lnglblwidth = lngtxtwidth
    lngLblleft = lngTxtLeft
    lngLblTop = 0.5 * twips
    lngLblheight = lngTxtHeight
End With

For j = 0 To lstcount
   Set Ctrl = CreateControl(frm.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .FontName = "Verdana"
       .Width = (0.5 * twips)
       .FontSize = 8
       .ForeColor = 0
       .BorderColor = 12632256
       .NAME = FldList(j)
       .BackColor = 16777215
       .BorderStyle = 1
       .SpecialEffect = 0
       lngTxtLeft = lngTxtLeft + (0.5 * twips)
    End With
   
   Set Ctrl = CreateControl(frm.NAME, acLabel, acHeader, , FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
    With Ctrl
       .Caption = FldList(j)
       .NAME = FldList(j) & " Label"
       .Width = (0.5 * twips)
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .BorderStyle = 1
       .FontWeight = 700 ' Bold
       lngLblleft = lngLblleft + (0.5 * twips)
    End With
Next

lnglblwidth = 4.5 * twips
lngLblleft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblheight = 0.323 & twips
lnglblwidth = 4.5 & twips
 Set Ctrl = CreateControl(frm.NAME, acLabel, acHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 16
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With
   
DoCmd.OpenForm frm.NAME, acNormal

Tabular_Exit:
Exit Function

Tabular_Err:
MsgBox Err.Description, , "Tabular"
Resume Tabular_Exit
End Function

Public Function Columns()
'-------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Sept-2000
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
Dim cdb As Database, FldList() As String, Ctrl As Control
Dim frm As Form, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
Dim lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
Dim strTblQry As String, intflds As Integer, lstcount As Long
Dim FrmFields As ListBox, j As Integer
Dim HdSection As Section, DetSection As Section

'Create Form with Selected Fields

On Error GoTo Columns_Err

Set FrmFields = Me.SelList
lstcount = FrmFields.listcount

If lstcount = 0 Then
   MsgOK "Fields Not Selected for Form", "FormWizard"
   Exit Function
Else
   lstcount = lstcount - 1
End If

ReDim FldList(0 To lstcount) As String

Set cdb = CurrentDb
Set frm = CreateForm
Application.RunCommand acCmdFormHdrFtr
With frm
    .DefaultView = 0
    .ViewsAllowed = 0
    .DividingLines = False
    .Section(acFooter).Visible = True
    .Section(acHeader).DisplayWhen = 0
    .Section(acHeader).Height = 0.5 * 1440
    .Section(acFooter).Height = 0.1667 * 1440
End With

Set HdSection = frm.Section(acHeader)
    HdSection.Height = 0.6667 * twips

Set DetSection = frm.Section(acDetail)
    DetSection.Height = 0.166 * twips


For j = 0 To lstcount
  FldList(j) = FrmFields.ItemData(j)
Next

With frm
    .Caption = strFile
    .RecordSource = strFile
    lngtxtwidth = 1 * twips
    lngTxtLeft = 1.1 * twips
    lngTxtTop = 0
    lngTxtHeight = 0.166 * twips

    lnglblwidth = lngtxtwidth
    lngLblleft = 0.073 * twips
    lngLblTop = 0 '0.5 * twips
    lngLblheight = lngTxtHeight
End With

For j = 0 To lstcount

   Set Ctrl = CreateControl(frm.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
    With Ctrl
       .ControlSource = FldList(j)
       .FontName = "Verdana"
       .FontSize = 8
       .ForeColor = DarkBlue
       .BorderColor = DarkBlue
       .NAME = FldList(j)
       .BackColor = RGB(255, 255, 255)
       .ForeColor = 0
       .BorderColor = 9868950
       .BorderStyle = 1
       .SpecialEffect = 2
       If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
        lngTxtTop = 0
        lngTxtLeft = lngTxtLeft + (2.7084 * twips)
       Else
        lngTxtTop = lngTxtTop + .Height + (0.1 * twips)
       End If
    End With

   Set Ctrl = CreateControl(frm.NAME, acLabel, acDetail, FldList(j), FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
    With Ctrl
       .Caption = FldList(j)
       .NAME = FldList(j) & " Label"
       .Width = twips
       .ForeColor = 0
       .BorderColor = 0
       .BorderColor = 0
       .BorderStyle = 0
       .FontWeight = 400 ' Normal 700 ' Bold
       If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
        lngLblTop = 0
        lngLblleft = lngLblleft + (2.7083 * twips)
       Else
        lngLblTop = lngLblTop + .Height + (0.1 * 1440)
       End If
    End With
Next

lnglblwidth = 4.5 * twips
lngLblleft = 0.073 * twips
lngLblTop = 0.0521 * twips
lngLblheight = 0.323 & twips
lnglblwidth = 4.5 & twips
 Set Ctrl = CreateControl(frm.NAME, acLabel, acHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
   With Ctrl
        .Caption = strFile
        .TextAlign = 2
        .Width = 4.5 * twips
        .Height = 0.38 * twips
        .ForeColor = DarkBlue
        .BorderStyle = 0
        .BorderColor = DarkBlue
        .FontName = "Times New Roman"
        .FontSize = 18
        .FontWeight = 700 ' Bold
        .FontItalic = True
        .FontUnderline = True
   End With
   
DoCmd.OpenForm frm.NAME, acNormal

Columns_Exit:
Exit Function

Columns_Err:
MsgBox Err.Description, , "Columns"
Resume Columns_Exit
End Function

Private Function RightAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

On Error GoTo RightAll_Err
If SelectionType = 0 Then
   Exit Function
End If
Set FldList = Me.FldList
Set SelctList = Me.SelList

listcount = FldList.listcount - 1
strRSource = SelctList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If FldList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = FldList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & FldList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = FldList.ItemData(j)
                Else
                    strRS2 = strRS2 & ";" & FldList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = strRS2
        SelctList.Requery
        FldList.Requery
    Case 2

        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = FldList.ItemData(j)
            Else
                strRSource = strRSource & "; " & FldList.ItemData(j)
            End If
        Next
        SelctList.RowSource = strRSource
        FldList.RowSource = ""
        SelctList.Requery
        FldList.Requery
End Select

RightAll_Exit:
Exit Function

RightAll_Err:
MsgBox Err.Description, , "RightAll"
Resume RightAll_Exit
End Function


Private Function LeftAll(ByVal SelectionType As Integer)
Dim FldList As ListBox, SelctList As ListBox, strRSource As String
Dim listcount As Long, j As Long, strRS2 As String

On Error GoTo LeftAll_Err

If SelectionType = 0 Then
   Exit Function
End If

Set FldList = Me.FldList
Set SelctList = Me.SelList

listcount = SelctList.listcount - 1
strRSource = FldList.RowSource: strRS2 = ""

Select Case SelectionType
    Case 1
        For j = 0 To listcount
            If SelctList.Selected(j) = True Then
                If Len(strRSource) = 0 Then
                    strRSource = SelctList.ItemData(j)
                Else
                    strRSource = strRSource & "; " & SelctList.ItemData(j)
                End If
            Else
                If Len(strRS2) = 0 Then
                    strRS2 = SelctList.ItemData(j)
                Else
                    strRS2 = strRS2 & "; " & SelctList.ItemData(j)
                End If
            End If
        Next
        SelctList.RowSource = strRS2
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
    Case 2
        For j = 0 To listcount
            If Len(strRSource) = 0 Then
                strRSource = SelctList.ItemData(j)
            Else
                strRSource = strRSource & "; " & SelctList.ItemData(j)
            End If
        Next
        SelctList.RowSource = ""
        FldList.RowSource = strRSource
        SelctList.Requery
        FldList.Requery
End Select

LeftAll_Exit:
Exit Function

LeftAll_Err:
MsgBox Err.Description, , "LeftAll"
Resume LeftAll_Exit

End Function

NB:

Name

On Click

[Event Procedure]

On Click

[Event Procedure]

Open the FormWizard in normal View. Select one of the Form Design type Column or Tabular and select a Table or Query from the Combo Box below and Click OK. The List Box will appear with Field Names in the next step.

You can select one or more data fields of your choice and click the button with the > label to move the selected fields to the right side List Box. You can select all the Fields from the List by clicking on the command button with the >> label.

If you have second thoughts you can Remove the fields from the selected list the same way you selected from the first List Box, using the < and << labeled Command Buttons.

When you are ready to create the Form click on Finish Command Button.

The Form will be created and will open in Normal View. You may Save the Form and change to design view for modifications.

Download - File: FormWizard.zip (Size:99K)

Wave Shaped Reminder Ticker
No Data and Report Error
Lost Links of External Tables
Link External Tables with VBA
Source Connect Str Property and ODBC

Rounding Function MRound of Excel

noreply@blogger.com (a.p.r. pillai) — Sat, 06 Dec 2008 08:33:00 +0000

Normally we round numbers in calculations when the fractional part is 0.5 or more to 1 (next highest integer) or truncate it altogether. But, there are times that this fractional part itself to be rounded to a certain level so that it can be used for disbursement of money, like wages.

For example, in Currencies when the value ends up in 15 cents, 30 cents they should be rounded to 25 cents, 50 cents respectively for determining the requirement of correct number and denominations of Coins for disbursement. If we decide we need only 50 Cents Coins then a value of 25 Cents or more should be rounded to 50 Cents and less than 25 Cents to zero.

So, we need a function that can accept a value as precision and find multiples of that value and when the remainder value is half or more of the precision value it should be rounded to the next level. Not necessary that it should be a fractional value it can be any value as precision.

In MS-Access we have Round() Function that will only round the Double Precision numbers into the required number of Decimal Places or to the next integer level applying the normal rules that we are already familiar. There is a Worksheet Function MRound() in MS-Excel that can do these kind of calculations, but found nothing like that in MS-Access. We cannot go to Excel when we want this in Access.

We will write a MRound() Function in Access with the same name.

So here it is:


Public Function MRound(ByVal Number As Double, ByVal Precision As Double) As Double
Dim Y As Double

On Error GoTo MRound_Err

   Y = Int(Number / Precision) * Precision
   MRound = IIf(((Number - Y) / Precision * 100 + 0.1) >= 50, Y + Precision, Y)

MRound_Exit:
Exit Function

MRound_Err:
MsgBox Err.Description, , "MRound()"
MRound = 0
Resume MRound_Exit
End Function

Copy and paste the above Code into a Global Module of your Database and save the Module.

Open the Debug Window (Ctrl+G) to try it out directly.

Sample Runs:

? Mround(123.3,.2)

Result: 123.4

? Mround(123.24,.5)

Result: 123

? Mround(123.25,.5)

Result: 123.5

? Mround(123.74,.5)

Result: 123.5

? Mround(123.75,.5)

Result: 124

? Mround(10,3)

Result: 9

? Mround(11,3)

Result: 12

If you would like to share this Function across your Other MS-Access Databases then create a Library Database with the Function in it and set a Reference to the Library Database through Tools- ->References in VBA Window.

No Data and Report Error
Lost Links of External Tables
Link External Tables with VBA
Source Connect Str Property and ODBC
Access Live Data in Excel-2