Book

Suggestions


Enter your email address:

Delivered by FeedBurner



Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?
Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Thursday, January 29, 2009 – Permalink –

Sort Forms

Create a sorting function

Here is one way to provide your users with a means to reorder fields in forms.

"Chris Weber develops a solution that allows users to sort the data in their forms (or subforms) that you can add to your application easily.

  • The sorting interface should be generic to any form in any database.

  • It should also work with subforms.

  • It needs to be implemented as expediently as possible to fulfill the bid, and be easily maintained in the future.

  • It should be intuitive or reminiscent of other interfaces in Access and Windows.
Let Your Users Sort it Out



Scroll down to the bottom of the page. There is a file that can be downloaded with the code.




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:04 AM

Comments: Post a Comment


  Saturday, September 13, 2008 – Permalink –

Make Null Zero

It's nothing


When it is desirable to return a zero (or another value) rather than an empty field, Access (Visual Basic) has a function Nz():

Nz(variant, [valueifnull])


The Nz function has the following arguments.

variant
A variable of data type Variant.
Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
valueifnull


This example demonstrates how you can simplify an IIF function

Instead of:


varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")


You could use:

varResult = IIf(Nz(varFreight) > 50, "High", "Low")


Helen Feddema offers a suggestion about forcing a zero when Nz() doesn't work

When you want to display zeroes in text boxes (or datasheet columns) when there is no value in a field, the standard method is to surround the value with the Nz() function, to convert a Null value to a zero. However, this doesn't always work, especially in Access 2003, which is much more data type-sensitive than previous versions. In these cases, you can force a zero to appear instead of a blank by using two functions: first Nz() and then the appropriate numeric data type conversion function, such as CLng or CDbl. Here is a sample expression that will yield a zero when appropriate:

NoAvailable: CLng(Nz([QuantityAvailable]))

ACCESS Watch Vol 7 No. 5


See all Topics

Labels: , ,


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  Friday, July 18, 2008 – Permalink –

Signing Macros

Security levels


There are three levels of Macro security:

High:
A computer user can open without a prompt a digitally signed project from a trusted publisher. Otherwise, the application blocks opening signed projects from untrusted publishers as well as unsigned projects.
Medium:
A computer user can open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.
Low:
A computer user can open an unsigned project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.
Securing Access Databases
"If you've used Access 2003, you've probably seen several security warning messages - Access 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.

A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped."

Also:
Signing Access 2003 Projects

Other links:

How to make sure that your Office document has a valid digital signature in 2007 Office products and in Office 2003

Also:
HAL-PC MS Office & Excel SIG in Houston, Texas:
Digital Certificates and Trusted Sources for running Excel Macros under High Macro Security



See all Topics

Labels: ,


<Doug Klippert@ 5:01 AM

Comments: Post a Comment


  Sunday, April 13, 2008 – Permalink –

Canada/US Postal Codes

Automatic Input masks



If you have a mix of Canadian and US postal codes, you might play with the following code inserted as a Country control "After Update" Event property.

Private Sub Country_AfterUpdate()
Dim strCountry As String
strCountry = Me.Country

