ch08.htm

Microsoft_Access_97_Quick_Reference.zip

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Go into Excel and look up the <I>syntax</I> for the function including
				any required <I>arguments</I> within the parentheses. For example, the <TT>Round</TT>
				function requires a value and number of decimal places.
				<P><B>2.</B> Return to Access and open an existing <I>module</I> in <I>Design View</I>
				or click the <U>N</U>ew button on the Modules tab to create a new module.</P>
				<P><B>3.</B> Make sure Excel is registered by choosing <U>T</U>ools, Re<U>f</U>erences
				and checking Microsoft Excel in the Available References <I>list box</I>. Choose
				OK.</P>
				<P><B>4.</B> In the function statement, include the new function name (it can be
				the same name as the Excel function) and any arguments required. For example, type
				<B>Function ExRound (Number as Single, Places as Byte)</B>. Access will automatically
				add <TT>End Function</TT> at the end of the procedure.</P>
				<P><B>5.</B> On the line that returns the value, type <B>Functionname = Excel.Application.ExcelFunctionname</B>
				and include the names of the arguments you added in Step 3. Replace <I>Functionname</I>
				with the name you want to use in Access, and <I>ExcelFunctionname</I> with the name
				of Excel's function. For example, type <B>ExRound = Excel.Application.Round (Number,Places)</B>.</P>
				<P><B>6.</B> Save the module and test the procedure.
			</OL>
			<P>The first time you run this function, it will take a few moments to run as Excel
			is opened and the function is retrieved. You can use this function in <I>VBA</I>
			procedures, queries, forms, reports and the <I>Debug window</I>.
			<H2><FONT COLOR="#000077"><B>Programming: Create Messages</B></FONT></H2>
			<P>Often you will want to create messages that tell users what is going on in your
			program or ask users for input. You add the <TT>Msgbox</TT> statement as part of
			your procedure. You first need to create a procedure or function. (See &quot;Programming:
			Create a Procedure&quot; or &quot;Programming: Create Function.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Open the <I>module</I> with the procedure in <I>Design view</I> and click
				the Procedure <I>list box</I> (the arrow on the right side below the VBA module window
				title bar) to choose your procedure.
				<P><B>2.</B> In the upper portion of your procedure, declare a return value for the
				message box. For example, type <B>Dim RetValue as Integer</B>. You do not have to
				use the return value in your procedure, but if you do, Access will save the value
				associated with the button that the user chooses so you can use it later in the procedure.</P>
				<P><B>3.</B> Move to the location in your <I>code</I> to insert the message box and
				type <B>RetValue=MsgBox(&quot;Prompt&quot;,Buttons, &quot;Title&quot;)</B> where
				<I>Prompt</I> is whatever you want the <I>dialog box</I> to say, and <I>Title</I>
				is the title of the dialog box. <I>Buttons</I> is a Visual Basic constant(s) identifying
				the buttons and icons on the dialog box. Some examples include <TT>vbYes</TT>, <TT>vbYesNo</TT>,
				<TT>vbOKOnly</TT>, <TT>vbQuestion</TT>, and <TT>vbExclamation</TT>. You can include
				a button and an icon by using an <I>expression</I> such as <TT>vbYes + vbQuestion</TT>
				where the <TT>Buttons</TT> argument is needed.</P>
				<P><B>4.</B> If you want to use the <TT>RetValue</TT> later in your procedure, test
				it with some statement like <TT>If RetValue = vbYes Then</TT>.</P>
				<P><B>5.</B> When finished with your procedure, click the Save and test it.
			</OL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> For more choices for the <TT>Buttons</TT>
			argument, look up <TT>MsgBox</TT> function in the online help.<BR>
			You can also create message boxes by choosing the <TT>MsgBox</TT> action in a <I>macro</I>
			and filling in the action <I>arguments</I> in the lower half of the screen. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Debug with Breakpoint</B></FONT></H2>
			<P>If you've tried debugging your program (see &quot;Programming: Debugging&quot;)
			and there are no visible errors, and you know there is still something wrong with
			your program, you might need to step through your program one line at a time until
			an error occurs. When you want to see the changing values associated with your variables,
			you can set a watch expression. As you move through your procedure, the watch expression
			changes. A breakpoint enables you to stop the procedure at a specific statement and
			then step through the rest of the program to look for errors.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Open the procedure in <I>Design View</I> and click the mouse in the first
				line of questionable <I>code</I>. The cursor cannot be on a blank line, a line with
				only a comment, or on lines declaring variables.
				<P><B>2.</B> Click the Toggle BreakPoint button.</P>
				<P><B>3.</B> If you want to see the value of variables or expressions during the
				procedure, select the <I>variable</I> or <I>expression</I> and click the Quick Watch
				button.</P>
				<P><B>4.</B> Run the procedure by performing the <I>event</I> that triggers the procedure
				or clicking the <I>Debug Window</I> button and typing the procedure name. Include
				values for <I>arguments</I> in parentheses if the procedure requires arguments, and
				type a <B>?</B> before function names. After you press Enter, Access will run until
				it reaches the location where you set the breakpoint, then display the code window
				and Debug window. Any variables or expressions you chose will appear on the Watch
				tab of the Debug window.</P>
				<P><B>5.</B> Click the Step In button to go to the next line of your code. If the
				line of code calls another procedure, click Step Over if you don't want to step through
				the sub procedure. Continue this step, viewing the Debug window for any watches you
				set until you find your error.</P>
				<P><B>6.</B> If you want to run your code to the end without going step-by-step,
				click the Go/Continue button. If you want to stop running your code, click the End
				button.
			</OL>
			<H2><FONT COLOR="#000077"><B>Programming: Debugging</B></FONT></H2>
			<P>When you create a procedure (see also &quot;Programming: Create a Procedure&quot;
			or &quot;Programming: Create a Function&quot;), there are a few things you can do
			to find errors with your program.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To help you avoid misspelling <I>variable</I>
			names, choose <U>T</U>ools, <U>O</U>ptions; click the Modules tab; and check R<U>e</U>quire
			Variable Declaration. To view this statement or add it manually, choose General on
			the <I>Object</I> drop-down list (on the left) and Declarations on the Procedure
			drop-down list (on the right). Type <B>Option Explicit</B> in this section if it
			doesn't appear. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> As you type statements and press Enter, <I>syntax</I> errors appear in
				red with an error message.
				<P><B>2.</B> If there are no apparent errors in your procedure, click the <I>Compile</I>
				Loaded Modules button to see if you have any compile errors. If you have any errors,
				Access will highlight the line or variable containing the error</P>
				<P><B>3.</B> If you have no apparent syntax or compile errors, try running the procedure
				by clicking the <I>Debug Window</I> button or performing the <I>event</I> that calls
				the procedure.</P>
				<P><B>4.</B> Fix the error in your program, and click the Save button to save your
				changes.
			</OL>
			<P>If there are no syntax, compile, or runtime errors, and you know the program is
			wrong, you have a <I>logic error</I>. You might need to add a breakpoint and a watch
			<I>expression</I> and then step through the program. (See also &quot;Programming:
			Debug with Breakpoint.&quot;)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> In the preceding Step 3, the procedure
			you test can be in different locations. If the procedure is on a <I>module</I>, click
			the Debug Window button to open the Debug window, type the procedure name, and press
			Enter. If the procedure is a function procedure that returns a value, type a question
			mark and the function name. If either the sub procedure or function procedure require
			<I>arguments</I>, enclose values in parentheses after the name in the Debug window.
			If there is a runtime error, Access might describe the error well enough for you
			to debug the program. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT> You can also press Ctrl+G to open the Debug
			window. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Find Procedures</B></FONT></H2>
			<P>If you want to view or edit Visual Basic procedures, there are a number of ways
			you can find the procedure.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> In Design View of a module, click the <I>Object</I> Browser button on the
				toolbar.
				<P><B>2.</B> In the top drop-down list, find the name of the current <I>database</I>
				file. In the second drop-down list, type the name of the procedure (or text within
				the procedure) and then click the Search button.</P>
				<P><B>3.</B> In the Search Results area of the Object Browser window, double-click
				the procedure.
			</OL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> You can also go to procedures in other
			ways depending on where the procedure is. If you are in the <I>Design View</I> of
			a <I>module</I> and the current procedure calls another procedure, click the name
			of the procedure and press Shift+F2 to move to the referenced procedure.<BR>
			<BR>
			In Design View of a <I>form</I> or <I>report</I>, open the <I>Property</I> sheet
			and go to the <I>event</I> procedure. Click the Build button.<BR>
			<BR>
			If in Design View of a module with the current procedure, click the Object drop-down
			list and choose the object associated with the procedure (if it is a form module,
			for example). Then click the Procedure drop-down list and choose the procedure name
			or event. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Printing</B></FONT></H2>
			<P>To document your work or show examples, you will want to print your Visual Basic
			programming statements.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Open a module in Design View. If you are in Design View of a form or report,
				click the <I>Code</I> button.
				<P><B>2.</B> Click the Print button to print all procedures within the module.</P>
				<P><B>3.</B> If you want to print multiple modules at one time as well as see other
				properties including owner, date created (and <I>controls</I> for forms and reports),
				choose <U>T</U>ools, Anal_yze, <U>D</U>ocumentor and choose the objects you want.
				Then click OK. The document will show in Print Preview. To print, click the Print
				button.
			</OL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT> If you use the Documentor, there will
			be many pages for each form or report. If you want to print just the code, choose
			the Options button on the Documentor <I>dialog box</I> and uncheck everything in
			the include area except <U>C</U>ode. In the Include for <U>S</U>ections and Controls
			area, choose Nothing. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Variables Setting</B></FONT></H2>
			<P>At the beginning of each procedure, you need to identify the variables that you
			will be using. <I>Variables</I> are useful if you will be referring over and over
			to the same value or <I>object</I>. When you declare variables, it is a good idea
			to also declare the <I>data type</I> instead of letting Access use the default <TT>Variant</TT>
			type to help determine programming errors.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Open up the <I>module</I> in <I>Design view</I>.
				<P><B>2.</B> To identify <I>global</I> variables which you will use in more than
				one procedure, choose General from the Object drop-down list and Declarations from
				the Procedure drop-down list. To identify local variables which you will only use
				in the procedure, choose the object (such as a <I>command button</I>), if necessary,
				from the Object drop-down list and choose the procedure name or <I>event</I> from
				the Procedure drop-down list.</P>
				<P><B>3.</B> Type <B>Dim</B>, a space, and the <I>variable</I> name.</P>
				<P><B>4.</B> Type <B>As</B>, a space, and then a data type such as <B>String</B>
				(for text), <B>Integer</B>, <B>Single</B>, <B>Double</B>, <B>Boolean</B> (for Yes/No),
				<B>Control</B>, <B>Form</B>, or other data type. If you leave off <B>As</B> and the
				data type, Access assumes a <TT>Variant</TT> type which can be any data type.</P>
				<P><B>5.</B> Repeat Steps 3 and 4 for all variables. Finish the procedure, save the
				module, and test it.