You can easily add a custom form button that enables your users to choose which records to print. Mary Ann Richards steps through the process.

When you want to enable your users to print only the records they need, you can set up a form that lets them do just that. For example, to create a form that generates a report listing only the records of employees hired during a specific period, follow these steps:

1. Click the Text box tool in the Toolbar options box. (In Access 2007, click the Text box control on the Design tab.)

1. Click and drag to create the first unbound text box. Change the text box label to From:.

1. Click the Text Box tool in the Toolbar options box. (In Access 2007, click the Text Box control on the Design tab.)

1. Click and drag to create the second unbound text box. Change the text box label to To:.

1. Right-click the first text box and select Properties. Click the All tab and then click in the Name property box and type StartDate.

1. Right-click the second text box and select Properties. Click the All tab and then click in the Name property box and type EndDate.

1. Click the Command tool in the Toolbar Options box. (In Access 2007, click the Command control on the Design tab.)

1. Click and drag in the form to locate cialis soft the Command button. (Click Cancel to close the Command wizard, if necessary.)

1. Right-click the Command tool and then click Properties.

1. Click the All tab and then click in the Caption property box and type Print Report.

1. Click the Event tab.

1. Click the Build button in the On Click property box.

1. Click Event Procedure.

1. Enter the following code at the prompt, as shown in Figure A, and press Alt + Q:

Figure A

To use this form to generate a report for all employees hired from 2001 to 2003, you would enter 1/1/2001 in the From box and 1/1/2003 in the To box (Figure B). Then, just press the Print Report button.

Figure B

If you need to know how many words are in a document, and some of them are inside a text box, you’ll have to perform at least one extra step. Here’s a look at how to get the numbers you need.

The Word Count command in Word 2002/2003 excludes text boxes when totaling the number of words in your document. As Figure A shows, the Word Count dialog shows a count of 0, even though the document contains two linked text boxes filled with text.

Figure A

Since the text boxes are linked, you can find the number of words in those boxes by following cialis soft tabs vs cialis these steps:

1. Press Ctrl + A.

1. Go to Tools | Word Count.

The Word Count box now shows the correct number of Words in the text boxes, as shown in Figure B.

If the text boxes had not been linked, you would have needed to perform this operation on each box separately and then add the totals of each box to get a final count. Likewise, to calculate the total number of words in a document with one text box surrounded by other text, you would go to Tools | Word count to find the total for the surrounding text, follow steps 1 through 3 to find the total for the text box, and then add the two numbers for your total.

The spinner offers an easy way to test different input variables to see the effect on a financial model — without altering your underlying formulas. This example shows how to put this useful feature to work.

Sensitivity analysis determines how a spreadsheet’s outputs will vary in response to changes made to the spreadsheet’s inputs. Excel gives us a number of tools for sensitivity analysis. One of these is the spinner. A spinner is linked to a cell containing one of the variable inputs. As the user clicks on the spinner, the value of the linked cell changes.

For example, the following worksheet can be used to examine how changes in three variable cialis soft tabs online inputs (price, unit cost, and fixed cost) affect profit. To set up the formulas for the worksheet, follow these steps:

Figure A

1. Go to Tools | Options, click the Calculation tab, and then select the Accept Labels In Formulas check box. (In Excel 2007, click the Formulas tab, click Create From Selection in the Defined Names group, and then click OK.)
2. Enter 2 in cell D1, which is the current price.
3. Enter the rest of the formulas as shown in Figure B.

Figure B

1. Type Enter Unit Cost (in hundredths) in cell H1.
2. Type Enter Fixed Cost (in thousands) in cell H2.
3. Enter 44 in cell I1 (the current Unit Cost).
4. Enter 20 in cell I1 (the current Fixed Cost), as shown in Figure C.

Figure C

1. Go to View | Toolbars | Forms to display the Forms toolbar. (In Excel 2007, click the Developer tab.)
2. Click the Spinner button on the Forms toolbar. (In Excel 2007, click Insert in the Forms Group and then click the Spin button under Form Controls.)
3. Click and drag in cell F1 to create the Price spinner.
4. Repeat step 9 and then click and drag in cell F3 to create the Unit Cost spinner.
5. Repeat step 9 and then click and drag in cell F4 to create the Fixed Cost spinner.
6. Right-click the spinner in F1, click Format Control, and enter the values shown in Figure D.

Figure D

1. Right-click the spinner in F3, click Format Control, and enter the values shown in Figure E.

Figure E

1. Right-click the spinner in F4, click Format Control, and enter the values shown in Figure F.

Figure F

Your manager just learned that increases in material costs have raised the unit cost to .60. Using the spinner to adjust for this higher cost, the profit decreases to $45,800. Clicking the Price spinner up arrow once to raise the price to $3 immediately shows a drop in demand (assuming the demand formula is correct). However, even with this drop in demand, profit goes up significantly, which may warrant the price increase.

You can get a list of the columns in a primary key manually, but depending on the task at hand, you may want to handle it programmatically instead. Susan Harkins shares a VBA function that uses ADOX objects to obtain the column information.

Working with key values is serious work, and assigning a primary key is just the beginning of the process. If you need to manipulate a primary key programmatically, you need to know the columns that the key comprises. There are easy ways to do that manually, but doing so programmatically can prove useful if the task is part of the application’s internal workings or you’re dealing with external tables.

