This document describes about deploying MS Access Reports into VB.NET Windows Application. It describes the steps to design the report in Access and how it can be rendered within an .NET App.

1  Open a MS Access Report in VB.Net

1) Instantiate MS Access application
         Dim reportAccess As Access.Application = New Access.Application
2) Open the current data base
         reportAccess.OpenCurrentDatabase(MS Access Database Path)
3) Open a report in print preview mode.
         reportAccess.DoCmd.OpenReport(“report name” , Access.AcView.acViewPreview)
4) Enable visible property
         reportAccess.Visible = True
5) Finally close the report
         reportAccess.Quit()
         reportAccess = Nothing

2  Applying filters for MS Access Reports in VB.Net

When generating Microsoft Access Reports, it may be necessary to apply parameters based upon values to allow your users to view data relating to specific information. The following screen gives the user various options of populating fields based upon preset values and also allows the user to supply their own values for the data that the report should be based upon.

To begin with, we will create the Windows Form that includes various controls as shown below:

As you will see, the form includes text boxes, combo boxes to supply the data that will supply criteria to the report. You also notice that it includes two buttons Run and Cancel.

Run button to preview a report. Cancel button to cancel the form.

The text boxes, combo boxes will allow the user to input. So the resulting report will be based on the input data.

We now need a report that will take the information from this form to use as part of its criteria. In this example we use a report that includes a single year field shown in the sample below:

ie.) Year = 2000

This report is based upon a query that will take the criteria supplied by the form, and use it when generating the report. We supply the criteria to the query by doing the following:

The above generated report used the values entered into the year text box on the form as a filtering criteria.

The Run Button uses the following procedure to generate the report.

Private Sub runButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles runButton.Click
     ‘null checking
     If Not year.Equals(String.Empty) Then
         openReport()
     End If
End Sub

Private Sub openReport()
Try
     Dim reportAccess As Access.Application= New Access.Application
     reportAccess.OpenCurrentDatabase(MS Access Database Path)
     ‘open the selected report
     reportAccess.DoCmd.OpenReport(rpt_Title , Access.AcView.acViewPreview)
     Dim year As String = CStr(yearTextBox.Text)
     Dim strFilter As String = Nothing
     strFilter = “[Year] ” & ” ‘” & year & “‘”
     With reportAccess.Reports(rpt_Title)
     .Filter = strFilter
     .FilterOn = True
     End With
     reportAccess.DoCmd.OpenReport(rpt_Title , Access.AcView.acViewPreview, strFilter)
     reportAccess.Visible = True
     Catch ex As Exception
     reportAccess.Quit()
     reportAccess = Nothing
     MessageBox.Show(ex.Message)
End Try
End Sub

3  Creating Report in MS Access

An Access Report displays data from a record source you specify (a table or query) and you can customize
the way the data is displayed through its design. Reports are used for viewing and printing data.

 

Query to create the table:

Situation:

1) Consider you are creating a report using select query and implementing some of the arithmetic operation
in the Report. By doing so you may get a error as like below:

* Multi-level GROUP BY clause is not allowed in a subquery.

This type of error can be solved by using tables instead of queries.

Suppose your Record Source Query is like below:

 

SELECT u.Name,u.Amount,i.Tax,i.Rate

FROM

Unordered as u,Invoice as i

WHERE

U.Status = 1;

 

Now you have to create the new query in MS-Access by just copy and paste the old query and add
“INTO new_table_name” just before the FROM keyword in the query. Consider new table name is
tbl_report1.Now your New query in the form of:

 

SELECT u.Name,u.Amount,i.Tax,i.Rate

INTO tbl_report1

FROM

Unordered as u,Invoice as i

WHERE

U.Status = 1;

 

Saving and running the query above will create a new table called tbl_report1 in your database table’s tab.
Now you can use this table as record source for your report.

4  Sorting and Grouping of records

You can group records in a report based on the values in one or more fields. You can also calculate totals and other values for each group.

For example the following report was grouped by “Method” field.

The various payment methods like cash, cheque and credit card are grouped separately. This report prints
the “Total due” for each day.

 

1. To enable Sorting and Grouping in your report, click View menu (MSAccess) -> Sorting and Grouping.

2. Group header is use to place information, such as group title or field titles at the beginning of a group
of records.

Place the text boxes that identifies the group title, field title in the new group header

3. Group footer is use to place information, such as total, average of fields  at the end of a group of
records.

Place the text boxes that calculates the total, average for the fields in the group footer section.

4. You create a grouping by setting either Group Header or Group Footer or both to yes if you need.

