AppendValuesToExcel.mdb (intermediate)

This sample demonstrates how to append data from a database to an Excel Spreadsheet using Office Automation to find the next empty row in the spreadsheet.

The overall process:

1) Define Access and Excel object variables
2) Open Excel sheet as an Excel Object
3) Find the last row of data

4) Write data from Access query to Spreadsheet

5) Save and close spreadsheet

Look in the "basExport" module for the relevant code.

Note: This requires a spreadsheet called "ExportSpreadsheet.xls" (included with the sample) exist in the same directory as the application. Also requires references set to DAO 3.6 and Excel
Download Access 2000-2003 Version: AppendValuesToExcel.zip

Books.mdb (intermediate)

Books.mdb" and it's companion database "Books_be.mdb", make up a simple personal book inventory database. It uses the "BoilerPlate.mdb" sample to create a complete application.




Note: Put both databases in the same physical directory. If you do, the front-end (Books.mdb) will always find the tables in the back-end (Books_be.mdb).



Download

Access 97 (80 KB): Books97.zip

Access 2000 DAO (102 KB): Books2k.zip

BoilerPlate.mdb (intermediate)

This sample is a useful template for an application.


It has a number of standard forms for maintaining reference tables, viewing reports, checking database locations, and users logged on.

The tables, MainDataEntry form, and reports are just to show how the template works
This sample is a useful template for an application.




It has a number of standard forms for maintaining reference tables, viewing reports, checking database locations, and users logged on.



The tables, MainDataEntry form, and reports are just to show how the template works



Download

Access 97 (191 KB): BoilerPlate2_97.zip

Access 2000 DAO (115 KB): BoilerPlate2k.zip

AutomatingPowerpoint.mdb (intermediate)

A simple example of automating the creation of a Powerpoint Presentation from Access.




Download

Access 2000 (544 KB): AutomatingPowerpoint2k.zip

AuditTrail.mdb (intermediate)

This sample illustrates one way to produce a simple audit trail table containing: machine name, login name, user name (access security), record number, field name, original value, new value, and a date/time stamp. It tracks all changes made to a record in either the form or subform. It also tracks when new records are inserted and when records are deleted and the values of the deleted fields. It does not work properly for deleting if Cascade Deletes is set. It will show the deletes from the main form, but not the subform. Code is contained in Module1 and is called from each text box on each form. There are no doubt many improvements that can be made. This is just for illustration purposes.
Download


Access 97 (58 KB): AuditTrail97.zip

Access 2000 DAO (59 KB): AuditTrail2k.zip

AppendValuesToExcel.mdb

This sample demonstrates how to append data from a database to an Excel Spreadsheet using Office Automation to find the next empty row in the spreadsheet.




The overall process:

1) Define Access and Excel object variables



2) Open Excel sheet as an Excel Object



3) Find the last row of data



4) Write data from Access query to Spreadsheet



5) Save and close spreadsheet



Look in the "basExport" module for the relevant code.





Note: This requires a spreadsheet called "ExportSpreadsheet.xls" (included with the sample) exist in the same directory as the application. Also requires references set to DAO 3.6 and Excel



Download Access 2000-2003 Version: AppendValuesToExcel.zip

 

http://www.bmsco.tk/

ActionQueriesInCode.mdb (beginner)

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process. Illustrates how to suppress confirmation messages when running an Action query in VBA code. Documentation included.

Download


Access 2000 (96 KB): ActionQueriesInCode2k.zip

www.bmsco.tk

Animation.mdb ( intermediate )

This sample shows how to implement a variety of simple animations in an Access Form.




Download

Access 97 (65 KB): Animation97.zip

Access 2000 (79 KB): Animation2k.zip

www.bmsco.tk

Student Search on Database

Student Search on Database

Searching for a record based on a text box value in a form: There are times, when developing your Microsoft Access databases, that you may wish to allow your users to search for a record in the form that contains a certain value which will be entered into a textbox. Below shows the example Microsoft Access form containing an unbound text box and command button (Search) which will enable the user to enter a value to be searched on. Form containing the unbound text search facility. To perform the search you will need to enter a value (this search is based on the student number primary key) into the unbound text. The code below is attached to the command button - cmdSearch. This code checks for a value (if no value is entered or no match found it will then return one message and set the focus back to the search field. If a value is found it will return the appropriate record and will give a message then clear the search control. First click the command button on the form(without typing any value into the search field) and you will be told to enter a value. Error message when no search value is entered. Next try entering a value of XXXXXXX. The message will advise that no student found with this value. Error message when an invalid search value is entered. Finally type in DD27676. This will return a match and take you to the corresponding record. Message when matching search value is found

for more information and support

www.bmsco.tk

Send Email from Access to Outlook

Download the Access Visual Basic Outlook API to open up an email message from an Access form.

You can use this VB code to automatically fill in the subject and body text of the email.  The download also demonstrates the ability to attach an external document to the Outlook email.

Here is a snapshot of the code contained in the database example download:

Private Sub Command20_Click()

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = Me.Email_Address
    .Subject = Me.Mess_Subject
    .HTMLBody = Me.Mess_Text
    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
        .Attachments.Add (Me.Mail_Attachment_Path)
    End If
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing
     it in your sent bin
    .Send
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

Click here to download the Download Access Database .

for more information and support

www.bmsco.tk

ActionQueriesInCode.mdb (beginner)

ActionQueriesInCode.mdb (beginner)

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process. Illustrates how to suppress confirmation messages when running an Action query in VBA code. Documentation included. Download Access 2000 (96 KB): ActionQueriesInCode2k.zip

for more information and support

www.bmsco.tk

CreateQueries5.mdb (advanced)

CreateQueries5.mdb (advanced)

This set of samples illustrate how to create a flexible query generator for your users. It is similar to CreateQueries4.mdb, but no longer limits the query to 3 criteria. Because the information for the criteria is stored in a table, there are multi-user considerations. If you use the Singe-User edition in a multi-user environment, users will be over-writing each other's queries. Therefore the Multi-User Edition creates a temporary database on the user's C: drive to store the Where Criteria. The Basic query generator shows just the basics of getting the unlimited Where Clause to work. The Advanced query generator allows you to choose a table or a query for the FROM clause, allows you to select which fields to show in the Field List, and allows parentheses in the Where clause. To use: Each form is self-contained with all the code needed. Just copy the form you want and the two tables: CriteriaTable and TableFields into your application. Access 2000 DAO (89 KB): CreateQueries5-2k.zip

for more information  and support

www.bmsco.tk