| 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 http://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 |