MS Access

Introduction to Event Procedures

2009-06-21T19:36:00.000-07:00

Procedures are simply containers for VBA code. Access VBA contains four types of procedures:
• Subprocedures
• Function procedures
• Property procedures
• Event procedures
Each type of procedure is designed to accomplish specific tasks. For example, event proce-
dures are designed to catch and respond to user initiated events such as a mouse click on a
command button or system initiated event such as a form loading. In this section I concen-
trate an event procedure, as they are the foundation for an event-driven language such as
VBA. In subsequent chapters, you learn about other types of procedures in detail.
As mentioned, objects such as the Form object contain methods and properties. They also
contain specialized events that are automatically provided after the object has been added
to your database. VBA takes care of naming your object’s events for you. Their naming con-
vention follows.

ObjectName_EventName
For example, a form added to your Access database called Form1 has a number of events,
including the following.
Private Sub Form_Load()
End Sub
Private Sub Form_Unload(Cancel As Integer)
End Sub

Notice the naming convention used for each event procedure: object name followed by the
event name with an underscore in between. The objects and their events in Figure 2.2 are
accessed from the VBE Code window.

The leftmost list box in the Code window identifies available objects. The rightmost list box
contains all available events for the object selected in the left list box. Each time you select
an event, VBA creates the event shell for you automatically. This saves you from having to
manually type each event’s beginning and ending procedure statements.
TRICK Each procedure in the VBE code window is separated by a horizontal line.
Empty event procedures serve no purpose until you write code in them using VBA statements.

Object-Based Programming

2009-06-04T19:50:00.000-07:00

The key to programming in VBA is using objects. Objects have properties that describe the
object and methods, which perform actions. For example, say I have an object called Person.
The Person object contains properties called HairColor, Weight, Height, and Age that describe
the object. The Person object also contains methods that describe an action the object can
perform such as Run, Walk, Sleep, and Eat. As you can see, understanding the concept of
objects is really quite simple!
Many Access VBA objects also contain data structures called collections. In a nutshell, collections
are groupings of objects, which you are introduced to in this chapter.
Access VBA supports many objects such as the Form object, which is simply a window or dia-
log box. The Form object contains many properties such as Caption, Moveable, and Visible.
Each of these properties describes the Form object and allows VBA programmers to set char-
acteristics of a user’s interface. Like the object Person, the Form object contains methods such
as Move and Refresh.
Many objects share common characteristics such as properties and methods. To demon-
strate, the Label object (which implements a label control) shares many of the Form proper-
ties such as Caption and Visible.
Properties and methods of objects are accessed using the dot operator (.) as demonstrated
in the next two VBA statements.
Label1.ForeColor = vbBlue
Label1.Caption = “Hello World”
Don’t worry about the details in the previous statements for now, but do realize that prop-
erties such as ForeColor and Caption belong to the Label1 object and they are accessed using
the dot operator. I discuss this in more detail in sections to come.

The Event-Driven Paradigm

2009-06-03T19:39:00.000-07:00