Select Case strCountry
Case "Canada"
Me.[PostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[PostalCode].InputMask = "00000-9999;;_"
Case Else
'If the country is not Canada or USA no input mask will be used
Me.[PostalCode].InputMask = ""
End Select
End Sub


comp.databases.ms-access forum

Working with postal codes in Access

As a rule, if you won't be performing numeric calculations on the data, entries should be stored as text. Social Security numbers, Phone numbers and postal codes should be stored as text.


You can use alphabetic characters in an input mask. For example, one of the sample input masks is >L0L\ 0L0 used to represent a Canadian postal code.

The ">" character in the input mask converts all the characters that follow to uppercase.

The "L" character requires an alpha entry; the "0" (zero) requires a numeric entry.

A "\"character causes the following character to be displayed as a literal character rather than a mask character.

A space appears between the three character pairs.
For example, V5P 2G1 is one valid postal code that the user could enter. The mask would prevent the user from entering two sequential alphabetic characters or numbers.

See:

Trinity University - San Antonio, Texas:
Input mask

Definition characters used to create an input mask
Some validation rules

You can manipulate postal codes in Access by changing the data type, input mask, or format of a postal code field.

Microsoft KB 207829:
ACC2000: How to Manipulate ZIP Codes in Microsoft Access.

Also see:
Postal Codes



See all Topics

Labels: , , ,


<Doug Klippert@ 5:57 AM

Comments: Post a Comment


  Thursday, March 27, 2008 – Permalink –

Entry Checker

A second chance


Unlike Word or Excel, Access does not warn you when data is changed.
Unless you make a structural or code change, Access thinks you know what you want to know and allows you to enter or change data and the close the application without a squeak.

There is a way around this:


"In Microsoft Office Access 2007, by default, users are not prompted to confirm changes after modifying and saving records on a form. But often you might want to prompt users to confirm their changes before the record is saved.

You can use a BeforeUpdate event procedure to display a confirmation prompt and handle a user's response to either cancel or continue with the save.

This visual how-to topic illustrates how to display a custom dialog box to prompt users to cancel or continue with saving changes to a record.

User Prompts
(with a video)



See all Topics

Labels: , ,


<Doug Klippert@ 7:33 AM

Comments: Post a Comment


  Saturday, February 09, 2008 – Permalink –

Display the Current Record Number

Without navigation


You may want to remove the navigation buttons from an Access form but still display the current record number. Not the ID or serial number, but the record number that would appear in the navigation box.


To provide this feature, you can use VBA to place the form's CurrentRecord value in an unbound text box, and then update the value during the Current event.


To utilize this property, add an unbound text box to your form in Design view. Then, on the Event tab of the form's Property list, click the ellipsis or Build button. Choose Code Builder.

Add the following code in the Visual Basic Editor:

Private Sub Form_Current()
MyTextBox = Me.CurrentRecord
End Sub


(where MyTextBox is the name of the control that displays the record number.)


Now, when you navigate from record to record, the MyTextBox control will update automatically to reflect the current number.



See all Topics

Labels: , ,


<Doug Klippert@ 7:09 AM

Comments: Post a Comment


  Saturday, January 19, 2008 – Permalink –

Convert Access Macros to VBA

Macros to Modules


Before Access 2000, the speculation was that Access would lose "Macros" and enter the exclusive world of VBA. It hasn't happened yet.


If you have macros in a database that you would like to convert to code, doing so is easy.

In Access 97: Right-click on the macro in the Database window and then choose Save As/Export from the shortcut menu. Then, select the Save As Visual Basic Module option button and click OK. You are then given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In Access 2000/2002+: Right-click on the macro in the Database window and then choose Save As from the shortcut menu. Enter the name of the module you want to create in the text box and choose Module from the As dropdown list. Next, click OK. You will be given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In 2007 go to Database Tools and look in the Macros group.


Sam's Publishing:
Taking More Control of Access
By Gordon Padwick.


Access 2007 introduces a new type of macros called embedded macros. Embedded macros are macros that are stored on an event instead of as a separate object. Embedded macros support name fix-up and are used extensively through-out our templates. They are largely targeted to information workers that don’t write code but useful for developers that are trying to perform some simple actions.



See all Topics

Labels: ,


<Doug Klippert@ 7:05 AM

Comments: Post a Comment


  Tuesday, July 17, 2007 – Permalink –

Flag Access Controls

Tag Property


The TAG property allows you to associate up to 2,084 characters of text with any form, report, section, or control. This is especially helpful when you want to single out a specific subset of controls.

For instance, say that you want to hide certain controls on a form when a user clicks a button.
You can flag which controls will be hidden by entering the word "Hide" (or any other consistent word) in each control's Tag property. Then, attach the following code to the command button's Click event procedure:
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Hide" Then
ctl.Visible = False
End If
Next





See all Topics

Labels: , ,


<Doug Klippert@ 6:42 AM

Comments: Post a Comment


  Friday, May 18, 2007 – Permalink –

Highlight the Current Control

Code vs. property


Many users have trouble knowing which text box on a form they're currently working with. One way to make it clear for users is to highlight the current one, for example, with a yellow background.

Access allows you to do this with conditional formatting, but you can also get a similar result using code.


To do so, create a new module and add the following code:

Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
ctrl.BackColor = 65535
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = 16777215
End If
End Function


Save and close the module, then open the form you want to apply the highlighting to in Design view.

Click the Code button and insert

Highlight("GotFocus")

in each textbox control's GotFocus event procedure. Likewise, add
Highlight("LostFocus")

to each textbox's LostFocus event procedure.

When you've finished,save the changes, close the VBE, and switch to Form view.


When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.



See all Topics

Labels: ,


<Doug Klippert@ 6:14 AM

Comments: Post a Comment


  Monday, April 16, 2007 – Permalink –

Time Interval

Run code at timed intervals


You may occasionally want to run a procedure associated with a form at set intervals. To do so, add the code to the form's Timer event procedure. Then, set the form's TimerInterval property to the number of milliseconds that should elapse between each time the code is run. The maximum you can set is 65,535.
(in Access 2007, the TimerInterval property setting is a Long Integer value between 0 and 2,147,483,647.)


Keep in mind that you shouldn't use a very small TimerInterval, otherwise your application will likely suffer a performance hit. To prevent the Timer event from firing, set the TimerInterval to 0.


Also see:
HOW TO: Create a Stopwatch Form in Access



See all Topics

Labels: ,


<Doug Klippert@ 6:10 AM

Comments: Post a Comment


  Saturday, March 31, 2007 – Permalink –

Comment Code

Edit toolbar



You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.




To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

Ross, suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.



See all Topics

Labels: ,


<Doug Klippert@ 7:05 AM

Comments: Post a Comment