| Description | Tip |
|---|---|
| Access input masks | In a recent tip, we showed you how to see a control's input mask before
entering data. To do so, we recommended adding the line SendKeys("x" & "{bs}") to the control's Enter event procedure. This technique works great when you're entering new records. However, as several subscribers have pointed out, this routine destroys existing data if the control is already populated. Andrew X (andrew@andrewsite.com) suggests using the following routine instead: If IsNull(Me!FieldName) Then SendKeys("x" & "{bs}") The code can be further modified to avoid causing an error if the field is numeric. When using the code with a numeric field, the end result appears the same; however, Access produces an error beep. As another reader points out, this problem is easily rectified by substituting "1" for the "x" in the SendKeys method. |
| Access through the Startup Property dialog box | The new Startup Property dialog box determines how Access starts your
database, and, in many ways, has replaced the AutoExec macro. Of the more
noteworthy options are the Application Title, Application Icon, Display
Form, and Display Database Window settings. The Application Title feature
allows you to change the text on the Access Window's title bar from
Microsoft Access to a name that best suits your database. This name also
appears as the button caption on the Window's Taskbar. The Application
Icon property can either be a bitmap or icon file and will be displayed
next to the Application Title on the title bar and taskbar button. Perhaps
even more useful is the Display Form setting. Clicking the dropdown arrow on this field displays a list of available forms in your database. When it starts the database, Access will automatically display whichever form you select. Finally, the Display Database Window check box determines if the Database window will be visible when the database opens. Even when this option is turned off, however, the Database window can still be accessed by normal means, such as by pressing [F11], or by clicking the Database Window button on the toolbar. To access the Startup Property dialog box, from the Database window choose Tools/Startup... from the menu bar. |
| Add self-disabling, custom navigation buttons to a form | Access makes it easy to use the Control Wizard to add your own
navigation buttons to a form. However, the buttons that the wizard creates
don't disable themselves when you reach the beginning or end of a form's
records. If you click the "previous" button when you're in the
first record, Access generates an error. Similarly, if you're in the last
record or a new record and you click the "next" button, Access
also generates an error. To avoid this behavior, you can make these custom
navigation buttons self-disabling; that is, they'll disable and enable
themselves as necessary. To make a "previous" button disable itself, in Design view, open the form's Property Sheet and select [Event Procedure] for the form's On Current property, then click the Build button. At the insertion point, type the following code: btnPrevious.Enabled = CBool(Me.CurrentRecord - 1) The CBool function converts all numbers except 0 to -1, or True. So, btnPrevious remains enabled until the CurrentRecord value equals 1. To create the self-disabling Next button, press [Enter] to add a new line to the above procedure, then type btnNext.Enabled = Not Me.NewRecord As you probably know, the NewRecord property returns True when the current record is a new record, so VBA enables btnNext whenever the current record is NOT a new record. If you don't allow additions in your form, however, you won't have new records, so instead you'll want to test whether the current record value is less than the total number of form records. You can set the button's Enabled property equal to this test, like so btnNext.Enabled = (Me.CurrentRecord < Me.RecordsetClone.RecordCount) Under this expression, whenever the CurrentRecord value is less than the total record count, the evaluation returns True, enabling the button. Otherwise, the evaluation returns False, utomatically disabling btnNext. |
| Adding multiple controls of the same type | In Form View, you choose a control from the Toolbox to add a control of the selected type to a form. If you want to add more than one control of the same type, double-click the appropriate button on the Toolbox. Then, you can add as many controls to your form as you like without re-clicking the Toolbox. When you finish, simply click the Toolbox to reset it. |
| Alternate large and small logos on a report | Sometimes a report can be spruced up by using a large company logo on
the first page (in the report header), and then a smaller version of the
logo on succeeding pages (in the page header). You may wonder, though, how
to prevent the smaller logo from printing along with the larger one on the
first page. The trick is to suppress the appearance of the smaller logo
when the Report Header prints. While Access gives you the option to
suppress the entire Page Header section when the Report Header prints,
there may be column headings or other tags in the Page Header you want to
display on the report's first page. To suppress just the small logo, use
the Page Header's On Print event and a simple module-level variable. First, in the report's Design view, set the small logo's Visible property (named Logo2 here) to No. Next, click the Code button on the toolbar to open the report's Module window. In the General Declarations section, declare the following module-level variable Dim Flag As Boolean Now, select ReportHeader from the Module window's Object dropdown list. By default, Access inserts the Sub and End Sub statements for the Format event. At the insertion point type Flag = False Next, select PageHeader from the Object list and enter Logo2.Visible = Flag Flag = True Close the Module window and save the report, then click the Preview button. The appropriate logo will appear on the first and succeeding pages. |
| Avoid the Access bug when you use the Combobox Wizard | You may not be aware, but if you use an Access 97 version earlier than
SR-2, the Combobox Wizard can generate code that will cause severe data
corruption if you have more than 250 records in the form's underlying
table. When you use the wizard to create a combobox that searches for
existing records in a form, the wizard generates code for the combobox
similar to this: Sub Combo0_AfterUpdate() ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[TIPID] = " & Me![Combo0] Me.Bookmark = Me.RecordsetClone.Bookmark End Sub To ensure that your data remains pristine, you should place Me.Requery before the Me.RecordsetClone.FindFirst "[TIPID] = " & Me![Combo0] To do so, open the form with the combobox on it in Design view, then click the Code button on the Toolbar. When you do, Access displays the form's Module window. If you don't see the combobox's AfterUpdate event procedure, find the combobox name in the Object dropdown box in the Module window's upper-left corner, then, if necessary select AfterUpdate from the Procedure dropdown box to the left. Insert the Me.Requery statement and save the form. The completed code should look like the following: Sub Combo0_AfterUpdate() ' Find the record that matches the control. Me.Requery Me.RecordsetClone.FindFirst "[TIPID] = " & Me![Combo0] Me.Bookmark = Me.RecordsetClone.Bookmark End Sub |
| Coloring a command button | You can't change the color of a command button, but you can make it look like you have by following these steps: 1. Add a command button to your form. 2. Set the button's Transparent property to Yes. 3. Create a rectangle the same size as your button, and use the Fill/Back Color tool (or the Palette tool in version 2.0) to choose a color. 4. Set the rectangle's Special Effect property to Raised. 5. Drag the command button over the rectangle. Having a rectangle under the command button won't affect its performance. However, setting its Transparent property to Yes inhibits any caption you might add. You can work around this though by adding a label control to the top of the two-tiered button. |
| Creating a table of error codes and their descriptions | Access has a ready-to-use function in the Help section that will create
a table of error codes and their descriptions. If you're using version
2.0, open the Help menu, choose Search..., enter error codes, and click
Display. In the resulting window, click Determining Used Error Codes at
the top of the page. Then, highlight just the code portion of the help
information and click the Copy button. Return to your database, open a
blank module, and press [Ctrl]V to paste the code into your module.
Replace the Sub keyword in the function's title statement with the
Function keyword and then click the Compile Loaded Modules button. To run
the function, open the Immediate window and type ? CreateErrorsTable() The procedure will take a moment to run and prompt you when it's done. Just click OK to create the table Errors Table. If you're running Access 95, the process is basically the same, except you'll find the function in the Help section under the Visual Basic topic. Within this topic, you'll need to find the subheading for error handling and click Display. Next, double-click Determining the Error Codes Reserved by Visual Basic. At this point, highlight just the code and continue as discussed above until you're ready to run the procedure. Then, press [Ctrl]G to open the Debug window (rather than the Immediate window). If you're running Access 97, you'll find the function, which is called AccessAndJetErrorsTable(), in the Help section under error codes. Once you locate the function, simply cut and paste it to a module and then run it via the Debug window. |
| Determining if the current record is a new record | Access 95 introduced a form property, New Record, which returns a True
or False value. If the active record is new (meaning unsaved), New Record returns True; otherwise, it returns False. This property makes it easy to alert your users that they're working with a new and unsaved record. To use the property, just attach the following procedure to your form: Private Sub Form_Current () If Me.NewRecord Then MsgBox "This is a _ new record." End Sub |
| Displaying a form | When your form is in Design View, you can get a quick glimpse of your work by pressing [F5]. Doing so displays your form in Form View without your having to return to the Database window. |
| Efficient parameter queries | If you want to prompt users for more than one query parameter, you might think you need more than one criteria expression, but you don't. For instance, if you need two parameters--a beginning and ending date--you don't have to enter two criteria expressions in the form >=[Enter Beginning Date] <=[Enter Ending Date] Instead, you can combine both in the same expression using the form Between [Enter Beginning Date] And [Enter Ending Date |
| Filling a combo box with a dynamic list of objects | It's easy to offer your users a list of combo box items that updates
itself automatically. For instance, suppose you want to fill a combo box
with a list of all the reports in the active database and you want Access
to update the list if you add reports to or delete reports from the
database. To do so, you'd use the following code: Private sub cboReport_GotFocus() Dim db AS Database, cnt As Container, doc As Document Dim strTemp As String, strList As String Set db = CurrentDB Set cnt = db.Containers!Reports For Each doc in cnt.Documents strTemp = doc.Name strList = strList & strTemp & ";" Next doc Me!cboReport.RowSource = strList Set db = Nothing Set cnt = Nothing Set doc = Nothing In addition, be sure to set the combo box's Row Source Type property to Value List. If you want to fill the control with a list of forms instead of reports, replace the Set cnt = db.Containers!Reports statement with the statement Set cnt = db.Containers!Forms |
| Grouping months in a Totals query | If you've ever tried to group date values in a Totals query, you
probably used something similar to the Format() function to group by month
and year, or perhaps even just the Month() function. Both of these pose
problems, however. The Format() function returns a String value, not a
date. This makes it difficult to sort the results in any meaningful way
without resorting to odd date formats, like 199808 or something of the
sort. Of course, the Month() function returns a numeric value, but what
happens if your data extends over a period of years? Using this function
places all records of the same month into the same group regardless of the
year. A better alternative for grouping dates by month is to use the
DateSerial() function. This function follows the syntax DateSerial(Year, Month, Day) and returns a Date value. To group by month, simply change the Day argument to a 1. For example, the expression in your query field might look similar to this one MyMonthGroup:DateSerial(Year([TableDateField]), Month([TableDateField]),1). Using this expression, 8/3/98 and 8/14/98 would both be grouped as 8/1/98. |
| Indexes aren't what they're cracked up to be | Although you can use indexes to sort data, we don't recommend that you rely on this technique because indexes don't always work as you expect. Access uses an index to locate data more efficiently using its internal logic and rules. Therefore, Access may ignore your index. Furthermore, an index that's contrary to the internal rules Access follows can actually slow down your application. |
| Inhibiting the system error message | You can inhibit system error messages by setting the system's error
state to 0. You do so by adding the statement Response = acDataErrContinue to event procedures that pass a Response integer. (The version 2.0 constant is DATA_ERRCONTINUE.) |
| InputBox() function as an Access 97 query parameters | If you used our previous tip on the InputBox function as an Access 97
query parameter, Access probably informed you that the function isn't
available in expressions. According to the Microsoft Knowledge Base, the
error occurs in both Service Release 1 and Service Release 2. To correct
the problem, wrap the InputBox() function inside the Eval() function. Use
two sets of quotation marks or one set of apostrophes around each of the
InputBox() function's text arguments. So, instead of the expression InputBox("Enter a Date","Parameter",#1/1/95#) use Eval("InputBox(""Enter a Date"","" Parameter"",#1/1/95#)") or Eval("InputBox('Enter a Date',' Parameter ',#1/1/95#)") |
| Let DateDiff() determine if two dates are in the same month | If you need to determine whether two dates are in the same month, your
first instinct may be to simply use the Month() function with each date,
then compare the results. However, under these circumstances, the comparison would equate 1/1/2000 with 1/1/1999. Instead, the DateDiff() function provides a better way to make this determination, like so: DateDiff("m", Date1, Date2) In this expression, the DateDiff() function finds the difference between the two dates in calendar months. If the expression returns zero, then the two dates are in the same calendar month. To include this feature in a conditional expression, you can include it in a query, as in SameMonth: IIF(DateDiff("m", Date1, Date2), "No", "Yes") In this expression, Access evaluates any non-zero value returned by the DateDiff() function as -1, or True (returning 'No'). It evaluates zero as False (returning 'Yes'). |
| Navigate between main and subforms with the keyboard | Subforms can create many challenges, one of which is navigating between the subform and back to the main form during data entry. While the focus moves into the subform as part of regular tabbing, once you're there, tabbing or pressing the [Enter] key only cycles you through the subform's controls and records. Of course you can always resort to the mouse and click on the next control in the main form. When you?re entering data, however, it's very inefficient to have to move your hand off the keyboard to the mouse. To avoid this, you can take advantage of the [Ctrl][Tab] key combination, which will move the focus out of the subform back to the main form. When you do, the main form control with the next highest tab index receives the focus. You can even take advantage of this key combination to switch between two subforms on the same main form. If they're in sequential tab order, then pressing [Ctrl][Tab] would transfer focus from one to the other. |
| Pause code or macro execution when you open a linked form | Often, you may want to launch a second form from another one, enter new
data, close the second form, then execute additional commands based on the
information that you just entered. For example, say you have one form that
displays departmental information and shows the number of employees in
that department. You also have a button that opens an employee detail form
that allows you to add employees to the department. However, after you
enter new employee records and close the employee form, you want Access to
update the count in the department listing. To do so, you might attach the
following code (or a macro) to a button's Click event: DoCmd.OpenForm "frmEmployeeDetail" Me.Requery As you probably know, the Requery method forces Access to generate a new batch of data for a form. To duplicate this code in a macro, you'd use the OpenForm action followed by the Requery action. As is, however, the above example won't display the correct employee count after you close frmEmployeeDetail. That's because Access doesn't automatically pause code (or macro) execution to wait for events to occur in the second form. So, in the above example, Access opens frmEmployeeDetail then immediately requeries the first form before you have a chance to enter new employee records. Fortunately, you can make a Sub procedure or macro pause execution until an opened form closes. To do so, set the OpenForm method's windowmode argument equal to acDialog. (or Dialog in macros), as in DoCmd.OpenForm "frmEmployeeDetail", , , , acDialog Me.Requery Now, Access opens frmEmployeeDetail as a dialog box and waits for you to close it before requerying the department form. |
| Prevent new additions to a subform | Many times when you create a main form and subform, you want to prevent
Access from adding new data into the subform until you've entered key
data, like a Primary Key, into the main form. Say for example, you have a
main form with Customer information and a subform with Order information.
You don't want new Order data entered until there's an associated Customer
record. Of course, when you enforce referential integrity between the
underlying tables, Access prevents you from entering orphan records.
However, it only prevents you AFTER you've typed in the data. This can be
very frustrating. It's better practice to prevent additions to the subform
entirely, until you've entered the key value into the main form. To
accomplish this, you can take advantage of a form's AllowAdditions
property. Simply toggle this property on and off depending on the null
state of the key field. Using our customer/order example, in the main
form's Current event procedure, we'd enter the following code statement [OrderSubForm].AllowAdditions = Not IsNull([CustomerID]) When the CustomerID field is null, this statement evaluates as follows [OrderSubForm].AllowAdditions = Not IsNull(Null) [OrderSubForm].AllowAdditions = Not True [OrderSubForm].AllowAdditions = False As a result, the subform prevents new additions. Note, that you'll also want to place the above code statement in the control's BeforeUpdate event. That way, when you delete key data in an existing record, the subform prevents additions. |
| Printing relationships | There are two ways to print a graphical representation of your
database's relationships. First, if you're using Access 97, you can
download the Print Relationships Wizard-a free add-in from Microsoft. To
download this add-in, visit www.microsoft.com/accessdev/articles/printwiz/default.htm and follow the instructions given-be sure to note where your system stores the file (our system stored the file in the Windows\System directory). Once you've downloaded the add-in, locate the installation file named Prels80.exe and double-click it to start the installation process. After installing the wizard, launch Access 97, pull down the Tools menu, select Add-Ins, and choose Print Relationships from the resulting submenu. The wizard will create a report that shows a graphical representation of the relationships that exist between the tables in the current database. The second method will work with version 2.0 and Access 95. It requires a few more steps than the wizard: * In 2.0, pull down the Edit menu and choose Relationships...; in 95, select Tools/Relationships. * Select the appropriate tables, queries, or both in the Add Table dialog box, clicking Add after each selection, and then close the dialog box (you use the Show Table dialog box in 95) * Press [Alt][PrintScreen] * Open WordPad * Press [Ctrl]V |
| Proper case with the StrConv() function | When it comes time to print a report, you can't always be sure the text
in your database will be formatted correctly. Input errors are to be
expected, but even so, a customer letter that starts with "Dear Mr.
lARRY jOHANSEN" can be a problem. Fortunately, Access provides a way
to accomodate such potential embarrassments with its string conversion
function, called StrConv(). With it, you can reformat text three different
ways: all uppercase, all lowercase, and proper case--the first letter of
every word is capitalized. This function takes the following form: StrConv(string, conversion) The string argument can be a literal string, such as "lARRY jOHANSEN", or more likely, the field that contains the questionable text, [FullName], for example. Conversion is one of three VBA constants, vbUpperCase, vbLowerCase, vbProperCase. To use this function on a report or form, place it in the Control Source of a text box. =StrConv([FullName],vbProperCase) would display "Larry Johansen". |
| Querying for random records | To query your tables for random records, first base a Select query on
your table and include in the QBE grid the fields you want. Then, add an
expression in the form RandomEx: Rnd([valuefield]) where valuefield is any field in the query that contains values-that field can be an AutoNumber, Number, or Date/Time field. Next, apply a sort order to the RandomEx field-it doesn't matter which. When you run the query, the RandomEx field will return random values for each record. Access will then sort the records based on those values. While the values will be sorted, the records themselves will appear to be random. To return only a percentage of random records, you can set the query's Top Values property to the appropriate percentage. |
| Quickly copying data | When copying table data from Access to Excel, select the entire Access
table by clicking the gray square at the top-left corner of the table and
press [Ctrl]C. Then, switch to Excel, select the top-left cell of the
range you want to copy the data to, pull down the Edit menu, and choose
the Paste Special... command. (Don't press [Ctrl]V to paste the data, or
Excel will truncate the text.) To copy Excel data to Access, highlight the range you want to copy and press[Ctrl]C. Then, switch to Access, open a blank table, select the entire table by clicking the gray square at the top-left corner of the table, press [Ctrl]V, and click Yes. If you want to append records to an existing table, open that table, select it, pull down the Edit menu, choose Paste Append, and click Yes. |
| Quickly displaying a list of constants | To display a list of constants, simply open any module (including a blank one) in Design View and press [Ctrl]J. Access will respond by displaying a scrollable list of constants. You can also press [F2] to display the Object Browser and then choose the appropriate library. |
| Quickly identify invisible controls | No doubt at one point during your form and report creation, you've made one or more controls invisible. You do this to hide information you don't want another user to see. Unique record ID numbers are an example of such information. To make a control invisible, you set its Visible property to No. Then, when you open the form or report in View mode, the control's no longer visible. To save form and report real estate, you probably sized these invisible controls so they took up as little room as possible. When you make them smaller, however, it sometimes becomes difficult to tell they're there. Also, if you've got a lot of regular controls on your form, it may be hard to differentiate between the visible and non-visible ones. To alleviate this problem, change the invisible control's ForeColor property to red, or some other distinctive color. That way, when you switch to the form's or report's Design view, you can easily spot the invisible controls. |
| Referencing subforms | Access treats subforms as controls. So, when referencing them, the
proper syntax is Forms![formname]![controlname] When referencing a control on a subform, you use the Form identifier in the form Forms![formname]![controlname].Form![subformcontrolname] |
| Resize lines on a report with ease | Unless you have a rock-steady hand, manually resizing a line on an Access report can be a real pain. If you move the mouse pointer up or down even a small amount as you drag the sizing handle, you can wind up with an angled line instead of a straight one. One way to alleviate this difficulty is to group the line with a label of similar size, then resize the label to the desired width. Because you've grouped the line and the label together, Access resizes the line as you drag the label's sizing handle. If you don't want the label to be as wide as the line, you can easily return the label to its original width. To group two controls together in Design view, simply hold down the [Shift] key as you click on each control. |
| Returning the last date in a group | You can use a Totals query to group your records and then set the group aggregate to First or Last to determine the first or last record in the group. Of course, the First or Last aggregate field must be a Date field. |
| Setting your forms' default appearance by creating a new template | Once you've designed an attractive form, you can specify that look as
your form default and save yourself a lot of design time. To do so, you
simply change the template that Access uses when you create a form without
relying on a form wizard. First, pull down the Options... menu and click the Forms/Reports tab (or choose the View menu in version 2.0). By default, the Forms Template text box contains the reference to the Normal template. Simply replace this with the name of your form, and then click OK. If you're using version 2.0, highlight Form Report Design in the Category list box. Then, in the Items box, select the word Normal in the Form Template text box and type the name of the new form. Finally, click OK. |
| Size To Fit Form option | The Size To Fit Form option on the Window menu automatically adjusts the
size of an open form to fit the current record. Depending on the form's
Default View (Single or Continuous) this option behaves differently. For Single Forms, Access shrinks or expands the window accordingly to display the entire record. For Continuous forms, if only part of the bottom record is showing, Access shrinks the form to encompass the previous record. If only part of one record is displayed, then the form lengthens to show as much of that record as possible. In all cases, the form widens or narrows to accommodate the record's width. |
| Spinning through selections in a ListBox | By coordinating a SpinButton (also called UpDown) with a ListBox, you
can provide a great user-friendly feature that lets you click the
SpinButton's up and down arrows to move the ListBox selection up and down
the list. Even better, you can provide this feature with a single
expression. The tricky part, though, is coordinating the movement of the
ListBox's selected item. For example, say we have a ListBox that displays
the seven days of the week. We've already set the SpinButton's Min
property to 0 and the Max property to 6. To coordinate two controls, it
may seem that you can simply use the SpinButton's current value as the
index in the ListBox's selected property. For instance, in the
SpinButton's Change event, we might use the following: ListBox1.Selected(SpinButton1.Value) = True Now, if the SpinButton value is 5, then VBA highlights the 6th item in the list. (Remember, ListBox items are zero-based). However, a SpinButton's value property increments as you press the up arrow, whereas in a ListBox, the Item index number increases as you move DOWN the list. So, using the above code, when you click the up arrow, the selection moves down the list, and when you click the down arrow, the selection moves up. To alter this confusing behavior, set the SpinButton's Min property to -6 and the Max property to 0. By default, VBA sets the SpinButton's value property to 0, which is exactly what we want. Next, place the following expression in the SpinButton's Change event: ListBox1.Selected(Abs(SpinButton1.Value)) = True Now, when you run the form and click the down arrow, the value becomes -1, which the Abs function converts to 1, highlighting the ListBox's second item. |
| Testing for new records | Often you may need to verify that edits made to existing data should
actually take place. For example, if you have a Company Name field in your
database you could generate a message box in the BeforeUpdate event asking
if the user really wanted to change the existing name. The code for this
prompt might look something like: Private Sub CompanyName_BeforeUpdate(Cancel As Integer) Dim Response as Integer Response = MsgBox("Do you really want to change the Company Name?") If Response = vbNo Then Me!CompanyName.Undo Cancel = True End If End Sub However, as is, the prompt would appear even when you enter a new record. To avoid this, you can use the form's NewRecord property to test for new records. The code below illustrates how to do so: Private Sub CompanyName_BeforeUpdate(Cancel As Integer) Dim Response as Integer If Not Me.NewRecord Then Response = MsgBox("Do you really want to change the Company Name?") If Response = vbNo Then Me!CompanyName.Undo Cancel = True End If End If End Sub |
| To quote or not to quote | When you create a Where expression for the OpenForm, OpenReport, or any
of the Domain functions (DLookup, DSum, DMax, DMin, etc) you may have
wondered when and when not to enclose the criteria value in quotes. For
instance, in an OpenReport action in a macro, you might use [SSN] = 111111111 or [SSN]= "111111111" Which version you use depends on the value in the SSN field. If the field contains String values, then enclose the search criteria value in quotes. For numbers, the quotes aren't necessary. These same rules apply when you use a criteria value from a control-you must add quotes to the Where clause if the control contains a string, such as DLookup("[Bubba]", "tblBubbas", "[FirstName] = '" & [Forms]![MyForm]![FirstName] & "'") |
| Two easy ways to switch a control's Enabled property | You can enable or disable most controls by modifying the control's
Enabled property. You can take advantage of that property's Boolean value
settings by using a statement in the form control.Enabled = Abs(control.Enabled) - 1 where control is the name of the object you're modifying. This statement simply switches the property between the two Boolean values, -1 and 0; -1 is enabled, and 0 is disabled. You can also use the Not operator to switch a control's Enabled property. This technique uses True and False values instead of the Boolean values -1 and 0. The setup is basically the same, but the code is a little different. The Not technique uses a statement in the form control.Enabled = Not control.Enabled If the Enabled property is True, this statement changes it to False and vice versa. |
| Using Format() with dates | You can use the Format() function to display different date components.
For instance, to return a date's day, month, or year, you'd use the functions Format([datefield],"d") Format([datefield],"m") Format([datefield],"y") respectively. To display the month, you can use the form Format([datefield],"mmm") to return the abbreviated month or the function Format([datefield],"mmmm") to return the month's full name. You can also combine the formats to create unique formats, as in Format([datefield],"dmmmyy") which would display the day, abbreviated month, and a two-digit year value, with no spaces in between each component. |
| Using ListWidth property | A combo box's ListWidth property determines how wide the dropdown list box portion will be. While the list can be set wider than the actual combo box, it can't be narrower. The default setting, Auto, extends the width to match the combo box itself. To make the list box wider, enter a number in inches or centemeters into the ListWidth property field. Similarly, the List Rows property determines the maximum number of rows to display in the dropdown list box. This value can be any integer between 1 and 255. |
| Using pop-up forms | Pop-up forms are great for displaying additional information about the current record or for requesting more information from the user. There are two kinds of pop-up forms: modeless and modal. A modeless pop-up form, which you use to display additional information, allows users to access other objects and menu commands while it's open. A modal pop-up form, which you use to request additional information, forces a user to attend to its task before he or she can continue to work. To set up a modeless pop-up form, first restrict data entry by setting the Allow Edits, Allow Deletions, Allow Additions, and Data Entry properties. (Version 2.0 users should set the Default Editing property to Read Only.) Then, set the Pop Up property to Yes and the Modal property to No. A modal pop-up form requires some data entry-so you should set the Allow Additions and Data Entry properties to Yes. Set the remaining data entry properties (listed above) to No. (Version 2.0 users should set the Default Editing property to Data Entry.) In addition, set the Pop Up and Modal properties to Yes. Regardless of the mode, setting the Border Style property to Dialog will enhance the form's display. Doing so also inhibits the Maximize and Minimize buttons. |
| View an input mask before you enter data | Sometimes, you may want to see a control's input mask before you begin
to enter data in it. To accommodate this need, place the following line in
the control's Enter event procedure: SendKeys ("x" & "{bs}") This code will enter an x in the control and then immediately backspace over it, leaving the input mask visible. |
| Viewing Access reports without Access | There are three main ways to let others without Access view Access
reports. 1. You can export the report to Word. To do so, select the report, then choose the Publish to MS Word option from the Office Links dropdown menu. Doing so, however, changes the report to Rich Text Format, which strips out any graphics or lines that you?ve used to format the page. 2. Save the report as HTML and then view it with a web browser. From the Database window, select the report and choose File/Save As HTML from the menu bar. The Publish to the Web Wizard will lead you through the steps necessary to create the web page. After you've finished, launch your web browser. Netscape users should select File/Open Page from the menu bar, then click the Choose File button. Internet Explorer users choose File/Open, then click the Browse button. You can then search for the saved HTML report on your computer. This method also removes lines and graphics from the layout, and creates a separate file for each report page. 3. Save the report in Snapshot Format, which maintains the graphical layout and shows multiple pages. To do so, select the report in the Database Window, then choose Save/Export from the File menu. Access displays the Save As dialog box. Click OK to save To An External File Or Database. When the next Save As box appears, change the Save As Type from Microsoft Access to Snapshot Format, select a location to save the report, then click the Export button. To view the file, you'll need the Snapshot Viewer, which you can download for free from www.microsoft.com/accessdev/prodinfo/snapshot.htm |
| Working with the Apply Filter event | By using the Apply Filter event, you can instruct VBA to display custom
messages during a filtering task. Access executes this event when you
click the Filter by Selection button or choose the Apply Filter/Sort
option from the Records menu. (Check the Help section for a more complete
list.) You'll probably use the Apply Filter event to check or change the
current filter, but you can also use it to restrict the filter to certain
fields. The following code will display the current filter and allow you
to continue or cancel: Private Sub From_ApplyFilter(Cancel As _ Integer, ApplyType As Integer Dim strMessage As String, intResponse As _ Integer If ApplyType = acApplyFilter Then strMessage = "The current search criteria _ is:" strMessage = strMessage & vbCrLf & _ Me.Filter intResponse = MsgBox(strMessage, _ vbOKCancel + vbQuestion) If intResponse = vbCancel Then Cancel = _ True End If End Sub |
| A caveat when using ASP's Response.Redirect method | When ASP encounters the Response or End methods, it immediately halts script execution in both the GLOBAL.ASA file and in the application page. As a result, ASP won't execute any script that follows the Redirect method. Instead, it either ends the session or begins processing the new page. For this reason, you should always make the Redirect method the last call in any script. |
| Avoid boolean comparison errors in ASP | Often in VBScript, you'll want to determine if a numeric value is
greater than zero then perform some action based on the results. For
example, you'll often want to know if a recordset contains records. To do
so, you can simply use the results in a boolean expression, like so If rst.RecordCount Then 'Do something End If This works because VBScript, like VB and VBA, considers any non-zero number (negative or positive) a True boolean value. Be warned, however, that bugs can occur when you try to use Not with this same expression, as in If Not rst.RecordCount Then You might think this test would determine if the recordset didn't contain records. It does when the RecordCount property returns -1 ((Not -1) = 0). However, if the property returns the actual number of records, then this condition statement returns True. That's because when you use Not on a number, VBScript returns the opposite value of that number minus one. For instance, the expression Not 15 returns -16, which as you know VBScript interprets as True. With regard to the record count test, if the property returns -1, then there's no problem, since the result is (1-1) or 0. However, when the property contains an actual value, then Not rst.RecordCount evaluates to a non-zero number, which again VBScript evaluates as True. To get around this minor glitch, you can use one of the following statments: If Not Cbool(rst.RecordCount) Then or If Not (rst.RecordCount<>0) Then or simply If rst.RecordCount = 0 Then |
| Create Visual InterDev 6.0 ASP code templates | Instead of retyping the same code lines on virtually every ASP page that
you create in Visual InterDev 6.0, simply open Program Files | Microsoft
Visual Studio | VIntDev98 | Templates | Web Project Items | New ASP
Page.asp file, and then enter the code that you want duplicated, i.e.
included files, titles, metatags. Save your work, and any new ASP page
that you create will contain the new code. Submitted by: Will Smith [will@newtweb.com] |
| Determine browser capabilities in an ASP page | When you use the Browser Capabilities component in an ASP page, you can
determine client browser attributes: such as if the calling browser
supports frames, VBScript, or javascript. You can also determine the
browser's version and type, such as Internet Explorer 5.0, or Netscape
Navigator 4.6. For example, to determine if a browser supports frames, you
could use code similar to: <% Dim objBrowsCap Dim blnAllowsFrames Set objBrowsCap = Server.CreateObject ("MSWC.BrowserType") blnAllowFrames = objBrowsCap.frames If blnAllowFrames Then 'Code for frames goes here Else 'Code for non-frames goes here End If Set objBrowsCap = Nothing %> Unfortunately, this component relies upon the server's browscap.ini file to determine the latest information on client browser. The Browser Capabilities component simply reads the INI file and returns the values contained therein. If the browscap.ini file is out of date and doesn't have information on the requesting browser, the component uses the INI file's default values, which may or may not contain the correct capablilities. To avoid having to constantly update the INI file yourself, you can check out Cyscape, Inc.'s Web site at http://www.cyscape.com/browscap. They offer a continually updated INI file. In addition, they've developed a utility called BrowserHawk that, among other features, automatically detects new browsers and updates the appropriate files. |
| Eliminate default page renaming | Have you ever experience the annoying problem where Copy Web copies the Web to the appropriate server but renames the default.asp page to default.htm? The problem is really caused by a bug in the Microsoft FrontPage extensions. Any Web application that contains a Default.asp in the root will rename the file when copied using the "Copy Web Application" command to default.htm in the destination Web application. To eliminate the problem, simply add a page named default.htm to the existing Web site. |
| Ensure accurate string comparisons in ASP pages | As you probably know, the StrComp() function provides a quick way to
compare two strings. It takes the following syntax: StrComp( string1, string2, comparemode) VBScript compares the two strings according to either the comparemode argument, or the default setting on the server. This function returns 1 if the first string is greater than the second string, 0 if the two are equal, and -1 if the first string is less than the second. However, if you want to compare strings taken from user input, to ensure that the results are accurate, remove any extra spaces from the two strings. For example, you could use something like: StrComp( Trim(str1), Trim(str2)) Strings with extra spaces will provide inaccurate results. To illustrate, create the following ASP page, and then view it in Internet Explorer. <html> <head> <script language="VBScript"> sub mySort() Dim str1, str2, ary, sCompare str1 = document.all.txt1.value str2 = document.all.txt2.value Select Case StrComp(trim(str1), trim(str2)) Case 1: sCompare = "greater than" Case 0: sCompare = "equal to" Case -1: sCompare = "less than" End Select MsgBox str1 & " is " & sCompare & " " & str2 end sub </script> <body> <form> <input type="text" id="txt1" /><br /><br /> <input type="text" id="txt2" /><br /><br /> <input type="button" value="Compare" onclick="mySort" /> </form> </body> </html> First, enter an A; into the first field and a Z; into the second, then click the Compare button. When you do, VBScript informs you that A is less than Z, as you would expect. Now, enter a space before the Z and click the button again. This time the message box inaccurately claims that A is greater than Z. |
| Ensure proper remote scripting parameter types in ASP | As you probably know, remote scripting is a great way to access
server-side functions and procedures from client-side scripting. However,
when you use parameters in a server-side function intended for client-side
use, remember that IIS converts all parameter values into strings. As a
result, if necessary, you'll want to convert these values back into their proper data type within the server-side function. Also, keep in mind that for security reasons, IIS doesn't let you pass structured data (objects or arrays) as remote scripting parameters. |
| Generate accurate ADO RecordCount values in ASP | As you know, the ADO RecordCount property returns the number of records
in an ADO recordset. Of course, in several instances, this property also
returns a -1 instead. The value RecordCount returns depends on the
recordset's cursor type: -1 for a forward- only cursor; the actual count
for a static or keyset cursor; and either -1 or the actual count for a
dynamic cursor, depending on the data source. You may be surprised to learn that RecordCount will be -1 for recordsets created with the Execute method from a Connection or Command object. That's because this method generates a forward-only recordset, which, as we mentioned earlier, returns -1. As an example, enter and run the following procedure in a standard ASP page. When you open it, the page displays -1 for the recordset based on myConRst, and 6246 for myKeyRst. <% Dim myConn, myComm, myConRst, myKeyRst Dim sConnection sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:\Microsoft Visual Studio\VB98\Biblio.mdb" Set myConn = Server.CreateObject("ADODB.Connection") Set myKeyRst = Server.CreateObject("ADODB.Recordset") myConn.Open sConnection myComm = "Select * From Authors" Set myConRst = myConn.Execute(myComm, , 1) myKeyRst.Open myComm, myConn, 1 %> RecCount <BR/> From Connection: <%=myConRst.RecordCount %><BR/> From Recordset: <%=myKeyRst.RecordCount%> <% Set myKeyRst = Nothing Set myConRst = Nothing Set myConn = Nothing %> |
| Globally replace text using just 5 lines of VBScript | If you've ever tried to implement a text search and replace function in an ASP page, you probably resorted to lengthy parsing loops that used Instr() and an entire host of string functions. If so, you'll be glad to know that there's a much easier way. With the advent of VBScript 5.0, (IIS 5.0) Microsoft introduced the Regular Expression engine. If you've used Perl or javascript, you may be familiar with these pattern-matching powerhouses. In a nutshell, regular expressions let you define a pattern, literal or representative, which you can then match against a second string. For example, the literal pattern 'abc' as a regular expression would find a match in 'dabcef', 'abcdef', or 'defabc'. To further refine a regular expression, the Regular Expression engine offers a host of special metacharacters, similar to wildcard characters. For example, using these metacharacters, you could search for any word that began with a letter and ended in a digit. To view these metacharacters and their uses, visit http://msdn.microsoft.com/scripting/default.htm then search in the VBScript documentation for the RegExp object's Pattern property. (If you don't have IIS 5.0, all you really need is the latest version of VBScript, which you can download from www.microsoft.com/msdownload/vbscript/scripting.asp) To create a simple find and replace subroutine with client-side VBScript, first create a RegExp object, like so <script language="VBScript"> Sub GoReplace() Set myReg = new RegExp Next, set the object's properties. myReg.IgnoreCase = True myReg.Global = True myReg.Pattern = "abc" Here, we've used a literal string to create a global, case- insensitive pattern. Finally, you execute the object's Replace method on a target string, as in document.all.myTextArea.value = myReg.Replace(document.all.myTextArea, "def") End Sub </script> That's *all* there is to it! This example would replace "abc" with "def" wherever it occurred within myTextArea. To execute the replace on the first matching string only, set the Global property to False. |
| How to Increase the Speed of Large Uploads | If you anticipate large uploads, increase Internet Information Server's
(IIS) UploadReadAhead value to enable faster transfers. UploadReadAhead is
an IIS parameter that contains the amount of data that the server will
read before passing control to the application. The application is then
responsible for reading the rest of the data. The default UploadReadAhead
is 48kb. With a higher UploadReadAhead, transfers will be faster, but
uploading will utilize more physical memory. If you increase the
UploadReadAhead value, increase the amount of RAM on the server as well. To increase the UploadReadAhead value, 1. Go to Start\Run 2. Enter regedt32 to open the registry 3. Open, HKEY_LOCAL_MACHINE\ SYSTEM\ CurrentControlSet\ Services\ W3SVC\ Parameters\ UploadReadAhead 4. Highlight the UploadReadAhead value (in the right frame) 5. Open Edit and select DWORD 6. Enter a new value in the Data window If the UploadReadAhead parameter does not exist in your registry, you can add it. To add the parameter, 1. Open the Edit menu 2. Select Add Value 3. Enter "UploadReadAhead" in Value Name 4. Enter REG_DWORD in Data Type 5. Click Ok Enter the value in kb |
| Include VBScript variables in ASP SQL statements | Often, you'll no doubt want to include a VBScript variable in an ASP SQL
statement. For example, suppose you want to let a user search a table for
names that begin with a letter they specify. To use a variable in a SQL
statement, simply concatenate the various parts of the statement together.
To see how this works, create an ASP page with the following code: <HTML> <BODY> <FORM Name="test" Action="SQLVars.asp" Method="POST" > <INPUT Name="txtSearch" TYPE="TEXT" Size=15 VALUE="SearchForMe"> <INPUT Type="SUBMIT" VALUE="Submit"> </FORM> <% Dim strSQL1 Dim strSQL2 Dim strSearch strSearch = Request.Form("txtSearch") If Len(strSearch) Then strSQL1 = "Select From Customer(MyField) Where " _ & "Name(Col.Name) Like 'strSearch%'" strSQL2 = "Select From Customer(MyField) Where " _ & "Name(Col.Name) Like '" & strSearch & "%'" End If %> <UL> <LI>Before concatenation: <%=strSQL1%> <LI>After concatenation: <%=strSQL2%> </UL> </BODY> </HTML> When you run the page, only strSQL2 contains the variable's actual value, which can then be sent on to SQL Server, or any other database that processes SQL statements. Also, notice the use of the single quote before the double quote and after the percent sign. This ensures that the database interprets the variable's value as a string. |
| Let the Scripting type library verify a file's existence | The FileSystemObject offers a great way to manipulate a server's
directory structure. With the FileExists method, you can determine if a
file exists at runtime. This method conforms to the following syntax: object.FileExists(filespec) where object is a FileSystemObject variable and filespec is the file's path (absolute or relative). If you expect the file to be in the same folder as the ASP page running the script, then you only need to specify the filename. This method follows the same syntax in both VBScript and javascript. So, suppose you wanted to use this handy method to search for a file in an images folder. To do so, use code similar to blnExists = fso.FileExists(Server.MapPath("/images/Some.gif") Here, the code searches the server's root directory for the Some.gif file. It returns True if the file exists, and False if not. In addition to the FileExists method, the FileSystemObject also exposes FolderExists and DriveExists methods, both of which follow the same syntax. |
| Obtain a header list with ASP's ServerVariables collection | The SeverVariables collection contains all kinds of information about
the calling client and the server. For instance, you can determine a
visitor's browser type with the HTTP_USER_AGENT variable. Or, you can use
HTTP_REFERER to determine the referring Internet address. Not all browsers
support the same headers, however. To see a quick list of the headers
supported within a browser, run the following code. <HTML> <BODY> <TABLE BORDER=1> <TH COLSPAN=2>ServerVariables</TH> <% For Each var in Request.ServerVariables With Response .Write("<TR>") .Write("<TD><B>" & var & "</B>:</TD>") .Write("<TD>" & Request.ServerVariables(var) _ & "</TD>") .Write("</TR>") End With Next %> </TABLE> </BODY> </HTML> |
| Parsing multiple selected options in server-side ASP | In a previous tip, we showed you how to gather multiple selections from
an HTML Select element on the client side. However, when you obtain these
values after a Get or Post request, you need to use a different technique.
That's because these multiple values get passed as a comma-delimited
string. So, say you had a Select element named "Lst1" on a form.
If you selected three options with values 5, 6, and 7, and submitted the
form, then Request.Form("Lst1") would contain the string "5, 6, 7". As a result, you'll need to parse this string to get the individual values. To do so, you have two options. First, you can use the Split() function to place the values into an array. Or, if you don't have a version of IIS that supports the Split() function, you can use the Instr() function and search for the comma character within the string. As an example, create an HTML page named GetOptions.html and add the following form to it: <form id="frm1" action="GetOptions.asp" method="post"> <select size=10 multiple="multiple" name="Lst1" id="Lst1"> <option value="1">Sunday</option> <option value="2">Monday</option> <option value="3">Tuesday</option> <option value="4">Wednesday</option> <option value="5">Thursday</option> <option value="6">Friday</option> <option value="7">Saturday</option> </select> <br /><br /><input type="submit"> </form> Next, create an ASP page named GetOptions.asp and add the following code: <% ' Parse request string using Split() Dim sSelected, arySelected, x sSelected = Request.Form("Lst1") Response.Write("You selected: " & sSelected & "<br />") Response.Write("<br /><b>Split()</b><br />") arySelected = Split(sSelected, ",") For x = 0 to Ubound(arySelected) Response.Write("Choice " & x & ": " & arySelected(x) & "<br />") Next Response.Write("<br /><b>Instr()</b><br />") ' Parse request string using Instr() Dim subString x = 0 sSelected = sSelected & "," Do Until Instr(sSelected,",") = 0 Response.Write("Choice " & x & ": " & Left(sSelected, _ instr(sSelected,",") -1) & "<br />") sSelected = Mid(sSelected, instr(sSelected,",") + 1) x=x+1 Loop %> Note, that the same techniques would work with Response.QueryString("Lst1") as well. |
| Prevent spam before it starts with our Jscript | If you're like us, then you're getting really, *really* tired of all the spam mail piling up in your in box. While we can't tell you how to get rid of it entirely, we can give you a tip that will help cut down on spam. As you may know, most spam list generators work by browsing the Web and gleaning email addresses from Web sites. Because of an email address' distinct format, it's pretty easy to find one in a page. Well, you'll be happy to know there's a technique that lets your email addresses show up in a page, but still defeats the spam list spiders. The trick is to use a javascript function, like the one that follows, to generate the email addresses: <script language="javascript"> <!-- Hide from old browsers function ShowAddress(part1, part2) { var addr = part1 + "@" + part2; var result = ("<a href='" + "mail" + "to:" + addr + "'>" + addr + "</a>") return result; } //--> </script> To make an email address appear in the page, call the function like so: <script language="javascript"> <!-- document.write(ShowAddress("wdv","zdjournals.com")); //--> </script> Now, won't those spam list spiders be baffled! Submitted by: Vik Nokhoudian, World Wide Web Associates [vik@wwwa.com] |
| Register WSC scripts before using them in ASP | The Windows Scripting Component is a great tool that let's you create
COM components for use in ASP pages. What's more, you can use either
VBScript or javascript to give them functionality. When you create a WSC
component, however, you must register it before you can use it in an ASP
page. To do so, open Windows Explorer and right-click on the WSC file you
just created. Select Register from the shortcut menu. To create an object
variable based on the new script object, use code similar to Set objXMLCreate = Server.CreateObject("MyScriptObj.WSC") At this point, you're free to access any of the object's methods and properties. |
| Setting checkbox values from an Access database | Often you'll want to display a checkbox value stored in an Access
database in an ASP page. Depending on the setting in your Access table,
Access may display Yes/No, On/Off, or True/False in the field when you
view the table in the database. As a result, you may think to use ASP
similar to <input type="checkbox" name="chk1" value="Yes" <% if rs("checkfield")="yes" then %> checked="checked" <% end if %> /> However, this doesn't actually work. That's because even though Access displays the field contents as a Yes or No, it stores the contents as True or False, (-1 and 0 respectively). To get an accurate checkbox setting in ASP, use <input type="checkbox" name="chk1" value="Yes" <% if rs("checkfield")=True then %> checked="checked" <% end if %> /> |
| Simplify loading stored |
When loading an <OPTIONS> list from a database table, it's much
easier to call a procedure that uses process parameters. This way, you can
use the same procedure for several different operations. For example,
suppose you want to fill a Listbox with a list of animals. In addition,
you've set up several checkboxes on a Web page that let the user simply
view the list, or load the list with one of the animals already selected.
The following example shows how you might process the resulting page. (To
save space, we hard-coded the two parameters.) <!-- #include File="DBConnect.Asp" --> <HTML> <BODY> <FORM> <SELECT name=Atype size= 5> <% Dim chkMode chkMode = 2 'or Request.Form("chkMode") fillOptionList chkMode,"Snake" %> </SELECT> <% Sub fillOptionList(myMode, optionValue) Dim rsAnimal Dim strSelected set rsAnimal = CreateObject("ADODB.recordset") With rsAnimal .Open "tblAnimals", strDB, adOpenStatic,,adCmdTable Do While Not .EOF Select Case myMode Case 1 strSelected = "" Case Else If optionValue = rsAnimal("Animal") Then strSelected = " SELECTED" Else strSelected = "" End if End Select Response.Write("<OPTION" & strSelected & " VALUE=" _ & rsAnimal("ID") & ">" & rsAnimal("Animal") & "</OPTION>") .MoveNext Loop .Close End With Set rsAnimal = Nothing End Sub %> </BODY> </HTML> For the OPTION elements, this code produces output HTML like this: <SELECT name=Atype size= 5> <OPTION Value=1>Cat</OPTION> <OPTION Value=2>Cow</OPTION> <OPTION Value=3>Dog</OPTION> <OPTION SELECTED Value=4>Snake</OPTION> <OPTION Value=5>Elephant</OPTION> <OPTION Value=6>Fish</OPTION> </SELECT> Submitted by: Faisal Ladak [fladak@systech-inc.net] |
| Use a single ADO connection to reduce ASP server load | If you need to execute multiple SQL statements on a single database in a
single ASP page, it's often wise to open a single Connection and reuse it
multiple times. Doing so reduces the number of times the server must open
and close the same database, which can significantly increase the server's
load. To use a single connection, create the database Connection at the
beginning of the page, then destroy it at the end. When you Execute a SQL
statement, whether it returns a Recordset or not, specify the active
Connection. Here's an example. <% strDSN = "DSN=database;UID=username;PWD=password" Set conn = Server.CreateObject("ADODB.Connection") conn.Open strDSN strSQL = "select column from table" Set rsResults=conn.Execute(strSQL, , 1) if not rsResults.eof then temp = rsResults("column") else temp = "No Results" end if 'close the Recordset, but leave the Connection open rsResults.Close strSQL = "delete from table where column=123" conn.Execute strSQL, , 1 'Done executing SQL statements at this point, so close the Connection. conn.Close set rsResults = Nothing set conn = Nothing %> Submitted by: Brian Coverstone [brian@pcioh.com] |
| Use ADO's native OLEDB drivers instead of ODBC | When you create an connection string, ADO gives you a choice between
indicating a data source driver as either a Driver, as in Driver={SQL Server};DBQ=database_file or a Provider, such as Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database_name However, when you use the first option, ADO uses older ODBC drivers to connect to the data source; whereas the second form uses OLEDB, which is ADO's native data access interface. For this reason, you should use the Provider option whenever possible. Such native OLEDB drivers exist for SQL Server, Index Server, Site Server Search, and Oracle, among others. |
| Use VBScript's RegExp object to validate email address syntax | Nowadays, if your Web application requires a user to enter specific company information, you probably have a field for an email address. No doubt, you'll want to ensure that the address not only contains the @ and dot, but that the remaining characters contain only letters, numerals, or underscores (and perhaps a dash or period). At first, this may seem like a daunting task. And if you use standard VBScript's string functions alone, it will be. Fortunately, the RegExp object provides an easier way. The following code validates an email address in a textbox named Text1: <head> <script language="VBScript"> Sub checkEmail(sEmail) Dim myReg Set myReg = New RegExp myReg.IgnoreCase = True myReg.Pattern = "^[\w-\.]+@\w+\.\w+$" msgbox myReg.Test(sEmail) End Sub </script> </head> <body> <form> <input type="text" id="txtEmail" name="txtEmail"></input> <input type="button" onclick="checkEmail(document.forms(0).txtEmail.value)" value="Verify"></input> </form> </body> Here, the pattern accepts any number of numeric, underscore, letters, periods, or dash characters before the @ character and only numerals, underscores, or letters before and after the dot. |
| Why relative filespecs may not work in ASP | Even though the helpfile states you can use relative directory paths
with FileSystemObject objects, you probably encountered an error if you
tried to use them in an ASP path. That's because the directory that
contains the ASP page doesn't become the current directory, as far as the
FileSystemObject is concerned. To illustrate, run the following VBScript
code in an ASP page: <% Set FileObject = Server.CreateObject("Scripting.FileSystemObject") filespec="\test.jpg" CurrDir = FileObject.GetAbsolutePathName(".") Response.Write "Using CurrDir: " & CurrDir & filespec & "<br>" if FileObject.FileExists(filespec) then Response.Write "[CurrDir] File Found<br>" else Response.Write "[CurrDir] No File found<br>" end if Response.Write "Using MapPath: " & Server.MapPath(filespec) & "<BR>" if FileObject.FileExists(Server.MapPath(filespec)) then Response.Write "[MapPath] File Found<br>" else Response.Write "[MapPath] No File found<br>" end if Set FileObject = Nothing %> |
| DESIGNING A LIST OR DATABASE | When making a list you might be first inclined to just make it in Word
(preferably in a table) - unfortunately that doesn't give you the sorting
and list manipulation flexibility you need. At the other end of the
list/database spectrum is Access - a great database program but
excessively complex and time-consuming for a relatively simple list. In the middle is Excel - you might think Excel is just used for calculations but one of its most useful features is as a powerful list creator. Excel gives you options not available in Word like simple filtering, yet you can copy the list results into your final Word document or report. Generally, worksheets fall into two categories - forms and lists. A form is a worksheet designed to contain information about a particular item, such as a purchase order form for a single order, expense report for a specific month, an invoice statement for one client, or a simple take home pay calculation. A list is a worksheet designed to contain information about many items. Examples include: an employee roster, a client database, a price list, a list of all orders received during the year, or even a list of companies and the total amount each company has in outstanding invoices. |
| Determine if a worksheet exists in an Excel workbook | In VBA, to determine if an Excel workbook contains a specific worksheet,
add a new Module to the workbook, then enter the following Public
Function: Public Function SheetExists(strSearchFor As String) As Boolean SheetExists = False For Each sht In ThisWorkbook.Worksheets If sht.Name = strSearchFor Then SheetExists = True End If Next sht End Function To see how this function works, open the Debug window and enter MsgBox SheetExists("Sheet1") and press [Enter]. If your workbook contains a worksheet named Sheet1, then Excel displays True in the message box. Otherwise, it displays False. Replace "Sheet1" with any other worksheet name to test for its existence. |
| Adding a subject to your mailto links | When you create an email link, people can easily send you an email
message from a Web page. To make the process even easier, you can pre-fill
the message's subject field. To do so, open the Create E-mail Hyperlink
dialog box. Type your email address as you ordinarily would, but add the
following text to the end of the address: ?Subject=Web Site Feedback (substituting your desired subject for Web Site Feedback). Now, when a user clicks your mailto link, the resulting email will include your pre-filled subject. You can also pre-fill other fields in the message by substituting Body, CC, or BCC for Subject. However, you can only pre-fill one field per message. To pre-fill multiple fields, you'll need to use a form instead. |
| Deleting FrontPage 98's temp files | Have you ever run out of hard disk space and wondered what else you can
delete off your hard drive? Or perhaps you just want your system to work
as efficiently as possible. Unlike most other Windows programs, FrontPage 98 stores its temporary files (cache) in its own "temp" subdirectory, typically located at "C:\Program Files\Microsoft FrontPage\temp." Unfortunately, there's no provision in FrontPage 98 for cleaning this temporary directory out, so it just continues to build up. If you've been working with FrontPage for a while, this directory could be relatively large! To clean out this temp subdirectory, simply use Windows Explorer to locate the FrontPage temp subdirectory; then select and delete all its contents (don't delete the temp subdirectory itself). And, in case you're wondering, Microsoft corrected this problem in FrontPage 2000, which has a built-in provision for deleting the contents of this temp subdirectory cache. Submitted by: Chris Whitehead [whitehead_christopher@colstate.edu] |
| Determining download speeds in FrontPage | How fast will your FrontPage-generated pages load? To get a rough estimate,look at the lower-right corner of the FrontPage Editor (or FrontPage 2000)window. A time (in seconds) will be displayed there, indicating the estimated download time. In FrontPage 98, the speed is based on a 28.8Kbps connection. In FrontPage 2000, clicking on the time display brings up a context menu that lets you change the speed that the calculation is based on -- from 14.4Kbps to a T3 connection. |
| Free online FrontPage tutorials from Microsoft | Are you looking for a basic introduction to FrontPage concepts but don't
want to spend a lot of money? Then, surf on over to Microsoft's K-12
Education Web site at http://www.microsoft.com/education/k12/classroom/tutorial.htm There, you'll find some excellent--and free--FrontPage 98 and FrontPage 2000 tutorials. Although they're geared toward K-12 classes, they're useful to the rest of us as well. Submitted by: Kevin J. Judge [cst1kjj@njcmail.ups.com] |
| Making FrontPage hover buttons with Cascading Style Sheets | If you don't want to use FrontPage's javascript-based hover buttons,
here's a way to create them using only Cascading Style Sheets (CSS). Add the following HTML code between the <head> and </head> tags on your page: a:link { background: #9c9; color: #633; font-family: arial, helvetica, sans-serif; text-decoration: none; font-weight: bold; font-size: smaller;} a:active { background: #633; color: white; font-family: arial, helvetica, sans-serif; text-decoration: none; font-weight: bold; font-size: smaller;} a:visited { background: #9c9; color: #633; font-family: arial, helvetica, sans-serif; text-decoration: none; font-weight: bold; font-size: smaller;} a:hover ( background: #fc6; color: #633; font-family: arial, helvetica, sans-serif; text-decoration: none; font-weight: bold; font-size: smaller;} You can make your buttons look even better by adding a nonbreaking space-- --before and after the link text: <a href="whatever.htm"> :Click Here </a> The code above will give you green buttons that turn gold while the mouse cursor is hovering over them. Of course, you can change the fonts, colors, and the class name to suit your taste. Older browsers just display a normal link. Some CSS browsers display the button but the color doesn't change. The best part is that there's never a script error, no matter what browser your reader is using, and no matter how they've got their options set. Submitted by Ken Collins [KenCollins@KenCollins.com, www.KenCollins.com] |
| Navigating to pages outside of the current web in FrontPage | While FrontPage 98's Navigation Bar functionality is a great help in
keeping your site organized, it's limited to working with pages within the
subweb you're currently developing. Wouldn't it be great to be able to
create a navigation block to jump someplace else--to a different subweb
within your intranet or even to an outside site? Well, here is an easy way to create that functionality. For each external link you want, create a page with the following HTML: <html> <head> <meta http-equiv="refresh" content="0; url=MYLINK"> <title>XXX</title> <meta name="Microsoft Theme" content="none"> <meta name="Microsoft Border" content="none"> </head> <body> <p>Preparing to jump to XXX page...</p> <p>If this page remains on your screen for very long, please click <a href="MYLINK">here...</a></p> </body> </html> Substitute an appropriate title for "XXX", and the URL for your destination for MYLINK. The "refresh" meta tag causes an immediate jump to the desired destination, while the extra hyperlink in the body allows the user to go directly to that page if their browser doesn't properly support refresh. The Microsoft Theme and Border meta tags, set to none, prevent FrontPage from putting extraneous borders and formatting on the jump page, keeping the overhead low. Note that FrontPage 2000 offers external navigation directly--just right-click on a page in Navigation view and choose External Link from the context menu to create a link to any page outside the subweb. Submitted by: Woodrow Windischman [Woody.Windischman@us.millwardbrown.com] |
| Printing your FrontPage task list | For some reason, FrontPage doesn't let you print out the task list
associated with a Web. However, you can print the list from your browser
-- you just have to know where the list is stored. So, where is the task list? It's in two HTML documents that FrontPage generates automatically and stores within your Web. The two files are _x_todo.htm, which is the active task list, and _x_todoh.htm, which contains the task history (completed tasks). You'll find the files in the _vti_pvt directory within your Web. Open those files from Internet Explorer to print them out. |
| Protect your FrontPage shared borders | On sites that use shared borders and have multiple authors,
inexperienced users can accidentally change a shared border. This can
create significant grief for the webmaster who has to reconstruct the
shared border. A simple way to protect your shared borders is to create the border, then copy it to another file in the _borders directory (e.g. top_include.htm). Next, open the original border file (e.g., top.htm) and delete all its contents. Add an Include Page component to the page, specifying your renamed file (top_include.htm) as the page to be included. With this configuration, it's harder to edit a border, but if a shared border gets overwritten, the include file will remain intact and the border can be salvaged much easier. Submitted by: Ken Signorello [ken@harborwatch.com] http://www.harborwatch.com |
| Quickly linking to bookmarked pages in FrontPage | Have you ever tried to create a link from one FrontPage document to a
bookmarked spot on another page? Doing so is easy--in the Bookmark text
box in the Create Hyperlink dialog box, just type the bookmark's name. To improve on the technique, leave the linked page open in FrontPage Editor. When you do so and then select that page in the Create Hyperlink dialog box, any bookmarks on the linked page will show up on the Bookmark dropdown menu. No more having to re-open a page to find out exactly how to spell the bookmark. Submitted by: Jesse P. Luna [jluna@compdist.com] |
| Reversing the order of your guest book entries | By default, FrontPage lists entries in your guest book in chronological
order. So if you have dozens of entries, people have to scroll all the way
to the bottom of the list to see the newest entries. To reverse the order,
open your guest book page in FrontPage Editor (or Page view in FrontPage
2000) and right-click on the guest book form. Choose Form Properties from
the pop-up menu to access the Form Properties dialog box and then click
the Options button to access the Options For Saving Results Of Form dialog
box. Uncheck the Latest Results At End check box and click OK twice. From now on, the comments will be recorded in reverse chronological order. Note: If you're using FrontPage 98, you must also install the FrontPage 98b patch to change the display order. You can download the patch, which also fixes other minor problems, from: http://officeupdate.microsoft.com/downloadDetails/fp98bupd.htm |
| Saving your changes in FrontPage | When you click the Publish button in FrontPage, the program compares the
files in the local copy of your Web with those on the Web server. If a
newer version of a file exists locally, FrontPage uploads it to the
server. Sometimes, however, FrontPage seems to not upload pages that are open in the editor. The reason is not that they're open, but that you haven't saved any changes you've made. Before publishing, then, you should always save your changes by choosing Save All from FrontPage Editor's File menu. If you're using FrontPage 2000, however, you may have noticed that the Save All command is missing, which means you have to manually save all the pages that are open in the editor-a potentially tedious process. Fortunately, thanks to the integration of Visual Basic for Applications (VBA) with FrontPage 2000, you can easily restore the Save All command. For complete details, visit this page on the Microsoft Web site: http://officeupdate.microsoft.com/2000/articles/fpvba.htm |
| Sounding off on hover buttons | FrontPage's hover buttons are easy to create and attractive, as many
FrontPage users have discovered. Many users have also discovered, however,
that they have trouble attaching sound files to hover buttons. The reason for this problem is that you can use 8-bit, 8000 Hz, mono, u-law audio (.au) files only, not .wav, .ra, or other files with hover buttons. This Microsoft Knowledge Base article offers more information on using sound files with hover buttons: http://support.microsoft.com/support/kb/articles/q175/1/75.asp |
| Squashing a "bug" in FrontPage's Scheduled Image component | The Scheduled Image component in FrontPage (called Scheduled Picture in
FrontPage 2000) lets you add an image to a page--but only have it appear
during a specified period of time. For example, by using the component to
add a "new" image next to new items on your links page, you
don't have to remember to go back and remove that image after a couple of
months. Many FrontPage users have avoided the Scheduled Image component, however, because it doesn't seem to work. Even after an image's expiration date has passed, the image continues to appear on the page. The reason for this apparent bug is that FrontPage only updates your scheduled images when you republish your Web--it happens during the "Processing Web Updates" phase of the publishing process. If you plan to use the Scheduled Image component, then, you should make it a habit to hit the Publish button on a regular basis. (Note: The same thing applies to the Scheduled Include Page component.) |
| To publish or not to publish | If you have a fast Internet connection, you may be accustomed to editing
your Webs live on the fly. Doing so gives you immediate gratification --
your changes appear on the Internet as soon as you hit the Save button. But so will your mistakes. That's why FrontPage's publish feature may be the better way to go. With that feature, you create your Web locally and then click Publish to upload it to the Internet -- as long as your Web server has the FrontPage server extensions installed. If you want to create a local version of a live Web, open the live version and use the publish command to "publish" the Web to your local hard drive. Submitted by: Rob Hutchinson, MCSE [website@home.com] |
| Using hover buttons on a frames page | Would you like to change the contents of one frame by clicking a hover
button in a second frame? You can do so by taking advantage of a hidden
parameter for the hover button applet. Once you've created your hover button, switch to HTML view and add the following parameter to the list of parameters between the <applet> and </applet> tags: <param name="target" value=" FRAME NAME GOES HERE "> Submitted by: Aaron Johnson [webmaster@johnsonwebdesigns.com] |
| Using scripts in FrontPage include pages | The Include Page component in FrontPage lets you set up commonly used
content (e.g., a footer or a sidebar) just once and then display it on any
number of pages in your Web. Unfortunately, any scripts in your included
page will tend to generate javascript errors in the browser, even though
the scripts worked perfectly when the page was displayed on its own. The reason is that Front Page only includes the portion of the page between the <body> and </body> tags, ignoring everything between the <head> and </head> tags. So, if you put your scripts in the head section of the page--as the gurus tell you to--they'll be ignored, and a script error will result. Fortunately, the solution is simple: Just move your scripts into the body section of the page. Submitted by: Ivor Jones [ivor.jones@phnt.swest.nhs.uk] |
| Handling multiple Select options with javascript | While the HTML Select form control allows multiple selections, you may
have wondered how to manipulate those selections, for inclusion in a
database, for example. ASP offers several ways to do so. Of course,
client-side script and the DOM give you one way to do so. When you use
this technique, the basic idea consists of looping through the control's
options, check each option's Selected property, and where appropriate,
enter the value and text directly into a database or an array, as seen
below: <head> <script language="javascript"> function getSelected() { var sConnect = "Provider=Microsoft.Jet.OLEDB.3.51;Data " + "Source=D:\\Inetpub\\wwwroot\\ZDJindex\\index.mdb" var adoRst = new ActiveXObject("ADODB.Recordset") adoRst.open("tblDays", sConnect, 2, 2) //2-dynamic keyset, 2-table command var objSelect = document.forms["frm1"].elements["sel1"] var msg = new String("") //Insert selected items for (x=0; x<objSelect.length; x++) { if (objSelect.options[x].selected) { adoRst.AddNew() adoRst.Fields("Day") = objSelect.options[x].text adoRst.Fields("Value") = objSelect.options[x].value adoRst.Update() msg += objSelect.options[x].text + ", " } } adoRst.close() //Strip the trailing comma msg = msg.substring(0, msg.length-2) //Show selected items alert(msg) } </script> <body> <form id="frm1"> <select size=10 multiple="multiple" id="sel1"> <option value="1">Sunday</option> <option value="2">Monday</option> <option value="3">Tuesday</option> <option value="4">Wednesday</option> <option value="5">Thursday</option> <option value="6">Friday</option> <option value="7">Saturday</option> </select> <input type="button" value="Show Selections" onclick="getSelected()" /> </form> </body> While this script works just fine on the client-side in Internet Explorer, don't try the same technique when you post a form to server-side ASP. That's because the multiple selections get passed as a comma-delimited string. In an upcoming tip, we'll show you two different ways parse this string on the server side. |
| Quickly determine client browser types in javascript code | The document object model and ASP pages offer several ways to determine
a client's browser type. Most, however involve long parsing routines that
search for specific substrings within a larger string. Fortunately, using
javascript, there's a quick way to determine if the client browser is
either Netscape Navigator 4+ or Internet Explorer 4+. The basic idea is
that you test for objects in the browser's DOM that don't exist in the
other browser's DOM. For example, IE 4.0 + browsers include the
document.all collection, while NN4.0 + browsers support the
document.layers collection. If one of these objects is null, then your
code can infer that the client is the other browser type. For example, the
following script displays the boolean results of each test when you load a
Web page: <script language="javascript"> var nav4 = !(document.layers == null) var iex4 = !(document.all == null) alert("Nav: " + nav4 + " IE4: " + iex4) </script> As you can see, the first expression sets the nav4 variable equal to the opposite of document.layers==null. So, if you view this page in an IE 4+ browser, document.layers is null, and the entire expression evaluates to false. |
| Drawing perfect shapes in Excel | If you're more of an analyst than an artist, adding shapes to your Excel worksheets can be a source of frustration. Your circles look like eggs, you agonize over creating squares with equal dimensions, and you can't draw a straight line to save your life. Fortunately, Excel includes help for the artistically challenged. Simply select the drawing tool you want to use, press the [Shift] key, and click and drag to create your shape (if your drawing tools aren't visible, select View/Toolbars/Drawing). Using the Oval tool creates a circle, the Rectangle tool creates a square, and the Line and Arrow tools create perfect horizontal and vertical lines, as well as lines in 15 degree increments from their starting points. Holding the [Shift] key even helps create proportional AutoShapes. |
| Even-handed column distribution in Word | When you create a multicolumn document using Word's Columns feature,
Word formats the text as a continuous section that wraps from the bottom
of one page column to the top of the next, much like a newspaper.
Unfortunately, when Word creates columns, it distributes the text one
column at a time, filling the length of one column before beginning the
next. This usually results in uneven columns; unless you have enough text
to fill an entire page, the last column will end up being shorter than the
others. You could remedy this situation by inserting a column break in a
previous column, but this method isn't very precise if you're looking to
achieve exact distribution. To distribute columns evenly across a page, insert a continuous section break. To do so, first format the columns as desired. (Please note that you must be in Page Layout in order to properly view and edit columns.) Next, place the insertion point at the end of the text in the last column. Select Insert/Break from the menu bar to open the Break dialog box. In the Section Break Types panel, select the Continuous option. When you've finished, click OK. Word then redistributes the columns so that they're of equal length. |
| Quickly change the color scheme for multiple Power Point slides | To quickly change the color scheme for several slides without changing all of them, switch to the Slide Sorter View, hold down the [Shift] key, and click on each slide that you wish to modify. Next, choose Format/Slide Color Scheme from the main menu and make the appropriate changes. When you're satisfied with the new scheme, click Apply to assign the changes to the selected slides. |
| Recall sent Outlook messages | Have you ever wished you could take back something you said? Well here's
some good news: Outlook allows you to recall an email message that you
sent to another Outlook user! This feature works only if the message
you're trying to recall hasn't yet been opened by the receiver. To recall
a sent message, open the Sent Items folder, then open the message that you want to recall. Next, select Actions/Recall This Message from the menu bar. From the Recall This Message dialog box, you can choose to simply delete unread copies of the message, or you can replace unread copies of the message with a brand new message. You can also request to be notified whether the message recall was successful. Once you've selected the options you want, click OK, and Outlook attempts to recall the message you selected. |
| Find Fast 97 can be a drag on overall system performance | If you've installed Office 97 on your system, you may notice your hard
disk burst into a frenzy of activity at regular intervals and perform
slowly. The activity is generated by the Find Fast utility. This feature
creates and manages an index that Office applications such as Word and
Excel use to launch full-text searches from the File Open dialog box. By
default, Find Fast is configured to run every two hours, which is usually
too often for most casual Office users. To set a less frequent schedule, first open Control Panel and double-click the Find Fast icon. Then, pull down the Index menu and select the Update Interval... command. Now, enter a more reasonable time span, such as every 24 hours. |
| Display two time zones in your Outlook calendar | Do you work with people in another time zone and want to keep track of what time it is there? Add a second time zone to your Outlook calendar. To do this, right-click above the time bar that displays the hours in your calendar and choose Change Time Zone. (Or choose Tools/Options from the menu bar, click the Calendar Options button, and click on the Time Zone button.) In the Time Zone dialog box, select the Show An Additional Time Zone check box and select a time zone from the Time Zone dropdown list. In the Label text box, type the name of the area to remind you which time zone you're looking at. Then click OK until you're back to the calendar. Now you'll see the second time zone next to the current time zone. |
| Sending a quick email message | If you're using Microsoft Outlook, you can send a quick email message by
using the mailto command. Begin by choosing Start | Run. Next, type
mailto: in the Open text box and click OK. You'll see a new message dialog
box. Click Send when you're ready to send the message. Note: Although
Outlook doesn't have to be running for you to create and send a new
message using mailto, your message won't be sent until you open Outlook. This tip was contributed by Patrice Bonnefoy, Patrice.Bonnefoy@edfgdf.fr |
| Choosing a sort order | If you're just now installing SQL Server, you can make choices about how
you install your server. For example, you choose your server's sort order
when you install SQL Server. The sort order you choose has an impact on
your server's performance. You can choose from the following sort orders:
binary, dictionary order case-sensitive, dictionary order
case-insensitive, dictionary order case-insensitive uppercase preference,
and dictionary order case-insensitive accent-insensitive. If you want to get the highest possible performance out of your server, you should choose the binary sort order. The dictionary order case-sensitive, dictionary order case-insensitive, and dictionary order case-insensitive uppercase preference sort orders are 20 percent slower. The dictionary order case-insensitive accent-insensitive sort order is 35 percent slower. |
| Creating scripts for database objects | Here's a quick and easy technique you can use to generate scripts for
tables, views, procedures, rules, defaults, and user-defined data types in
SQL Server 7.0. Open SQL Server Enterprise Manager. In the right pane of
SQL Server Enterprise Manager, right-click on the object for which you
want to create a script, and choose Copy. Then, open a text editor like
Word or Notepad and choose Paste. There's your script! Submitted by: Thomas Peterson [Tom.Peterson@Rainier.com] |
| Diagramming your SQL databases | SQL Server 7.0 includes the ability to create a graphical diagram of
your database. You can use this diagram to view the structure of all
tables in the database, as well as links between the tables in the
database. SQL Server displays the primary key-foreign key link between
tables. To create a database diagram, open SQL Server Enterprise Manager. Expand the database on which you want to create the diagram. Right-click on the Diagrams object, then choose New Database Diagram. You can then select some or all of the database's tables to include in the diagram. You can save the diagram so that you can retrieve it at a later date. In addition, you can print the diagram to document the structure of your database. |
| Publishing SQL Server data on a Web page | One of the easiest ways you can generate a Web page based on your SQL
data is to use the Web Assistant Wizard. This wizard enables you to choose
the database from which you want to publish and the table you want to
include on the page--and the columns from this table. You can also
schedule a job to create the Web page: you can configure SQL Server to
generate the page immediately, on demand, at a specific point in time,
when the data changes, or at regularly scheduled intervals. If you choose to have SQL Server update the Web page when your database's data changes, you can even choose which columns you want SQL Server to monitor for changes. The Web Assistant Wizard will also let you add some formatting to the Web page, as well as links to other sites. Once you've generated your Web page, you can go to a more sophisticated tool for formatting the page such as Microsoft FrontPage 98. |
| Recovering from a crashed master database in SQL Server 7.0 | If you lose your master database, you must first rebuild it by using the
new "rebuildm" utility in SQL Server 7.0 before you restore your
backup. To start the utility, use Windows NT Explorer to access the
\MSSQL7\BINN folder, then double-click on rebuildm. Next, specify a path
to your SQL Server CD-ROM by clicking on the Browse button. Then click on
Settings to specify your server's character set, sort order, and unicode
collation configuration. Finally, click on Rebuild to rebuild your master
database. Once you've rebuilt the master database, you can restore your
backup of the master database by starting your server in single-user mode
(type sqlservr -m at a Command Prompt). Next, start SQL Server Query
Analyzer and run the following query: RESTORE DATABASE master FROM DISK = 'name_of_your_backup_device' WITH RECOVERY This will restore your backup of the master database and automatically shutdown your server. You should now be able to restart your server by using SQL Service Manager or the Services icon in Control Panel. (Note: When you rebuild the master database, SQL Server automatically rebuilds the msdb database. You should also restore your backup of the msdb database after rebuilding the master database.) |
| Server Status | Q. When I launch Enterprise Manager for SQL Server 7.0 and expand the
group of registered servers, a window is supposed to show the status of
the server (such as started or stopped) without connecting to the server
itself. Unfortunately, I haven't found a way to implement this
functionality. I'm using the registered servers collection and the SQL
Server collections that SQL Distributed Management Objects (SQL-DMO)
provides. The registered server class doesn't have a status property, and
the Status property of the SQL Server COM class appears only after I
connect to the server. How can I determine the status of the SQL Server
machine without connecting or using SQL-DMO? And why is there a delay
between the time Enterprise Manager displays the registered servers and
the time that it displays the servers' status? Are multiple threads at
work here? A. You must use the Win32 services APIs if you want to detect the status of SQL Server services without connecting to SQL Server. To answer your second question: Yes, more than one activity is going on here. SQL-DMO calls the Win32 API to check on the service status. You can find the API in the Win32 software development kit (SDK), or you can search the Microsoft Developer Network (MSDN) site. The delay is caused by SQL Server calling the Windows API and waiting for the API to respond. |
| Sharing server registration information in SQL Server Enterprise Manager | When you register servers in SQL Server Enterprise Manager, SQL Server
stores this information in the Registry. One problem you might run into,
though, is that if you log on to Windows NT as a different user, or you
move from computer to computer, you'll have to register your SQL servers
in SQL Server Enterprise Manager for each user (or when you move to a
different computer). SQL Server 7.0 enables you to share your registration
information on the server so that you can access it from anywhere. To create shared registration information, begin by configuring your server to not store the registration information for each user. Open SQL Server Enterprise Manager, then choose Tools, Options. In the Properties dialog box, uncheck the Store User Independent option, then click OK to save your change. When you close the Properties dialog box, you'll have to re-register your servers to create the shared registration information on your server. You can then access this shared registration information from any instance of SQL Server Enterprise Manager by choosing Tools | Options. In the Properties dialog box, select Read From Remote. In the Server Name text box, type the name of the SQL server on which you created the shared server registration information, then click OK. You should now be able to see all of the servers you've registered in SQL Server Enterprise Manager. |
| Let DateDiff() determine if two dates are in the same month | To determine if two dates are in the same month, your first instinct may be to simply use the Month() function on each date, then compare the two resulting numbers. However, under these circumstances, the comparison would equate 1/1/2000 with 1/1/1999. Instead, the DateDiff() function provides one quick way to make this determination, like so: DateDiff("m", Date1, Date2) In this expression, the DateDiff() function finds the difference in calendar months between the two dates. If the expression returns a zero, then the two dates are in the same calendar month. To include this feature in an conditional expression, you could use the following in a query: SameMonth: IIF(DateDiff("m", Date1, Date2), "No", "Yes") In this expression, Access evaluates any value <> 0 returned by the DateDiff() function as -1, or True (returning 'No'), and 0 as False (returning 'Yes'). |
| Calling a script function without user input | The most common way for a script function to be called is by having an
element trap a event, which in turn calls the function, as in: <DIV id = "myelement" onclick = "myfunction()" > Click here </DIV> But what if you want a function to perform some task as soon as the page loads, without any user input? In that case, you can use the onload event, placing it in the BODY element of your page, as in: <BODY onload = "myfunction()" > The function will be called immediately after the page loads, and no user input is required. |
| Dynamically loading different files into frames | You can load any number of different files into a frame at runtime using
the frame's location object. As you would when building any project using
frames, first build a file that defines your frame. For our example, we've
defined a frame called myframe. The code below would then go in a separate
file. In the BODY of our code, we've placed a list of files from which the user can choose. When a user clicks on the file name he wants to load, the name of the file is passed as a parameter to the function. The function appends the ".htm" extension to the name, and assigns the complete name to the location object of the frame myframe, which loads the appropriate file into that frame. <HTML> <HEAD> <TITLE> History topics </TITLE> <SCRIPT> function loadfile(whatfile) { parent.myframe.location = whatfile + ".htm"; } </SCRIPT> </HEAD> <BODY> <DIV onclick = "loadfile('file1')" > Load file 1</DIV> <DIV onclick = "loadfile('file2')" > Load file 2</DIV> <DIV onclick = "loadfile('file3')" > Load file 3</DIV> </BODY> </HTML> |
| Manually registering ActiveX controls | If you're having trouble placing an ActiveX control on your page, you
may need to explicitly register it in the Windows Registry. Along with the
unique CLSID for the control, the Registry stores information such as the
version of the control, where it's located, etc. You can manually register an ActiveX control from the DOS command prompt. On the command line, type REGSVR32 <path>\<activexfile.ocx> where <path> is the path to the file containing the ActiveX control and <activexfile.ocx> is the name of the file. If the control successfully registers, you'll receive a pop-up window stating that the registration succeeded. If you're not a DOS fan, you can also register the control from the Start menu's Run option in Windows 95/98/NT. One word of caution: be careful not to modify any of the entries in your Windows Registry. Tinkering with the Registry can have disastrous results! Submitted by: Bill Pitzer Contributing Editor, Microsoft Web Builder |
| Separate your scripts from your HTML code | You can easily use one script for a variety of different Web pages by
placing your javascript code in a separate file. Create the file in a text
editor, and name it using a .js extension. Then, in your HTML code, assign
that file to the src attribute of a <SCRIPT> tag. For example, if
you've got your script in a file called "validate.js", place a
<SCRIPT> tag in your Web page document like this: <SCRIPT language="javascript" src="validate.js"></SCRIPT> Of course, you can have more than one <SCRIPT> tag within a Web page document. If you place generic functions within a .js file that you assign as a src to one <SCRIPT> tag, you can have a second <SCRIPT> block in your page that contains function calls to the functions within the .js file. This way, you can have several different Web pages calling the generic functions in the .js file, but passing the functions data that is specific to each page. |
| Use javascript's floor() method to do integral division | Although javascript does not have a mathematical operator that results
in the integer part of a division operation (called integral division, or
Div in some languages), you can use the Math object's floor() method to
create that result, as long as the values you are working with are
positive. The Math object's floor() method returns the next integer less
than or equal to the value it is passed. For example: var result = Math.floor(5/3) //result = 1 The result is the equivalent of having the decimal part (or remainder) removed after the division operation has taken place, which is exactly what an integral division operation would do. |
| Use onSubmit to confirm that your user is ready to submit his form data | Before a user submits a form to the server, use the onSubmit event
handler to ask him if he's really ready to submit the data that he's
entered. Here's how: Suppose your button for submitting the form looks something like this: <INPUT type = "submit" value = "Send Form"> In the <FORM> tag on the page, add the onSubmit event handler, asking it to call a function that will prompt the user, and ask him if he wants to continue with the submission. The <FORM> tag would look like this: <FORM onSubmit = "return sendForm()"> If the onSubmit handler returns true (that is, if the sendForm() function returns a true) submission continues; if it returns false, submission stops. Next, write the sendForm() function, which uses the window object's confirm method to interact with the user: function sendForm() { return confirm("Do you want to continue the submission?") } The confirm method displays a dialog box with the message that's been placed in the parentheses, and two buttons: OK and Cancel. If the user clicks OK the function returns true and submission continues; if he clicks Cancel the function returns false and submission is halted. |
| Use the properties of the select list's options array | Use the properties of the select list's options array to access
information about your list options The options in a select list are accessible using the select list's options[] array. In other words, suppose you had a select list like the following: <SELECT name = "memberlist"> <OPTION value = 0> Select a membership category <OPTION value = 100> Family <OPTION value = 75> Single <OPTION value = 50> Senior <OPTION value = 30> Youth </SELECT> The option at memberlist.options[0] would be the first option in the list, memberlist.options[1] would be the second member, etc. But did you know that you can assign a value to the value property of each option, which can provide additional information about the user's selection? And, you can also access the text of the option using the option's text property. Both properties are accessible via the options array. Here's an example. Suppose the user has selected the Single membership option. (selectedIndex = 2). We could then use the following snippet of code in any script: var fee = parseInt(memberlist.options[memberlist.selectedIndex].value) var phrase = memberlist.options[memberlist.selectedIndex].text fee = fee + (fee * 0.07) alert("We welcome you as a " + phrase + " member." + "\n") alert("Please pay $" + fee + " tax included") The code above would display the following message: We welcome you as a Single member. Please pay $8.25 tax included. |
| Use the Window object's open method to open locations in a new window | If you have links on your page that will take users away from your site,
consider having the links open a new window rather than having the new
page open in the current window. That way, your users continue to have
your own site in view, and may be more likely to return to it by just
closing the newly opened window. Using the Window object's open method
allows you to not only open a new browser window, but also allows you to
specify the window's size and look. The open method accepts three parameters: A URL to be loaded into the window, an optional name for the window, and an optional string of window features that will determine what the window will look like. For example, the window features can specify whether the window should have a menu bar, location bar, status bar, or scrollbars. They can also specify the size of the window and whether or not it should be resizable. The list of window features that you want your new window to exhibit are placed inside a quoted string, separated by commas, and with no spaces anywhere within the string. Here's an example. To open a new window when a link is clicked, set up the link like this: <A href = "javascript:openwindow('http://www.zdjournals.com')">Check out ZDJ's Daily Buzz!</A> Then, to open a new, unnamed window with a status bar and scrollbars that is 400 pixels high and 600 pixels wide, and is resizable, the script would be: <SCRIPT> function openwindow(address) { window.open(address,"","status,height=400,width=600,scrollbars,resizable") } </SCRIPT> For a full list of the open method's window features, see the Microsoft site at http://msdn.microsoft.com/workshop/Author/dhtml/reference/methods/open_0.asp |
| Using the appVersion property with Internet Explorer | The appVersion property of javascript's Navigator object returns the
version of the current browser, along with information about the browser's
platform. For example, running Internet Explorer 4.0 in Windows 95 would
create the following value for the appVersion property: 4.0 (compatible; MSIE 4.01 Windows 95) You might, at first glance, assume that you can simply extract the first character of the appVersion string to determine the version of Internet Explorer that you're using. However, that first number is the compatible Netscape version; the Internet Explorer version is actually to be found in the string "MSIE 4.01." To extract the Internet Explorer version from the string, try using a combination of the indexOf() and charAt() String methods, as in: function getversion() { //Get the position of the string "MSIE" in the appVersion string position = navigator.appVersion.indexOf("MSIE" ) //If the string is found if (position >-1) { //Move to the position of the version number and test switch (navigator.appVersion.charAt(position+5)) { case "3": alert("Using Internet Explorer 3") break case "4": alert("Using Internet Explorer 4") break case "5": alert("Using Internet Explorer 5") break } } else alert("Not using Internet Explorer") } |
| Avoid invalid XML characters in ASP pages | If you hard code XML data islands into a Web page, you'll want to avoid
several special characters that are part of XML's syntactic structure. XML
won't interpret these characters correctly if left within an XML data
source. The characters and the alternate character sequences to use in
their place are as follows: < < & & > > " " ' ' So, instead of <Greeting>Hello, 'World'! <This> will bomb!</Greeting> you'd want to enter <Greeting>Hello, 'World'! <This> will bomb!</Greeting> Of course, if you use the XML Document Object Model to create XML nodes, the XMLDOM takes care of this encoding for you. As a result, you won't need to use the alternate characters. So, MyGreetingNode.Text = "Hello, 'World'! <This> will bomb!" is perfectly valid. |
| Build XML documents graphically with XML Notepad | XML is quickly becoming the data model for most Web applications.
However, if you've found yourself cringing at the thought of coding even
the smallest XML data island, then the Microsoft XML Notepad is for you.
This handy tool provides a nice UI for editing XML documents. With it, you
can add nodes, attributes, and values as well as see a graphical
representation of the data structure. Even better, XML Notepad takes care
of all the actual coding for you. You can download this utility for free
from http://msdn.microsoft.com/xml/notepad/intro.asp |
| Convert ADO fields to XML tags without errors | XML tag names can't contain several special characters or spaces.
Therefore, when you use ADO field names as XML tags, make sure to rename
the fields, if necessary, in the SQL statement. For example, if you use
the following SQL statement: Select [Employ ID] AS EmployID, Name FROM Employees |
| Syntax requirements for integrating XSL into ASP and HTML | Since an XSL stylesheet is an XML document itself, its markup must
conform to XML grammar rules. Most notably, you must ensure that you close
all tags. Since HTML is considerably more forgiving in this regard, make
sure that the markup in XSL sheets will successfully parse. For example,
to use <BR> to express a line break, you must enter <BR/>
instead. To read more about the W3's recommendation for XHTML, which
addresses this issue, see http://www.w3.org/TR/xhtml1/ |