	AllExperts > Using MS Access
	Search

About Sayedaziz
Expertise
database designing,event driven programming,capturing/updating data programmitically using ms access 2000 and ms access 2002

Experience
12 yrs. as a vb programmer

Organizations
Print Media

Publications
Math Skill Test VB6 Game Source Code published at Planet-Source --------------------------------------------------------------- http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72322&lngWId=1 Holy Quran Source Code published at Planet-Source ------------------------------------------------- http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=72343&txtForceRefresh=82020091635140766 Right to Left Treeview & Listview Source Code published at Planet-Source ------------------------------------------------------------------------ http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72376&lngWId=1

Education/Credentials
B.Com, AICWA (Associate member of Cost & Works Accountants Of India)

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Query before reporting

Using MS Access - Query before reporting

Expert: Sayedaziz - 11/2/2009

Question
Dear Sir,
I have more than 3000 data in a table in access. I have created a report format with which i can get report of all data at a time. In my dataset I have three columns Division, District and Upazila, where Division>District>Upazila (These are administrative units from large to small). Now I want to do the following:
         1. When I ask for a report It should ask Me for Division
Name (Also should show In a drop down Menu).
2. Then it should ask me for the District Name and show the districts under that Division in a drop down menu.
And same for Upazila.
3. After selecting those the report should be generated showing the data within that region.
It will be highly appreciated if you please help me in this regard.
please answer me considering that I'm a novice.
Thanks

Answer
I have uploaded a sample database at :
http://rapidshare.com/files/301260984/Mahfuz.zip.html

In this database you will find 3 tables tblUpzila/tblDistrict/tblDivision

if you open frmZila you will find 3 combos each for upzila, district and division.

Query for cmbzila
=================
SELECT tblUpazila.UpaZilaId, tblUpazila.UpaZilaName FROM tblUpazila ORDER BY [UpaZilaName];

Query for cmbDistrict
=====================
SELECT tlDistrict.DistrictId, tlDistrict.UpaZilaId, tlDistrict.DistricName FROM tlDistrict WHERE (((tlDistrict.UpaZilaId)=Forms!frmZila!cmbZila)) ORDER BY tlDistrict.DistricName;

Query for cmbDivision
=====================
SELECT tblDivision.DvisionId, tblDivision.DivisionName, tblDivision.DistrictId FROM tblDivision WHERE (((tblDivision.DistrictId)=Forms!frmZila!cmbDistrict)) ORDER BY tblDivision.DvisionId;

Code for Form (frmZila)
=======================
Option Compare Database
Option Explicit

Private Sub cmbDistrict_GotFocus()

If IsNull(Me.cmbZila) Then Me.cmbZila.SetFocus Else Me.cmbDistrict.Requery

End Sub
Private Sub cmbDivision_GotFocus()

If IsNull(Me.cmbZila) Or IsNull(Me.cmbDistrict) Then

  If IsNull(Me.cmbZila) Then
   Me.cmbZila.SetFocus
  Else
   Me.cmbDistrict.SetFocus
  End If

Else
   Me.cmbDistrict.Requery
End If

End Sub
Private Sub cmdReport_Click()

   If IsNull(Me.cmbZila) Or IsNull(Me.cmbDistrict) Or IsNull(Me.cmbDivision) Then

      Beep

       If IsNull(Me.cmbZila) Then
          Me.cmbZila.SetFocus
       ElseIf IsNull(Me.cmbDistrict) Then
          Me.cmbDistrict.SetFocus
       Else
          Me.cmbDivision.SetFocus
       End If
   Else

       DoCmd.OpenReport "Report1", acViewPreview

   End If

End Sub

Add to this Answer Ask a Question