For a limited time we are offering some special bonuses when you sign up with our lifetime subscription. You will get all of the following:
That's $119 worth of products and services for only $59.95 (over 50% savings!). But hurry, this offer will not last long! To order, jump to http://www.codeoftheweek.com/order.html and order our lifetime subscription. If you are currently a subscriber and wish to upgrade your subscription, select the Lifetime Upgrade offer for $44.95.
If you are not a paid subscriber, you must have signed up for our free trial at http://www.codeoftheweek.com. Our ezine is not an unsolicited message (in other words a spam email). Keep in mind that if you signed up for our free trial you can still receive a total of four issues at no cost to you. After you receive the four issues you will be notified about continuing your subscription.
If you do not wish to continue to receive this ezine, please email us at cancel@codeoftheweek.com
Recently we have received many requests for some routines specific to Microsoft Access. The source code in this issue is designed for Microsoft Access 97. Similiar code should work in Visual Basic 5.0 and up.
If you have any questions about this issue, please email us at questions@codeoftheweek.com
The routines included in this issue shows how to update counters and pad strings is Access.
Updating counters are useful when you want to control how a number gets incremented and the AutoNumber feature does not work since you need to have some control over the starting number or the sequence.
Padding strings are useful when you are exporting some data to a text file and need it to be formatted in a specific way. For example, we were creating text file extracts for EDI (Electronic Data Interchange) and were required to pad data to the correct length. These functions let you perform that padding very easily.
If you have any other requests for specific Access source code, let us know at requests@codeoftheweek.com
There are three public functions in this module that you can use.
The first is GetNextValue which can be used to increment the value stored in a database. It is very useful for invoice numbers, check numbers or any other type of number that needs to be incremented.
PadRight will add blanks to the right side of the string and PadLeft will add them to the left side of the string. For example PadRight("923", 6) will return the string "923 " (there are three blanks after 923). PadLeft("923", 6) will return " 923" (there are three blanks to the left of 923).
Public Function GetNextValue(sTable As String, sField as String) As Long Public Function PadRight(sData As String, lLength As Long) As String Public Function PadLeft(sData As String, lLength As Long) As String
GetNextValue returns a Long Integer value. PadRight and PadLeft return the padded string.
MsgBox PadRight("LASTNAME,FIRST", 20)
' Will print LASTNAME,FIRST followed by 6 blanks.
MsgBox PadLeft("123.99", 10)
' Will print 123.99 preceeded with 4 blanks.
MsgBox PadRight("LASTNAME,FIRST", 8)
' Will print LASTNAME
' When the source string is less than the length it is truncated.
' Example for GetNextValue
' Assumes rs is an open recordset in edit mode
' InvoiceCounter is the table name and InvNumber is the field name
rs.Fields("Invoice Number") = GetNextValue("InvoiceCounter", "InvNumber")
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0062.html
That concludes this issue of COTW. We hope you find the source code useful in your development.
The below describes the ways you can supply us some feedback about COTW. We would like to see our members help mold COTW into the best Visual Basic source code resource available. But to do that we need your feedback about what you like and what you do not like about COTW.
If you are interested in advertising in COTW please email us at sponsor@codeoftheweek.com Our rates are VERY reasonable, actually they are almost FREE. We reach over eight thousand Visual Basic developers each week.
If you have any suggestions for topics you would like to see covered or questions about this issue, please email them to info@codeoftheweek.com or use online feedback form at http://www.codeoftheweek.com/feedback.html.
If you have any source code you would like to submit for possible inclusion in COTW, please fill out our online submission form at http://www.codeoftheweek.com/submission.html.
Thank you for trying Code of the Week for Visual Basic.
Your free trial expires after you receive your fourth issue. If you want to continue to receive Code of the Week you can get 52 issues of COTW for only $19.95. This is a full year of Visual Basic source code and information to help with all your development. So don't wait, subscribe now! The quickest way to subscribe is to jump to our online order form at http://www.codeoftheweek.com/order.html