Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Custom Calculator and Eval Function

When we think of a Calculator the image that comes into our mind is the traditional type with button labels 0-9 and with Arithmetic Operators. We have it in Windows Accessories Menu that can be converted into a Scientific Calculator as well. So we don't need to build that again in MS-Access.

We are considering another type of Calculator that can accept a User Defined Expression (consisting of Data Field Names, Constants, Arithmetic Operators, Parenthesis to alter the Order of Calculations), evaluate that expression and provide a quick result.

We don't need to embark on a fancy design task for this Calculator. All we need is a Text Box to enter the Expression, a Command Button and few lines of Code. The result of the calculation can be displayed either in MsgBox or in another Text Box.

Before we start working on the Calculator Project, let us take a look at one of the built-in functions EVAL(), which is the main work-horse of our Custom Calculator. With this Function we don't have to worry much about the calculation rules, like evaluate expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), do addition and subtractions last etc. All you need to do is to give the Expression in String Form into EVAL() Function and get the result as output, as simple as that.

Want to try few examples quickly to get a feel of it? Open the VBA Module Window (Alt+F11) and open the Debug Window (Ctrl+G). Type the following expressions in Debug Window and press Enter Key:

? EVAL("2+3*4/2")

Result: 8

? EVAL("(2+3)*4/2")

Result: 10

? EVAL("log(SQR(625))/log(10)")

Result: 1.39794000867204

? Eval("10^" & "Eval('log(Sqr(625))/log(10)')")

Result: 25

? EVAL("Datediff('yyyy',Forms!Employees!BirthDate,date())")

Result: 45 (the Employees Form must be open)

What we can see from the above examples is that you can write an expression using built-in Functions, Data Field References and Numeric Constants in a Text Box on a Form and submit it to Eval() Function, it can give you the result of that Expression. It gives the User the ability to define an expression and do calculations with the use of Data Field values on the Form.

Tip: The Eval() Function can do lot more than simply evaluating the formula, it can run another Function, trigger the Click Event of a Command Button or Run a Macro. For more details and examples of Eval() Function search Microsoft Access Help in VBA Window.

I have created a sample Database (you can download it from the bottom of this Post) for an Auto Dealer who extends Credits to his Customers with a nominal charge of interest rate 6%, 7% or 8% repayable in 24, 36 or 60 installments slabs respectively. The Salesman has been instructed to deal with the Customers suitably to promote Vehicle Sales. The Salesman has to decide which slab of repayment is appropriate for a particular customer, percentage of down payment, repayment schedule with applicable interest rate. The Salesman is given the freedom of allowing a discount up to a maximum of 20% on Retail Price.

An image of the Form he uses to run these calculations is given below:

This is a stand alone Form (can be linked to Table) with Unbound Text Boxes. The Text Box names are given as Labels to the left. While writing the Expression it is a must to qualify the control Names correctly like Forms!CustomCalculator!Balance. It will not accept references like Me!Balance. It is difficult to use these lengthy names to build the expression when several Field names are involved in the Formula.

We have a small VBA routine to accept the Text Box Names in square brackets [ ], to pick their values and replace it in the expression before submitting it to EVAL() function.

A Combo Box with Arithmetic Operators and Text Box Names in square brackets is also provided on the Screen for easy entry of expressions. The colored control below displays (displayed by the routine for information only) the replaced value of Text Boxes in the expression before submitting it to the EVAL() Function.

When the Calculate Command Button is clicked the result of the calculation is displayed in the Text Box with dark background and the label Result. The VBA Code is given below:

Private Sub cmdCalc_Click()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date    : November, 2008
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim str As String, loc1 As Integer, chk As Integer
Dim strout As String, loc2 As Integer, loc3 As Integer
Dim strin As String
Const sqrleft As String = "["
Const sqrright As String = "] "

On Error GoTo cmdCalc_Click_Err

'change the reference if different
str = Me![Expression]

loc1 = InStr(1, str, sqrleft)
If loc1 > 0 Then
   strin = Left(str, loc1 - 1)
   strout = Left(str, loc1 - 1)
   loc2 = InStr(loc1, str, sqrright)