The event-driven paradigm is a powerful programming model that allows pro-
grammers to build applications that respond to actions initiated by the user or
system. Access VBA includes a number of events that are categorized by the
objects they represent. VBA programmers write code in event procedures to
respond to user actions (such as clicking a command button) or system actions
(such as a form loading).
To demonstrate the event-driven model, consider a form, which has a corresponding Form
object that contains many events such as Click, Load, and MouseUp. As seen next, both Click
and MouseUp events are triggered by the user performing an action with the mouse on the
form.
Private Sub Form_Click()
‘write code in here to respond to the user clicking the form
End Sub
Private Sub Form_MouseUp(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
‘write code in here to respond to the user releasing a mouse button
End Sub

I discuss the details of these event procedures soon enough. For now, understand that
objects have related events, which can be triggered by users. You, the VBA programmer,
write code in these event procedures to respond to user actions. Moreover, events can be trig-
gered by the system or the program itself. For example, the Load event seen next is triggered
when a form’s Form object is first loaded into memory.
Private Sub Form_Load()
‘write code in here to respond to the form loading into memory
End Sub
If you’re new to event-driven programming, this may seem a bit awkward at first. I promise
you, however, it is really not that difficult. In fact, VBA does a great job of providing much
of the detail for you. By the end of this chapter, you will be writing your first Access VBA
event-driven programs with ease.

Introduction to Access VBA

2009-06-01T19:30:00.001-07:00

Like many professional RDBMS (Relational Database Management Systems),
Microsoft Access comes with its own programming language called VBA.
VBA, or Visual Basic for Applications, is a subset of Microsoft’s popular
enterprise programming language Visual Basic. VBA follows the Visual Basic lan-
guage syntax and comes with many of its common features such as an integrated
development environment (IDE) and many common controls for building profes-
sional event-driven and data-driven applications.
Though VBA supports the look and feel of Visual Basic, it is not Visual Basic. A
main difference being that Visual Basic allows for creation of executable programs,
whereas VBA does not. Moreover, VBA for Access is specifically designed for
Microsoft Access. Meaning, it has knowledge of and support for the Microsoft
Access object model. The concept of an object model is different for each
Microsoft Office application. For example, both Microsoft Excel and Microsoft
Word support VBA, but each has its own object model.

Introduction to Access VBA

2009-06-01T19:30:00.000-07:00

Migrating to Access 2003

2009-05-15T21:46:00.001-07:00

Migrating to Access 2003
Depending on which previous Access version you were leveraging, migration woes range
from none to minor. In fact, migrating from Access 2002 is real easy. In most cases you simply
need to open your previously created Access 2002 database in Access 2003 once installed.
Your Data Access Pages and VBA code from Access 2002 should also open in Access 2003 with
no problems.
If you’re migrating from Access 2000, it is also easy to upgrade as Access 2003 supports the
same file format. Like previous versions of Access 2002 databases, Access 2003 should import
your Access 2000 databases with no problems, including your Access 2000 VBA code. How-
ever, you need to convert any Data Access Pages built in Access 2000 by simply opening the
Data Access Page in Design view with an Access 2002 or higher database. Microsoft Access
should then prompt you to convert the Data Access Page.
For versions older than 2000, such as Access 97, you must convert the Access files first.
Microsoft provides step-by-step procedures and toolkits for converting older Access files in
this situation.
For more information on converting older Access files, simply use the key phrase “convert-
ing an Access file” in the Microsoft Office online help in Access 2003 or on the web at
http://office.microsoft.com.
Your First Access Data

Relationships

2009-01-19T18:40:00.000-08:00

As mentioned before, Access implements a relational database, allowing database developers

to link one or more tables using keys or relationships. To demonstrate, follow the next

sequence of steps to link two tables called Students and HomeWork_Results.

1. Create and save a new Access file and database.

2. Create two new tables called Students and HomeWork_Results.

3. The Studentstable should have the following fields: Student_Id (AutoNumber),

First_Name(Text), Last_Name(Text), Middle_Initial (Text), Gender(Text), and Age (Number).

4. Set the Student_Id in the Students table as the primary key by right-clicking the

Student_Id field in Design view and right-clicking the Primary Keymenu option.

5. The HomeWork_Resultstable should have the following fields: Homework_Id(Number),

Student_Id (Number), Homework_Completed(Yes/No), and Homework_Score(Number).

6. Set two fields as the primary key in the HomeWork_Resultstable by holding down your

Shift or Ctrl key and clicking both the Homework_Idand Student_Id fields.

7. While both fields are highlighted and still holding the Shift or Ctrl key, right-click in

the gray column to the left of one of the highlighted fields and select the Primary

Keymenu option. This is known as a multifield key !

8. Ensure both tables are saved and named then select Tools, Relationships from the

Access menu. An interim window is displayed from where you will add both tables to

the relationship window. After which, the relationship window displays your two

new tables. Note the primary keys in both tables are highlighted in black as seen in

Figure 1.10.

9. Drag and drop the Student_Id field from the Students table onto the Student_Id field

in the HomeWork_Resultstable. A new window appears, as depicted in Figure 1.11.

10. Click the Create New button and you have created a one-to-many relationship, which

means for every student in the Students table, there are many occurrences of that

student in the HomeWork_Resultstable.

The primary key in the Students table, Student_Id, has also now become a foreign key

in the HomeWork_Resultstable.

You must make other considerations about a relationship:

• Enforcereferential integrity, which means values entered into the foreign key must

match values in the primary key.

• Enforcecascading updates between one or more tables, which means related updates

from one table’s fields are cascaded to the other table or tables in the relationship.

• Enforcecascading deletes between the two tables. In short, this means any relevant

deletions from one table cascade in the other table(s).

• Both cascading updates and deletes help enforce referential integrity.

Figure 1.10

Viewing

relationships

between two

tables.

Figure 1.11

Editing a

relationship

between two

tables.

To better visualize the relationship, enter a few records into the Students and

HomeWork_Resultstables by double-clicking each table, one at a time, from the main

Access window. Remember, opening a table from the main Access window allows

you to manage field values directly!

11. After you’ve entered data in both tables, open the Students table again and you

should see a plus sign (+) to the left of each Student_Id.

Click the plus sign and you should see the related HomeWork_Resultsrecord for the

student, as depicted in Figure 1.12.

Figure 1.12

Viewing related

table information

after creating a

table relationship.

Your First Access Database

2009-01-17T17:41:00.000-08:00

IT professionals, teachers, students, accountants, managers, scientists, computer enthusiasts, and many more people just like you have a need to collect and manage data. Data that is complex or changes frequently is best stored and managed in a database such as Access.

Let’s say you are a school teacher who has a list of students, attendance, grades, tests, quizzes, and homework. You could keep track of your students, their attendance, grades, and such in a notebook or in an electronic document. But suppose one of your students has a name change in the middle of the school year. To solve this record-keeping issue, you need to update all occurrences of this student’s name in your lists, whether paper or electronic.

In a well-designed Access database, you only need to make this change once.

Databases allow you to ask questions of your data. For example, you could group your students by their grade point average, find out which students missed fewer than 3 school days, and which students have failed to turn in 10 or more homework assignments and have a grade point average less than 2.0.

Access implements a relational database to link information such as homework results to one or more students. Looking at Figure 1.1, you can easily see how Access links a student to one or more homework assignments using tables and keys.

TRICK A relational database stores data in separate tables by subject matter and pro-

vides a system or user-defined link (key) between the tables to produce useful

information through the relationship.

Figure 1.1

Relational data in

Microsoft Access.

The two sets of tables in Figure 1.1 are relational in that the HomeWork_Results table maintains a student key (in other words, a relationship) back to the Students table.

Getting Started with Access 2003

2009-01-15T21:43:00.000-08:00

Microsoft has done a great job making Access 2003 intuitive and easy to learn by
providing a simple interface and an excellent help system. If you’re already famil-
iar with previous versions of Access databases, you’ll appreciate the new and
updated functionality for
• Viewing object dependencies
• Enabling automatic error checking in forms and reports
• Propagating control property changes to bound fields
• Smart Tags with Access objects
• Database backups
• XML support
• Improved Microsoft Office online help
• Collaborate development efforts with friends and colleagues using Microsoft
SharePoint Services
• Improved macro and function security
• Context-based help for SQL view
• Manage SQL Server databases with Access projects
• Updated Microsoft Office 2003 Object Model
• New Visual Basic objects, properties, and methods

MS-Access and E-Mail

2008-12-31T15:24:00.000-08:00

It is unlikely that you will use Microsoft Access to send your personal e-mails through Internet, when so many other options are available to you.

But, when you need to share information from your Access Application with remotely located users regularly and you are sending printed Repor ts through conventional mail services then you can seriously think about the e-mail option.

If you are connected to a Local Area Network (LAN) you can distribute Reports to the recipients through Intranet Mail Service Applications like Lotus Notes.

For both Internet and Intranet mails to work Microsoft Outlook must be configured to act as a communication link between Microsoft Access and the Mail Transport; the SMTP Server of your Internet Service Provider(ISP) or the Intranet Mail Transport, like Lotus Notes. If you are already using Microsoft Outlook for Sending and Receiving e-mails then it is already configured. MS-Outlook will maintain the Contact List from which we can select the prospective mail recipients.

NB: The procedure for configuring Microsoft Outlook is beyond the scope of this Article. You may refer the Help topics of Microsoft Outlook or Help documents provided by your ISP for guidance. But, we will go through the procedure while configuring MS-Outlook for Lotus Notes for Intranet Mails in the forthcoming Posts.

There are two versions of Microsoft Outlook installations. The normal one that we see on our PCs, for Internet Mails and the Intranet type for Corporate or Workgroups. You can check your installation version by displaying the About Microsoft Outlook Screen from the Help Menu. If it is a Corporate or Workgroup installation this exact label will appear on the top area of the About Screen.

Let us explore few scenarios that warrants for sending e-mails from Microsoft Access.

Your Company is a Service oriented Organization, like a Bank, and you want to send Birthday Greetings to your Customers automatically or need to inform them the revision of Interest Rates on Fixed Deposits.

OR

Your Company enters into Agency Agreements with other Parties, which is renewable periodically and your Access Application should monitor and send Mail Alerts to the respective Departments well in advance so that they can act in time for renewal of Agency Agreements.

OR

Your company extends Credits to the Customers based on renewable Bank Guarantees and your Access Application should send mail alerts to the parties concerned for taking it up with the Customers well before renewals are due.

In all the above or similar scenarios we can automate the Mail Alert procedure with the help of programs and we will look into a sample procedure for sending e-mails automatically through Lotus Notes, at the end of this series.

In the meantime let us look into a simple mail sending example that sends a Report as attachment, in Microsoft Access Snapshot Format, manually.

Open your Database and Click on a Report (avoid the Dynamic Report that we have created in the earlier session).

Point your mouse on the Send To.. option in File Menu and Click on Mail Recipient (as Attachment).

Select Snapshot Format from the displayed file formats menu.

A dialogue control will open up asking to select the Mail Transport Profile already configured in Microsoft Outlook, or you can create a new profile.

Select the default profile that you are using to send your e-mails. If you select the options and set this as the default Profile then this dialog control will not appear next time.

When you click OK the e-mail Editor Screen will appear and you can modify your mail before sending it.

See the image below.

Click on the To… Button to pick e-mail addresses from the Contacts List of Microsoft Outlook. The Cc… Button also works the same way.

You can see the Report that we have selected is already converted into Microsoft Access Snapshot Format and attached to the Mail. Instead, if you prefer to send an external Object like Word or Excel File you can Click on the Attach… Button and select the file from your Hard Disk.

The right side blue colored panel will not appear till you click on the Attachment Option… Button that you see next to the blue colored panel.

You can compose your mail in Plain Text and click the Send Button on the Toolbar above. Microsoft Outlook will take care of the rest.

A sample image of the Sent Items display of Microsoft Outlook 2003 is given below for reference.

If you are sending a regular Report as attachment with fixed wordings of mail body text then you can automate this action with a Macro. See the SendObject Command and its settings in the Macro image below:

The SendObject Command is selected in the Action Grid. The E-Mail Field values are filled-in in the Property Sheet of the SendObject command below. If you don’t want to send an Access Object as attachment then you can leave the first three property values empty. If you set the ‘Edit Message’ property to Yes then the E-Mail Client will open for editing as we did earlier, otherwise the mail will be passed on to Microsoft Outlook, without appearing on the screen, for sending through Internet.

You can attach this Macro to a Command Button or run the macro from VB Code using Docmd.Runmacro “MacroName”. If you want to control the frequency of the e-mails with the Report attachment then you must set up procedure to cross check the scheduled Date or Day before running the macro. We will look into such a program that sends mail automatically when it hits the weekly schedule, at the end of this series.

Next, we will explore the procedure that involves configuring Lotus Notes to use as Mail Transport to send e-mails within Local Area Network (LAN).

Custom Report Wizard

2008-12-31T14:55:00.000-08:00

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

Do the following few simple steps and the ReportWizard is ready:

Make a copy of the FormWizard and rename it as ReportWizard.

Open the ReportWizard in Design View.

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

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

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

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

Copy and Paste the Code given below into the ReportWizard Form Module and save the Form:


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.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)
'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

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_Exit
End Function

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

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

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


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

