Thursday, November 20, 2008

How to reset AutoNumber in Microsoft Access table?

After testing my own application, now it's time for deploying it. 
Well, there are many junk records being added in the table, and now it's time to reset everything.
The AutoNumber field value in Access does not automatically reset when you delete the rows in the table. Finally, I've found the solution here.

1. Delete the AutoNumber field from the table.
2. Click Queries on the left pane, and double-click Create query in Design view on the right pane.
3. In the Show Table dialog box, select the table. Then click Add to add the table and Close the dialog box.
4. Double-click all the fields in the table to select the fields.
5. Select the required Sort order, if there's any.
6. Click Make-Table Query on the Query menu. Type the new table name in the Table Name text box and then click OK.
7. Click Run on the Query menu.
8. A dialog box appears with the text that follows: You are about to append # row(s) into a new table. Click Yes to insert the rows.
9. Click Close on the File menu. Click No to close the Make-Table Query window to discard saving the query.
10. Click Tables on the left pane. Right-click on the new table and then click Design view.
11. In the Design view for the table, add an AutoNumber field with the same field name that you deleted in step 1. Then save the table.
12. Close the Design view window.
13. Delete the original table, and rename new table name to the original table name.
Now the AutoNumber has started from 1.

For more information, please refer to Microsoft Help and Support.

Tuesday, November 18, 2008

To lock your computer

My colleague just sent us this email, regarding how to lock the computer.
I think it's quite useful if you are using the computer in the office, and always want to lock it when you are away.

The easiest shortcut: Press Windows logo key + L

Or, you can create a shortcut on your desktop.

1. Right-click the desktop.
2. Point to New, and then click Shortcut.
3. The Create Shortcut Wizard opens. 
In the text box, type the following:
rundll32.exe user32.dll,LockWorkStation
4. Click Next.
5. Enter a name for the shortcut. e.g. "Lock Workstation"
6. Click Finish.

You can also change the shortcut's icon. e.g. padlock icon in shell32.dll
To change the icon:
1. Right click the shortcut and then select Properties.
2. Click the Shortcut tab, and then click the Change Icon button.
3. In the Look for icons in this file text box, type: Shell32.dll
4. Click OK.
5. Select one of the icons from the list and then click OK.

This is the shortcut I've created on my desktop: 

Tuesday, November 11, 2008

StrConv function - to capitalize the first character in the sentence

Reference: Chennai IQ

I wanted to write a function to capitalize the first character of every word. For example, I want to change "cell address" to "Cell Address". Before I started the function, I was thinking perhaps there already has a easy way to do that. Luckily, I did a search in the Google and found this: StrConv

This is the function I've written:
Public Function CapitalizeFirstCharacter(ByVal Value As String) As String
    ' Make the whole string lower case, then only capitalize the first character
    CapitalizeFirstCharacter = StrConv(LCase$(Value), vbProperCase)

End Function

StrConv (String, conversion[, LCID])

String - required. String expression to be converted.
conversion - required. Integer. The sum of values specifying the type of conversion to perform.
LCID - optional. The LocaleID, if different than the system LocaleID. (default: system LocaleID)

conversion argument:
vbUpperCase1Convert to uppercase
vbLowerCase2Convert to lowercase
vbProperCase3Convert the first letter of every word in string to uppercase

There are also vbWide, where values are 4, 8, 16, 32, 64, 128 to convert the string with specific usage. (Please refer to the reference page)

Monday, November 10, 2008

New line character in Excel cell

This is really simple, but I didn't know how to do it until just now. 
Everytime when I press the "Enter" key, I'll be brought to the next cell of the Excel.
Until just now, I found out actually there is a way to go to the next line in the same cell.

This is pretty simple, just press Alt & Enter key, that's it! :D

It's called Line Feed character.
In VB, it can be done by calling Chr(10) or vbLf.
"a) Sentence 1" & Chr(10) & "b) Sentence 2" or
"a) Sentence 1" & vbLf & "b) Sentence 2" or

Information extracted from AllExperts site.