Note: This information is also available as a PDF download, along with a BAS file containing the code listing.

The code

You might think that listing the columns in a primary key would be easy, but that’s not the case. Perhaps the most efficient process is to use ADOX objects. Specifically, the function in Listing A uses ADOX catalog, table, index, and column objects. A series of For…Each loops and If…Then…Else statements cycle through three collections to determine the table’s primary key index and then build a string variable from the names of the columns that belong to that key. All that, just to list a few columns!

Listing A

Function ListPK(tbl As String) As String

'List primary keys for passed table. 'Must reference ADOX library: 'Microsoft cialis soft tablets ADO Ext. 2.8 for DDL and Security. Dim cat As New ADOX.Catalog Dim tblADOX As New ADOX.Table Dim idxADOX As New ADOX.Index Dim colADOX As New ADOX.Column

  cat.ActiveConnection = CurrentProject.AccessConnection

  On Error GoTo errHandler

  For Each tblADOX In cat.Tables

If tblADOX.Name = tbl Then If tblADOX.Indexes.Count <> 0 Then For Each idxADOX In tblADOX.Indexes With idxADOX If .PrimaryKey Then For Each colADOX In .Columns ListPK = colADOX.Name & ", " & ListPK Next End If End With Next End If End If Next

  If ListPK = "" Then    ListPK = "No primary key" Else ListPK = Left(ListPK, Len(ListPK) – 2) End If

  Set cat = Nothing  Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing Exit Function

errHandler:  MsgBox Err.Number & ": " & Err.Description, vbOKOnly, _ "Error" Set cat = Nothing Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing

End Function

To enter this function, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu, enter the code, and save the module. This code uses ADOX objects, so be sure to reference the Microsoft ADO Ext. 2.8 For DDL And Security library. Choose References from the Tools menu, select the library (Figure A), and click OK.

Figure A

Reference the ADOX library.

To execute the function, open the Immediate window by pressing Ctrl + G. Type the following line:

ListPK("tablename")

where tablename is the table for which you’re listing primary key columns. Now, press Enter. Figure B shows the results of passing the Northwind table Employees to ListPK(). (Northwind is an example database that comes with Access.)

Figure B

Use the Immediate window to pass a table to the ListPK() function.

The first For…Each loop cycles through the Tables collection looking for tbl, the passed string, which in this case is Employees. When the code finds a match, the next statement makes sure that Employees has at least one index to examine. If it does, the code loops through the Indexes collection until it finds the primary key index. The next For…Each loop builds a string that includes the names of all the columns in the primary key in column1, column2, column3 format. Finally, the function returns that string.

If a table has an index but no primary key, the function returns the string “No primary key.” If the table has no index, the function returns the string “No primary key.” You could just as easily use a subprocedure to print the results to the Immediate window.

Print keys

Primary keys are an integral part of any relational database. You can use ListPK() while debugging a new database. With some customization, you could use it to manipulate primary keys programmatically.

Word 2003 offers a gallery of Table AutoFormat styles — but they may not always suit your needs. See how to build your own style to quickly make tables look exactly the way you want.

Word’s Table AutoFormat feature offers an assortment of prefab styles you can use to jazz up your tables. But those styles may not necessarily match your document design or serve your table’s purpose. Luckily, you can specify your own set of attributes and save them as a user-defined style. Then, you can just apply the style to a table whenever you want to use your custom formatting.

Note: This information is also available as a PDF download.

To demonstrate, let’s say you generate a weekly dashboard report whose data is contained in a nice-looking, easily interpreted Word table… but manually formatting the table is getting a little old. Time to build a custom style:

Figure A

1. Enter a name for the style (e.g., Dashboard) and choose the style you want to base your new style on (Figure B). We’re going to base our sample style on the basic Table Grid style, but you can start off with something fancier if you prefer. Or choose Table Normal, which is unformatted, if you want to start with a blank slate.

Figure B

1. Now you can use the various tools in the New Style dialog box to specify the desired formatting. Just choose the table component you want to format from the Apply To drop-down list (Figure C) and make your selections. For this example, we specified 11-point Arial for the entire table, 14-point bold formatting and a light yellow fill for the heading row, a 1.5-point blue outside border, a 1-point yellow inside border, light blue fill for odd rows and light yellow fill for even rows (Figure D). (You’ll notice that some of the formatting — such as the font — doesn’t display in the preview.)

Figure C

Figure D

1. Click the Format button to access additional options governing the appearance of table elements and table text (Figure cialis shelf life E). In this case, we selected Paragraph and specified 6 points of space above and below each paragraph. Not all options are available to include in your table style. For instance, you can’t set Preferred Width or Text Wrapping in the Table Properties dialog.

Figure E

1. If you want the style to be available to other documents based on the current template, click Add To Template. Otherwise, the style will belong to the current document only. Click OK to return to the Table AutoFormat dialog box.
2. If you’d like this table style to be the default for all new tables you create, click Default in the Table AutoFormat dialog box. Word will let you choose between setting the default for the current document or for all documents that use the current template (Figure F). Make your selection and click OK. If you don’t want to set a default, skip this step and simply close out of the Table AutoFormat dialog box.

Figure F

The payoff

To apply the style, click in a table and open the Table AutoFormat dialog box. Choose User-Defined Table Styles from the Category drop-down list box to display your custom style(s) (Figure G). Now, just select the style and click Apply.

Figure G