End If
Do While loc2 > 0
   strin = strin & Mid(str, loc1, (loc2 - loc1) + 1)
   strout = strout & Me(Mid(str, loc1, (loc2 - loc1) + 1))
   loc1 = InStr(loc2 + 1, str, sqrleft)
   If loc1 > 0 Then
     loc2 = InStr(loc1, str, sqrright)
      If loc2 = 0 Then
         MsgBox "Errors in Expression, correct and retry. "
         Exit Sub
      Else
         strout = strout & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
         strin = strin & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
      End If
   Else
     loc3 = loc2
     loc2 = 0
   End If
Loop

If Len(str) > loc3 Then
   strout = strout & Mid(str, loc3 + 1)
End If

'this line can be removed if not required
Me![parsed] = strout

'change the reference, if different
Me![result] = Eval(strout)

cmdCalc_Click_Exit:
Exit Sub

cmdCalc_Click_Err:
MsgBox Err.Description, , "cmdCalc_Click()"
Resume cmdCalc_Click_Exit
End Sub

Private Sub cmdReset_Click()
Me![Expression] = Null
Me![parsed] = Null
End Sub

Note:There is no validation check included in the Code to detect misspelled names or unbalanced parenthesis etc. These short comings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections and re-try.

You can implement this program on any Form with small change in str = Me![Expression] and Me![result] = Eval(strout) lines in the Code, if different names are used. Customize the Combo Box contents based on your input Field Names.



Download Demo CustomCalc.zip


Share:

Data Editing And Security Issues

Data Entry and Editing is a major activity for maintaining the information up-to-date in databases, before we are able to prepare any meaningful output for human consumption. It is important that we make these tasks easier to the User by providing Combo Boxes, Check Boxes or Calculated Fields, like Payment-Due-Date after 30 days of Material-Delivery-Date and so on.

Another important aspect is data security. I am not referring to the protection against unauthorized access from outside, for which we have adequate built-in MS-Access Security features that can be put to use. Here, our emphasis is on unintentional changes or deletions of important information by Authorized Users.

For example, assume that our Employee database has a Memo Field that holds important detail information on educational and past experience of employees. Normally when the Cursor (Insertion Point) moves into a field the entire field contents will get highlighted and selected. At this point if the User's attention is drawn to something else and touches a key by mistake the field contents will be erased. If she is not aware of this or forgot to recall the lost data by pressing Ctrl+Z (Edit - -> Undo Typing) then it is lost for ever.

We will concentrate on this particular behavior of the Cursor and how we can give some form of protection against such inadvertent mishaps. The Cursor behavior, while entering into a field, is governed by the settings in the Keyboard Tab of Options. . . in Tools Menu. Find the three different options available under the Behavior entering field Options Group in the image below:

The first option Select entire field is the default setting. One of the other two settings is the most preferable one to avoid lose of information that we are focusing on. Out of the other two Options I prefer the last one, Go to end of field, why because even if you press the Delete Key or any other Key by mistake, while the insertion point is at the end of the field contents, nothing will happen to the existing information.

Since, this is the global setting of MS-Access Application, the changes that you make here manually affects all the Forms in all Databases that you load into your machine. Conversely, the database that you have designed on your machine will not carry this option setting along with it when open on some other machine in a multi-user environment. More over, we may not need this setting to take effect on all Forms in the Database either.

So the best approach is to enable this feature through VBA Sub-Routines, wherever we need it on Forms and turn it back to the default settings while the Form closes. If your database is shared on a Network the Behavior Entering Field default settings can be different in other machines set by the User. We don't want to alter this settings permanently.

Following are the numeric value of each Behavior Entering Field Option setting:

Behavior | Descritpion | Option Values
  1. Select Entire Field - 0
  2. Go to Start of Field - 1
  3. Go to End of Field - 2

