How to export all VBA code to individual files. Please see Q-Built Solutions "Export All Code Modules" article.
Top
How to format a string with varying numbers of leading zeros.
Q: I would like to automate sending E-mails to employees at my company. Since some employees have the same name, I can use each employee's EmployeeID to uniquely identify each recipient's mailbox. But each employee has a varying number of digits in his EmployeeID, and the mailbox requires leading zeros for those who have fewer digits than the maximum allowed. For example, employee 123 needs M000123@MyCompany.com, but employee 98765 needs M098765@MyCompany.com. Is there an easy way to do this?
A: Yes. Build your E-mail address string with a user-defined format function to pad as many leading zeros as necessary (up to 6 in this case):
strEmpEmail = "M" & Format(strEmpID, "0000000") & "@MyCompany.com"
where strEmpID is a string that contains the numeric Employee ID.
Top
How to get individual items passed to another form using OpenArgs.
Q: I passed several items to another form by using the OpenArgs parameter. I can read the other form's OpenArgs Property, but all of the items are listed within a string, separated by commas. What function can I use to get individual items from this string? Is the function fool-proof so that if I accidentally request the third item and there are only two items in the OpenArgs Property, it won't produce an error?
A: Paste the following code into a standard module:
'======================================
' Function: getOpenArg( )
' Author: Q-Built Solutions, www.QBuilt.com
' Date: 21 Oct. '05
'
' This function extracts the string value at an ordinal position
' from the form's OpenArgs Property. The string values are
' expected to be delimited by a comma. nth is the ordinal
' value in the OpenArgs string to retrieve the substring from.
' No string will be passed back from this function when there
' is no OpenArgs Property and when nth exceeds the number
' of substrings in the OpenArgs Property.
'======================================
Public Function getOpenArg(sOpenArgs As String, nth As Long) As String
On Error GoTo ErrHandler
Dim pos As Long
Dim idx As Long
Dim nStart As Long
nStart = 1
For idx = 1 To nth
pos = InStr(nStart, sOpenArgs, ",", vbDatabaseCompare)
If (idx = nth) Then
If (pos = 0) Then
getOpenArg = Mid$(sOpenArgs, nStart, _
Len(sOpenArgs) - nStart + 1)
Exit For
ElseIf (pos > 0) Then
getOpenArg = Mid$(sOpenArgs, nStart, pos - nStart)
Exit For
End If
ElseIf (pos = 0) Then
Exit For ' Ordinal # exceeds # of OpenArgs.
End If
nStart = pos + 1
pos = 0
Next idx
Exit Function
ErrHandler:
MsgBox "Error in getOpenArg( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
getOpenArg = ""
End Function ' getOpenArg( )
Top
How to get the Error Description from the Error Number.
Q: When I have a runtime error while debugging, I can remember the error number, but not always the description. Is there a list of error descriptions or an easy way to get the description from the number? For example, how would I get the error description for Err.Number = 13?
A: Yes. Open the Immediate Window (<CTRL><G>) and type:
then press <ENTER> to read the description for this error. For a complete list, see the Q-Built Solutions' VBA tip which includes the source code from the Access 97 Help topic to create a table of Access and Jet Errors.
Top
How to hide tables in VBA.
Q: When I right-click on a table's name in the Database Window, I can select Properties to open the Properties dialog window and select the "Hidden" Attributes check box. How can I do this with code so that I can hide these tables from the users?
A: If you are using Jet 4.0 (Access 2000, 2002, and 2003), then use the following code:
Public Sub hideTable()
On Error GoTo ErrHandler
SetHiddenAttribute acTable, "tblMyTable", True
Exit Sub
ErrHandler:
MsgBox "Error in hideTable( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
In order to unhide the table, use the following code:
SetHiddenAttribute acTable, "tblMyTable", False
However, to view tables that have been hidden by applying the "Hidden" attribute, the user only needs to select the Tools -> Options... menu to open the Options dialog window and select the "View" tab, then mark the Show "Hidden objects" check box. A better way to hide these tables is to make them user-defined system objects by renaming the table with a USys prefix. For example, tblMyTable would be renamed as USysMyTable. Then in order to view this table in the Database Window, the user would need to mark the Show "System objects" check box in the Options dialog window, which most users rarely do because they don't need to see the system tables.
Top
How to link to a table located in a database that has a database password.
Q: How can I link to a table within a database that has a database password?
A: Use the following code:
Public Sub linkTblInDBwPswd()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim sPath As String
Dim sTblName As String
Dim sLinkedTblName As String
Dim fOpenedDB As Boolean
sPath = "C:\Data\MyDB.mdb"
sTblName = "tblSomething"
sLinkedTblName = "tblSomething_Link"
Set db = CurrentDb()
fOpenedDB = True
Set tbl = db.CreateTableDef(sLinkedTblName, _
dbAttachSavePWD, sTblName, ";Database=" & sPath & _
";Pwd=MyPswd")
db.TableDefs.Append tbl
Exit Sub
CleanUp:
Set tbl = Nothing
If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If
ErrHandler:
MsgBox "Error in linkTblInDBwPswd( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
In this example, tblSomething is the name of the table in the password-protected database, C:\Data\MyDB.mdb, and the password is MyPswd. tblSomething_Link is the name of the linked table to be created in the current database.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
