Microsoft Office Suite TechGuides

A quick method for counting Access records in a form

There are several ways to count the records in a form's recordset. In fact, Access forms display the record count by default.

Microsoft Access

---

A quick method for counting Access records in a form

There are several ways to count the records in a form's recordset. In fact, Access forms display the record count by default.

For many, this built-in feature is more than adequate. However, some people choose to inhibit the Navigation buttons, which display the record count.

If users need to see the number of records populating a form and the form doesn't display the Navigation buttons, drop in a Count() function to display the record count as follows:

1. With the form in Design view, insert a text box control.

2. Enter the expression in the form =Count(primarykey) as the control's Control Source property.

The Count() function takes just one argument, and in this case, that's the name of the field that contains a unique value for each record — usually the primary key.

With the form in Form view, the expression returns the number of records in the primary key field. In the following figure, the primary key is the ProductID value in Northwind's Products table. Notice that the result of the Count() function in the form's header matches the form's Navigation button--there are 77 records.

The Count() expression will also accommodate a filtered recordset, as shown below. In this case, there are 13 records in the filtered recordset.

In both cases, the Count() function works fine. This method won't work in every situation, but it's flexible enough to handle most.

Microsoft Excel

---

10 ways to recover a corrupted Excel workbook

Even if you faithfully back up your Excel workbooks, corruption can still be a problem. The backup files won't always contain your most recent work, so you'll probably end up re-entering data.

Repairing a corrupted workbook, if possible, is a better option. In this respect, Excel can help. If you attempt to open a corrupted workbook, Excel will engage File Recovery mode, which attempts to repair the workbook. If that works, great!

Unfortunately, Excel's automated File Recovery feature sometimes fails to repair a damaged workbook. When this happens, you'll need alternatives. Try the easiest solutions first. The more complex methods usually recover data, but no formulas, formatting, charts, or macros.

1: Let Excel try
If Excel's automatic recovery mode fails, there's a manual feature for recovering a corrupted workbook:

1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
2. Using the Look In control, locate and specify the corrupted workbook.
3. From the Open button's dropdown list, shown in Figure A, choose Open And Repair.
4. If this is your first attempt to manually recover the workbook, click Repair in the resulting dialog box, shown in Figure B.

Figure A

Attempt to manually recover a corrupted workbook.

Figure B

You can repair a workbook or extract the data from a corrupted workbook.

If you're lucky, Excel will repair the file. However, this feature recovers files when something goes wrong with Excel, so it won't recover every corrupted file. Once you've made every attempt to recovery the file, you might have to make do with recovering just the data. When that's the case, click Extract Data in step 4.

2: If the corruption occurs while the workbook is open
If corruption occurs while the workbook is open, do not save the workbook. You'll just save whatever corrupted the workbook along with the most recent changes.

Instead, revert to the last saved version. Doing so, without saving first should discard the corrupted component. You'll possibly lose data, but often, you can rebuild quickly enough (a good reason to save your work often).

To revert to the last saved version, do the following:

1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
2. Using the Look In control, locate and specify the corrupted workbook.
3. Click Open.

You're really just reopening the workbook--there's nothing special going on.

3: Disable automatic calculation
If Excel fails to open the file on its own or via the Open And Repair option, try setting the calculation method to manual and try again. To reset the calculation setting:

1. Open a new blank workbook.
2. From the Tools menu, choose Options and click the Calculation tab. In Excel 2007, click the Office button, click Excel Options, and select Formulas in the panel to the left.
3. In the Calculation section (Calculation Options in Excel 2007), click Manual.
4. Click OK.

Then, try to open the corrupted workbook. Sometimes Excel can open a corrupted workbook if it doesn't have to recalculate.

4: Try Microsoft Office Tools
If Excel can't open the corrupted workbook, give Microsoft Office Tools a whirl:

1. From the Start menu, choose All Programs (in Windows XP).
2. Select Microsoft Office.
3. Choose Microsoft Office Tools.
4. Select Microsoft Office Application Recovery.
5. In the resulting dialog box, shown in Figure C, choose Microsoft Office Excel.
6. Click Recover Application. The process could take a few minutes.
7. Respond to the Send Report To Microsoft prompt.

Figure C

Office Tools might recover a corrupted workbook.
The recovery tool will close Excel and then restart it, displaying a list of recovered workbooks (if any). If you're lucky, your corrupted workbook will be in the list. Simply open it and count your blessing.

5: Move the file
Sometimes, a corrupted workbook isn't really corrupted in the traditional sense. It just isn't accessible as usual; networks and servers often mask errors as corrupted files. If you encounter a seemingly corrupted workbook that Excel can't repair, move the corrupted file to another folder, drive, or server.

6: Let the competition try
Some people claim extraordinarily good results using OpenOffice Suite, a free open source alternative to Microsoft Office. This suite's Excel counterpart, Calc, should open a corrupted Excel file. Unfortunately, it means a lengthy download, but if it works, it's well worth the effort.

7: Open the corrupted workbook in WordPad or Word
If you can't repair the workbook, try opening it in WordPad. If it works, WordPad will convert everything to text--but you'll be one step closer to recovering important data.

Unfortunately, this method won't recover formulas. On the other hand, unlike many other data recovery methods, WordPad will recover your VBA procedures (macros). Simply search recovered text for Sub and Function to find them.

You might also be able to open the corrupted .xls file in Word, but the results will be limited. For this method to work, you must install the Microsoft Office Excel converter. And unlike WordPad, Word won't recover your procedures.

8: Use external references
Sometimes, you can recover data by referring to the actual cells in a corrupted workbook. You won't recover formulas, formats, charts, macros, and so on, but recovering the data is better than recovering nothing. To recover data by referencing the corrupt workbook, do the following:

1. Open a new workbook and enter into cell A1 a formula in the following form to reference A1 in the corrupted workbook: nameofcorruptedworkbook!A1. (You don't need to include .xls in the filename.)
2. Press Enter.
3. If the corrupted workbook isn't in the same folder, Excel will display the Update Values: nameofcorruptedworkbook dialog box. Use the Look In control to locate the corrupted file. Select the file and click OK.
4. If the Select Sheet dialog box appears, as shown in Figure D, select the appropriate sheet and click OK. Excel will display the value in cell A1 of the corrupted workbook.
5. Select cell A1 and drag it across as many columns as needed to accommodate the data in the corrupted file. If Excel displays the Update Values: nameofcorruptedworkbook dialog box, select the corrupted file and click OK.
6. Repeat step 5, copying row A down as many rows as necessary to accommodate the data in the corrupted file.
7. Select the data and choose Copy from the Edit menu.
8. Select Paste Special from the Edit menu and choose Values, as shown in Figure E.
9. Click OK.

Figure D

If the corrupted file has multiple sheets, specify a sheet.

Figure E

Replace the referencing formulas with data.

9: Try SYLK format to recover data
Microsoft recommends using the SYLK format to filter out corrupted elements, especially if the corruption is printer related. You must be able to open the corrupted workbook for the following to work:

1. From the File menu, choose Save As. In Excel 2007, click the Office button.
2. From the Save As Type control, choose SYLK(Symbolic Link) (*.slk), as shown in Figure F.
3. Give the active sheet a descriptive name, such as corruptedworkbooknameSheet1. If the workbook has only one sheet, this step is unnecessary.
4. Click Save.
5. If the workbook has multiple sheets, Excel will ask you if you want to continue because the selected format doesn't support multiple sheets. Click OK.
6. If Excel prompts you with a warning that the workbook might contain features that aren't compatible with the SYLK format, click Yes.

Figure F

Save each sheet using the SYLK format.

It's important to know that the SYLK format saves only the active sheet. However, you won't notice that the format has stripped all of the pages but the active one until you close and reopen the .slk file.

To recover data from all the sheets, you must reopen the corrupted workbook and save each sheet individually. That's why step 3 instructs you to give the sheet a descriptive name. You'll find those names helpful when reconstructing the multiple-sheet workbook.

After saving all the sheets to the SYLK format, open one of the .slk files and rename it using the .xls format. Be careful not to use the corrupted workbook's name. Once you've reconstructed the workbook, you can discard the corrupted file or rename it, freeing up the original name. Don't replace it until you're sure you've recovered as much data as possible.

Then, reconstruct the workbook by importing or copying data from the .slk files. It's a tedious process, but worth the effort if nothing else works. This method saves values resulting from formulas, but not the formulas themselves.

10: Recover macros
If you can recover data but not macros via one of the previous tips, you can still save macros--at least sometimes. To try, do the following:

1. Open Excel, but don't open the corrupted workbook.
2. Set the calculation mode to Manual (see #3).
3. Choose Macro from the Tools menu, select Security, and choose the High option. In Excel 2007, click the Office button, click Excel Options, and choose Trust Center in the left panel. Then, click the Trust Center Settings button, select Macro Settings in the left panel, select Disable All Macros Without Notification in the Macro Settings section, and click OK twice.
4. Open the corrupted workbook. If Excel opens the workbook, you'll see a notice that the macros are disabled. If Excel shuts down, this method won't work.
5. Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
6. Using the Project Explorer (press [Ctrl]+R), right-click a module, and choose Export File.
7. Enter a name and folder for the module.
8. Repeat steps 6 and 7 as many times as necessary to export all the modules.
9. Close the VBE and exit Excel.
10. Open a new blank workbook (or the newly constructed workbook that contains recovered data from the corrupted workbook) and import the modules.

Microsoft Word

---

How to get rid of a sticky Word border

Word has a number of auto-generating formats, and most of them come in handy. For instance, if you type three hyphens and press [Enter], Word will turn those three hyphens into a solid line that extends from the left to the right margin. For better or worse, sometimes the line seems to stick to text or to the bottom of a page. No matter what you do, you can't get rid of it.

That's because the line is really a paragraph format-–it's a border and not a line object. If you enter the three hyphens directly under text, Word will attach a border to the text as a paragraph format. If there's a line between existing text and the hyphen-generated line, the line stands alone.

Here's how to reproduce this annoying behavior:

1. Type a line of text and press [Enter].
2. Type three hyphen characters (- - -) and press [Enter] twice.
3. Now, backspace over the line, in the hopes of deleting it. You can't.
4. Enter a blank line between the text and the line and try to delete the line. You can't.

If you move the text, the line goes with it. In addition, you can't select the line and delete it. The line remains even if you delete the text.

The line isn't a line at all. It's a border and it's assigned to the paragraph. To get rid of it, treat it like a border, not an object. Simply position the cursor anywhere in the paragraph and choose No Border from the Borders tool on the Formatting menu.

If you don't want any hyphen-generated borders, choose AutoCorrect Options from the Tools menu. Then, click the AutoFormat As You Type tab and uncheck the Border Lines option in the Apply As You Type section.

Unfortunately, if you disable the Border Lines option, Word will no longer generate a line from a group of hyphens. Most likely, you won't want to disable this feature. Instead, just delete the border if it gets sticky.

If you don't want the border attached to text, be sure to insert at least one blank line between any text and the hyphen characters.

Note: Thanks to Tina Norris Fields for sharing her expertise on this subject.

See also:  Microsoft Office, Method, File, Tool, Tools Menu, Microsoft Excel 2007, File Menu, Microsoft Excel, Microsoft Access, dialog box

+ Add tag

To add tags, you need to become a member. It's FREE.

Log in or sign up now!

Cancel