Open Tables and Queries with VBA, Startup Form

Locked lesson.

Upgrade

About this lesson

Create a command button to open a table or query and make it the Startup form when the database opens.

Quick reference

Open Tables and Queries with VBA, Startup Form

Steps

Command button to Open a Table

  1. Go to the Design View of your form
  2. Choose the Button tools from Controls on the DESIGN ribbon of FORM DESIGN Tools
  3. Click on your form where you want the button
  4. Cancel the Wizard if it comes up
  5. Open the Property Sheet if it is not showing by pressing Alt-Enter
  6. On the Other tab, give your button a good Name (i.e.: cmd_OpenMyTablename)
    WHERE MyTablename is the name of your table
  7. In the Event tab, in the Click event for the button, type
    [ (to choose [Event Procedure])
  8. Click the Builder button, ... or press Ctrl-F2 for the builder
  9. In the code, type:
  10. From the menu, choose Debug, Compile then Save.

Command button to Open a Query

  1. Go to the Design View of your form
  2. Choose the Button tools from Controls on the DESIGN ribbon of FORM DESIGN Tools
  3. Click on your form where you want the button
  4. Cancel the Wizard if it comes up
  5. Open the Property Sheet if it is not showing by pressing Alt-Enter
  6. On the Other tab, give your button a good Name (i.e.: cmd_OpenMyQueryname)
    WHERE MyQueryname is the name of your query
  7. In the Event tab, in the Click event for the button, type
    [ (to choose [Event Procedure])
  8. Click the Builder button, ... or press Ctrl-F2 for the builder
  9. In the code, type:
  10. From the menu, choose Debug, Compile then Save.

Specify a Startup Form

  1. From the menu, choose FILE, Options, Current Database.
  2. Drop the choices for Display Form and choose the form you want to automatically open (i.e., f_MAINMENU).
  3. When you close the database and open it again, that form will be displayed.

Change Company Information

  • To customize the database for your company, click My Company, and change the Company name in the MyCompany table.

Concepts

qListOfObjects.

This is a new query in the database that shows you a list of the database objects when you open it.

Analyzer for Microsoft Access

To download the latest version of the free Analyzer for Microsoft Access, go to CodePlex, which is Microsoft's sharing site for developers.

http://analyzer.codeplex.com/

Login to download
  • 00:04 This is lesson 39 of Access 2013.
  • 00:08 We are nearly done with our main menu.
  • 00:12 In this lesson, we will add buttons
  • 00:15 to open tables that don't have forms yet,
  • 00:18 queries that don't have reports,
  • 00:21 and make it the Startup form when the database opens.
  • 00:24 Hi, this is Crystal.
  • 00:27 Here is a list of the tables and queries in this database.
  • 00:31 The data was generated from a query that was added
  • 00:35 to the exercise file for this lesson called qListOfObjects.
  • 00:40 The data was then copied and put into Excel
  • 00:43 to add formatting and more information.
  • 00:47 If you want a more comprehensive analysis of the database,
  • 00:50 an essential tool for every Access developer is the free
  • 00:55 Analyzer for Microsoft Access
  • 00:58 which is posted on CodePlex,
  • 01:00 the developer sharing site for Microsoft.
  • 01:03 http://analyzer.codeplex.com
  • 01:07 A link to this free tool, and a couple screen shots are
  • 01:10 in the Quick Reference Guide for this lesson.
  • 01:14 In the list, the objects in blue
  • 01:16 need a way to open from our menu.
  • 01:19 There are 3 tables,
  • 01:21 which is the same as the number of forms we have.
  • 01:24 Copy the rectangle, label, and forms buttons
  • 01:28 and place the copy to the right.
  • 01:31 Change the label for Forms to Tables.
  • 01:35 Keep in mind that ALL buttons should
  • 01:38 open Forms, not Tables directly,
  • 01:41 but since we have not yet created forms for these objects,
  • 01:45 I will show you how to open tables
  • 01:47 so users can edit the information.
  • 01:51 Oops, I didn't grab the bottom button.
  • 01:53 I will copy another one so there are 3.
  • 01:56 Rename the table buttons and the captions to:
  • 02:00 Name: cmd_OpenCustomersTable
  • 02:03 Caption: Customers
  • 02:06 Name: cmd_OpenMyCompanyTable
  • 02:09 Caption: MyCompany
  • 02:13 Name: cmd_OpenTimeTypesTable
  • 02:16 Caption: Time Types
  • 02:20 Create code for the Click event of each command button
  • 02:23 by modifying MyTableName in the following statement:
  • 02:27 DoCmd.OpenTable "MyTableName"
  • 02:44 Debug, Compile the code and Save.
  • 02:49 Go back to the form and click on each of the table buttons
  • 02:54 to open it up and test it to make sure it works.
  • 03:01 We have 6 queries to add.
  • 03:04 The names are longer, like for reports.
  • 03:08 Copy the rectangle, label, and command buttons for reports
  • 03:12 and place the copy below the reports.
  • 03:15 Delete the bottom button in the first column.
  • 03:18 Make the rectangle shorter.
  • 03:21 After you select the rectangle, you may need to make
  • 03:24 the section a little taller to see the bottom resizing handle.
  • 03:29 Copy the 3 buttons in the new rectangle and paste.
  • 03:34 Place the copied buttons to the right
  • 03:36 so there are 2 columns of 3 buttons.
  • 03:39 Select each row of 2 buttons and click Align Top on the QAT.
  • 03:47 Change the label on the left to say Queries instead of Reports.
  • 03:52 For each query:
  • 03:54 Copy the name of each Query.
  • 03:56 Change the button name to "cmd_" and
  • 04:00 then paste the name of the query.
  • 04:03 Change each caption to reflect what the query does.
  • 04:08 Create a Click event for each button
  • 04:11 by modifying MyQueryName in the following statement:
  • 04:15 DoCmd.OpenQuery "MyQueryName"
  • 04:24 Once you have the first procedure written,
  • 04:27 you can select it, then Ctrl-Drag to new procedures.
  • 04:31 Make sure you are holding the Ctrl key
  • 04:33 when you let go of the mouse
  • 04:35 to drag a copy instead of moving the lines.
  • 04:40 After the lines are copied,
  • 04:41 replace the old query name with the new one.
  • 04:45 When the cursor is an I-Beam and you double-click,
  • 04:48 the entire word is selected.
  • 04:51 New typing or what is pasted replaces what is selected.
  • 04:56 To create an event, click in the event name
  • 04:59 and type [ then press Ctrl-F2.
  • 05:05 If you have extra blank lines in the code, they can be deleted.
  • 05:10 A blank line betweeen procedures is good
  • 05:12 as it helps to separate them.
  • 05:15 For the Captions of the buttons,
  • 05:17 you can paste the query name,
  • 05:19 then edit it to add spaces and
  • 05:21 take out extra words if the text is long.
  • 05:26 On the All tab of the Property Sheet,
  • 05:28 Name and Caption are listed at the top.
  • 05:32 In the Navigation Pane, to copy the name of an object,
  • 05:36 right-click and choose Rename or press F2.
  • 05:41 Press Ctrl-C to copy and then ESCape to cancel renaming
  • 05:46 or click away from the object without renaming.
  • 05:50 After creating the code to open each query,
  • 05:53 Debug, Compile and Save.
  • 05:56 Test each button to ensure that it works.
  • 06:00 And it does.
  • 06:03 Now that we have a Main Menu, make it the Startup Form.
  • 06:07 From the menu, choose FILE, Options, Current Database.
  • 06:13 Then drop the choices for Display Form
  • 06:15 and choose f_MAINMENU.
  • 06:20 When you close the database and open it again,
  • 06:23 the main menu will be displayed.
  • 06:26 To customize the database for your company, click My Company
  • 06:31 and change the Company name in the MyCompany table.
  • 06:36 In the next lesson, we will add a logo to the Main Menu.
  • 06:40 The next lesson is also the last lesson in this series,
  • 06:44 so we will also review what has been covered.

Lesson notes are only available for subscribers.

Gift this course