When we open a Form, where we need this change of behavior, we will go through the following steps to enable it and when the Form is closed put the default setting back in its place:

  1. Save the current default setting of Behavior Entering Field, before changing it.
  2. Change the setting to Go to end of Field behavior for the current session of the Form.
  3. Reset it back to the saved value in Step-1 above before closing the Form.

We can achieve this with the following Event Procedures in the Form Module:

Option Compare Database
Dim DefaultBehavior As Integer

Private Sub Form_Load()
    DefaultBehavior = Application.GetOption("Behavior Entering Field")
    Application.SetOption "Behavior Entering Field", 2
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Application.SetOption "Behavior Entering Field", DefaultBehavior
End Sub

Copy and paste the above Code into the Form's Code Module and save the Form. The Dim DefaultBehavior As Integer statement must be placed at the Global area of the Module as shown above.

Open the Form in normal View and try moving the Cursor from one field to the other by tapping Tab Key or Enter Key. The insertion point will position at the end of the field contents.

Share:

Event Trapping Summary On Datasheet

How do we use Event Procedures like LostFocus(), GotFocus() on Datasheet view?

How to display Summation of numeric values on Datasheet view?

For answering both questions, we need a sample Table and a Datasheet Form.

Import the following Tables from the Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

  • Order_Details
  • Products

We require the Products table also, because it has a reference in the ProductID field of Order_Detail Table in a Combo Box. We don't want to leave the ProductID field empty on the Order_Detail Table, without the Products Table.

  1. To Create a Datasheet Form, click on the Order_Detail Table, select Form from Insert Menu and select Autoform: Datasheet from the displayed list of options in the Formwizard.

    A Datasheet form will be created and will open the Table contents in Datasheet View.

  2. Select Save As. . . from File Menu and give the name Order Details for the Form. The Datasheet Form will be saved, but it will not be closed.
  3. Select Design View from View Menu to change the Form in Design View mode.

The Form in Design View looks like the image given below or may be different depending on your version of MS-Access.

Doesn't matter how the Fields are arranged on the Form, whether in row format or Column Format the data will be always displayed in Datasheet format. The placement of Fields will not affect the way the data displayed on the Datasheet but the Tab Order does.

Let us try and find out how the Tab Order of Fields influences the Datasheet View.

  1. Change the Form into Datasheet View (View - - > Datasheet View) and check the order of fields appearing in there.
  2. Change the View into Design mode again and re-arrange the ProductID and UnitPrice fields by switching their places.
  3. Change the View back to Datasheet and inspect the data field order.

    There will not be any change in the Order of Fields displayed from previous View. If you move the Cursor using Tab Key then it moves in the same order of the fields' placement as you have seen earlier before switching the fields.

  4. Change the Form back to Design View again.
  5. Select Tab Order. . . from View menu. Click at the left border of the UnitPrice field on the Tab Order Control, click and drag it up and place below the OrderID field.

    Tip: You can click on the Auto Order Command Button to re-arrange the Tab Order according to the field placements on the Form.

  6. Open the Form in normal view now and check the change of field placements.

We will add one Unbound Text Box on the Form to calculate the Extended Price after adjusting Discounts of each item.

  1. Open the Form in design view, if you have closed it.
  2. Drag the Form Footer Section down to get more room to place another Text Box below, or you can place it to the right of the Discount Field too.
  3. Create a Text Box and write the formula =(1-[Discount])*[UnitPrice]*[Quantity] in it.
  4. While the Text Box is still in selected state display the Property Sheet (View - -> Properties).
  5. Change the Format Property value to Currency format. Change the Name Property value to Extended Price.
  6. Open the Form in normal view and check the newly added Text control's heading on the top. It will be something like Text10:.

    In Datasheet View of Forms MS-Access uses the Caption of the Child Labels attached to the Text Boxes as Field headings. We have changed the Name Property of the Text Box to Extended Price, but that is ignored here.

  7. Now, change the Form into design view and delete the Child Label attached to the Extended Price Text Box.
  8. Change to Datasheet view again and check the field name appearing on the top, it will be Extended Price now.