Open the ReportWizard in Normal View.

Select a Table or Query from the Combo Box.

Select Tabular Wizard option from above.

Click OK to load the selected Table/Query Field List and open the List of Fields.

Select the Fields for the Report from the List Box.

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: Reports 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 made to the Detail Section Controls.

DrawPageFooter()

ReSizePageFooter()

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

Create 3D Headings on Forms

Border 2D Heading Text

Border 3D Heading

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.

Access Password Recovery Master

2008-12-14T17:58:00.000-08:00

Access Password Recovery Master is a program that helps the user to recover lost or forgotten passwords for MS Access database files (*.MDB) and user-level passwords stored in the workgroup information file (*.MDW).

Purchase

Download

Overview

MS Access is one of the most popular database programs used in millions of offices worldwide. Password protection is an important feature that limits access to authorized personnel only and prevents strangers from obtaining confidential data. Unfortunately, people do lose or forget their passwords, making it impossible to access that data. Luckily, it does not mean that the data is lost forever.

Access Password Recovery Master does exactly what the name says – recovers passwords for protected MS Access databases. The program recovers MDB passwords for the MS Access documents and user-level passwords for the workgroup information files (*.mdw). The software automatically finds recently accessed *.mdb files and, if it is password protected, displays file name and the password for it. User-level passwords from workgroup information files (*.mdw) are restored in a similar manner. The software is extremely simple. The retrieved information can be stored in the formatted text file or copied to clipboard. Importantly, the program is capable of recovering passwords containing non-English characters as well – a feature that not all password recovery applications offer.

The software worked great! Thanks!

Trevor Giberson

More opinions | Add opinion

Related Software

Office Password Recovery Toolbox

Features and benefits

All versions of MS Access are supported (including MS Access 2003).
Recovers passwords for MS Access database files (*.MDB).
Recovers user-level passwords stored in the workgroup information files (*.MDW).
Multilingual passwords are supported.
All passwords recovered instantly.
Stores retrieved information in a formatted text file.
Allows to copy recovered passwords to clipboard.
Easy and user-friendly interface.

User interface

The main window of the program containes two tabs having the following titles:

MDB Files Passwords - displayes Microsoft Access database passwords (passwords for the *.mdb files);
User-level Passwords - displayes names of MS Access users and their passwords.

When the program starts, it tries to find recent MDB files opened in the Microsoft Access by scaning the system registry. If the found file is protected with a password, the program shows it in its main window, on the first tab. On the second tab the program shows the names of the MS Access users and their passwords retrieved from the default workgroup information file. So, when running the program you can see all the information retrieved.

System requirements

Windows 9x, ME, NT 4.0, 2000, XP, 2003.
The program supports MS Access 97 / 2000 / XP / 2003.

MS Access

Introduction to Event Procedures

Object-Based Programming

The Event-Driven Paradigm

Introduction to Access VBA

Introduction to Access VBA

Migrating to Access 2003

Relationships

Your First Access Database

Getting Started with Access 2003

MS-Access and E-Mail

Custom Report Wizard

Access Password Recovery Master

Overview

Opinions

Related Software

Features and benefits

User interface

System requirements