5. The above report contains two group fields named paymethod and bank.

6. The bank group footer get displayed only in cash and cheque paymethods. In the case of credit card paymethod
the bank footer is invisible

Private Sub BankFooter_Format(ByVal Cancel As Integer,ByVal FormatCount As Integer)
On Error GoTo Err_msg
     If [Bank] = “BANK DEPOSIT”
     Then
     titleBankTxtBox.Visible = True
     totaBanklDueTxtBox.Visible = True
     Else
     titleBankTxtBox.Visible = False
     totaBanklDueTxtBox.Visible = False
     End If
     Exit_err:
     Exit Sub
     Err_msg:
     Resume Exit_err
End Sub

5  DoCmd.Open Report

Syntax :

DoCmd.OpenReport(report name, view,[filters],[where condition],[window mode],[open args])

1. Report name( Required )

2. View:

We can view the reports in different types as shown below:

Access.AcView.acViewDesign,Access.AcView.acViewPreview,

Access.AcView.acViewNormal, Access.AcView.acViewPivotChart,

Access.AcView.acViewPivotTable.

3. Filters(optional)

4. Where condition optional)

5. Window mode(optional)

We can view the reports in different types of window mode as shown below:

Access.AcWindowMode.acDialog, Access.AcWindowMode.acHidden,

Access.AcWindowMode.acIcon, Access.AcWindowMode.acWindowNormal.

6.Open args(optional)

 

6   DoCmd.Open Query

Syntax:

DoCmd.OpenQuery(Query Name, View, DataMode)

 

1. Query Name (Required)

2. View

We can view the query result in different types as shown below:

Access.AcWindowMode.acDialog, Access.AcWindowMode.acHidden,

Access.AcWindowMode.acIcon, Access.AcWindowMode.acWindowNormal.

3. DataMode

We can execute the query in following modes:

Access.AcOpenDataMode.acEdit, Access.AcOpenDataMode.acAdd,

Access.AcOpenDataMode.acReadOnly.

 

7  If Condition in MS Access

Syntax:

IIf(expression, condtion true part, condition false part)

 

Returns one of two parts, depending on the evaluation of an expression.

For example;

Textbox txt_ASP =([txt_Due])/([txt_Txns]). Before division check whether the dividend (i.e.) txt_Txns is
zero or not. If its zero it produces divide by zero error so in order to avoid the error we replace 0 by
1 as shown below.

 

txt_ASP =([txt_Due])/IIf(([txt_Txns]=0),1,[txt_Txns])

 

8  Data Formatting in MS Access

Named Date/Time Formats:

 

The following name identifies the predefined date and time format:

General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time.

Example:

Format(Date() , “Long Date”) -> Tuesday, july28,2005

Format(Date() , “Short Date”) -> 26-Jul-05

 

User-Defined Date/Time Formats:

We can create user-defined date/time formats as like the examples below:

Format(Date() , “dd-mmm-yyyy”) -> 28-jul-2005

Format(Now() , “hh:nn AM/PM”)  -> 3.39 PM

 

FormatDateTime Function

Syntax :

FormatDateTime(Date,NamedFormat)

 

The NamedFormat argument is optional, it has the following settings:

 

vbGeneralDate (0) – Display a date and/or time. If there is a date part, display it as short date.
If there is a time part,

            
display it as a long time. If present, both parts are displayed

vbLongDate (1)     – Display a date using the long date format.

vbShortDate (2)     – Display a date using the short date format.

vbLongTime (3)     – Display a time using the computer time format.

vbShortTime (4)     – Display a time using the 24-hour format (hh:mm).

 

Example:

FormatDateTime(CStr(Month(Date()))+”/”+CStr(Day(Date()))+”/”+CStr(Year(Date())),1)

 

9  DateDiff Function

Returns a number specifying the number of time intervals between two specified dates.

Syntax:

DateDiff(interval, date1, date2,firstdayofweek,firstweekofyear)

 

Interval String expression that is the interval of time you use to calculate the difference between date1 and date2.

The interval argument has following formats:

yyyy – Year, q- Quarter, m- Month,

y – Day of year, d- Day, w- Weekday,

ww – Week, h- Hour, n- Minute, s- Second.

date1 , date2 Two dates which you want to use in the calculation.
Firstdayofweek Optional
Firstweekofyear Optional

Example:

DateDiff(“w”,CDate([minContractDate]),Date())

 

10. Open Report in Maximized mode from .Net:

DimreportAccess As Access.Application =
New Access.Application

reportAccess.OpenCurrentDatabase(MS Access Database Path)

reportAccess.DoCmd.Maximize()