Datasheet Event Procedure

  1. To try an Event Procedure on the Datasheet view, copy and paste the following VBA Code into the Form's Code Module (View - -> Code to display the Code Module of the Form) and save the Form with the Code.
    Private Sub UnitPrice_LostFocus()
    Dim newUnitPrice As Double, msg As String
    Dim button As Integer
    
    button = vbQuestion + vbYesNo + vbDefaultButton2
    
    If Me![UnitPrice] = Me![UnitPrice].OldValue Then
       msg = "Replace UnitPrice: " & Me![UnitPrice].OldValue & vbCr & vbCr
       msg = msg & "with New Value: " & Me![UnitPrice]
    
       If MsgBox(msg, button, "UnitPrice_LostFocus()") = vbNo Then
            Me![UnitPrice] = Me![UnitPrice].OldValue
        End If
    End If
    
    End Sub
    

    We will attempt to trap the change in the UnitPrice field and will ask the User to confirm whether to retain the change or Cancel it.

  2. Open the Form in datasheet view and make some change in the UnitPrice Field and leave the Field by pressing Tab Key or Enter key.

A Message Box will appear asking for permission to retain the change or to cancel it.

Datasheets can be programmed with Event Procedures (Field level or Form level) for validation checks and display of information.

Display of Summary Information

Method-1

We will attempt to answer the second Question we have raised on top of this page.

  1. Open the Order_Details Form in Design View.
  2. Drag the Form Footer Section down to get enough room to place two Text Boxes. Create two Text Boxes in the Form Footer Section.
  3. Write the formula =Sum([Quantity]) in the first Text Box.
  4. Display the Property Sheet of the Text Box and change the Name Property value to TOTALQTY.
  5. Write the formula =Sum((1-[Discount])*[UnitPrice]*[Quantity]) in the second Text Box.
  6. Change the Name Property Value to TOTALVALUE.

When we open the Order_Details Form in Datasheet View it will calculate the Summary V
alues in TOTALQTY and TOTALVALUE Text Boxes on the Footer of the Form, but we must do something to display it.

The first thought that comes into one's mind is to create a MsgBox and display the results in it on some Event Procedure of the Form. Besides, changes may takes place on the records and they should reflect in the result summary values and we must be able to refresh the change before displaying it again.

We will implement this method before we settle down with a better one.

  1. Copy and paste the following Code into the Form's Code Module and save the Form:
    Private Sub Form_DblClick(Cancel As Integer)    
    Dim msg As String
         Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
    
         MsgBox msg
    End Sub
    
  2. Open the Form in Datasheet View.
  3. Double-Click on the Record Selector at the left border of the Form.

    A Message Box pops up with the Summary Values from the Text Boxes in the Form Footer Section.

  4. Make some changes on the Quantity/UnitPrice Fields and try Step-3 again. The change of Value will reflect on the Message Box.
  5. You can filter the Data on ProductID or on OrderID by Right-Clicking on these fields and selecting Filter by Selection or other Options available on the displayed Shortcut Menu and by executing Step-3 to get the Summary of selected records.

Method-2

After trying out the above method your response may be something like "Yah.. it serves the purpose, but it doesn't give the impression of a sophisticated method. After all it takes so many clicks and pop-up Message Boxes". I agree with you too.

