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()