Microsoft Access

221K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

Excel Error Handling Part 3 -- Run and Fix Bugs
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel.

Part 1 of this series discussed basic error handling code using VBA.
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.
http://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

Although helpful, it is not necessary to watch parts 1 and 2 before this lesson.

This lesson runs code to see what it does and then breaks working code so we can explore errors.  We run and fix, debug, compile, use and not use Option Explicit, step through code while it is running, look at the watch window to see values of variables, set and clear breakpoints, stop, continue running, and learn how debugging and error handling work.

01. For a list of macros, press Alt-F8


   When you are in an Excel Workbook, press Alt-F8 for a list of Macros.

02. To go to VBA, press Alt-F11


   When you are in an Excel Workbook, press Alt-F11 to go to the Visual Basic Editor (VBE) where you can write Visual Basic for Applications (VBA).

03. To watch variable values, press Ctrl-W


   When you are in VBA code, press Ctrl-W to open the Watch window and set expressions to watch the value of.  If a variable name is highlighted when Ctrl-W is pressed, it will be filled in the Expression.

04. Stop


   Add a Stop statement to the code to cause the code to stop on that line when it runs.

05. To single-step, press F8

2

Expert Comment

by:Erika Anderson
Thank you!
1
Basic Error Handling code for VBA and Microsoft Office
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.

This lesson, Part 1, is the basics.  Whether you are writing VBA for Excel, Access, Word, or another Microsoft Office application,  basic error handling is the same.

01. Set up the error handler


   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Exit Code


   After whatever your procedure does, a line label for the exit code (such as Proc_Exit: ) is used to signify what happens at the end of the procedure. This can be code to cleanup object variables, or simply code to gracefully exit.

03. Error Handling Code


   After the exit code, a line label for the error handling code (such as Proc_Err: ) is used to begin what happens if there is an error.
Books_START_ErrorHandling_CopyDownB.xlsm
Books_ErrorHandling_CopyDownBlanks_.xlsm
3

Expert Comment

by:chris pike
For someone who is trying to wrap their brain around VB for the first time, this video is starting to shed light on the subject.
Well done video, very helpful.

Thanks so much.
I will definitely look out for more videos from crystal (strive4peace).
0
LVL 25
thank you, Chris and you're welcome  ~ if you have any questions about basic error handling, please post them here.
0
How to install the Office 2016 desktop applications that come with the free trial of Office 365 Home
In a previous video Micro Tutorial here at Experts Exchange, I explained how to get a free, one-month trial of Office 365, which provides the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. The previous tutorial ended at the point of downloading the installer for the Office 2016 desktop modules for Windows. This new tutorial goes through the installation process for those applications.

1. Run the downloaded installer


Using Windows/File Explorer (or whatever file manager you prefer), locate the downloaded installer for the Office 2016 apps that are included as part of the Office 365 Home subscription. The name may vary depending on your operating system, but it will look something like this:

Setup.<lots of other characters here>.exe

Run it (usually, via a double-click, but that depends on your file manager and settings) and then click the "Run" button on the "Security Warning" dialog.

step1

2. Accept the User Account Control dialog


Depending on your User Account Control (UAC) settings, you may or may not get the UAC dialog. If you do, click the "Yes" button.

step2

3. Wait until all Office 2016 apps are installed


Although it says, "We'll be done in just a moment", grab a cup of coffee.

step3

4. Check for the Office Tools shortcuts


Check to make sure that the installer created a "Microsoft Office 2016 Tools" program group, with two shortcuts in it.

step4

5. Check for the Office shortcuts


Check to make sure that the installer created shortcuts for all of the Office 2016 apps. It does not
0
How to get a free trial of Office 365 with the Office 2016 desktop applications
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Personal. However, only one of them offers a free trial — Office 365 Home. This Experts Exchange video Micro Tutorial explains how to go through the process of obtaining the free, one-month trial for Office 365 Home, which includes the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. In a subsequent EE video Micro Tutorial, I show how to install the downloaded desktop versions of those Office 2016 modules in a Windows 7 system.

1. Visit the website for Office 365 Home


Visit the site with the only Office 365 edition that currently offers a free trial:
https://products.office.com/en-us/compare-microsoft-office-products

Step1

2. Request free trial


Click the "Try for free" button.

Step2aClick the "Try 1-month free" button.

Step2b

