Feeds:
Posts
Comments

Data record links to documents

June 18, 2010 by mooreknowledge

The challenge

There are times when you would like to have stored documents attached to records in your database.  For example, perhaps you create invoices in an Access database and you would like to scan copies of invoices and attach them to the invoice record in your database.  Or, perhaps you manage property and you would like to attach documents related to each property or to events/activity records tied to each property.  Both of those scenarios could be accomplished by having embedded links to the documents in a field in your data record.

For this example, let’s assume that we are going to store invoice images tied to customer invoices.  Let’s also assume that each document is a PDF file.  It is also completely conceivable to print the documents with a barcoded document identifier and then use a scanner with OCR capability to store the documents into dynamically assigned folder structures based on key pieces of printed information on the document… But that is a whole other issue for another post.

The Solution

Access 2007/2010

If you are using Access 2003, the solution varies slightly from Access 2007/2010.  See below for the Access 2003 solution.

To create this document link field, add a field to the table and set the type of the field be “Attachment”.  Then within each data record, links to multiple files can be stored and “attached” to that record.  When you click on that field, a pop-up window is displayed with a list of the linked files.  You can then double-click on any file listed to open that file.

Table Design View with Attachment field

Table Design View with Attachment field

Datasheet View with Attachment Field

Datasheet View with Attachment Field

Attachment Dialog Window

Attachment Dialog Window

Access 2003

In Access 2003, the first step is to add a new field into your table which will contain the link to the document for each record.  If more than one document is desired for a given record in the table, then you have a couple of options.

The first option is that you could add multiple document link fields (OLE Object fields) into the table structure for each record.  By taking this approach then every record in your table would have the same number of potential document links.  If the number of document links you desire will vary significantly from one record to another, then the second option could possibly a better approach.  The first set of images shown here displays the table configuration with an OLE Object field added to the first table.

Access 2003 table design view with OLE Object field

Access 2003 table design view with OLE Object field

Once the OLE Object field has been added to the table, you can use the Insert Object commend from the Insert menu to insert the OLE object link.  In the OLE Object Window, a “Link” checkbox appears.  This link box gives you the option to insert a copy of the document into your database record or to insert a link to the document into your record.  If you insert a link to the document then any future changes to the document will be reflected when the document is opened.  If you insert the document then a snapshot of the current document will be retained with the data record.

Access 2003 OLE Object Insert window

Access 2003 OLE Object Insert window

Access 2003 Datasheet view with OLE Object field

Access 2003 Datasheet view with OLE Object field

The second option would be to create a sub-table (another table which would be linked back to the customer table by customer number) where the sub-table would be indexed on the customer number and its own internal record ID number.  This would allow a potentially unlimited number of documents to be attached to any given customer.  It would also conserve space in that customers would no documents to attach would not require any space to be assigned but customers with many links would get as much space as they need (in terms of table record space).

Access 2003 table relationships if creating a secondary document table

Access 2003 table relationships if creating a secondary document table

When you use this field in a form or in the table view directly, you will paste the path & filename of the document to be linked.  In this way, if you need to retrieve the document you can simply click on the link in this field of the database to retrieve the document. **

**NOTE:  If your database is used in a multi-user environment where other users have access to the same records, be sure to locate your documents somewhere that other users who could potentially need to view the document would also have security access.  Other users will receive an error if they attempt to open a document that is unavailable to them for security access reasons.  This location could be a shared directory on a networked PC but it could also be a shared folder on a network server or storage device.  It could also be a cloud storage location such as Google Docs or Microsoft SkyDrive.  In any of these locations just verify that other potential users have access permissions to the location or document where it is stored.

Posted in Fields, Tables | Tagged | Leave a Comment »

Timer Auto-Refresh Forms

May 11, 2010 by mooreknowledge

The Challenge

One of my clients is using an Access database in a multi-user environment.  They had a form that they use on their production floor for scheduling jobs.  They wanted to have the form update automatically for the PCs on the production floor to reflect the changes made in the office for scheduling jobs.  If the form was closed and re-opened it would automatically reflect the scheduling changes but if the form was left open for long periods of time, those changes would not be reflected.

The Solution

Access provides an ability to create a timer driven activity within the forms.  You can specify the period of time that the timer should occur and then what activity should take place when the timer expires.  That timer function is driven by the TimerInterval attribute of the form.  The time interval is specified in the FormLoad procedure.  The timer interval is measured in 1000ths of a second so 300,000 is 5 minutes.

Private Sub Form_Load()
     Me.TimerInterval = 300000
 End Sub

The activity that should be performed when the timer expires should be defined in the Form_Timer procedure.  In my example, I set up a command to refresh the form display.  However, any desired code could be constructed in this procedure.

Private Sub Form_Timer()
    DoCmd.Requery
    Me.Refresh
End Sub

Posted in Forms, VBA Code | Tagged , | Leave a Comment »

Cool new trick – using TempVars

April 22, 2010 by mooreknowledge

I knew that Access provided an in-session variable array called TempVars and I have used them a few times but never had much need for them.  However, a project that I have been working on this past couple of weeks required some creativity.  TempVars were just the ticket.  You can use TempVars through either macros or VBA code.  Up to this point, I had only worked with TempVars through the macros.  I had never attempted to manipulate them through VBA code.

The Goal:

I had created a datasheet form but was using simple parameter value prompts from the underlying query to obtain user input.  I wanted to embed those parameter prompts into the datasheet form to clean up the process and make it more of a single-step approach on the user interface.  (There are 3 parameter values being obtained in this particular form.)

The Solution:

I created un-bound text boxes at the top of the datasheet form and then added a command button to the form.  In the VBA code of the command button, I added the code to establish the temporary variables and set them to the values provided in the text boxes.  I also added some code to check the values of the text boxes to make sure that they were not empty when the Update Display button was pressed.  The three text boxes that I created are txtProdCode, txtBegDate and txtEndDate.  The three temp variables that I am establishing are ProdCode, BegDate and EndDate.  Here is a code sample of how those variables are established and the values are set:

Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Click_Err

 On Error Resume Next
 lblErrMsg.Visible = False

 If IsNull(Me.cboProdCode) Then
     Beep
     sMsg = "Please Select a Product Code before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 If IsNull(txtStartDate) Then
     Beep
     sMsg = "Please Enter a Start Date before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 If IsNull(txtEndDate) Then
     Beep
     sMsg = "Please Enter an End Date before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 TempVars.Add "ProdCode", Me.cboProdCode.Value
 TempVars.Add "StartDate", Me.txtStartDate.Value
 TempVars.Add "EndDate", Me.txtEndDate.Value
 DoCmd.Requery

cmdUpdate_Click_Exit:
     Exit Sub

cmdUpdate_Click_Err:
     MsgBox Error$
     Resume cmdUpdate_Click_Exit

End Sub

Here is a screen clipping of what the form looks like:

Temp Variables on a datasheet form
Example datasheet for with three un-bound fields set to temp variables through VBA code.

Posted in Datasheet, Forms, TempVars, VBA Code | Tagged , , , , , | Leave a Comment »

Follow

Get every new post delivered to your Inbox.