MS Access Media Clipping Database Template


Direct Download Link

Get it from CNET Download.com!

Visit the application page on download.com

I have developed this media clipping database template in Microsoft Access after so many experiments, and sharing it as a gift with those who are interested in databases and would like to manage their print, electronic and broadcast media clippings efficiently.

Important Features

  1. Rather than embedded attachments, the application uses paths to store clipping files in a directory, resulting a very light-size database file.
  2. Auto-generated reports and analytical graphs
  3. Web and desktop versions

MCD

System requirements

Microsoft Access 2010/2013

How to use the template

Download the zip file, extract the folder ‘Media Clipping Database’ and save it on C partition on your hard disk. Go to the folder and open the web or desktop file. The latter offers more features, like, analytical graphs, detailed reports, etc. Replace the filled in fields with your data and see the results.

You can publish your clippings or reports as pdf or export as html files.

Structure

Here is the structure of the database elements:

Tables

Media

ID: An auto number generated automatically with each record
Media: Name of media outlet
Medium: Like, newspaper, TV, radio, etc.
Language: Like, English, Urdu, Sindhi
City Head Office: City in which the head office is located
Comments: Any extra information you would like to add
Logo: Path to the image of the media logo

Contacts

First name, last name, job title, company (media), contact details, etc.

Topics

List of coverage topics

Employees

ID: An auto number generated automatically with each record
Full Name: Full name of employee updating the database

Clippings

The Clippings table stores the information about clippings; field-wise details are given below:
ID: An auto number generated automatically with each record
Date: Publication/on air date
Region: Area/country
Entity: Company entity
Department: Company department
Nature: Nature or tone of the clip, like, negative, neutral, and positive
Coverage Type: Like, brief news, features, etc.
Source: Like, arranged, not arranged or released by your company
Media: Name of media outlet
Media Contact: Like, name of the reporter
Heading/Hyperlink: Heading, hyperlinked with an online link to the coverage, if any
Summary: A brief summary of the news (maximum 255 characters)
Body Text: Contents of the news
Topic Category: Select a topic from the list if you would like to divide your coverage topic-wise, like, eHealth, AIDS, pregnancy, etc.
Current Story Topic: Main topic of a coverage, like, National Symposium, Teachers’ Day, etc. Headings could be different under that main topic.
Event Title: Like, World Diabetes Day
Release Title: Title of press release or photo caption. If the release is related to an event, keep the event and release title same.
Image Path: Browse to a directory to save the path to display a clip image in the database
Position: Like, page number
Duration: For video and audio coverage, in minutes.
Edition: Like, Karachi edition
Employee: Employee signed in
Update Date: Date on which the record updated

Tips

By clicking on a field in the table, you can change the properties as per your requirement, like:
Field size: Maximum characters allowed
Default value: Auto filled, like, current date in the date field that can be modified by the user
Required: If checked, the record could not be saved without filling in this field, etc.

In lookup fields, that display a list to select an item, you can right-click on the arrow on a form and edit the list items.

Browse to File Function in MS Access


If you are using images frequently and saving them as attachments in MS Access database, you might end up with a heavy file. The software has also a limit of 2GB file size. To avoid that situation, you may like to save your images in a separate folder and display them in the database by a text path. Through the following example, I will show you how to do it.

BrowseTo

The example has a table and a form with the fields: ID (auto number) and Path (text). There are two more elements on the form: BrowseTo button and Image frame. When a user clicks on BrowseTo button, it will browse to a file and save its path in the field Path. If the file is an image, it will be displayed in the frame Image.

After creating all the required elements, right-click on BrowseTo button, go to Properties > Event > On Click, select [Event Procedure] and click on ellipses (three dots) to open VB application. Select the code area and paste the following code:

Private Sub cmdBrowseTo_Click()
Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
With fdg
.AllowMultiSelect = False
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strSelectedFile = vrtSelectedItem
Next vrtSelectedItem
Me![Path] = strSelectedFile
Else
End If
End With
Set fdg = Nothing
End Sub

Stay in the VB application, go to the menu Tools > References and check Microsoft Office xx.x Object Library.

Before testing the button, right-click on the frame Image, go to Properties > Data > Control Source and select Path, so that it can display the image through the path.

CLICK HERE TO DOWNLOAD THE SAMPLE DATABASE

MS Access Before Change or Before Update Function: How to Make Another Field Required?


In MS Access 2010, if you want to make a field ‘required’ or compulsory based on a data inserted in another field, you can do it in different ways. Through the following example, I will show you how to do it using data macros and Visual Basic for Applications (VBA).

The example has two tables with the following fields:

ID (autonumber)
Source (lookup: “Arranged by My Org”, “Released by My Org”, “Not Arranged by My Org”)
MainMessage (text)

We want users to insert main message(s) in the field MainMessage if they select either “Arranged by My Org” or “Released by My Org” in the field Source.

Data Macro

Open your table. Go to the menu Table, click on Before Change and follow the following steps:

  1. Select If and paste the following code: [Source]=”Arranged by My Org” Or [Source]=”Released by My Org” And IsNull([MainMessage])
  2. Select RaiseError, type 1 in Error Number, Please insert main message(s) in the field Main Message in Error Description, and save.

Open FormDataMacro and try the code.

VBA

Open FormVBA in design view, select form properties, click on event, under Before Update select Event Procedure, click on the ellipsis (…) to open the VBA code area, and paste the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = “Please insert main message(s) in the field Main Message”
If (Me![Source] = “Arranged by My Org”) Or (Me![Source] = “Released by My Org”) Then
If IsNull(Me![MainMessage]) Then
MsgBox strMsg
Cancel = True
End If
End If
End Sub

Save and try the code by inserting data in the form.

Click here to download the sample database

Auto filling or auto populating MS Access fields in a form


Download sample database

Required: MS Access 2010

Desktop database:

1. Create the required fields in your main table (tblTable2) similar to the data type of other table (tblTable1), where from you want to extract data for auto filling (text fields, numbers fields, etc). You may also keep the field names similar.

2. In tblTable2 design mood, click the tab ‘Lookup’ and select ‘Combo Box’. Go to ‘Row Source’ and click the three eclipses to switch to query mood. Select tblTable1 and double click the matching field; save and close. Repeat the same procedure for all the other fields.

3. Prepare a form based on tblTable2. Among the auto filling fields, select the main field (e.g., MediaOrganisation). Go to the tab ‘Design’ > ‘Properties’ > Data > open ‘Row Source’ and select all the fields in the query that you want to auto fill. Close and go to ‘Properties’ > ‘Format’ and fill in ‘Column Count’ and ‘Column Widths’ (e.g. Column Count: 3; Column Width: 1”,1”,1”).

4. Go to the tab ‘Event’, ‘After Update’, open ‘code builder’ and type the following code:

Option Compare Database
Private Sub [combo box name with the prefix cbo]_AfterUpdate()
End Sub

Private Sub [main filed name]_AfterUpdate() [this is column 0]
Me.[field 1 of tblTable2] = Me.[ main filed name].Column(1)
Me. [field 2 of tblTable2] = Me. [main filed name].Column(2)
End Sub

Or copy the following code:

Option Compare Database
Private Sub cboMediaOrganisation_AfterUpdate()
End Sub

Private Sub MediaOrganisation_AfterUpdate()
Me.Medium = Me.MediaOrganisation.Column(1)
Me.Language = Me.MediaOrganisation.Column(2)
End Sub

5. Now try the form by selecting a name in the field MediaOrganisation. You will notice that ‘Medium’ and ‘Language’ fields are auto filled.

Web database:

Drag joined query fields in your main table (tblTable2) query and create a form to display them.