With a small change of the above Code we can make the results the way you like it, I hope!.

  1. Open the Form in Design View.
  2. Display the Property Sheet (View - - > Properties).
  3. Select the Mouse Move Property and select Event Procedure from the drop down control.
  4. Click on the build (. . .) button at the right side of the Property to open the Form's Code Module.
  5. Cut the Code lines from within the Form_DblClick() Event Procedure:
    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    

    Leave the above lines alone and paste the Code into the Form_MouseMove() Event Procedure.

  6. Change the line that reads:

    MsgBox msg

    to

    Me.Caption = msg

    After the change the Code will look like the following:

    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    
    Private Sub Form_MouseMove(button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim msg As String
        Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
        Me.Caption = msg
    End Sub
    
  7. Open the Form in Datasheet View and move the Mouse over into the data area by crossing the Field Headings or the Record Selectors at the left side.
  8. Check the Title Area of the Datasheet View and you will find the Summary information is displayed there. A sample image is given below:

Now you can try changing the field values or filtering the data and moving the Mouse over the Field Headings or Record Selectors at the left to get the result on the Title Bar instantly. No Message Boxes or Double-Clicks and what you say about that?

Share:

Sum Min Max Avg ParamArray

I know your immediate response after looking at the Title will be, "I know all those things, tell me something that I don't know". Well, if you haven't come across the last item (that is an odd one) in the Title before, then that is what I am trying to do here, read on. The first four words are very familiar to us they are Built-in Functions in MS-Access and Worksheet Functions in Excel.

We will catch up with the last one later, after checking the usage of Min() Function (will represent the first four items in the title) in Excel and why we have some difficulty with it in MS-Access when compared with Microsoft Excel.

We are not forgetting the other Functions DCount(), DSum(), DMin(), DMax() and DAvg() of Access at all.

Let us look at the usage of Min() Worksheet Function in Excel. It can find the minimum Value from a Range of Cells in a single Column, from a Row of Cells across Columns or from a Range of Cells spread over several Columns and Rows.

But, when we come back to MS-Access the Min() Function can be used only in a single column (on a single Field) of Data in Query and in Header/Footer Sections of Forms or Reports. Then what do we do to find the Minimum value from more than one Field of data?

Have a look at the sample Table given below to get the gravity of the issue we are in here.

We have received Quotations for Electronic Items from three different Suppliers and we need to know which one is the lowest and from which Supplier? In this case our Min() Function has no use here unless we re-organize the above data into the following format:

To get the required result out this data we need two Queries and we will ignore the duplication of Descriptions, Supplier Names and the Table size in Records etc. for now.

  1. Need one Total Query to group on Desc field and the Min() Function to find the minimum Value from the Values Field.
  2. Need a second Query, using the first Query and the Table above as Source, JOINed on Desc and MinOfValues Columns of the Total Query with the Desc and Values Fields of the Table to pick all the records from the Table matching with minimum quoted values and Description.

I consider these steps are excessive work and I know you will agree too. Instead, we can write a User Defined Function with the use of ParamArray and pass the Field Names to the Function and find the Minimum Value from the list. Here is a simple Function with the use of ParamArray declaration to find the Minimum Value from a List of Values passed to it.

Public Function myMin(ParamArray InputArray() As Variant) As Double
'------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : November-2008
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim arrayLength As Integer, rtn As Double, j As Integer

'calculate number of elements in Array
arrayLength = UBound(InputArray())

'initialize Null values to 0
For j = 0 To arrayLength
   InputArray(j) = Nz(InputArray(j), 0)
Next
'initialize variable with 1st element value
'or if it is zero then a value with high magnitude
rtn = IIf(InputArray(0) = 0, 9999999999#, InputArray(0))

For j = 0 To arrayLength
    If InputArray(j) = 0 Then
 GoTo nextitem
   If InputArray(j) < rtn Then
        rtn = InputArray(j)
    End If
nextitem:
Next

myMin = rtn
End Function

Copy and Paste the above Code into a Global Module and save it.

Few simple rules must be kept in mind while writing User Defined Functions using the ParamArray declaration in the Parameter list of the Function.

  1. While declaring the Function, the Parameter Variable InputArray() (or any other name you prefer) must be declared with the keyword ParamArray, in place of ByRef or ByVal we normally use to declare parameters to functions.
  2. The Data Type must be Variant.
  3. The ParamArray declaration must be the last item in the Parameter list if the UDF accepts more than one Parameter.
  4. The Optional parameter declarations should not appear before the ParamArray declaration.
  5. Since the data type is Variant it can accept any type of values.

With the use of the above myMin() Function we have created a Query on the first Table given above. The SQL and the result image of the Query in Datasheet View are given below.

SELECT MaterialQuote.Desc,
 MaterialQuote.Supplier1,
 MaterialQuote.Supplier2,
 MaterialQuote.Supplier3,
 mymin([supplier1],
[supplier2],
[supplier3]) AS Minimum,
 IIf([minimum]=[supplier1],"Supplier1",IIf([minimum]=[supplier2],"Supplier2",IIf([minimum]=[supplier3],"Supplier3",""))) AS Quote
FROM MaterialQuote;

In the above example we have used only three Field Values to pass to the Function and these can vary depending on your requirement.

A modified version of the same Function is given below that accepts a Calculation Type value (range 0 to 3) as first Parameter and depending on that we can find Summary, Minimum, Maximum, or Average of values passed to it through the InputArray() Variable.

Public Function SMMAvg(ByVal calcType As Integer, ParamArray InputArray() As Variant) As Double
'------------------------------------------------------------------------
'calType : 0 = Summary'        : 1 = Minimum
'        : 2 = Maximum'        : 3 = Average
'------------------------------------------------------------------------
'Author  : a.p.r. pillai'Date    : November 2008
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim rtn, j As Integer, arrayLength As Integer
Dim NewValue As Variant

On Error GoTo SMMAvg_Err

If calcType < 0 Or calcType > 3 Then
     MsgBox "Valid calcType Values 0 - 3 only", , "SMMAvg()"
     Exit Function
End If

arrayLength = UBound(InputArray())
For j = 0 To arrayLength
   InputArray(j) = Nz(InputArray(j), 0)
Next

Select Case calcType
    Case 1
        rtn = InputArray(0)
        rtn = IIf(rtn = 0, 9999999999#, rtn)
    Case 2
        rtn = InputArray(0)
    Case Else
        rtn = 0
End Select

For j = 0 To arrayLength
    NewValue = InputArray(j)
    If NewValue = 0 Then
 GoTo nextitem
    Select Case calcType
        Case 0, 3
            rtn = rtn + NewValue
        Case 1
            rtn = IIf(NewValue < rtn, NewValue, rtn)
        Case 2
            rtn = IIf(NewValue > rtn, NewValue, rtn)
    End Select
nextitem:
Next

If calcType = 3 Then
   rtn = rtn / (arrayLength + 1)
End If

SMMAvg = rtn

SMMAvg_Exit:
Exit Function

SMMAvg_Err:
MsgBox Err.Description, , "SMMAVG()"
SMMAvg = 0
Resume SMMAvg_Exit
End Function 

The Function name was defined using the first letters of the Calculation Types that the Function can perform and I hope you like it too.

When any of the values in the InputArray() element is Zero then that is ignored and will not be taken as minimum value.

We can use this Function in Text Boxes on Forms or Reports by passing Values from other Controls. Use it at your own risk.

Share:

Textbox And Label Inner Margins

Introduction

Let it be a Form or Report, a good design will always catch the eye of the User and the onlooker alike. We all design them, but if we give the same Report or Form to five different people to design, they all will do it differently based on their individual skill and tastes, unless they all use the same built in wizards.

The user may give importance mainly to the information contained in a Report and insist only on getting it in the right order and layout. But, how to present this to him/her is your responsibility alone and depends on how much time you have to work on it. You will be designing a Report only once and it is part of your Project, how you do it is your own choice.

Your Report may also travel to places through Faxes or E-mails and likely to have a wider audience to it. Where-ever it goes I expect some one to ask who designed it when compared with other Reports floating around. Luckily, MS-Access has all the right Tools you need for designing strikingly beautiful Reports or Forms. Little more of your time and imagination can do wonders with simple tools available in Access.

Here, I would like to introduce you to few Properties of Text Boxes and Labels on a Report and how simple design changes can transform it into a nice looking Report. The following is an image of a Tabular Report designed with the Report Wizard of MS-Access.

Wizards are very good in laying out all the objects quickly on Forms or Reports with default formatting of Font Type, Size, Style and saves a fair amount of design time. All you need to do is to modify them to your liking.


A Sample Project

If you would like to try out this simple design step by step then you may Import the Shippers Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb MS-Access sample Database. The location address shown here is for MS-Access2003. If your Version of Access is different then look for change in \Office11\ part of the address. After importing click on the Table and select Report from Insert menu and select Auto Report: Tabular from the displayed options. The above Report will be created in no time. An image of the above Report in Print Preview is given below:

The modified version of the same Report in Print Preview below:

The transformation was easy with only few changes to the above design and I know what change you have noticed first. If I have turned on the borders of the Text Boxes and Labels alone then the Print Preview will look like the one below:

Make the following changes to the above design:

  1. Delete the thick line under the Header Labels.
  2. Point the Mouse on the vertical ruler to the left of the Header Label Shippers, so that it turns into an Arrow pointing to the right, and then Click and drag along the ruler downwards so that you could select all the Labels and Text Boxes in the Report Header, Page Header and Detail Sections together.

    Alternatively you can click on an empty area of the Report and drag the Mouse over all the controls to select them. Do not select the Page Footer Section Controls. We don't need them on this Report.

  3. Display the Property Sheet (View- -> Properties) and change the following Values:
    • Border Color = 9868950
    • Special Effect = Flat
    • Border Style = Solid
    • Border Width = Hairline

    You need to change only the Border Color Value, others will be there as default, if not then change them as given above.

  4. Select all the Field Header Labels alone in the Page Header Section, as we did in Step-2 above. Select Format- - > Align - -> Left to arrange the labels close together horizontally without leaving gaps between them.
  5. Display the Property Sheet of the selected Labels (View - -> Properties) and change the Top Property Value to 0 and Height Property Value to 0.4167"

    Centralizing Text Vertically

  6. Centralize the Text horizontally within the Labels by changing the Text Align Property Value to Center, while all the Labels are still in selected state.
  7. Select all the Text Boxes in the Detail Section together and select Format- - > Align - -> Left to arrange the Text Boxes close together without leaving gaps between them.
  8. Display the Property Sheet of the Text Boxes (if you have already closed it) and change the Top Property Value to 0 and the Height Property Value to .2917" so that the data lines are not too close and crowded when Previewed/Printed.
  9. If there is gap below the Labels in the Page Header Section and below the Text Boxes in the Detail Section then close them by dragging up the Detail Section Header and the Page Footer Bars.
  10. Delete all the Page Footer Section controls. Close the gap by dragging the Report Footer Bar up.
  11. Now we must resize the Report Header Label with the Shippers heading and make it as wide as the width of all Field Header labels in the Page Header Section put together. You can either do it by resizing manually with your eyes as guide. Or display the Property Sheet of all the Header Labels one by one and take the Width Property Value of each label and add up to get the total of all and change the Width Property Value of the Shippers heading label.
  12. Change the Height Property Value to 0.416 and the Text Align Property Value to Center.
  13. Save your Report with a Name of your choice.

    With the above modifications the Report will look like the image given below, when Print Previewed.

     

The Report looks good, but with few more cosmetic changes it will look even better.

  1. The Field Header Labels' Text must be vertically centered.
  2. The Shipper ID Numbers and other field values are too close to the Border Line and they should be positioned little away from the border.
  3. Open the Report in Design View and select all the Field Header Labels together as we did earlier.
  4. Display the Property Sheet and drag the right scroll bar of the Property Sheet down to the bottom. There you will find the Inner Margin Properties that you can use to position the Text within the Controls.

    NB: These Properties are available only in MS-Access2000 and later versions.

  5. Change the Top Margin Property Value of Header Labels to 0.1?
  6. Select the Text Controls together on the Detail Section and change the Top Margin Property Value to 0.0701".
  7. Select the Shipper ID Text Box in the Detail Section and change the Right Margin value to 0.1?
  8. Select the Company Name Text Box and change the Left Margin Value to 0.0597? and set the same Left Margin Value for Phone Number also.
  9. Save your Report and open it in Print Preview. It will look like the 3rd Image from Top of this page.

Even though it took lengthy steps to explain it, you can do it quickly in a few minutes, when you know what to do to get what you want.

https://www.msaccesstips.com/2008/10/textbox-and-label-inner-margins.html
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts