Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Custom Report Wizard

Introduction.

After designing and working with a Form Wizard it is natural to think about designing a Report Wizard too. The designing task of a Form and Report is almost the 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: The Custom-made Form Wizard to understand the designing steps of the Form Wizard or to download it from there.

Designing Report Wizard.

Do the following few simple steps and the Report-Wizard is ready:

  1. Make a copy of the Form Wizard and rename it as ReportWizard.
  2. Open the ReportWizard in Design View.

  3. Change the List Box and Combo Box headings to read as Report Format and Select Table/Query for Report respectively.

  4. Change the word 'Form' to Report in the left side labels.

  5. Display the Code Module of the ReportWizard by selecting View - ->Code (or Alt+F11).

  6. Press Ctrl+A to select the entire code in the Form Module and press Delete Key to delete the Code.

  7. Copy and Paste the code given below into the ReportWizard Form Module and save the Form:

    Report Wizard VBA Code

    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.WizListlcount = 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
    

    Create Left-side ListBox Items.

    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.FldListSet
     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.RequeryEnd Select
    
    LeftAll_Exit:
    Exit Function
    
    LeftAll_Err:
    MsgBox Err.Description, , "LeftAll"
    Resume LeftAll_Exit
    
    End Function

    Create Right-side ListBox Items.

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

    Create Report In Tabular Format.

    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_ExitEnd Function
    

    Create Report In Columns Format.

    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
    

    Create Report's Page-Footer Contents.

    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
    

    Try out the Report Wizard.

  8. Open the ReportWizard in Normal View.
  9. Select a Table or Query from the Combo Box.
  10. Select the Tabular Wizard option from above.
  11. Click OK to load the selected Table/Query Field List and open the List of Fields.
  12. Select the Fields for the Report from the List Box.
  13. 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 are made to the Detail Section Controls.

  1. DrawPageFooter()
  2. ReSizePageFooter()

There are other Reports related to 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 to 3D Text Styles and you can download the 3D-Text Wizard from any of them:

  1. Create 3D Headings on Forms
  2. Border 2D Heading Text
  3. Border 3D Heading
  4. 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 Custom Report Wizard


Download Demo ReportWizard.zip


Share:

Custom Made Form Wizard

Introduction.

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:

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

  2. It creates Text Boxes of various sizes and shapes depending on the data type and needs more time to shape it up.

Built-in Form Wizard Review

If you create a Tabular Form using Employee Table from the Northwind.mdb sample database, you will know what I am talking about.

Up to Access2000, when the Table or Query has more fields, then all of them cannot be placed on the 22cm width of the Form and ends up in error. Later Versions squeeze the controls to accommodate all the fields on the Form.

Our Own Customized Form Wizard

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 in one step. Fields that need 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 is easy to make, except for the VBA Programs. You can download this FormWizard from the link given at the end of this post and check the design, Property settings, VBA Routines, etc. An image of the Form Wizard 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.


The Design Task.

Form and Tab Control

  1. Open a new Form.

  2. Insert a Tab Control on the Detail Section of the Form.

  3. Select the First Page of the Tab Control and display the Property Sheet (View - -> Properties).

  4. Change the Caption Property Value to Select Table. This description now appears on the First Page of Tab Control.

    List Box and Property Settings

  5. Create a List Box as shown on the design and position its child label on the top and give the Caption value Form Type.

  6. Create a Label on the left side of the List Box and enter the Caption Text as shown.

  7. Click on the List Box and display the Property Sheet.

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

    Combo Box and Property Settings

  9. Turn off the Wizard Tool in 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: Files List.
    • Row Source Type: Table/Query
    • Row Source: WizQuery
    • Column Count: 1
    • Column Width: 1"
    • Bound Column: 1
    • List Width: 1"
    • Limit to List: Yes
  10. Create a Label to the left of the Combo Box and change the Caption value as shown.

    Command Buttons

  11. Create a Command Button below and change the Caption to OK.

  12. Create a second Command Button to the right and change the Caption to Cancel.

  13. Select the second Tab Control Page, and change its Caption property Value to Select Fields.

    List Boxes on Tab Page2

  14. Create a List Box for Field List and a Second List Box for Selected Fields side by side as shown on the above design.

  15. 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
  16. Select the second List Box and change the Name Property to SelList and change other Properties to the same Values as given above.

    Command Buttons between List Boxes

  17. Create four small Command Buttons between the List Boxes as shown on the design.

  18. Change their Name property Values as cmdRight, cmdRightAll, cmdLeft., cmdLeftAll from the first Command Button on the top of the fourth one at the bottom respectively.

  19. Change their Caption Property Values with >, >>, < and << symbols as shown.

  20. Create three Command Buttons below the List Boxes.

  21. Change the Name Property Value of the left Command Button to cmdBack and the Caption Property Value to <.

  22. Change the Name Property Value of the Command Button in the middle to cmdForm and the Caption Property Value to Finish.

  23. Change the Name Property Value of the right side Command Button to cmdCancel2 and the Caption Property Value to Cancel.

  24. Click to the right side of the second page of the Tab Control to select the Tab Control and display the Property Sheet.

  25. Change the following Property Values:

    • Name: TabCtl0
    • Back Style: Transparent
    • Style: None

     After setting the last two properties of the Tab Control it disappears and nobody can tell that we have designed the whole Wizard on a Tab Control Object.

  26. Save the Form (File - -> Save) with the name FormWizard.

    One important step left to complete is copying, and pasting the complete VBA Routines into the Form Module of the Form Wizard.

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

    The Form Wizard VBA Code

    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 = 8388608twips = 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
    

    Create Left-side ListBox Items.

    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

    Create Right-side ListBox Items.

    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
    

    Create Tabular Type Form.

    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
    

    Create Form in Columns Format

    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
    

    NB: You must ensure that the Name Property Values of all objects are given correctly to avoid Run time errors. If you find any of the controls are not working as expected check whether the Name Property Value is given correctly and the On Click property is set with the Value [Event Procedure]. The On Click Property Value will automatically insert the [Event Procedure] if the Name of the control is the same as it is referenced in the VBA Routines.

  28. Open the Form Wizard 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.

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

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

  31. When you are ready to create the Form, Click on the Finish Command Button.

The Form will be created and will open in Normal View. You may Save the Form and change it to the design view for modifications.

Download the Demo Database:


Download Demo FormWizard.zip


Share:

Rounding Function MRound of Excel

Introduction.

Normally we round numbers in calculations when the fractional part is 0.5 or more to 1 (the next highest integer) or truncate it together. But, there are times that this fractional part itself is 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 or 30 cents they should be rounded to 25 cents, and 50 cents respectively, for determining the requirement of the 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 a 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 with. There is a Worksheet Function MRound() in MS-Excel that can do these kinds of calculations, but found nothing like that in MS-Access. We cannot go to Excel when we want this in Access.

The MRound Function

We will write an 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

Save the Function and do Test Runs.

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

Add to your Function Library

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.

Earlier Post Link References:

  1. Roundup Excel Function in MS Access
  2. Proper Excel Function in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel-Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel- 2
  11. Opening Excel Database Directly
  12. Create Excel, Word Files from Access
Share:

Custom Calculator and Eval Function

Introduction.

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 on 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, and 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 TextBox to enter the Expression, a Command Button, and a 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 workhorse of our Custom Calculator. With this function we don't have to worry much about the calculation rules, like evaluating expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), doing 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.

EVAL() Function Usage

Want to try a 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 the 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)

EVAL() the Expression Parser

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 TextBox.  The Eval() Function parses the expression and gives the result. 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 a lot more than simply evaluate 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 the VBA Window.

The sample Demo Project.

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 of 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, the percentage of down payment, repayment schedule with the applicable interest rate. The Salesman has been given the freedom of allowing a discount up to a maximum of 20% on the Retail Price. 

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

This is a stand-alone Form (which 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.

The VBA Code

We have a small VBA routine to accept the Text Box Names in square brackets [], to pick their value and replace them 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 the 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 parentheses etc. These shortcomings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections, and re-try.

Download

You can implement this program on any Form with a 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.


Share:

Data Editing And Security Issues

Introduction.

Data Entry and Editing are the major activities 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 for 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 detailed information on the 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 forever.

Protecting from un-intended Changes.

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 the Tools Menu. Find the three different options available under the Behavior entering field Options Group in the image below:

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

Since this is the global setting of MS-Access Application, the changes that you make here manually affect all the Forms in all Databases that you load into your machine. Conversely, the database that you have designed for your machine will not carry this option setting along with it when open on some other machine in a multi-user environment. Moreover, 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 class. 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 these settings permanently.

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

Behavior | Description | 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 the 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 in the Global area of the Module as shown above.

Try out the New Setting

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

Introduction.

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

How to display the Summation of numeric values on the Datasheet view?

For answers to 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.

Design a Datasheet Form

  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 is displayed on the Datasheet but the Tab Order does.

The Tab Order of Controls

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 the 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 the View menu. Click at the left border of the UnitPrice field on the Tab Order Control, click and drag it up and place it 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.

The Unbound Text Box

We will add one Unbound Text Box on the Form to calculate the Extended Price after adjusting the 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 the 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 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 TextBoxes 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 the 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 the datasheet view and make some changes 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.

Displaying 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 TextBoxes. Create two TextBoxes 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 Values in TOTALQTY and TOTALVALUE TextBoxes 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 take 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 TextBoxes in the Form Footer Section.

  4. Make some changes to 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 them, 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 on 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 on 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 do you say about that?

Share:

Sum Min Max Avg ParamArray

Introduction.

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 about". 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.

Difference between Excel and Access

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

Let us look at the usage of the 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 a 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?

Have a look at the sample table given below to get the gravity of the issue we are 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 in the following format:

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

  1. Need one Total Query to the group on the 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.

The ParamArray Method

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

A 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 a Variant type.
  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 value.

With the use of the above myMin() Function, we have created a Query on the first Table given above. The SQL and the resulting 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.


Modified Version of VBA Code

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

Option Compare Database

Enum SMMA
    accSummary = 0
    accMinimum = 1
    accMaximum = 2
    accAverage = 3
End Enum

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 As Double, 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())
'Init Nulls, if any,  to 0
For j = 0 To arrayLength
   InputArray(j) = Nz(InputArray(j), 0)
Next

For j = 0 To arrayLength
    NewValue = InputArray(j)
    'skip 0 value
    If NewValue = 0 Then
 GoTo nextitem
    End If
    Select Case calcType
    'Add up values for summary/average
        Case accSummary, accAverage
            rtn = rtn + NewValue
        Case accMinimum
            rtn = IIf(NewValue < rtn, NewValue, rtn)
            rtn = IIf(rtn = 0, 9999999999#, rtn)
        Case accMaximum
            rtn = IIf(NewValue > rtn, NewValue, rtn)
    End Select
nextitem:
Next

'Calc Average
If calcType = accAverage 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 the minimum value.

Sample Runs on Immediate Window:

? SMMAvg (0,0,10,5,7) 'Summary
 Result: 22 

? SMMAvg (1,0,10,5,7) 'Minimum value from 0,10,5,7
 Result: 5
 
? SMMAvg (2,0,10,5,7) 'Maximum value from 0,10,5,7
 Result: 10 
 
? SMMAvg (3,0,10,5,7) 'Average
 Result: 5.5 
  

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

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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