3. Sign into your Microsoft account


Enter your email or phone for your Microsoft account, your password, and click the "Sign in" button.

Step3

4. Go through the payment process


Even though it is a free trial, you must provide a payment method and go through the payment process. So be prepared with a credit/debit card or a bank account or PayPal. If you are unwilling to provide a payment method, you cannot get the free trial.

Step4

5. Go through the install process

0
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Thank You for sharing Joe :)
0
LVL 65

Author Comment

by:Joe Winograd
You're welcome, Yashwant. I'm glad you like it! Regards, Joe
0
MS Access – Using DLookup
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.

1. Specify the first argument, which is the expression to be returned

2. Specify the second argument, which is the data source. This may be a table or query

3. Specify the third argument, which is the criteria

4. How to return multiple fields from a single DLookup()

5. Pitfalls to avoid

3
Access Desktop Databases – A Quick Tour
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
4
MS Access – Basics of Designing Tables
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database.

1. Split up all multi-value fields into single values

2. Split up fields that belong to other things into separate tables

3. Make sure that all records will have the same “shape” (all fields can be filled in) and that there are no repeating fields

4. Make sure that all fields are independent of one another. No field should rely on another for it’s value

5. Assign primary keys

6. Add copies of primary keys to tables (called foreign keys)

3
MS Access – Basic Query Design
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
0
MS Access – Adding “Page ‘x’ of ‘y’” Over a Group in a Report
Learn how to number pages in an Access report over each group.

1. Activate two pass printing by referencing the pages property

2. Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to determine what pass your on.

3. Use an unbound control to display the page count

1
LVL 62

Author Comment

by:Jim Dettman (EE MVE)
Just noticed that I never actually gave the code in the tutorial:

Option Compare Database
Option Explicit

'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks
'

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant
Dim GrpNamePrevious As Variant
Dim GrpPage As Integer
Dim GrpPages As Integer


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)

  Dim i As Integer

  If Me.Pages = 0 Then
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)
    GrpNameCurrent = Me!ID
    
    If GrpNameCurrent = GrpNamePrevious Then
      GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
      GrpPages = GrpArrayPage(Me.Page)
      For i = Me.Page - ((GrpPages) - 1) To Me.Page
        GrpArrayPages(i) = GrpPages
      Next i
    Else
      GrpPage = 1
      GrpArrayPage(Me.Page) = GrpPage
      GrpArrayPages(Me.Page) = GrpPage
    End If
  Else
    Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If
  
  GrpNamePrevious = GrpNameCurrent

End Sub

Open in new window

0
Using a Criteria Form with an Access Report
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that appears when the user runs the report.

1. The video provides two examples of reports that load forms on the reports' open events.

2. The code both in the reports and the criteria forms is explained so that the viewer can understand all of the steps involved in capturing report criteria on a form.

5
Creating and Using an Access Class Module
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from a less-experienced developer.

1. The video begins by walking the viewer through the process of creating a class module.

2. We then add properties and methods to the custom class that we create.

3. Finally we write code to instatiate the class, set the values of the custom properties, and execute a method of the class.

1
Using a SQL Server View from Within Access
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access is a SQL Server view. After linking to a SQL Server view, the developer can create queries, forms, and reports based on that view. In fact, in most cases the data is editable!

1. The video first shows the viewer how to create a view in SQL Server Management Studio.

2. Once the user creates the view, they learn how to link to the view using an ODBC datasource.

3. The video demonstrates how the data in the view is editable.

4. The video then illustrates how easy it is to create forms and reports based on the view data.

1

Expert Comment

by:peterslove
I will like to know how to update, delete and add records to a linked sql table in an Access form.
0
Executing a Windows API Function from Access
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Using the Windows API, you can extend the functionality of Microsoft Access.

1. To call Windows API functions, you must first declare the functions and required type structures.

2. This video covers the steps necessary to declare Windows API functions

3. Declare the related type structures and variables

4. Then call the functions from a module.

6
LVL 34

Expert Comment

by:Mike Eghtebas
Hi TechMommy,

I am one of your remote students from 1990's and had purchased your Access 2.0 videos (I guess it was published by key-stone). This is my first opportunity to thank you the wonderful work you had done on those videos.

Back to this "Windows API" project. Is it possible to have the code files to save time typing them?

Thank you,

Mike
0

Microsoft Access

221K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.