<< A B C D E F G H I J K L M N O P Q R S T U V W X Y Z


Access table, open from an outside application (like Outlook)

Sub OpenTable()
   objAccess = GetObject(, "Access.Application")
   'if Access already open
   objAccess.DoCmd.OpenTable( "company")
   objAccess = Nothing
End Sub

add records, prevent – see record, new, suppress or prevent in a form

address parse example

Public Sub loop1()
   ' addresses start out clumped together in 3 or 4 lines each
   ' 3 lines
   ' Davy Jones
   ' 726 Briney St Apt A
   ' Bloomfield, NJ 07005

   ' Or 4 lines
   ' Sam Snead
   ' 7300 US Highway 55
   ' MS#SL430 AX 12345
   ' Princeton, NJ 08540

   Dim i, k As Integer
   Dim thisRec,thisStateZip As String
   ReDim outputRec(5) As String
   strSQL = "SELECT * FROM CPAMailingListKatzRaw"
   Dim db As DAO.Database
   Dim rstSource As Recordset
   Dim rstTarget As Recordset
   Set db =DBEngine(0)(0)
   Set rstSource =CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
   ' ... and an emptycopy of the target with 6 fields
   Set rstTarget =db.OpenRecordset("output", dbOpenDynaset)
   If rstSource.RecordCount > 0 Then
   i = -1
   thisRec = ""
   Do While Not rstSource.EOF
       i = i + 1
       thisRec = rstSource(1)
       outputRec(i) = rstSource(1)
       If InStr(thisRec, ",") > 0 Then ' we're lucky in this example 'cause commas ONLY show upbetween city and state (once per record)
           With rstTarget
                 For k = 0 To 3
                     Select Case k
                     Case 0 ' full name
                         .Fields(k) = outputRec(k)
                     Case' streetaddress, which may or may not contain 2nd address
                         .Fields(k) = outputRec(k)
                     Case' either2nd address or city/state/zip
                          If outputRec(3) = "" Then   ' we onlyhave 3 fields, so 3rd field only contains city/state/zip
                             .Fields(3) = Left(outputRec(2), InStr(outputRec(2), ",") -1)   ' split out city
                             thisStateZip = Mid(outputRec(2), InStr(outputRec(2), ",") + 1)  ' stash state/zip
                          Else    'we have 4 fields, so 3rd field has 2nd addr, 4th field has city/state/zip
                             .Fields(2) = outputRec(2)
                             .Fields(3) = Left(outputRec(3), InStr(outputRec(3), ",") -1)   ' split out city
                             thisStateZip = Mid(outputRec(3), InStr(outputRec(3), ",") + 1)  ' stash state/zip
                          End If
                     End Select
                    outputRec(k) = ""
                 Next k
                thisStateZip = LTrim(thisStateZip)  ' stashedstate/zip
                 ' split out state
                .Fields(4) = Left(thisStateZip, InStr(thisStateZip, " ") - 1)
                 ' split out zip
                .Fields(5) = Mid(thisStateZip, InStr(thisStateZip, " ") + 1)
             End With
            i = -1
        End If
    End If
End Sub

ADO recordsets, how to bind Microsoft Access forms

The following example demonstrates how to bind a form to an ADO recordset that is based on SQL Server data that shares an ADO connection with Microsoft Access.

  1. Open the sample project NorthwindCS.adp.
  2. Open the Customers form in Design view.
  3. Clear the RecordSource property of the form to unbind the form.
  4. Set the OnOpen property of the form to the following event procedure:

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set cn = CurrentProject.AccessConnection
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
   End With
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Setrs = Nothing
   Setcn = Nothing
End Sub

  1. and then close the form.

.adp file, change source database for

Through GUI (Access 2007)

Windows button / Server / Connection

Select or enterserver name, select the database on the server

Using commands

in the Debug window (ctl-g):

? currentproject.BaseConnectionString

It will return something like this:


Just replace the Database_Name andthe Server_Name w/ the names of your db on SQL Server and server (computer)where it resides. You don't need the Workstation parameter.

strConnect = CurrentProject.BaseConnectionString

strConnect = Replace(strConnect, "Old_DB_Name", "New_DB_Name")

strConnect = Replace(strConnect, "Old_Server", "New_Server")

After getting the old connectionstring & replacing the db name & server name, you can close theconnection using CloseConnection:


Then open a new connection to the"real" SQL Server db:


.adp file, create – when you’re creating a blank database, click the little folder icon to browse for a location and then choose the last item, “Microsoft Office Access Projects (*.adp)”

arguement for a function – see also optional arguments

arguments, pass multiple – see multiple arguments, pass

array, return from function


'----- function definition -----
Function fReturnArray() As Variant

    fReturnArray =Array("Red", "Green", "Blue")
End Function
'----- In Immediate Window -----
a = freturnarray : for i = lbound(a) toubound(a) : ?a(i) : next i


SubWorker(a() As Integer)
    Dim i As Integer
    For i = 1 To 5
       a(i) = i * 10
End Sub
Sub Caller()
    Dim a(10) As Integer, i as Integer
    Call Worker(a())
    For i = 1 To 5
       Debug.Print i & " --- " & a(i)
End Sub

ASCII – chr()to generate a character from the ASCII code, asc() to do the opposite: generate ASCII codefrom a character

associated with “.mdb” extension, make access be program – C:\Program Files\Microsoft Office\Office\MSACCESS.EXE /NOSTARTUP "%1"

* The path to access 97

* Required argument

* The file to open

automatically logout users for DB maintenance – one way uses the following tack:

  1. Open and hide a form that periodically checks for the logout flag
  2. When flag is true, open and show the countdown form
  3. Continue scanning to verify flag was not reset to false
  4. Quit application at end of countdown.

automatically open a form on start up - see form, automatically open upon start up, hide ribbon when access starts

autonumber, convert to - Copy the table (structure only), add a autonumber field to the new table, and then run an append query to move all the fields--except append the old key field to the new autonumberfield with a

[newAutoNumberField] = CLng([oldKeyField])

This will work only if the old key values are unique, and less than 2,147,483,647. The next record added will be assigned the highest"oldKey" (which is now in the autonumber field) + 1.

autonumber, determine number of just added

1st way

'create aconnection
set conn =server.createObject("ADODB.connection")
conn.Mode = adModeReadWrite
conn.Timeout = 20
conn.open "DSN","user","pass"
'create a recordset
set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = conn
rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Source = "table name"
'Add the new record
rs("field1") = "value"
rs("field2") = "value"
'get the newly created autonumber from the recordset
AutoNumber = rs("Auto-Number-Field")
'close therecordset and kill the object
set rs = nothing

2nd way

rs!LastName = "Abraham"
rs!FirstName = "Lincoln"
rs.Bookmark = rs.LastModified
Autonumber = rs.EmployeeID

3rd way

Supposedly, “Select @@IDENTITY” works somehow.  But I haven’t gotten it to work in Access yet…


beginning of record (BOF) test doesn’t work for ADO recordsets – see first record of recordset, determine whether

binary hexadecimal unprintable characters, replace with empty string – see hexadecimal unprintable characters, replace with empty string

binary file: read, convert to ASCI, search for string

Sub ReadBinaryFile()
    Dim sFileName As String
    Dim iFileNum As Double
    Dim btAR() As Byte
    iFileNum = FreeFile()
    sFileName = "C:\test\x.msg1"
    ReDim btAR(1 To FileLen(sFileName))
    Open sFileName ForBinary Access Read As #iFileNum
    Get #iFileNum,1, btAR()
    strMsg = Stream_BinaryToString(btAR(),"us-ascii")
    ReDimstrFieldName(3) As String
    strFieldName(1) = """6"""
    strFieldName(2) = """8"""
    strFieldName(3) = """10"""
    For i = 1 To 3
        x = InStr(strMsg,"<field id=" & strFieldName(i) & ">")
        y = Mid(strMsg,InStr(strMsg, "<field id=" & strFieldName(i) &">") + Len(strFieldName(i)) + 11)
        xName = Left(y, InStr(y,"</field>") - 1)
End Sub
'Stream_BinaryToString Function - 3rd method described here (http://www.motobit.com/tips/detpg_binarytostring/)
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
'CharSet - charset of the source binarydata - default is "us-ascii"
Function Stream_BinaryToString(Binary, CharSet)
    Const adTypeText = 2
    Const adTypeBinary = 1
    'Create Stream object
    Dim BinaryStream 'As New Stream
    BinaryStream = CreateObject("ADODB.Stream")
    'Specify stream type -we want To save text/string data.
    BinaryStream.Type = adTypeBinary
    'Open the stream Andwrite text/string data To the object
    'Change stream type To binary
    BinaryStream.Position = 0
    BinaryStream.Type = adTypeText
    'Specify charset Forthe source text (unicode) data.
    If Len(CharSet)> 0 Then
        BinaryStream.CharSet = CharSet
        BinaryStream.CharSet ="us-ascii"
    End If
    'Open the stream Andget binary data from the object
    Stream_BinaryToString = BinaryStream.ReadText
End Function

bind ADO recordsets to forms, how to – see ADO recordsets, how to bind Microsoft Access forms

blanks, trim leading - LTrim()

blank screen during print preview – this is usually due to not having a default printer or specifying a default printer that no longer exists or is now invalid.

BOF – beginning of record test doesn’t work for ADO recordsets – see first record of recordset, determine whether

break out of local workspace – ctl-z


calendar control

called function, to find source of – shift-F2

carriage return - chr(10) – for both carriage return andline feed: cr = Chr$(10) + Chr$(13) (or possibly the reverse: Chr$(Chr$(10)) – asc() does theinverse of chr()

carriage return, replace some arbitrary string with – Chr$(10)

UPDATE History SET History.memo = Replace(History.memo,"//xx??",Chr(10));

“Can’t find project or library” when starting up – hold downshift key to bring up Access.  Go to modules tab.  Design any moduleto get the menu to change.  Go to Debug/Compile and Save all Modules.  If it’s greyed out, go to Tools/References.  Click and unclick an unclicked module so it won’t be greyed out any more.  Go toDebug/Compile and Save all Modules again.

can’t open database – see opendatabase, can’t


' X is the value you want to round
' Factor is the multiple to which youwant to round
Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0))* Factor

characters, remove– see strip out characters

character, how many times a specific character appears in astring – see howmany times specific character appears in a string

chart, pivot – see pivotchart

checkbook – see generalledger

child/master/ field, link – see link master/child field

children, save newly created parent record before trying toadd child – see parent,save newly created parent record before trying to add children

“Command line that you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize.  Exit and restart Microsoft Access using valid command-line options” -- Need double quotes for arguments containing UNC machine names in the icon/start menu property

color, different for eachrow - use Conditional Formatting to solve your problem. If you need moreflexibility, you can programmatically control Conditional Formatting atruntime.

See http://www.lebans.com/conditionalformatting.htm A2KConditionalFormatting.zip is a sample MDB demonstrating how to programmatically setup Conditional Formatting to simulate:

1) Highlighting of the Current Row for a Form in Continuous or Datasheet View

2) Highlighting of Alternate Rows for a Form in Continuous or Datasheet

Or a different way

column of a combo box, refer to - Me!ShipName = Me![CustomerID].Column(1)

column, hide in datasheet view - display or hide a column on a subform control called subTestForm using acCmdHideColumns command

Private Sub cmdHideColumn_Click()
  Dim strForm As String
  If cmdHideColumn.Caption ="Hide Column" Then
    DoCmd.RunCommand acCmdHideColumns
    cmdHideColumn.Caption = "UnhideColumns"
    DoCmd.Echo False
    cmdHideColumn.Caption = "Hide Column"
    strForm = Me.subTestForm.SourceObject
    DoCmd.RunCommand acCmdDesignView
    DoCmd.OpenForm strForm, acFormDS
    DoCmd.RunCommand acCmdUnhideColumns
    DoCmd.RunCommand acCmdFormView
    DoCmd.Echo True
  End If
End Sub

combo box column, refer to - Me!ShipName = Me![CustomerID].Column(1)

combo box, paste into

For various reasons, we want to immediately start whittling down the combo box lists. The most compelling immediate reason is that a reasonable default population would be our entire database. But that's well over 10,000 records and Access only returns 10,000 records by default. Yes, you can change the default. But does a user really want to plod through 10's of thousands of entries? Of course not!

So, as soon as we get some meaningful user input, we try to cut the list down to size. In other words, we trigger the combo box's "change" event, pull in a subset of the list that matches at least the first letter, and then populate the combo box with that. Let's say you're looking for "Johnson". If you start typing "j", the combo box's change event is triggered, we immediately set its "RowSource" to a smaller subset of all the records that start with "j".

This is a little clunky 'cause, for some reason, once you update the list after the first "on change" event, the combo box itself still contains only that first "j", although if you click on the box's arrow, you see the whole subset in the drop down. In other words, the autofill doesn't work after that first rowsource update. But the autofill DOES seem to kick in after the user has typed in the second letter - especially if we only change the "RowSource" just once. In other words, if we only invoke the "RowSource" change for the first letter, and then don't do it again. Hence, the code below where we only change the "RowSource"

strEnteredSoFar = Me!cboFindLastName.Text
If Len(strEnteredSoFar) = 1 Then

In this case, the "o" in "Johnson" then triggers the autofill and, as the user types more of what he's looking for, the autofill keeps up and pretty soon he's very close to what he's looking for.

If you DON'T restrict changing the "RowSource" to only that first letter, the results seem to be unpredictable as the user continues on with subsequent letters. It seems every other letter will get an autofill. Or something. So, long ago, I decided that restricting the "RowSource" by just that first letter accomplished my purposes.

But some users felt otherwise. They want to paste in a last name to quickly see if the fellow's in the database. For some reason which I still haven't figured out, Access treats pasting differently than typing in a combo box. In this case, you would obviously have to remove that restriction of only modifying the "RowSource" to only that first letter 'cause the user will almost certainly be pasting in a name with a length greater than just one character. But when you paste in "johnson", the combobox appears to autofill in the first item in the subset of records.

If that first one happens to be the guy you're looking for and you select it, the "On Not in List" event gets triggered.  Only AFTER the "On Not in List" event gets ' triggered, do you THEN see the list - which isn't what you want. If that first record does NOT happen to be the one you're looking for (which, of course, is much more likely), you won't see anything in the drop-down list, even though you supposedly filled it in.  Again, not what you're after at all.

Needless to say, trying to explain all this peculiar behavior is tough enough here, let alone trying to explain to an end user. So I wimped out and, rather than trying to solve this problem head on, simply made up an ancillary txtFindLastName text box to accept any pasting in. It then trims down the possible candidates fills the cboFindLastName pick list with the list trimmed down that matches what you pasted. A lot simpler.

compare dates – see dates, compare

compile – see “ recompile, normal” or “ recompile, force – /decompile switch”

continue statement – MS says

Visual Basic does not support the Continue statement. However, you can achieve the same functionality by putting a statement label on the Loop statement and branching to it from the middle of the loop:

Dim LoopCounter As Integer
LoopCounter = 0
   Do While LoopCounter < 100
      LoopCounter = LoopCounter + 1
      Dim SkipToNextIteration AsBoolean   ' Local to this loop.
      ' Processing, which might change value of SkipToNextIteration.
      If SkipToNextIteration = TrueThen GoTo EndOfLoop
      ' More processing if SkipToNextIteration was still False.
EndOfLoop: Loop ' Acts like Continue.

But nextmight give you what you need…

convert Access tables to SQL scripts - see SQL for Access tables, generate

convert Access 2000 Databases to Visual Basic - Object Converter - shareware

convert phone number from 1-222-555-5555 to (222) 555-5555 format - ConvertedNumber: "(" & Mid([ToNumber],3,3) & ") " & Mid([ToNumber],7,8)

convert string to number – see string, convert to number

correlated subforms, adding grandchild (without | with missing | before) parent

First, correlate the child and grandchild using an invisible field (linkCustomer).  That’s the easy part.  Can’t seem to do it this way in adps, only accdb or mdb.  Instead put this in the child “on current” event:

Me.Parent!linkCustomer = Me!ID

And then link the grandchild to this link field on the main form.

Second, add this to the “Before Insert” of the grandchildform:

Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME.SetFocus ' "Parent" on "Grandparent"
If Nz( Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME) = "" Then
    Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME = Null ' a silly statement, but seemingly necessary!
End If
Me.Parent!subRestaurantCustomer.Form.Dirty= False
Me.Parent!linkCustomer= Me.Parent!subRestaurantCustomer.Form!ID
Me!CustomerFK= Me.Parent!linkCustomer

corrupt database– 1. Repair, 2. Export all files, import again back into clean install. See also “ repair, compact database doesn’t work”, MSysCompactError

count distinct values – see number of unique values

count how many records in a recordset - ? Me.Recordset.Recordcount

crosstab report

hard-coded fields - example that has 50 invisible text boxes that are made visible as needed

crosstab report, you’ve hard-coded fields but complains ‘cause this particular run of the underlying crosstab query didn’t generate all the fields – let’s say there are 2 fields called “BK” and “misc”.  Create the following OnOpen script:

Private Sub Report_Open(Cancel As Integer)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim BK, misc As Boolean

    BK = False
    misc = False

    Set cn =CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
    Set db = CurrentDb()
    strSQL = "SELECT[Management-LeadsThisMonthByDay].* " & _
    Set rs1 =db.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

    For x = 0 To rs1.Fields.Count - 1
        SelectCase rs1.Fields(x).Name
             Case Is = "BK"
                BK = True
             Case Is = "misc"
                misc = True
        End Select

    If BK Then
        Me!BK.ControlSource = rs1!BK.Name
        Me!BK.ControlSource = ""
    End If

    If misc Then
        Me!misc.ControlSource = rs1!misc.Name
        Me!misc.ControlSource = ""
    End If
End Sub


data access pages read only – make a primary key

data types

DataTypeEnum Values

" data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source" - you'll get this if you change a field on a form, and that form's query relies on that field.  For instance, let's say you have a list of projects who haven't paid.  You list those for the express purpose of marking them paid.  But as soon as you mark the record paid, it complains with this message.  Which makes sense.  But annoying.  See here for a discussion

His workaround? He created a temp table and filled it with the target table's PKID

INSERT INTO #reIdentify(JunkID)

I'm not sure why he bothered to set the only column as an identity and then go through all the SET IDENTITY_INSERT #reIdentify ON and OFF jazz.  So I modified to

alter PROCEDURE SP_Accounting_NotPaid


INSERT INTO #NotPaid (JunkID )
FROM dbo .Project
AND (DATE_RECORD_ENTERED > CONVERT ( DATETIME , '2012-01-01 00:00:00' ,102 ))

from Project as pr inner join #NotPaid as np on pr .ID =np .JUNKID

Anyway, it didn't work for me. Even though running the stored procedure within SQL Server returned the right rows, it always complains that it doesn't return any rows when you invoke it in Access using

strRecordSource = "Exec [SP_Accounting_NotPaid]"
Me.RecordSource = strRecordSource

in the "OnOpen" action 

You can (2) set the form's ResyncCommand to be the same as the query for the ADO Recordset (or in other words, the form's RecordSource) except that the ResyncCommand needs to end with a WHERE clause as show below with 2 question marks ("??"). The ResyncCommand must include the same fields, tables, and joins.

Me.ResyncCommand = "SELECT * FROM tblYourTable WHERE ID = ??"

I didn't get it to work at first 'cause both the sites above only put one question mark in.  Got to have 2!  And, again, use the same query as you do for the form's data source except replace everything in the original "where" clause with "WHERE ID = ??"

database, can’t open – see open database, can’t

database has been placed in a state by user ‘Admin’ on machine XXXX that prevents it from being opened or locked – get out of design mode – close and reopen the form

databases, compare two– HrzCompare.mdb

database corrupted – 1. Repair, 2. Export all files, import again back into clean install. See also “ repair, compact database doesn’t work”, open database, can’t

datasheet view – acFormDS, as in DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

datasheet view, hide column – see column, hide in datasheet view

date portion of date/time field –

DateValue(FieldHoldingTimeDateValue) – returns an integer

int(FieldHoldingTimeDateValue) – returns a double-precision number with the fractional part removed

dates, compare

Because dates and times are stored together as double-precision numbers, you may receive unexpected results when you compare Date/Time data. For example, if you type the following expression in the Immediate window, you receive a False result even if today's date is 3/31/2012:

? Now()=DateValue("3/31/2012")

The Now() function returns a double-precision number that represents the current date and the current time. However, the DateValue() function returns an integer number that represents the date but not a fractional time value. Therefore, Now() equals DateValue() only when Now() returns a time of 00:00:00 (12:00:00 A.M.).

To receive accurate results when you compare date values, use one of the following functions. To test each function, type the function in the Immediate window, substitute the current date for 3/31/2012, and then press ENTER:

• To return an integer value, use the Date() function:

? Date()=DateValue("3/31/2012")

• To remove the fractional part of the Now() function, usethe Int() function:

? Int(Now())=DateValue("3/31/2012")

dates, return consecutive

Public Function GetDates(StartDate As Date, EndDate As Date)
Dim InterimDate As Date
InterimDate = StartDate
Do Until InterimDate = EndDate
    InterimDate = DateAdd("d", 1,InterimDate)
    Debug.Print InterimDate
End Function

days elapsed – see elapsed days

debug window, to bring up -- ctl-g

step through – shift-F8

decompile – see recompile, force – /decompile switch, decompile

Example: "C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" "C:\Users\Joe\Documents\myapp.mdb" /decompile

delete using inner join – handy to delete all rows of a targettable if they’re present in a source table


FROM target INNER JOIN source ON target.email =source.email;

Make sure you set the UniqueRecords property to Yes or you might very well get “Could not delete from specified tables”.

  1. Open the delete query in Design view.
  2. On the View menu, click Properties.
  3. Set the UniqueRecords property to Yes.
  4. Save the query, and then close it.

When you set the UniqueRecords property to Yes, Microsoft Access includes the DISTINCTROW predicate in the SQL statement of the query. The DISTINCTROW predicate retrieves unique records in a multi-table query where fields have only been selected from the one-sided table in the query.

For example, if you add both the Customers and Orders tables to a query, but only select fields from the Customers table, the query returns multiple rows for each customer who placed multiple orders. When you set the UniqueRecords property to Yes, the query returns only one occurrence for each customer as long as that customer placed at least one order.

device Attached to the System Is Not Functioning – Answer found on http://support.microsoft.com/support/kb/articles/Q139/4/34.asp  and involves the following 2 files: Vbrun300.dll, Winoa386.mod

decompile from context menu

When using a shortcut to run the decompile switch on our databases, here's how you can right-click on an mdb in explorer view and decompile from the context menu that pops up.

  1. Open an Explorer window.
  2. Select "View|Folder Options"
  3. Select "File Types" and then select "Microsoft Access Database" from the list of file types.
  4. Click "Edit", then click "New" (if you also have Access 2.0 installed, you will need to do steps 4-6 for each version separately)
  5. In the "Action" box type what you want to appear in the context menu- e.g. "Decompile"
  6. In the "Application used to perform actions" box type the full path to MSAccess.exe and the rest of the command line, for example

"C:\ProgramFiles\Microsoft Office\Office\MSACCESS.EXE" /decompile "%1"

(That's all onone line and all the quotes are required)

  1. It’s rumored that after this, when you right-click on an mdb you should see Decompile as one of your options in the context menu.  But I’ve not seen this at all.

dialog box, create

how tocreate

DoCmd.OpenForm "ReportName", , , , , acDialog

directory, list all files in – see list all files in a directory

directory, open and select a file – see Call the standard Windows File Open/Save dialog box

directory, stuff contents of all files in a directory into atable

Sub StuffFilesInFolderToTable( ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    ' make sure you add"Microsoft Scripting Runtime" as a reference!
    ' example:StuffFilesInFolderToTable "C:\Users\joe\Documents\ACH\", True
    Dim db As Database
    Dim strSQL As String
    db = CurrentDb()
    Dim oFSO As NewFileSystemObject
    Dim oFS
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    FSO = NewScripting.FileSystemObject
    SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem InSourceFolder.Files
        'Debug.PrintFileItem.Path & FileItem.Name
        oFS = oFSO.OpenTextFile(FileItem.Path)
        Do Until oFS.AtEndOfStream
            sText = oFS.ReadLine
            strSQL = "INSERT INTO RawFiles (Directory,FileName, FileCreationDate, Line) " _
                & "VALUES(""" &FileItem.Path & ""","""& FileItem.Name & ""","""& _
                FileItem.DateLastModified & ""","""& sText & """)"
            db.Execute(strSQL, dbFailOnError)
    Next FileItem
    IfIncludeSubfolders Then
        For Each SubFolder InSourceFolder.SubFolders
            StuffFilesInFolderToTable(SubFolder.Path, True)
    End If
    FileItem = Nothing
    SourceFolder = Nothing
    FSO = Nothing
    db = Nothing
End Sub

distinct values, number of – see number of unique values

.dlls, register – regsvr32 /s %windir%\%sysDir%\Hrzshell.dll

unregister – regsvr32 /u /s %windir%\%sysDir%\Hrzshell.dll

A better way: double click on\\FFADMIN\HrzInstall\Library-Tools ShellMenusForCOMComponents.reg

.dlls, can’t see when trying to open or adding as references-- If none of the .dlls show up when you navigate, go to WindowsExplorer/View/Folder Options/General.  If “Classic Style” is selected,un-select it close out, get back in and re-select it.  If it’s notselected, select it.

DLookup(field, table or a query [, Criteria]) – DLookUp("[LastName]", "Employees", "[EmployeeID] =7")

dlookup in a query grid


The trick isgetting the single quotes right!



Docmd.TransferText – convert thiscommand from Access to SQL server – from here

Dim rs As New ADODB.Recordset
Dim strMsg As String
Dim strSQL As String
Dim strText As String
Dim strFileText As String
Dim strHeaderText As String
Dim fs
Dim a
Dim CommandText As String
Dim QueryName As String
Dim cn As ADODB.Connection
Set cn =CurrentProject.Connection
QueryName = "dbo.[" &QueriesCB.Value & "]"
strSQL = "SELECT * FROM " & QueryName
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'rs.Open CommandText,CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
If rs.RecordCount = 0 Then
 DoCmd.Hourglass False
 MsgBox "No Records to Export!"
 Exit Sub
End If
strHeaderText = strHeaderText &"""First""" & ","
strHeaderText = strHeaderText &"""Last""" & ","
strHeaderText = strHeaderText &"""Email"""
strFileText = strFileText & strHeaderText & vbCrLf
Do While Not rs.EOF
   strText = strText &"""" & rs(0) & """" &","
   strText = strText & """" &rs(1) & """" & ","
   strText = strText & """" &rs(2) & """"
   strFileText = strFileText & strText & vbCrLf
   strText = ""
Set fs =CreateObject("Scripting.FileSystemObject")
Set a =fs.CreateTextFile("C:\Program Files\xx\xx.csv", True)
a.WriteLine (strFileText)
Set rs = Nothing
'DoCmd.TransferText acExportDelim, , QueriesCB.Value, "C:\Program Files\xx\xx.csv", True

double quotes – see quotes

duration– see elapseddays, elapsed months, elapsed time

dynamic web pages



Eachrecord event – see event that fires for each record as it is displayed

elapsed days - = DateDiff("d", EarlierDate,LaterDate)

elapsed months - =DateDiff("m", EarlierDate, LaterDate)

elapsed time – Minutes: DateDiff("n",[StartDateTime], [EndDateTime])  To display as hours and minutes on a report,use a text box with this Control Source: =[Minutes] \ 60 & Format([Minutes]Mod 60, "\:00")

email – 3 ways:

SendObject – can’t handle HTML-formatted emails.  Nor,from what I’ve gathered, does it work too reliably inside a loop

DoCmd.SendObject acSendNoObject, ,acFormatRTF, Me!eMail

Outlook.application – can send HTML using .HTMLBody - see also here

Option Compare Database
Option Explicit
' Declare module level variables
Dim mOutlookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim mFolder As MAPIFolder
Dim mItem As MailItem
Dim fSuccess As Boolean
' Module contains only 2 methods:
'    1) GetOutlook()
'    2) SendMessage()

Private Function GetOutlook() As Boolean
' The GetOutlook() function sets the Outlook Application
' and Namespace objects and opens MS Outlook

On Error Resume Next
' Assume success
fSuccess = True
Set mOutlookApp = GetObject("","Outlook.application")
' If Outlook is NOT Open, then there will be an error.
' Attempt to open Outlook

If Err.Number > 0 Then
    Set mOutlookApp = CreateObject("Outlook.application")
    If Err.Number> 0 Then
       MsgBox "Could not create Outlook object", vbCritical
       fSuccess = False
        Exit Function
    End If
End If
' If we've madeit this far, we have an Outlook App Object
' Now, set theNameSpace object to MAPI Namespace
Set mNameSpace = mOutlookApp.GetNamespace("MAPI")
If Err.Number > 0 Then
   MsgBox "Could not create NameSpace object", vbCritical
    fSuccess = False
     Exit Function
End If
' Return theSuccess Flag as the value of GetOutlook()
GetOutlook = fSuccess
End Function
Public Function SendMessage()
' TheSendMessage() function reads user entered values and
' actually sends the message.
On Error Resume Next
Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String
Dim lngChars As Long
Dim intFile As Integer
' read our HTML file in to be the body
intFile = FreeFile 'return the next available filenumber
Open "C:\Documents and Settings\bob\MyDocuments\test.htm" For Input As intFile
lngChars = LOF(intFile)
strMsg = Input(lngChars, intFile)
strSubject = "test"
strRecip = "bob@yourdomain.com"
strAttachment = "C:\Documents and Settings\bob\My Documents\test.txt"
' Here's wherethe real Outlook Automation takes place
If GetOutlook = True Then
    Set mItem =mOutlookApp.CreateItem(olMailItem)
    mItem.Recipients.Add strRecip
    mItem.Subject = strSubject
   mItem.HTMLBody = strMsg ' .Body if plain text,not HTML
   ' This code allows for 1 attachment, but with slight
   ' modification, you could provide for multiple files.
    If Len(strAttachment) > 0 Then
       mItem.Attachments.Add strAttachment
    End If

End If
' Release resources
Set mOutlookApp = Nothing
Set mNameSpace = Nothing
End Function

If you get, “program is trying to automatically send e-mail on your behalf.  Do you want to allow this?  If this is unexpected, it may be a virus and you should choose ‘No’” – work around is to put some part of the code inside Outlook so it trusts itself

In Outlook 2003, if a MAPI MailItem object is created from within the VBA project (specifically the 'ThisOutlookSession' module), it is assumed to be "Trusted" and will not prompt the usual security messages when attempting to call the .Send method or when making use of the Outlook address book.

We can use this "Trusted" method to create an exposed Outlook VBA function that creates and sends the MailItem and then call this using Automation.  In our case, we will be calling the exposed Outlook VBA function from within Access.

For this example, we've created a function called FnSendMailSafe within the ThisOutlookSession module of Outlook VBA project.  This function creates the mail object, sets the parameters and then sends it.

One problem is that when Outlook is first opened, the VBA project doesn't expose the custom VBA function unless either a VBA event has fired, or the user has manually opened the VBA IDE.  The trick used is to also create a blank event called Application_Startup() in the ThisOutlookSession module - this event will fire as soon as Outlook opens and so the VBA project will load properly and our function will be exposed.

Finally, the Outlook Macro Security level must be set to LOW or MEDIUM otherwise the custom VBA function will not be exposed through automation.
(Note: If you have changed the Macro Security level you must restart Outlook).

Furthermore, if Outlook is closed when you try to send e-mails, you will probably need to set the macro security level to LOW rather than MEDIUM, otherwise you may receive a warning about unsafe macros.

Here's the Outlook 2003 VBA code: (copy and paste into the ThisOutlookSession VBA module)

Option Explicit

' Code: Send E-mail without Security Warnings
' OUTLOOK 2003 VBA CODE FOR 'ThisOutlookSession' MODULE
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Updated v1.3 - 11/11/2005
' Please read the full tutorial here:
' Please leave the copyright notices in place - Thank you.

Private Sub Application_Startup()

    ' IGNORE - This forces the VBA project to open and be accessible using automation
    '         at any point after startup

End Sub

' FnSendMailSafe
' --------------
' Simply sends an e-mail using Outlook/Simple MAPI.
' Calling this function by Automation will prevent the warnings
' 'A program is trying to send a message on your behalf...'
' Also features optional HTML message body and attachments by file path.
' The To/CC/BCC/Attachments function parameters can contain multiple items by separating
' them by a semicolon. (e.g. for the strTo parameter, 'test@test.com;test2@test.com' is
' acceptable for sending to multiple recipients.
' Read more here:
Public Function FnSendMailSafe(strTo As String,_
                                  strCC As String,_
                                  strBCC As String, _
                                  strSubject As String, _
                                  strMessageBody As String, _
                                   Optional strAttachments As String) As Boolean

' (c) 2005 Wayne Phillips - Written 07/05/2005
' http://www.everythingaccess.com
' You are free to use this code within your application(s)
' as long as the copyright notice and this message remains intact.

On Error GoTo ErrorHandler:

    Dim MAPISession As Outlook.NameSpace
    Dim MAPIFolder As Outlook.MAPIFolder
    Dim MAPIMailItem As Outlook.MailItem
    Dim oRecipient As Outlook.Recipient
    Dim TempArray() As String
    Dim varArrayItem As Variant
    Dim blnSuccessful As Boolean

    'Get the MAPI NameSpaceobject
    SetMAPISession = Application.Session
    If Not MAPISession Is Nothing Then
     'Logon to the MAPIsession
     MAPISession.Logon , , True, False
     'Create a pointer tothe Outbox folder
     Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
     If Not MAPIFolder Is Nothing Then
        'Createa new mail item in the "Outbox" folder
        Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)
        If NotMAPIMailItem Is Nothing Then
         With MAPIMailItem
             'Create the recipients TO
                TempArray = Split(strTo, ";")
                 For Each varArrayItem InTempArray
                     Set oRecipient = .Recipients.Add( CStr(Trim(varArrayItem)))
                    oRecipient.Type = olTo
                     Set oRecipient = Nothing
                 Next varArrayItem
             'Create the recipients CC
                TempArray = Split(strCC, ";")
                 For Each varArrayItem InTempArray
                     SetoRecipient = .Recipients.Add( CStr(Trim(varArrayItem)))
                    oRecipient.Type = olCC
                     Set oRecipient = Nothing
                 Next varArrayItem
             'Create the recipients BCC
                TempArray = Split(strBCC, ";")
                 For Each varArrayItem InTempArray
                     Set oRecipient = .Recipients.Add( CStr(Trim(varArrayItem)))
                    oRecipient.Type = olBCC
                     Set oRecipient = Nothing
                 Next varArrayItem
             'Set the message SUBJECT
                .Subject = strSubject
             'Set the message BODY (HTML or plain text)
                 If StrComp(Left(strMessageBody,6), "<HTML>", vbTextCompare) = 0 Then
                    .HTMLBody = strMessageBody
                    .Body = strMessageBody
                 End If
             'Add any specified attachments
                TempArray = Split(strAttachments, ";")
                 For Each varArrayItem InTempArray
                    .Attachments.Add CStr(Trim(varArrayItem))
                 Next varArrayItem
             .Send 'No return value since the message will remain in theoutbox if it fails to send
             Set MAPIMailItem = Nothing
          End With
        End If
        Set MAPIFolder = Nothing
     End If
    End If
    'If we got to here, then weshall assume everything went ok.
    blnSuccessful = True
    Set MAPISession = Nothing
    FnSendMailSafe = blnSuccessful
    Exit Function
    MsgBox "An error has occured in the user definedOutlook VBA function FnSendMailSafe()" & vbCrLf & vbCrLf & _
            "Error Number: " & CStr(Err.Number) & vbCrLf & _
            "Error Description: " & Err.Description, vbApplicationModal +vbCritical
    Resume ExitRoutine

End Function

At this point, I would recommend testing the code by sending a test e-mail from the Outlook Immediate window:

? ThisOutlookSession.FnSendMailSafe("youremailaddress@here.com","","","Test","Test")

Once you've confirmed that you have setup the VBA code correctly, it's time for the Access automation...

And here's the Access VBA code used to call the function via Automation (example uses late-binding object):

' ACCESSVBA MODULE: Send E-mail without Security Warning
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Updated v1.3 - 11/11/2005
' Please read the full tutorial & code here:
' Please leave the copyright notices in place - Thank you.

'This is a test function - replace the e-mail addresses with your own before executing!!
'(CC/BCC can be blank strings, attachments string is optional)

Sub FnTestSafeSendEmail()

    Dim blnSuccessful As Boolean
    Dim strHTML As String
    strHTML = "<html>" & _
             "<body>" & _
              "My<b><i>HTML</i></b> message text!" & _
             "</body>"& _
    blnSuccessful =FnSafeSendEmail("myemailaddress@domain.com", _
                                          "My Message Subject", _
    'A more complex example...
    'blnSuccessful =FnSafeSendEmail("myemailaddress@domain.com;secondrecipient@domain.com", _
                                       "My Message Subject", _
                                       strHTML, _
                                       "C:\MyAttachmentFile1.txt; C:\MyAttachmentFile2.txt", _
                                       "cc_recipient@domain.com", _

    If blnSuccessful Then
        MsgBox "E-mail messagesent successfully!"
        MsgBox "Failed to sende-mail!"
    End If

'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
                    strSubject As String,_
                    strMessageBody As String, _
                     Optional strAttachmentPaths As String,_
                     Optional strCC As String,_
                     Optional strBCC As String) As Boolean

    Dim objOutlook As Object ' Note: Mustbe late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If objOutlook Is Nothing Then
        'Outlookisn't already running - create a new instance...
        Set objOutlook =CreateObject("Outlook.Application")
        blnNewInstance = True
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1),0)
       objExplorer.CommandBars.FindControl(, 1695).Execute
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
    End If

    blnSuccessful = objOutlook.FnSendMailSafe(strTo,strCC, strBCC, _
                                                   strSubject, strMessageBody, _
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
    FnSafeSendEmail = blnSuccessful
End Function

MAPI.Session – some samples here and here.  This, however might fail ‘cause you don’t have the right components installed.

This ofteninvolves at least one line of code like this:

Dim mobjSession As MAPI.Session

Which mightfail with: “Compile error: User type not defined”.

Or, if you try instead to late bind the MAPI ActiveX control:

Set objSession =CreateObject("MAPI.SESSION")

it might fail with: “Run time error ‘429’: ActiveX component can’t create object”.

Need to have the right“Microsoft CDO 1.21 Library” which seems to come with Access 2003 – but not2007!  2007 only allows you to choose the “Microsoft CDO for Windows 2000Library”.  But this is the wrong library and doesn’t work.  I’m not sure, but this might also require MS Exchange.

email, split out last name from if you have a first name –

SELECT SalesArt_Combined.FirstName,SalesArt_Combined.LastName, SalesArt_Combined.email,Left([email],InStr([email],"@")-1) AS EmailPart,InStr([EmailPart],[FirstName]) AS WhereFound,IIf([WhereFound],StrConv(Left([EmailPart],InStr([EmailPart],[FirstName])-1),3),'') AS LeftMost,IIf([WhereFound],StrConv(Mid([EmailPart], InStr([EmailPart],[FirstName])+Len([FirstName])),3),'') AS RightMost

FROM SalesArt_Combined

WHERE (((SalesArt_Combined.LastName)='' Or (SalesArt_Combined.LastName) Is Null) AND ((SalesArt_Combined.email) Is NotNull))

ORDER BY SalesArt_Combined.email;

end a function – Exit Function

environment variables

to list ‘em all:

Dim i As Integer
i = 1
While Environ$(i) <> ""
    Debug.Print Environ$(i)
    'Debug.Print Mid(Environ$(i), 1, InStr(1, Environ(i), "=") -1)
    i = i + 1

Refer to the“user profile” as an example:

Dim strHomeDir As String
strHomeDir = Environ("USERPROFILE")
strHomeDir = strHomeDir & "\Documents\"

EOF – end of record test doesn’t work for ADO recordsets –see first record of recordset, determine whether and then compare to Recordset.RecordCount

error, message – err.Description

escape “for” loop – see for loop, escape

event that fires for each record as it is displayed or painted (drawn) - No the is no event that will do that.  There is of course the current event that fires when you move up and down the records in a continuous form.  One idea is to use an expression in a textbox on the continuous form. The expression refers to a function. You pass a field on the form as an argument to the function such that the function uses the argument to update a control.  The idea is that the expression service will call the function as each record is displayed. It stops when the form is 'full' and will start again as you scroll down.  This will generally act as a poor mans record 'paint' event. However what *you*will get may be an image control with wildly half drawn images flickering as itis changes for each record as they display down the screen.  Scrolling back up the screen is the captain chaos department and could produce anything.

exclusive (vs. shared) database – tools, options, advancedtab

exit “for” loop – see forloop, escape

exit a function – Exit Function (pretty simple)

exists, does a form exist yet (is it loaded yet) - see form open?


fields, display all field names and field types for a table

Function ListFields()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Set db =Workspaces(0).Databases(0)
    If Err.Number> 0 Then Exit Function
    Set td =db.TableDefs("Project")
     For i = 1 To td.fields.Count
        Debug.Print td.fields(i- 1).Name & ", " & td.fields(i - 1).Type
End Function

fields, loop through– see recordset, loop throughfields

file suffix, import a file with a suffix that Access doesn’t recognize as a text file – Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions and add the suffix (from here, 2).  Doesn’t seem towork so well…

file, read from into one variable

intFile = FreeFile 'return the next available file number
Open "C:\test.txt" For Input As intFile
lngChars = LOF(intFile)
strMsg = Input(lngChars, intFile)

file, read line by line

newer, better method

Sub Read_text_File()
    ' make sure you add "Microsoft Scripting Runtime" as areference!
    Dim oFSO As New FileSystemObject
    Dim oFS
    oFS = oFSO.OpenTextFile( "C:\Users\joe\Documents\sample.txt")
    Do Until oFS.AtEndOfStream
       sText = oFS.ReadLine
End Sub

older, not-so-good method

DimnFileNum As Integer,sText As String,sNextLine As String,lLineCount As Long
' Get a free file number
nFileNum = FreeFile
' Open Test.txtfor input. App.Path returns the path your app is saved in
Open App.Path & "Test.txt" ForInput As nFileNum
lLineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
    Line Input #nFileNum, sNextLine
    'do something with it
    'add line numbers to it, in this case!

    sNextLine = lLineCount& " " & sNextLine &vbCrLf
    sText = sText & sNextLine
    lLineCount = lLineCount + 1
TextBox1.Text = sText
  ' Close the file
Close nFileNum

file (binary): read, convert to ASCI, search for string –see binaryfile: read, convert to ASCI, search for string

files in a directory, list all – see list all files in a directory

FindFirst causes “Object doesn’t support this property ormethod” - the data type of the RecordSet is ADODB.Recordset, ADODB.Recordset does not support the FindFirst method. Use the Find method.  Also, to get around FindFirst failing on an MS Access form, need to create an ADO clone of the “native” rs:

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[ID] = " & projectID

first record of recordset, determine whether - Me.Recordset.AbsolutePosition.  The AbsolutePage property sets or returns a long value that specifies the page number in the Recordset object.  When you first open a Recordset, the current record pointer will point to the first record and the BOF and EOF properties are False.  If there are no records, the BOF and EOF property are True.  To determine whether you’re at the last record, compare to Me.Recordset.RecordCount.

fixes - see patches

floor – find the integer part of a number – int

focus, force an element on a subform to have

If the focus is currently in some non-subform control on a parent form, and you use SetFocus on a control on a subform of that parent, that subform's focus is indeed set to the control you referenced ... but the parent form's focus is still on the original control.  Not until you set the focus to the subform (on that parent form) will the control that really has the application's focus be the one on the subform.  Therefore, to get the application's focus onto the lowest level subform, you must execute multiple SetFocus calls if necessary to make sure that each of these subform controls is the control that has the focus on its parent form.

focus, which control has – Me.ActiveControl.Name

footer for a sub-report's page doesn't show – and it neverwill!  Use a report footer instead!

footer, conditionally suppress for a report.  Let’s say we have a footer that displays a count or sum and we want to suppress the footer when there’s only one.  On the “On Format” event of the footer, use the following code:

Private Sub ProjectsPerCustomerFooter_Format(Cancel As Integer, FormatCount AsInteger)
   If ProjectsPerCustomerPerMOnth = 1 Then
       Me.PrintSection = False
       Me.MoveLayout = False
       Me.PrintSection = True
       Me.MoveLayout = True
    End If
End Sub

Or, a little more elegantly:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
   Dim bShow As Boolean
   bShow = NZ((Me.txtCount > 1), False)
   Me.PrintSection = bShow
    Me.MoveLayout = bShow
End Sub

Note: this will NOT appear to execute during a regular preview.  This On Format code will only execute on a print preview!

for loop, escape

For j = 1 To 3
    x = InStr(strMsg, ",")
    If x = 0 Then
        Exit For
    End If
    y = 1

Note: In VBproper there’s also a “continue for” clause that will stop processing this record and get you to the next record.  But VBA doesn’t have this. Instead, use a label:

For int i= 1 To 10
    If i = 5 GoTo nextiteration
    ' do some more stuff

force recompile – see recompile, force – /decompile switch

form, automatically open upon start up - Tools, Startup, choose the form to open on startup from there.  See also hide ribbon when access starts

form, how to bind ADO recordsets– see ADO recordsets, howto bind Microsoft Access forms

form open?

SysCmd(acSysCmdGetObjectState, acForm, "yourForm")– check to see if <> 0.  If so, it’s open.  See “older customIsLoaded” function below…

built-in isLoaded function:

Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   SetFrms = Nothing
   Setcp = Nothing
End Function

Older custom IsLoaded function

FunctionIsLoaded(strName As String, Optional lngType AsAcObjectType = acForm) As Boolean
    IsLoaded = (SysCmd(acSysCmdGetObjectState, lngType, strName) <> 0)
End Function

Not a useful function if you are opening multiple instances of your forms. This is unlikely, but if it is the case then you will need to get more clever to work out if a specific instance of a form is opened as every instance of the form will have the same name.

form, pass multiple arguments to – multiple arguments, pass

form record, new, show only new record (suppress existing records on a datasheet form) – see record, new, show only new record (suppress existing records on a datasheet form)

form record, new, make new record show up on top instead of at the bottom of a form in datasheet or continuous forms view – see record, new, make new record show up on top instead of at the bottom of a form in datasheet or continuous forms view

form resize - Access Form Resizer, Version 2.1.  This works great.  The only weird thing is when you open up the database it comes up with a window right away with list of instructions.  As soon as you close the form the whole database is closed.  I haven't found any way to modify the module other than to import into another database and re-export.  I can't even find the form that automatically comes up.

forms, synchronize – see also synchronize two subforms

a way to do without depending on the built in master-slave

form, which is active – ActiveForm

Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
MsgBox "Current form is " & frmCurrentForm.Name

full outer join – see outerjoin, full

function, to find source of– shift-F2

function, end or get out of early – Exit Function

function, return an array from a – see array, return from function


general ledger


DAC –open with shift key, fix links first

generate SQL script for Access tables - see SQL for Access tables, generate

globalvariable, pass to parameterized query - replace the parameter with a function call that will return the value of the global variable:

function SetValue() as variant ' or whatever datatype you want to return
    SetValue = GlobalVariable
End Function

grandchild adding (without | with missing | before) parent on correlated subforms– see correlated subforms, adding grandchild (without | with missing |before) parent

grid –


HierarchicalData Grid in ASP


HasModule, can’t save ‘til set to “no” – After using Access 2010 nifty tool to quickly create a report off a query, it works just fine.  You then decide to add some code – perhaps on the “OnOpen” event to open up a pop-up for users to supply date parameters to the query/stored procedure/function driving the report.  Then you get this complaint:

“This form or report contains changes that are incompatible with the current database format.  The form or report was not saved.  In order to save your changes, you must remove any layouts that have empty cells in them and/or set the HasModule property for the form or report to no”

Which is somewhat misleading.  What to do?  You really want that code in there.  But if you set the HasModule property to “no” like it wants, then it wipes out your code!  I’ve found that deleting the automatically created summary at the bottom in the report footer seems to fix this.  You not only have to get rid of the box itself, but you have to get rid of that shadowy artifact that shows up when you click in that area that links the footer to the detail.  Luckily, so far, I’ve never really needed those report-level summaries.  I guess if I ever really did, then I’d have to stop using the nifty quick-create tool and go back to building my report by hand the old way.

help, extra – make sure you install “vbaxl8.hlp” into“C:\Program Files\Microsoft Office\Office” directory

hexadecimal unprintable characters, replace with empty string

Dim strMsg As String
Dim i As Integer
Dim RegEx As Object
RegEx = CreateObject("vbscript.regexp")
' Create illegal character string
Dim badChars As String
Dim pattern As String
For i = 0 To31
    ' Use ChrW instead ofChr to avoid boxing
    badChars = badChars & ChrW(Index)
'badChars = [\x00-\x1F]
badChars = badChars & ChrW(127)
With RegEx
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    .pattern = pattern
End With
' do something to populate strMsg with data that may include unprintable hex
strMsg = RegEx.Replace(strMsg, "")

hide column in datasheet view,– see column, hide in datasheet view

hide the ribbon when wccess wtarts

Method 1 -

In "newer" versions of Access (2007, 2010), click the "office ball" up at the upper left, "Access Options" toward the bottom of the menu the pops up. To enable or diable various menu components, go down to "Ribbon and Toolbar Options".  By default, all 3 boxes should be checked: "Allow full Menus", "Allow default menu options" and "Allow builtin toolbars".  Unchecking these will render them invisible.

Method 2

By default, Microsoft Office Access 2007 does not provide a method for hiding the Ribbon. This topic describes how to load a customized ribbon that hides all of the built-in tabs. To load the customized ribbon when Access starts, you should store its settings in a table named USysRibbons. The USysRibbons table must be created using specific column names in order for the Ribbon customizations to be implemented.

Method 3

In VBA add the line to HIDE the Office Button and Ribbon ...
DoCmd.ShowToolbar "Ribbon", acToolbarNo
Use ...
DoCmd.ShowToolbar "Ribbon", acToolbarYes
to turn them back on.

Method 4, Similar

The below code will hide ALL menu bars and ALL tool bars.
Ensure that you have a way to unhide the menu bars and tool bars before you hide them! You should place the hide all tool bars routine in your opening splash screen form for it only needs to be run once when the db is first opened.

This will hide all menu bars and tool bars
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

This will unhide all menu bars and tool bars
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = True
Next i

Method 5 - AutoExec macro

Similarly, AutoKeys

hours and minutes elapsed – see elapsed time

how many times specific character appears in a string - Len("x y z 1 2") - Len(replace("x y z1 2"," ","") will tell you 4 spaces in thisstring

Hungarian namingconvention hierarchy and control objects, data types


immediate window, get into – ctl-g

immediate window – edit a module, it’s now an item in the“View” menu

import first time into blank database – make sure “preview”is turned off

import text files, but text files isn’t even one of thechoices available– see .txt fileswon’t import

inner join update – see update inner join

invoice/purchase order template - INPO v1.01: Access 97Invoice/PO - shareware

invisible, make column indatasheet view,– see column,hide in datasheet view

install problems with runtime – see HKEY_CLASSES_ROOT\CLSID\{8CC49940-3146-11CF-97A1-00AA00424A9F}\Localserver32\Default This value points to the path of the Access executable on the machine

IP address, ping – see here


join, outer full – see outer join, full


kick users out – see automatically logout users for DB maintenance


label, associate with text box (which previously was notassociated) –

Highlight the label

Cut it

Highlight the text box


last record, go to in a form

In the Load event procedure of the form:

    If Not Me.NewRecord Then
       RunCommand acCmdRecordsGoToLast
    End If

last occurrence of a character – InStrRev

InStrRev searches the string in reverse order: from the end to the start. It locates the last occurrence of a string within another.

	string2 [, 
	start] [, 

leading characters, remove – see string, remove characters from

legal size paper, change a report to use – see paper size, control

libraries – see also utilities

The Access Web



Roger's Access


line feed – chr$(13) – for both carriage return and line feed: cr =chr$(10) + chr$(13)

link master/child field

Me!subfrmTasks.LinkMasterFields ="employeeID"

Me!subfrmTasks.LinkChildFields ="assigned"

linked table manager - J Street Access Relinker

linked tables - don't try to use ADO. Can only use DAO!

list all files in a directory - make sure you add "Microsoft Scripting Runtime" as a reference

newer, better way

Sub ListFilesInFolder( ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    ' make sure you add "Microsoft Scripting Runtime" as a reference!
    ' lists information about the files in SourceFolder
    ' example: ListFilesInFolder "C:\Users\joe\Documents\ACH\",True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    FSO = New Scripting.FileSystemObject
    SourceFolder =FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
       Debug.Print(FileItem.Path & FileItem.Name)
        ' display file properties
        'Debug.Print FileItem.Path & FileItem.Name
        'Debug.Print FileItem.Size
        'Debug.Print FileItem.Type
        'Debug.Print FileItem.DateCreated
        'Debug.Print FileItem.DateLastAccessed
        'Debug.Print FileItem.DateLastModified
        'Debug.Print FileItem.Attributes
        'Debug.Print FileItem.ShortPath &FileItem.ShortName
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder(SubFolder.Path, True)
        Next SubFolder
    End If
    FileItem = Nothing
    SourceFolder = Nothing
    FSO = Nothing
End Sub

old, not-so-good way

Sub ListFiles()
    ' make sure you add "Microsoft Scripting Runtime" as areference!
    Dim nDirs As Long, nFiles As Long, lSize As Currency
    Dim sDir As String, sSrchString As String
    sDir = "C:/ACH"
    sSrchString = "*.*"
    lSize = FindFile(sDir,sSrchString, nDirs, nFiles)
    MsgBox(Str(nFiles)& " files found in" &Str(nDirs) & _
           " directories", vbInformation)
    MsgBox( "Total Size = " & lSize & " bytes")
End Sub
Private Function FindFile( ByValsFol As String, ByVal sFile As String, _
   ByValnDirs As Long, ByVal nFiles As Long) As Currency
    ' make sure you add "Microsoft Scripting Runtime" as a reference!
    Dim tFld As Folder, fileName As String ', tFil As File
    Dim fso As New FileSystemObject
    Dim fld As Folder
On Error GoTo Catch
    fld = fso.GetFolder(sFol)
    fileName =Dir(fso.BuildPath(fld.Path, sFile), vbNormal Or_
                   vbHidden Or vbSystem OrvbReadOnly)
    While Len(fileName) <> 0
       FindFile = FindFile + FileLen(fso.BuildPath(fld.Path, _
       nFiles = nFiles + 1
       fileName = Dir()  ' Get next file
    End While
    nDirs = nDirs + 1
    If fld.SubFolders.Count > 0 Then
        For Each tFld In fld.SubFolders
            FindFile = FindFile + FindFile(tFld.Path, sFile, nDirs, nFiles)
    End If
    Exit Function
Catch: fileName = ""
        Resume Next
End Function

locking – tools, options, advanced tab

logged on, who is -

general discussion Who's logged in?

UserRoster (Microsoft) or users, see which are in the database for same code in this doc

logon, make it automatic bring up the logon dialog with a default user filled in – c:\windows\system\Wrkgadm.exe

loop, continue – see continue statement

loop through fields – see recordset, loop through fields

loop through records – see recordset, loop through records


machine ID, which machine is using the database right now - code

mail merge, both sides – Say you’re printing 2 columns of 8 rows of post cards onto an 11” x 17” sheet and want to print address on one side and two merged fields in the body of the postcard: “Dear John Smith” and “John, this is the most amazing opportunity…” on the other side.  One way of doing this is to take your list and duplicate it every 8 records, taking care to flip the 1st and 2nd,3rd and 4th, etc. so the columns line up right on the other side.  Here’s code to do this:

Sub duplicate8recordsAtaTime()
    ' Before running, import the Excel into "input" table.  Make sure you have
    ' Access create an autoincrement index field.  We need that when we export 'cause
    ' the order isn't always preserved right when we export and we'll use that to
    ' sort in Excel.  Copy that table (structure only) into "output".
    ' We want to do a mail merge.  Full info on one side where we're putting address.
    ' Only full name and 1st name on 2nd side.  8 postage cards to a sheet.
    ' Takes incoming records and makes a copy of each 8 in turn with only the
    ' full name (field (1)- the 2nd field) and first name (field (2) - the 3rd field)
    ' copied in that 2nd 8
    ' Once you're done,export "output" back into Excel.  You might have to sort the
    ' exported file by thefirst field and then delete that first field, which is
    ' extraneous to themail merge.
    Dim db As DAO.Database
    Dim rstSource As Recordset
    Dim rstTarget As Recordset
    Set db =DBEngine(0)(0)
    ' first dimension isrecord #, 2nd field #.  We don't actually need the 0th field,
    ' but it's easier to declare and discard rather than shifting things over.
    ReDim temp(8, 3)
    ' Starts with the fullsource ...
    Set rstSource = db.OpenRecordset("input")
    ' ... and an emptycopy of the target
    Set rstTarget = db.OpenRecordset("output", dbOpenDynaset)
    k = 0   'our counter to 8
    For i = 0 To rstSource.RecordCount - 1
        k = k + 1
        With rstTarget
             For j = 1 TorstSource.Fields.Count - 1 ' don't care about 1st(j=0) field - autonum
                .Fields(j) = rstSource.Fields(j)
                 ' Stash the 2nd & 3rd fields.  Keep track ofrecord count.  First 8.
                 If j = 1 Or j =2 Then
                    temp(k, j) = rstSource.Fields(j)
                 End If
             Next j
             ' Go ahead and write full information for 1st 8 records
        End With
        'If we've written out 8 records, then write 'em out again but this time
        ' only with the 2nd (Full name) and 3rd (First name) fields
        Ifk = 8 Then
             For l = 1 To 4
                 With rstTarget
                    .Fields(1) = temp(2 * l, 1)
                    .Fields(2) = temp(2 * l, 2)
                 End With
                 With rstTarget
                    .Fields(1) = temp(2 * l - 1, 1)
                    .Fields(2) = temp(2 * l - 1, 2)
                 End With
             Next l
            k = 0   ' clear our counter to start allover again with the next 8
        'Just a sanity check.  Not really necessary for the program to work
        Ifi Mod 1000 = 0 Then
             Debug.Print i   'let us know each 1000 records processed
    Next i
End Sub

master/child field, link – see link master/child field

maximum records returned, default– see records, default maximum returned

.mdw (security), change default – c:\windows\system\Wrkgadm.exe

menu, hide on startup - see hide ribbon when access starts


Mid (text, start_position, number_of_characters)

middle name, split out along with 1st and last names

Function SplitFirstMiddleLast( ByVal strComingIn As String, ByValstrWhich As String) As String
    howManyBlanks = Len(strComingIn) -Len(Replace(Trim(strComingIn), " ", ""))
    ' First Name
    If strWhich = "First" Then
        If howManyBlanks = 0 Then
            SplitFirstMiddleLast = strComingIn
        ElseIf howManyBlanks > 0 Then
            SplitFirstMiddleLast = Left(strComingIn, InStr(strComingIn, " ") - 1)
        End If
        'Middle name
    ElseIf strWhich= "Middle" Then
        If howManyBlanks = 0 Or howManyBlanks = 1 Then
            SplitFirstMiddleLast = ""
        ElseIf howManyBlanks > 0 Then
             ' what if we have a suffix like Jr or Sr?
             If Len(strComingIn) - InStrRev(strComingIn, "Jr") < 3 _
             Or Len(strComingIn) - InStrRev(strComingIn, "Sr") < 3 Then
                SplitFirstMiddleLast = _
                    InStr(strComingIn, " ") + 1, _
                    fFindNthLastOccur(strComingIn, " ",2) - InStr(strComingIn, " "))
                SplitFirstMiddleLast = _
                    InStr(strComingIn, " ") + 1, _
                    InStrRev(strComingIn, " ") - InStr(strComingIn, " "))
             End If
        End If
        ' Last Name
    ElseIf strWhich= "Last" Then
        If howManyBlanks = 0 Then
            SplitFirstMiddleLast = ""
        ElseIf howManyBlanks > 0 Then
             ' what if we have a suffix like Jr or Sr?
             If Len(strComingIn) - InStrRev(strComingIn, "Jr") < 3 _
             Or Len(strComingIn) - InStrRev(strComingIn, "Sr") < 3 Then
                SplitFirstMiddleLast = _
                    Mid(strComingIn, _
                    fFindNthLastOccur(strComingIn, " ",2) + 1, _
                    Len(strComingIn) - fFindNthLastOccur(strComingIn, "", 2))
                SplitFirstMiddleLast = Mid(strComingIn, InStrRev(strComingIn, " ") + 1)
             End If
        End If
        SplitFirstMiddleLast = "bad choice: please specify 'First', 'Middle' or'Last'"
    End If
End Function

minutes and hours elapsed – see elapsed time

modulo or modulus – Mod

Dim MyResult
MyResult = 10 Mod 5    ' Returns 0.
MyResult = 10 Mod3    ' Returns 1.

months elapsed – see elapsedmonths

month, subtract - DateAdd("m",-1,[TransDate]) where “TransDate”is an input field

most recent record for an ID– see also subselect (especially ifyou don’t want Access-specific syntax, such as when you’re connecting to SQLServer)

SELECT Orders.ID,Orders.OrderDate

FROM Orders

WHERE(((Orders.OrderDate)=DMax("OrderDate","Orders","ID ='" & ID & "'")));

assuming "ID" is character.  Otherwise, noextra "'".

msadox.dll - How to use a TypeLib(TLB) file to avoid a broken reference to the ADOX library (msadox.dll) – from here:

Symtom: A VB or MS-Access application has been developed on a computer with MDAC 2.7 (or newer) installed. When this application is run on a computer with an older version of MDAC (e.g. 2.5) installed, the reference to msadox.dll ("Microsoft ADO Ext. 2.7 for DLL and Security") is broken.

Cause: The ADOX libraries (msadox.dll) of different versions of MDAC have the same GUID ({00000600-0000-0010-8000-00AA006D2EA4}) but different interface version numbers. For the ADO library, the solution is to use the 2.5 TypeLib (msado25.tlb) in the references instead of the DLL (e.g.msado15.dll) (refer to MS-KB Q259379). But for the ADOX library, there is no TypeLibavailable from Microsoft.

Solution: The "TYPELIB" resource data from an old ADOX DLL can be extracted into a File (using a tool like EZ ExtractResource (use the "View" button to select the DLL and extract the TYPELIB resource)). When this type library file (e.g. msadox25.tlb) is used in the references of the application instead of the new DLL(msadox.dll), the application can be developed on a system with a new MDAC and is compatible with systems that have an older MDAC.

Note: If you had an old version of ADO (e.g. 2.5) installed on your computer before the new version was installed, it may not bepossible to add the typelib file of the old version (msadox25.tlb) to your VBproject references, because an entry with that name ("Microsoft ADO Ext.2.5 for DDL and Security") is already in the list and points to the new DLL (msadox.dll). To solve this problem, delete the old entry in the Windowsregistry (using regedit.exe). For 2.5 (msadox25.tlb) the following registry key must be deleted:"HKEY_CLASSES_ROOT\TypeLib\{00000600-0000-0010-8000-00AA006D2EA4}\2.5"

Downloadable files:
  msadox25.tlb (for compatibility with Windows 2000 or newer (>= ADO 2.5))
  msadox21.tlb (for compatibility with NT4, Office 2000 or newer (>= ADO 2.1))

MSysCompactError - download the JetCompact Utility

multiple arguments, pass (especially between forms)

First of all, you can cheat, eh?  Just use a global variable (or several) you declare in one of your modules.  Yes, I know.  This is frowned upon.  But it's fast and it works, dammit!

Second way - just use the DoCmd.OpenForm's 7th argument: "OpenArgs".  Here's a quick recap of  all 7 of DoCmd.OpenForm's 7 arguments:

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

But this really works best with just one argument...?

Third way - for a form, at least: Declare a variable in the form.

1. start by declaring a public variable up at the top of the module for the form, right under the "Option Compare Database".  So in our example, we put the code below in our "frmFaxConfirm" form where we want to know which form this was called from:

Option Compare Database
pvarCalledFrom As Variant

2. Specify the "frmFaxConfirm" form's variable in the "Private Sub cmdFaxConfirm_Click()" event's code in our "frmDeal" calling form:

Form_frmFaxConfirm.pvarCalledFrom = Me.Name

The last way uses a collection.  Seems ungainly to me.

1. fill an heterogeneous collection (the default kind ofcollection, in VB/VBA) with the appropriate arguments (just before you open theform):

Dim UserCol As New Collection
UserCol.Add 22,"FirstArgumentKey"
UserCol.Add Now(),"SecondKey"
UserCol.AddCurrentDb.OpenRecordset("Table1"), "ThirdArgument"

2. append that collection to a global collection.  Ina standard module, declaration section:

Public gCollection As New Collection

just before you open the form, following lines in step 1:

gCollection.Add UserCol,"SomeKey"

3. and to pass the key just used, as a string, for theOpenArgs argument.

DoCmd.OpenForm, ... ,OpenArgs:="SomeKey"

4. To read back, in the form open event, as example, just access the item specified by the OpenArgs of the global collection:

Dim sram = OpenArgs
Dim tmpCol AstrParam As String
strPa Collection

Set tmpCol= gCollection.Item(strParam)

5. and get the required arguments:

Dim mFirst As Long : mFirst =tmpCol("FirstArgument"Key)
Dim mSecond As Date : mSecond = tmpCol.Item(2)
Dim mThird As WhateverObjectStuff : Set mThird = tmpCol(3)

6. clean the global collection, removing the key item (probably best in the calling program if the form is opened in modal mode).

gCollection.Remove strParam

multiple lines, combine into one – see address parse example

“My Documents” location

Private Type SHITEMID
   cb As Long
   abID As Byte
End Type
    mkid As SHITEMID
End Type
Private ConstCSIDL_PERSONAL As Long= &H5
Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll"_
                         ( ByVal hwndOwner As Long, ByValnFolder As Long,_
                           ByVal pidl AsITEMIDLIST) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA"_
                         ( ByVal pidl As Long, ByVal pszPath As String) As Long
Public Function Rep_Documents() As String
    Dim lRet As Long, IDL As ITEMIDLIST,sPath As String
    lRet = SHGetSpecialFolderLocation(100&, CSIDL_PERSONAL, IDL)
    If lRet = 0 Then
        sPath = String$(512,Chr$(0))
        lRet =SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal sPath)
        Rep_Documents =Left$(sPath, InStr(sPath, Chr$(0)) - 1)
        Rep_Documents =vbNullString
    End If
End Function
'To call the function, simply create a  button and paste in the following code:
Private Sub CommandButton1_Click()
   Cells(5, 2) = Rep_Documents()
End Sub


naming convention – see Hungarian naming convention

next n after the top n -


FROM [select top 10 ID from

(select top 20 ID from employee order by ID asc)AS F

order by ID desc]. AS e1


new record, detect whether you’re at

If Me.NewRecord Then
     ' code here
End If

new record, force a form to go to when opening

Easiest way is to set the form's Data Entry property to Yes. That will cause  the form to go to a new record each time you open the form.  Alternatively, you can open the form and give an argument in the OpenForm command:

DoCmd.OpenForm "FormName", , , , acFormAdd

newly created parent record, save before trying to addchildren – see parent, save newly created parent record before trying to add children

newest record - see most recent

Novell network



nth instance of a pattern in a string

Public Function fFindNthOccur( ByVal pStr As String, _
                                   ByVal pFind As String, _
                                   ByVal pNth As Integer) As Integer
    ' Purpose:  Return location of nth occurrence of item in a string.
    ' Arguments: pStr: Thestring to be searched.
    '            pFind: The item to search for.
    '            pNth:  The occurrence of the item in string.
    'Input:     From the debug (immediate) window:
    '            x = "The quick brown fox jumped over the lazy dog"
    '            1) ? fFindNthOccur(x, " ", 3)
    '            2) ? left(x, fFindNthOccur(x, " ", 3))
    '            3) ? mid(x, fFindNthOccur(x, " ", 3)+1)
    'Output:    1) 16
    '            2) The quick brown
    '            3) fox jumped over the lazy dog
    Dim strHold As String
    Dim strFind As String
    Dim intHold As Integer
    Dim intSay As Integer
    Dim intKeep As Integer
    Dim n As Integer
    strHold = pStr
    strFind = pFind
    intHold = pNth
    intKeep = 0
    n = 0
    Do While n < intHold
        If InStr(strHold, strFind) = 0 Then
            fFindNthOccur = 0
             Exit Do
            intSay = InStr(1, strHold, strFind)
            intKeep = intKeep + intSay
            n = n + 1
            strHold = Mid(strHold, intSay + Len(strFind))
            fFindNthOccur = intKeep
        End If
End Function

nth last instance of a pattern in a string

Public Function fFindNthLastOccur( ByVal pStr As String, _
                                       ByVal pFind As String, _
                                       ByVal pNth As Integer) As Integer
    ' Purpose:  Return location of nth last occurrence of item in a string.
    ' Arguments: pStr: Thestring to be searched.
    '            pFind: The item to search for.
    '            pNth:  The occurrence of the item in string.
    'Input:     From the debug (immediate) window:
    '            x = "The quick brown fox jumped over the lazy dog"
    '            1) ? fFindNthLastOccur(x, " ", 3)
    '            2) ? left(x, fFindNthLastOccur(x, " ", 3))
    '            3) ? mid(x, fFindNthLastOccur(x, " ", 3)+1)
    'Output:    1) 32
    '            2) The quick brown fox jumped over
    '            3) the lazy dog

    Dim strHold As String
    Dim strFind As String
    Dim intHold As Integer
    Dim intSay As Integer
    Dim intKeep As Integer
    Dim n As Integer
    strHold = pStr
    strFind = pFind
    intHold = pNth
    intKeep = Len(strHold)
    n = 0
    Do While n < intHold
        IfInStrRev(strHold, strFind) = 0 Then
            fFindNthLastOccur = 0
             Exit Do
            intSay = InStrRev(strHold, strFind)
            n = n + 1
            strHold = Left(strHold, intSay - 1)
            fFindNthLastOccur = intSay
        End If
End Function

null, can’t test for because it gives object qualifier error– use Nz function

null, problems passing as an argument to a function – see optional arguments

number of unique values

select count(*) from

    (select distinct field1 from TableName)

numbering in SQL – see rank in sequence

numbering rows in a report – use a calculated control and the RunningSum property.  First, create a text box and set its ControlSource property to =1.  Then set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All.  The expression sets the value of the text box control to 1. Because the RunningSum property is used to accumulate the value, the text box is increased by one for every row. Entering a period (.) in the Format property box appends a period to the end of the number.


object qualifier error when testing for null – use Nzfunction

odbc connection fails – SQL Server Error 10060

Connection failed:

SQLState: '01000'

SQL Server Error: 10060

[Microsoft][ODBC SQL ServerDriver][DBNETLIB]ConnectionOpen


Connection Failed:

SQL State: '08001'

SQL Server Error: 11

[Microsoft][ODBC SQL ServerDriver][DBNETLIB]General network error.  Check your network documentation.

Try pinging the server in question

1. If it’s intermittent, address that network problem

2. If the serverreturns the wrong IP address, try restarting the DNS client service

odbc, test - osql -E -S yourservername – this should return a “1>” prompt which you can exit by typing in “exit”

old value of a form element before it changes – see previous value of a form element before it changes

open database, can’t some hints

open files

vba code

open, is a table open? -- ? SysCmd(acSysCmdGetObjectState,acTable, "tblClients")

open, is a form open - see formopen?

open an Access table from an outside application (likeOutlook) – see Access table, open from an outside application

operating system, which version?

First, declare these up at the top of your module:

   dwOSVersionInfoSize As Long
   dwMajorVersion As Long
   dwMinorVersion As Long
   dwBuildNumber As Long
   dwPlatformId As Long
   szCSDVersion As String * 128
End Type
Public DeclareFunction GetVersionExA Lib "kernel32" _
            (lpVersionInformation As OSVERSIONINFO) As Integer

Then you can invoke them here:

Public Function getVersion() As String
    Dim osinfo As OSVERSIONINFO
    Dim retvalue As Integer
   osinfo.dwOSVersionInfoSize = 148
    osinfo.szCSDVersion =Space$(128)
    retvalue =GetVersionExA(osinfo)
     With osinfo
        Select Case.dwPlatformId
             Case 1
                 Select Case.dwMinorVersion
                     Case 0
                         getVersion = "Windows 95"
                     Case 10
                         getVersion = "Windows 98"
                     Case 90
                         getVersion = "Windows Millennium"
                 End Select
             Case 2
                 Select Case.dwMajorVersion
                     Case 3
                         getVersion = "Windows NT 3.51"
                     Case 4
                         getVersion = "Windows NT 4.0"
                     Case 5
                          If .dwMinorVersion = 0 Then
                             getVersion = "Windows 2000"
                             getVersion = "Windows XP"
                          End If
                     Case 6
                         getVersion = "Vista"
                     Case Else
                         getVersion = "something new we don't know about"
                 End Select
             Case Else
                getVersion = "Failed"
        End Select
    End With
End Function

optional arguments

Only Variants can contain the value Missing. If you assign any other type to an optional parameter it will be initialized with a value (0 for numbers, "" for strings, etc). When you test if the optional argument is Missing, Access sees it has a value. As a result, IsMissing() returns False, even if the parameter was not supplied by the user.

For example, the simple function below intends to return True if the optional argument is missing. But declaring the argument as a Boolean initializes it to False. The test for IsMissing() then fails, and the function returns False as the default!

Function TrueAsDefault( Optional bIsTrue As Boolean) As Boolean
    If IsMissing (bIsTrue) Then
        'bIsTrue is *never* Missing!!!!
       bIsTrue = True
    End If
    TrueAsDefault = bIsTrue
End Function


1. Use Variantsfor optional parameters, or

2. Supply thedefault value in the function declaration, e.g.:

Function TrueAsDefault (Optional bIsTrue As Boolean= True) AsBoolean
   TrueAsDefault = bIsTrue
End Function

Hint: With Variant arguments, your procedure must test the type of the data passed in. Don't test for all the wrong types:

If IsMissing(MyParm) Or IsNull(MyParm) Or IsError(MyParm) Or ...

Instead, test for the desired type: IsDate(),IsNumeric() etc. The example above (corrected) becomes:

Function TrueAsDefault( Optional bvIsTrue As Variant) As Boolean
    If Not IsNumeric(bvIsTrue) Then
       bvIsTrue = True    'Default if Missing, Null, Error, invalid type.
    End If
    TrueAsDefault = bvIsTrue
End Function

order numbers, add – see rank in sequence

out of stack space window pops up - click "Debug" button in that window, hit "alt-L" or go into "View", "Call stack".  There's a good chance you'll see one function called way too many times.

outer join, full

Example full outer join (ANSI 92 standard syntax):

       ON employee .DepartmentID = department .DepartmentID
| LastName  | DepartmentID |DepartmentName | DepartmentID |
| Smith    |           34 |Clerical      |           34 |
| Jones    |           33 |Engineering   |           33 |
| Robinson |           34 |Clerical      |           34 |
| Jasper   |           36 |NULL           |         NULL |
| Steinberg|           33 |Engineering   |           33 |
| Rafferty |           31 |Sales         |           31 |
| NULL     |         NULL |Marketing     |           35 |

The same example, for use on databases that do not supportFULL OUTER JOIN:

     employee .LastName ,
     employee .DepartmentID , 
     department .DepartmentName ,
     department .DepartmentID
       ON employee .DepartmentID = department .DepartmentID
     employee .LastName ,
     employee .DepartmentID , 
     department .DepartmentName ,
     department .DepartmentID
       ON employee .DepartmentID = department .DepartmentID
       WHERE employee .DepartmentID IS NULL

Outlook contacts, link to an Access database – see Link Outlook contacts to an Access database


pad with spacesRight(Space$(1)& Month([MAILED]),2)

pad with zeroes -

1. Simplest

strBlockCount2 = String(6 -Len(strBlockCount), "0") & strBlockCount

2. If you want a function

Public Function Pad(Txt As Variant, HowManyZeroes As Byte) As String
'Function to pad out text with "0"
'Syntax : Pad("String", end length of sting)
'example:    : Pad("23",5) returns "00023"

Pad = String(HowManyZeroes - Len(Txt), "0") & Txt
End Function

3. If you’re dealing with a field in a table, do a custom format.  In the design view of table, enter 4 0s in the format section

page footer doesn't show in sub-report – and it never will!  Use a report footer instead!

paper size, control – here’s how to do it programmatically.  Not sure how to do it manually!  Don’t try to put this code directly in the “OnOpen” event of the report ‘cause part of what this code does is to open the report in design view.  It’ll croak if you’re already in the report trying to execute it when the codes tries to open the same report in design view.  I normally call this just once – with the name of the report in quotes – to change it to legal size.

' The following 2 types are used by SwitchtoLegal which changes a report's paper size from default
' letter to legal size

Type gtypStr_DEVMODE
    RGB As String * 94
End Type
Type gType_DEVMODE
    strDeviceName As String * 16
    intSpecVersion A Integer
    intDriverVersion As Integer
    intSize As Integer
    intDriverExtra As Integer
    lngFields As Long
    intOrientation As Integer
    intPaperSize As Integer
    intPaperLength As Integer
    intPaperWidth As Integer
    intScale As Integer
    intCopies As Integer
    intDefaultSource As Integer
    intPrintQuality As Integer
    intColor As Integer
    intDuplex As Integer
    intResolution As Integer
    intTTOption As Integer
    intCollate As Integer
    StrFormName As String * 16
    lngPad As Long
    lngBits As Long
    lngPW As Long
    lngPH As Long
    lngDFI As Long
    lngDFr As Long
End Type
Sub SwitchtoLegal( ByVal strName As String)
    ' Comments : Switches paper size to legal, regardless of original setting
    ' Parameters : strName- contains the name of the report
    ' Returns : Savesreport with new legal setting. User needs mod perm
    ' Created : Receivedfrom Brian Ward, 3/12/1999
    ' Modified : KimJacobson 3/12/1999
    'Call the procedurelike this:
    ' Dim strDocName AsString
    ' strDocName ="Report Name Here"
    ' SwitchtoLegal(strDocName)

    On Error GoTo Err_SwitchtoLegal
    Dim DevString As gtypStr_DEVMODE
    Dim DM As gType_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report
    DoCmd.Echo(False, "Checking default printer settings...")
    DoCmd.OpenReport(strName, acDesign) ' Opens report in Designview.
    rpt = Reports(strName)
    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra =rpt.PrtDevMode
        DevString.RGB =strDevModeExtra
        LSet(DM = DevString)
        DM.intPaperSize = 5 'set to legal, standard would be 1
        'DM.lngFields= DM.lngFields Or DM.intOrientation ' Initializefields.
        LSet(DevString = DM) ' Update property.
        Mid(strDevModeExtra, 1,94) = DevString.RGB
        rpt.PrtDevMode =strDevModeExtra
    End If
    DoCmd.Save(acReport, strName)
    DoCmd.Close(acReport, strName)
    DoCmd.Echo(True, "")
    Exit Sub
    DoCmd.Echo( True, "")
    Select Case Err
        Case Else
            MsgBox(Err & ":" & Err.Description, vbInformation + vbOKOnly,"SwitchtoLegal")
             Resume Exit_SwitchtoLegal
    End Select
End Sub

parameter for a function – see also optional arguments

parameter, pass variable to query – see also global variable,pass to parameterized query

pass a variable to a query is by means of a function that returns the variable, like this:

SELECT title_id, price, price * FetchPricePct() AS Adj_Price FROM titles;

In this particular demo, the method FetchPricePct() returns the value of a public variable.  In order for this to fit into your code paradigm, you will need to have a function that also sets this public variable to some value.  Alternatively, you could put a reference directly to a control on a form, like this ...

SELECT title_id, price, price * Forms!frmMain!txtPct AS Adj_Price FROM titles;

However, this creates a problem if the form is not open, or if the text box is empty, or if it doesn't contain a number.  You could, of course, write inline code to handle those exceptions in the query, but it gets kind of crowded ...

title_id, price, price *,
        Nz(Forms!frmMain!txtPct,1.1), 1.1) AS Adj_Price FROM titles;

It works, but it's not pretty.  There is one problem with this paradigm:  Access queries called from ASP web pages cannot resolve the source of the function.  They appear undefined to the ADODB layer and the query fails.  We need a table with a field that contains the price increase value.  Join this table to Titles table to replace the function with a simple field value.  However, on what column to join them?  No column, the answer turns out to be.  Create a Cartesian Product of the two tables by referencing both but assigning no kind of join.  The SQL for this solution looks like this:

SELECT title_id, price, price *Pct_Increase AS Adj_Price

FROM titles, PriceVariable;

There is a caveat: the tblPriceVariable table may have only one row.  If, for example, it contained two rows, then two records would be generated for every row in the Titles table.  Notice the right-most query result pane in the Figure 1 below and you will see what I mean.  Another thing you may notice from the image is that the tables using the Cartesian Product do not expose an "Add Record" line and the AddRecord button is grayed out.  Because of the nature of the join, this recordset is not updateable.

parameterized query for SQL Server – see stored procedures in SQL Server with parameters

parameterized query, create

you can specify it directly in the sql:

[forms]![frmDeal]![txtDealID] Long;
SELECT t.DealID, t.TransactionID, t.HouseSeller, t.HouseBuyer, p.SellBuy, qryDeal.DealDate,
 t.ProductCategory, t.Pipeline, t.ProductGrade, t.Price, t.TriggerDif, t.MercOverUnder,
 t.MercMonth, t.WhichMerc, t.SubjectToCredit, t.WireFunds, t.WireDays, t.PrePay, t.SaleSubjectTo,
 t.SaleParty1, t.SaleFromTo, t.SaleParty2, p.SellBuy, Trim(cn.FirstName) & " " & cn.LastName AS TraderName,
 t.TransType, p.TraderID, p.BrokerID, p.Commission, Trim(b.FirstName) & " " & b.LastName AS BrokerName,
 qryCompany.Name AS CompName, t.SpecificNotes
FROM ((((qryTransaction as t LEFT JOIN qryTransPlayer as p ON t.TransactionID = p.TransactionID)
 LEFT JOIN qryContact as cn ON p.TraderID = cn.ContactID) LEFT JOIN qryBroker as b ON p.BrokerID = b.BrokerID)
 INNER JOIN qryDeal ON t.DealID = qryDeal.DealID) LEFT JOIN qryCompany ON cn.CompanyID = qryCompany.CompanyID
WHERE (((t.DealID)=GetQueryID()))
ORDER BY t.DealID, t.TransactionID, p.SellBuy DESC

or you can specify it by right clicking in the design view of the query and selecting "Parameters..." from the pop-up list.

parameterized query, set default value - Nz([Enter BeginningDate],#1/1/06#)

parameterized reports – (see also multiple arguments, pass) how to get data from the user before starting a report (if you're using a SQL Server stored procedure, see the report section of  stored procedures in SQL Server with parameters)

1) This is the simple, not-so-elegant technique:  In the query behind the report, go to the column which contains an expression which you wish to limit.  For example:  suppose you wish to limit the query to a certain year:  Create a column with the expression: “TheYear: Year([EvalDate])”

Insert a prompt within square brackets in the “Criteria” field in this column.

For example:  “[Please enter the year in question:]”

The downside of this approach is that a simple small window, with a title of “Enter ParameterValue” appears.

2) This technique gives you the ability to show a slightly better looking window that asks the user for a value

In the “OnOpen” event of the report, establish a routine that asks the user for the value, and then modifies the “RecordSource” property on the fly.  Here is an example

Private Sub Report_Open(Cancel As Integer)

Dim iTheYear

iTheYear = InputBox(" Please enter the year to view Key Associates.", _

     "HR Horizons", Format(Date, "yyyy"))

Me.RecordSource = &_


"HAVING ((AND ((Year([EvalDate]))="& iTheYear & "));"

End Sub

3) This technique, where you can design you own custom form to ask for the value: is the most flexible, but most time-consuming technique:

You can use create a dialog box form named Customer Info with a control for the CustomerID field. By entering a customer ID in the dialog box, you could have the query return the correct custom dialog box to enter criteria for the query underlying a form or report. For example, a custom dialog box can determine what records a printed report includes.

  1. Create or open the query that will collect its criteria values from the dialog box.
  2. Make sure that the query includes the tables you want to use.
  3. Add to the query design grid the fields associated with each of the dialog box controls where you'll enter criteria values. For each of these fields, enter an expression in the Criteria cell that tells Microsoft Access to refer to the control on the dialog box for the criteria values. If you need help creating the expression, use the Expression Builder. For more information, click
  4. Add to the query design grid the fields whose values you want returned.

For example,you could crtomer's company name, address, and city. To create this query, you'd do the following:

Add the CustomerID field to the query design grid. In its Criteria cell, enter the expression that tells Microsoft Access to refer to the Customer ID control in the dialog box for the criteria values the query uses. In this case, you'd enter the expression Forms![Customer Info]![CustomerID].

Add to the query design grid the fields whose values you want returned based on the CustomerID value: CompanyName, Address, and City fields.

4) To pass a global variable - see global variable, pass to parameterized query

parent, save newly created parent record before trying to add children

If Me.NewRecord Then
Me.Dirty = False
End If

pass multiple arguments- see multiple arguments, pass


MicrosoftAccess Fixes, Patches and Updates


Public Sub Pause( ByVal pSng_Secs As Single)
    'Wait for the numberof seconds given by pSng_Secs
    Dim lSng_Start As Single
    Dim lSng_End As Single
    On Error GoTo Err_Pause
    lSng_Start = Timer
    lSng_End = Timer + pSng_Secs
    Do While Timer < lSng_End
        ''Correction if the timer moves over to a new day (midnight)
        ''86400-num of secs in a day
        IfTimer < lSng_Start Then lSng_End = lSng_End- 86400
    Exit Sub
End Sub

performance is slow – see slow performance

phone number, convert - see convert phone number


ping IP address – see here

pivot chart


DoCmd.OpenForm stDocName, acFormPivotChart, ,stLinkCriteria

presence of form, detect - see form open?

prevent ability to add new records in a form – see record, new, suppress or prevent in a form

prevent existing records from showing up on a datasheet form– see record, new, show only new record (suppress existing records on a datasheet form)

previous value of a form element before it changes - Me!PAID.OldValue.  You also need to have any references to .OldValue in the BeforeUpdate event rather than in the AfterUpdate event

print preview shows blank screen – this is usually due to not having a default printer or specifying a default printer that no longer exists or is now invalid.

print vertically – see rotatetext

proper case – from here:

Function ProperCase(strOneLine As String,intChangeType As Integer) As String
    '- This function willconvert a string to ProperCase           -
    '- The initial letterof each word iscapitalised.              -
    '- It will also handlespecial names such as O', Mc and         -
    '- hyphenatednames                                               -
    '- if intChangeType =1, all text is converted to proper case. -
    '- e.g. 'FRED' isconverted to'Fred'                           -
    '- if intChangeType =0, upper case text is not converted.     -
    '- e.g. 'fred' becomes'Fred', but 'FRED' remains unchanged.   -
    Dim I As Integer
    Dim bChangeFlag As Boolean
    Dim strResult As String
    '- No characters instring - nothing todo                -
    If Len(strOneLine) = 0 Then
        ProperCase = ""
        Exit Function
    End If
    '- Always set firstletter to uppercase                  -
    strResult = UCase$(Left$(strOneLine, 1))
    '- Now look at the rest of thestring                      -
    For I = 2 To Len(strOneLine)
        '-If the previous letter triggered a capital, change     -
        '-this letter to uppercase                               -
        If bChangeFlag = True Then
            strResult = strResult & UCase$(Mid$(strOneLine, I, 1))
            bChangeFlag = False
             '- In other cases change letter to lower case if required -
             If intChangeType = 1 Then
                strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
                strResult = strResult & Mid$(strOneLine, I, 1)
             End If
        End If
        '-Set change flag if a space, apostrophe or hyphen found -
        Select Case Mid$(strOneLine, I, 1)
             Case " ", "'", "-"
                bChangeFlag = True
             Case Else
                bChangeFlag = False
         End Select
    Next I
    '- Special handlingfor Mc at start of a name              -
    If Left$(strResult, 2) = "Mc" Then
        Mid$(strResult, 3, 1) =UCase$(Mid$(strResult, 3, 1))
    End If
    I = InStr(strResult, "Mc")
    If I > 0 Then
        Mid$(strResult, I + 3,1) = UCase$(Mid$(strResult, I + 3, 1))
    End If
    ProperCase = strResult
End Function


queries, list

SELECT Name, Type

FROM MSysObjects

WHERE Type =5;

query by form download

query, parameterized, set default– see parameterized query,set default value

query, run from a command button - DoCmd.OpenQuery"qryMyQuery"


If you want to be able to maneuver through a recordset that was the result of an executed Query, you must code like the following:

Dim qd as querydef, rs asrecordset
Set qd =ldb.QueryDefs(“Your query”)
qd.Parameters("MyParam") =10           'If you need to pass a parameter
qd.Execute                                'Fire up that query
Set rs =qd.OpenRecordset()           'allow recordset object "rs" to point to the returned recordset
'now you can dothings like:
Ifrs.RecordCount > 0 then msgbox "Yep –it's got records alright"


If a string contains a single quote, put it inside double quotes.

If a string contains a double quote, put it inside single quotes.

If is string contains both, you can use the Chr() function to generate (asc() does the inverse of chr())

whichever kind of quote you used outside, like:

  this is the " in a string

can be represented with:

  " this is the " & Chr(34) &" in a string"


rank in sequence – see also How to dynamically number rows in a SELECT Transact-SQL statement

select rank=count(*), t1.timeStamp

from timesheet t1, timesheet t2

where t1.timeStamp>= t2.timeStamp

group by t1.timeStamp

order by 1

read all files in a directory – see list all files in a directory

recompile, force – /decompile switch - see also decompile

With Access, sometimes even when you recompile, Access doesn't REALLY recompile everything.  You have to forcibly "decompile" everything and then recompile.  We do this with the/decompile "switch".  The easiest way is to run “msaccess/decompile” from the “Start/Run” menu.  Otherwise, here's a more complicated way how to do this which doesn’t seem to work so well now in ME.

  1. Do you have anicon set up?  If so, view the properties by right-clicking on the icon.  If not, it's probably best to make one just for this experiment.  Or, you can modify the properties of the start menu.  But I'll just stick with the icon for now.
  2. Looking at theproperties will bring up a pop-up form with two tabs: "General" and"Shortcut".  Click the "Shortcut" tab.  Look inthe "target" area on this form.
  3. It should read something like:

"C:\Program Files\MicrosoftOffice\OFFICE11\MSACCESS.EXE " /wrkgrp "C:\ProgramFiles\Horizons97\CM\HrzSys32.mdw" "C:\ProgramFiles\Horizons97\CM\hrzcm32.mdb"

Change it to:

"C:\ProgramFiles\Microsoft Office\OFFICE11\MSACCESS.EXE " /decompile /wrkgrp"C:\Program Files\Horizons97\CM\HrzSys32.mdw" "C:\ProgramFiles\Horizons97\CM\hrzcm32.mdb"

where what was added was the word "/decompile" right before "/wrkgrp".

recompile, normal

Open by holding down the “Shift” key when starting up the system.  (It’s only important to hold down the “Shift” key after the logon box comes up if you have security turned on.)

Access 97 should come up.  Goto the “Modules” tab of the main tabbed dialog box.  Highlight ANY module.  Click the “Design” button.  We do this so we get a new menu which doesn’t show until we do this.  In this new menu, go to “Tools/References…”.  In the pop-up dialog box, make sure all the libraries you expect arethere.  One to always look for is the “Microsoft DAO 3.51 Object Library” entry.  Check to see if it’s registered.

Whether or not it’s registered, it should be checked.  Whether or not their checked, we’re going to uncheck at least one of them – say the “Microsoft DAO 3.51 Object Library”.  Close the pop-up list, open again by again going to “Tools/References…”, and re-check.

We do this so that the “Compile and Save All Modules” sub-menu item under the “Debug” on the main menu become sun-greyed.  Go under “Debug/ Compile and Save All Modules” and click this.

Once this is complete, close outof the module you opened.

record locking – tools, options, advanced tab

record, new, show only new record (suppress existing records on a datasheet form)

To suppress the display of existing records, toggle the form's DataEntry property. From the main form, that would be:

    With Me.[NameOfYourSubformControlHere].Form
     .DataEntry = Not.DataEntry
   End With

record, new, make new record show up on top instead of at the bottom of a form in datasheet or continuous forms view

You can use TWO continuous subforms. The top one would have its DataEntry property set to True so that it shows either blank fields or the record currently being entered; the lower one would have AllowAdditions set to false so that it shows *only* the existing records. If you carefully position the two subforms, it can be made to look like one subform with the data entry at the top.

record, new, suppress or prevent in a form – in the data properties, change “Allow Additions” from “yes” to “no”

record save, force – Me.Dirty = True

records, default maximum returned – Office Button, Access Options, Advanced, Advanced, Default Max Records normally defaults to 10000

recordset count - ? Me.Recordset.Recordcount

recordset, loop through fields

For numFields = 0 To rs.Fields.Count - 1
    Debug.print “field count” & numFields

recordset, loop throughrecords

with rs
    do while not.eof
        'dosomething in here.
end with


Public Sub loop1()
    'Set cn = CurrentProject.AccessConnection
    strSQL = "SELECT * FROMCPAMailingListKatzRaw"
    'open the resultsread-only
    Set rst =CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.RecordCount> 0 Then
    Do While Not rst.EOF
        'dosomething in here.
    End If
End Sub

recordsets, how to bind ADO recordsets to forms – see ADO recordsets, how to bind Microsoft Access forms

references, can’t see DLLs – If when you browse for references in Access you cannot see DLLs in the browse window here is what'swrong and how to fix it:

Even though you may have checked the options

"Show all files"

"Hidden Files - Show allfiles"

and unchecked

“Hide file extensions for knownfile types"

in Windows Explorer under View/FolderOptions/View andclicked

"Reset All Folders" or

"Like Current Folder"

for Folder Views you may still not be able to see DLLs when setting references from a module in Access.

Doing the above will allow you to see all file types with their extensions in Windows Explorer but not in all of Windows. This bug is because all folder views throughout Windows have not really been reset only views in Windows Explorer.


To correct this, open Windows Explorer, pull down the View menu, select Folder Options. Click on the tab View. Now you must check either the option "Web Style - Your computer looks and acts like the Web (e.g., single-click)." or the option "Classic style - Your computer defaults to Windows classic settings."


This truly resets all folder views throughout Windows not just in Windows Explorer. THEN you can go back to the option "Custom, based on settings you choose:" and choose the settings you prefer, such as"Show all files" and "Hidden Files - Show all files" and uncheck "Hide file extensions for known file types". In the"Folder views - You can make all your folders look the same" you cancheck "Reset All Folders" or "Like Current Folder".


After doing this, settings references in Access and browsing for a file, you will have the option of file types "Executables(*.exe's and *.dll's)" and you will see both exe's and dll's, instead ofthe option "Executables" which will let you see only exe's not dll's.

also see References 101

registry key, bogus entries – whenever we start the latest production version of our software these are created.  After starting and stopping enough times, you can end up with thousands such entries.


You will find dozens, hundreds, or thousands of entriesthat look like:

ACBCustom Popup 521

Except other numbers instead of 521.  Delete ALL of these.  Deleting all these won't hurt anything.  Getting rid of them can speed up startup time by a factor of 10.

regular expression,use to replace unprintable hexadecimal characters with empty string – see hexadecimal unprintable characters, replace with empty string

regular expression, use in a query – see split a string on 2 or more spaces but leave single spaces alone for an example

remove characters – see strip out characters

repair, compact database doesn’t work – see also MSysCompactError

  1. Create a new database for the program
  2. Import all the objects from the old into the new database.  (May have to do one object type at a time to get it to work.  Also, make sure “preview” is turned off)
  3. Repair and Compact the new file
  4. Set the start-up properties in the new file (Tools/Startup)
  5. Add the required references to the HrzEC32_new file (Microsoft Office 8.0, Hrz supporting objects, etc)
  6. Attach to the Meta and Data-databases

replace some arbitrary string with another (say with a carriage return) –

UPDATE History SET History.memo =Replace (History.memo,"//xx??",Chr(10));

report – also see parameterized reports, multiple arguments, pass

report footer, suppress conditionally– see footer, conditionally suppress for a report

report off a crosstab – see crosstab report

report recordset, problems setting or retrieving – you can’t set or retrieve a report’s recordset.  See "Run-time error 2593" error when you set or you retrieve the Recordset property of a report

resize forms - see form resize

Return without GoSub - decompile - see more

ribbon, hide on startup - see hide ribbon when access starts

rotate text – see http://www.lebans.com/toc.htm – also View menu, click Properties, and then click the Other tab.  Set the Vertical property to Yes.  But often problems

round - Round([UnitPrice],2).  See also ceiling, floor

routines – see libraries,utilities

rows, default maximum returned – see records, default maximum returned

running sum in a report – create a text box and set its ControlSource property to the field or expression that you want to sum.  Then set the RunningSum property of the text box.

running total, query – dsum

runtime install problems– see install problems with runtime


screen resolution, resize forms to fit - see form resize

script - generate SQL for Access tables - see SQL for Access tables, generate

"search key not found in any record" error when importing – does one of your fields you're trying to import from an Excel spreadsheet where you select the first row to have field names have a blank name?

security .mdw, change default – c:\windows\system\Wrkgadm.exe

security warning setting, change – tools, macros, security

sequence numbers, add – see rank in sequence

setfocus for an element on a subform, force – see focus, force an element on a subform to have

shared (vs. exclusive) database – tools, options, advancedtab

sideways printing – see rotate text

single quotes – see quotes

slow performance - If all the clients have experienced a slowdown (after, say, rebuilding or compacting the database):

  1. If the application is client/server with the server portion on an NT box, try rebooting the NT server.
  2. Novell network?



snaking columns in a form – can’t do, but set up several subforms and then see next n after the top n

source of called function, to find– shift-F2

spaces, pad with– see padwith spaces

space, remove extra – see white space, remove, strip out characters, string, replace some arbitrary string with another

split a string based on a space – see also middle name, split out along with 1st and last names

FirstName: Left([name],InStr([name],"")-1)
LastName: Mid([name],InStr([name]," ")+1)

Or, to split based on the last instance of a space using InStrRev:

Zip:Mid([CityStateZip],InStrRev([CityStateZip]," ")+1)

See also here

There’s also a separate “split” function that splits a string into a one-dimensional array (like the Perl split function), but that’s something a little different.

split a string on 2 or more spaces but leave single spaces alone – you need a regular expression to do this.  In this example, someone gives us full name and address separated with a bunch of spaces and both the name and address have spaces themselves that we must retain but not split on.  There are a couple of ways to do it.

1. Finding location, splitting on that.  This is the more elegant of the two solutions.

Add a function:

Option Explicit
#Const LateBind = True
FunctionRegExpFind( ByVal FindIn, ByVal FindWhat As String, _
    Optional ByValIgnoreCase As Boolean = False)
    Dim i As Long
   #If Not LateBind Then
       Dim RE As RegExp, allMatches As MatchCollection, aMatch As match
       Set RE = New RegExp
        Dim RE As Object, allMatches As Object, aMatch As Object
       RE = CreateObject( "vbscript.regexp")
   #End If
    RE.Pattern = FindWhat
    RE.IgnoreCase =IgnoreCase
    RE.Global = True
    allMatches =RE.Execute(FindIn)
    Dim result As String
    ' if we don't find it, better not try to assign result
    If allMatches.Count > 0 Then
       result = allMatches(0).Value
    End If
    RegExpFind = result
End Function

Next, invokethis function in a query:

SELECT s.NameAddress,RegExpFind([NameAddress],"(  )+") AS Found,Left([NameAddress],InStr([NameAddress],Found)-1) AS Name, Trim(Mid([NameAddress],InStr([NameAddress],Found)+len(Found))) AS Address

FROM some_table as s;

What we didabove was find the 1st instance where we had 2 or more spaces ( "(  )+").  Then, split it out into 1st part ( InStr([NameAddress],Found)-1)) and 2ndpart ( Mid([NameAddress],InStr([NameAddress],Found)+len(Found))).  Note the Trim command: sometimes there can be an odd number of spaces…

2. Find the string, replace it with something else, and split on that.  Less elegant ‘cause you have the unnecessary intermediate step of temporarily replacing with some string you hope you won’t encounter.

Add a function:

Option Explicit
#Const LateBind = True
FunctionRegExpSubstitute( ByVal ReplaceIn, _
    ByVal ReplaceWhat As String, ByVal ReplaceWith As String)
   #If Not LateBind Then
     Dim RE As RegExp
     Set RE = NewRegExp
       Dim RE As Object
      RE = CreateObject( "vbscript.regexp")
   #End If
    RE.Pattern = ReplaceWhat
    RE.Global = True
    RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)
End Function

Next, invoke this function in a query:

SELECT s.NameAddress, RegExpSubstitute([NameAddress],"(  )+","_") ASReplaced, Left([Replaced],InStr([Replaced],"_")-1) AS Name,Mid([Replaced], InStr([Replaced],"_")+1) as Address

FROM some_table as s;

What we didabove was first replace all instances where we had 2 or more spaces ( "(  )+") with an underscore ( "_")) , or some other symbol you’re sure won’t be encountered in our string.  Then, split it out into 1st part ( Left([Replaced],InStr([Replaced],"_")-1))and 2nd part ( Mid([Replaced], InStr([Replaced],"_")+1)).

split a string into ALL its letters – use a byte array, which is an array of the ASCII character codes

Public Sub Sample()
    Dim bytArray() As Byte
    Dim i As Long
    bytArray = StrConv( "Hello,World!", vbFromUnicode)
    For i = 0 To UBound(bytArray)
        Debug.Print(i & ": " & bytArray(i) & " (" & Chr(bytArray(i)) & ")")
    Erase bytArray
End Sub

~sq_f - if you have a form that has a recordsource of only a table, you will return a querydef of "~sq_f" + the form name.  The temporary objects needed by Access to get it work done are stored with a ~ in the first character. That way it can never interfere with your naming convention

SQLcrib sheet

SQL, execute - DBEngine(0)(0).Execute "INSERT INTO Customer ( COMPANY_NAME) SELECT 'test5' AS CompanyName"

SQL for Access tables, generate

Access2MySQL- $35, 30 free uses

Access2SQL -$29.95 .exe file - works OK but only seems to like Access 97 databases

create INSERT SQL statements - DataFast Utility A97 &A2K at datafast

DBScripter Ver. 5.0 - free download works on 5 tables only

ExportSQL version 2.1, 3.2

SSW'sUpSizing Pro .exe file - $99

untested script from a Google message

start up form to automatically open - see form, automatically open upon start up

start-up properties of a database –

In "newer" versions of Access (2007, 2010), click the "office ball" up at the upper left, "Access Options" toward the bottom of the menu the pops up. So, for example, to enable or diable various menu components, go down to "Ribbon and Toolbar Options

In "older" versions of Access (2000, 2003),Tools/Startup

step through -- shift-F8

stored procedures in SQL Server with parameters

First, create stored procedure with a parameter:

CREATE PROCEDURE activeProjectsForAcctMgr (@acctMgr  char (20))
FROM          dbo .Project
where ACCOUNT_MANAGER = @acctMgr

To set a form’s recordset to this stored procedure with the parameter:

Private Sub Form_Open(Cancel As Integer)

    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command

    Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset


    With cmd

       .ActiveConnection = CurrentProject.Connection

       .CommandType = adCmdStoredProc

       .CommandText = "activeProjectsForAcctMgr"

       'Dim par As ADODB.Parameter

       'Set par = cmd.CreateParameter("@acctMgr", adVarWChar, adParamInput,20)

       '.Parameters.Append par

       .Parameters.Append cmd.CreateParameter ("@acctMgr", adVarWChar,adParamInput, 20)

       .Parameters("@acctMgr") = "Bob"

    End With


    rs.Open cmd, ,adOpenStatic    ' retrieve data

    Set Me.Recordset = rs

    Set rs = Nothing

    Set cmd = Nothing

End Sub

You’d think setting a report’s recordset to this stored procedure with the parameter would be very similar.  Of course,you’d be very wrong!  The way you need to do it for a report is:

Private Sub Report_Open(Cancel As Integer)

    Dim strRecordSource As String

    strRecordSource = "Exec[activeProjForAcctMgr] 'Bob'"

    Me.RecordSource = strRecordSource

End Sub

Unlessyou want to parameterize a sub-report, in which case it's even trickier.  Theparent's record source must provide the child's parameter.

ALTER PROCEDURE[dbo] .[Sales-SeminarsSelectedDay-Customer]

     @startDateString  varchar (40 )



      DECLARE @startDate DATETIME

      set @startDate = CONVERT ( datetime, @startDateString)


SELECT DISTINCT TOP (100 ) PERCENT dbo .Customer .ID , dbo .Customer .COMPANY_NAME ,

     dbo .Customer .SALES_REP , @startDate asstartDateString       This last parameteris solely for the purpose of passing to the child report – spell it the sameway!

FROM          dbo.RestaurSeminar

Make sure you parent report "consumes" all the fields the parent report's stored procedure generates except for the extra parameters!  For example, if the parent report's stored procedure generates 3 "regular" fields and 1"parameter" field, if your parent report only uses 2 of the 3, then the other "unused" data field will "spill over" into the 1 parameter field and cause problems.  It might also complain 'cause you're trying to set it twice ("Runtime Error 2191 - You can't set the recordsource property after printing has started").  So need to initialize a global variable once in the parent:

And then set it in the child:

If Not myOpenFlag Then

    Me.RecordSource ="[Sales-SeminarsSelectedDay-Project]"     noteyou don't have the "Exec " preceding the stored procedure, nordo you supply parameters!

    myOpenFlag = True

End If

And running stored procedure with the parameter directly as a view is even tougher.  Forget:

Dim cn As ADODB.Connection

Set cn = CurrentProject.AccessConnection

cn.Execute("[dbo].[Management-ProjectsNeededForEmployee] @employee = 29")

DoCmd.OpenStoredProcedure"Management-ProjectsNeededForEmployee @employee = 29", acViewNormal,acEdit

Oh no.  That won’t work.  Access can’t understand the parameter and instead goes looking for a stored procedure with the parameter as part of its name.  Instead, you need to read your stored procedure in as a string, replace the parameter, recreate it as a temporary new stored procedure and drop it (thanks Lyle):

Private Sub cmdProjectsNeeded_Click()

On Error GoTo Err_cmdProjectsNeeded_Click

    Dim TSQL As String

    On ErrorResume Next

    CurrentProject.Connection.Execute"DROP Procedure TempProcedure"

    On ErrorGoTo Err_cmdProjectsNeeded_Click

    TSQL =GetSQLStringFromSP("Management-ProjectsNeededForEmployee")

    TSQL = Replace(TSQL,"@employee", "29")

    CurrentProject.Connection.Execute"CREATE PROCEDURE TempProcedure AS " & TSQL




    CurrentProject.Connection.Execute"DROP Procedure TempProcedure"


    Exit Sub



    MsgBox Err.description


End Sub


Public Function GetSpTSQL(ByVal SpName AsString) As String

    DimTSQL As String

    TSQL = "SELECT text fromSysComments c JOIN SysObjects o ON c.ID = o.ID WHERE o.Name = '" &SpName & "'"

    GetSpTSQL = Trim(CurrentProject.Connection.Execute(TSQL).Collect(0))

End Function


Public Function GetSQLStringFromSP( ByVal SpName As String) As String

    DimspTSQL As String

    DimPosition As String

    spTSQL = GetSpTSQL(SpName)

    Position = InStr(spTSQL, "AS") + 2

    GetSQLStringFromSP = Mid$(spTSQL,Position)

    GetSQLStringFromSP =Replace(GetSQLStringFromSP, "RETURN", "")

    GetSQLStringFromSP =Trim(GetSQLStringFromSP)

End Function

And even this doesn’t work right if you’re not the dbo ‘cause when you look at how many records are returned from “Select * fromSysComments” as dbo you get way more than when you run that query as a “normal”user.  So the two tables joined will most likely return 0 rows which causes the code to fail.  Oh, why can’t Microsoft make running a parameterized stored procedure simpler?

string, convert to number –Val function converts a string that represents a number into a number (so that we can do arithmetic with it, for instance). For example:


returns the number 4.5 and:

Val("1234 Main Street")

returns the number 1234. Note, however, that Val does not recognize dollar signs or commas. Thus:


returns 0, not 12.00.  Opposite function is “Str” to convert number to string.

string function - create a string quickly that consists of a single character repeated a number of times. For instance:

sText = String(25, "A")

sets sText to a string consisting of 25 As. Also, the Space function returns a string consisting of a given number of spaces. For instance:

sText = Space(25)

sets sText to a string consisting of 25 spaces.

string, find location of –

this works in VBA

InStr([name]," ")

this works in VB.NET

Dim myString As String = "ABCDE"
Dim myInteger As Integer
myInteger = myString.IndexOf("D")  ' myInteger = 3

string, replace some arbitrary string with another (say with a carriage return) –

UPDATE History SET History.memo = Replace(History.memo,"//xx??",Chr(10));

string, remove characters from beginning, end –

Dim myString As String = "#####Remove those!######"
Dim oneString As String
OneString = myString.Trim("#")

Or, easier for blanks

Addr: LTrim([Address])

string, split based on a space – see split a string based on a space

string, remove characters from –see strip out characters

strip out characters – see also string, replace some arbitrary string with another, white space, remove

' Function StripString()
' Returns a string minus a set of specified chars.

Function StripString(MyStr As Variant) As Variant
   On Error GoTo StripStringError

   Dim strChar As String, strHoldString As String
   Dim i As Integer

   ' Exit if the passed value is null.
   If IsNull(MyStr) Then Exit Function

   ' Exit if the passed value is not a string.
   If VarType(MyStr) <> 8 Then Exit Function

   ' Check each value for invalid characters.
   For i = 1 To Len(MyStr)
      strChar = Mid$(MyStr, i, 1)
      Select Case strChar
         Case ".", "#", ",", "-"
             'Need to modify code if want to search for CR, LF or Tab
             'Chr(13) Or C = Chr(10) Or C = Chr(9)
             ' Do nothing
         Case Else
             strHoldString = strHoldString & strChar
      End Select
   Next i

   ' Pass back corrected string.
   StripString = strHoldString

   Exit Function

   MsgBox Error$
   Resume StripStringEnd
End Function

subform focus, force an element on a subform to have – see focus, force an elementon a subform to have

subform, go to a new record without having to save (.Update)it –



If you do want to save it, easier:

With  Me!subProjectTabular.Form.Recordset


   !CustomerFK = Me!ID


End With

subform record, new, show only new record (suppress existing records on a datasheet subform) – see record, new, show only new record (suppress existing records on a datasheet form)

subform record, new, make new record show up on top instead of at the bottom of a form in datasheet or continuous forms view – see record, new, make new record show up on top instead of at the bottom of a form in datasheet or continuous forms view

subforms, setting fields in - Forms!MainFormName!SubFormControlB.Form!Oranges = Forms!MainFormName!SubFormControlA.Form!Apples

subselect alternative – the way you’d select all records from a table which do not share a common ID with records from a second table using a subselect:

select * from table1

where field1 not in (select field2 from table2)

Since sub-queries are quite slow, an alternative using a join (which can be much faster):

select table1.* from table1

left join table2 on (table1.field1 = table2.field2)

where table2.field2 is null;

subselect example

SELECT DISTINCT t1.employeeFK, t1.timeStamp

FROM timesheet AS t1

WHERE (t1.timeStamp)=(select max(timeStamp) from timesheet as t2 where t1.employeeFK = t2.employeeFK );


See also timeclock example for a fairly horrible example

substring – see InStr, Left, Right

suppress ability to add new records in a form – see record, new, suppress or prevent in a form

suppress existing records on a datasheet form – see record, new,show only new record (suppress existing records on a datasheet form)

suppress report footer conditionally– see footer, conditionally suppress for a report

synchronize two subforms create common invisible field on master form

synchronize forms – see forms, synchronize


tab within a record or go to new record – form properties, “Other”, Cycle

tabs vs. tile in Access 2007 – Microsoft OfficeButton/Access Options/Current Database.  In the Application Optionssection, under Document Window Options, click Overlapping Windows.

table, copy one record, field at a time.  Obviously,there are easier ways to do this, but this gives basic outline to build on to customize.  For an example, see mail merge, both sides

Sub readWriteTable()
    ' Before running, import your data into "input" table.  Copy that table
    ' (structure only) into "scro_merge3".
    Dim db As DAO.Database
    Dim rstSource As Recordset
    Dim rstTarget As Recordset
    Set db =DBEngine(0)(0)
    ' Starts with the full source ...
    Set rstSource =db.OpenRecordset("input")
    ' ... and an emptycopy of the target
    Set rstTarget =db.OpenRecordset("output", dbOpenDynaset)
    For i = 0 To rstSource.RecordCount - 1
        With rstTarget
            For j = - To rstSource.Fields.Count - 1
                .Fields(j - 1) = rstSource.Fields(j)
             Next j
        Ifi Mod 1000 = 0 Then
             Debug.Print i
    Next i
End Sub

table, open from an outside application – see Access table, open from an outside application

table, read one record at a time

if it’s a simple table, not linked, pretty straightforward

Dim strRecip As String

Dim rs As New ADODB.Recordset

rs.Open "test", CurrentProject.Connection,adOpenDynamic, adLockOptimistic

Do While Not rs.EOF

    strRecip = rs!namedField1




Set rs = Nothing

If linked, however, a little different:

Dim rs As NewADODB.Recordset

Dim strsql As String

strsql = " SELECT [dbo_emailOnly-ITguys].eMailFROM [dbo_emailOnly-ITguys]"


With rs

    .ActiveConnection =CurrentProject.Connection

    .CursorType = adOpenStatic

    .Source = strsql

    .LockType = adLockPessimistic


End With

Could actually have done it the first way but just with the Select statement instead of just the “raw” table.

table, update one record at a time

     'First, strip outany trailing blanks
    strSQL = "select * from " &Me!cboTables
     Set rs =CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
     With rs
         .Fields![EmailAddress] = RTrim(.Fields![Email Address])
     End With

tables, list


FROM MSysObjects

WHERE [Type]=1 AND Flags=0;


Sub telnet1()
    'Create the shellobject
    oShell = CreateObject( "WScript.Shell")
    'Start up commandprompt
    With oShell
        'Startup command prompt
        .Run( "cmd.exe")
        'Sendkeys to active window; change the
        '    ip address as needed.
        .SendKeys( "telnet mail2 25")
        'Emulatethe enter key
        oShell.SendKeys( "{Enter}")
        'writethe user name to the cmd window
        'writethe password to the cmd window
        .SendKeys( "helo mail2")
        .SendKeys( "{Enter}")
        .SendKeys( "mail from: you@yourdomain.com")
        .SendKeys( "{Enter}")
        .SendKeys( "rcpt to: you@yourdomain.com ")
        .SendKeys( "{Enter}")
        .SendKeys( "Data")
        .SendKeys( "{Enter}")
        .SendKeys( "subject: This is a test mail")
        .SendKeys( "{Enter}")
        .SendKeys( "to: Joe")
        .SendKeys( "{Enter}")
        .SendKeys( "This is the text of my test mail.")
        .SendKeys( "{Enter}")
        .SendKeys( ".")
        .SendKeys( "{Enter}")
        'Exitthe program
        .SendKeys( "% ")
        .SendKeys( "{Enter}")
        .SendKeys( "quit")
        .SendKeys( "{Enter}")
    End With
End Sub

text box, set to result of a query - as long as the query returns just one value:


text files won’t import – see .txt files won’t import

" The datawas added to the database but the data won't be displayed in the form becauseit doesn't satisfy the criteria in the underlying record source." - set the UniqueTable property of the form to see if Access can create aResync Command string for you.  Otherwise, in the “BeforeUpdate” property:

Private Sub status_BeforeUpdate(Cancel As Integer)

sSQL = "SELECT * "& _

    " FROM tasks " & _

    " where status = '" & Me.Parent!cmdStatus & "'"& _

    " or status is null"


If IsNull(Me!ID) Then

   'Me.ResyncCommand = sq & "@@Identity"


  Me.ResyncCommand = sSQL

End If

End Sub

thermometer -- see meter

tile vs. tabs in Access 2007 – Microsoft Office Button/Access Options/Current Database.  In the ApplicationOptions section, under Document Window Options, click OverlappingWindows.

time clock example

SELECT [FirstName] & ' ' & [LastName] AS FullName,t1.timeStamp AS clockIn, t2.timeStamp AS clockOut,DateDiff( "n",[clockIn],[clockOut]) AS minutes

FROM timesheet AS t2, employee INNER JOIN timesheet AS t1 ONemployee.ID = t1.employeeFK

WHERE ((t1.timeStamp>#1/29/2006# Andt1.timeStamp<#2/12/2006#)

AND (t2.timeStamp =

(select min(timeStamp) from timesheet as t3 wheret1.employeeFK = t3.employeeFK and t3.timeStamp >[t1].[timeStamp]))

AND t1.employeeFK = [t2].[employeeFK]

AND t1.actionFK=1

AND t2.actionFK = 2)

ORDER BY [FirstName] & ' ' & [LastName], t1.timeStamp;


actionFK = 1 is “in” and actionFK =2 is “out”

time portion of date/time field - TimeValue(theDateField)

time elapsed - see elapsedtime or elapsed months


Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval =10000  '10 seconds in milliseconds
End Sub
Private Sub Form_Timer()
MsgBox "ten seconds gone by"
End Sub

tool bar missing – compact, repair database

top n is OK, but what about the NEXT n after that – see next n after the top n

trailing characters, remove – see string, remove characters from

translate phone number - see convert phone number


transpose rows, columns

TRANSFORM Sum(paper.boxesOnHand) AS sumOfboxesOnHand
SELECT "what we have in stock" AS [Total boxes]
FROM paper
PIVOT paper.color;

.txt files won’t import –won’t even show text files as an option when trying to import

The stock answer is to uninstall, reinstall Access. But for some reason, the MS Office install program does not really uninstall/install the 'ISAM drivers properly.  I don't know if isnecessary, but get the latest version of MS Jet 35:


Details here:


This program loads a bunch of DLLs in the system directory.  After it does its thing, use RegSvr32 to UNinstall 2 DLLs from the Registry:

RegSvr32 /u <path to your windows system directory>\mstext35.dll

RegSvr32 /u <path to your windows system directory>\msexcl35.dll

Then turn right around and installed them into the Registry:

RegSvr32 <path to your windows system directory>\mstext35.dll

RegSvr32 <path to your windows system directory>\msexcl35.dll

After that fire up MS Access and the TXT and other options should appear.


uncompile – see “ recompile,normal” or “ recompile, force –/decompile switch”

unhide column in datasheet view,– see column, hide in datasheet view

union, insert into new table

select      * into table_new
from  (
    select [x_code], pxc, day
    from    [table1]
    where   [x_code] like 'bic*'
    select [x_code], pxc, day
    from    [table2]
   ) as A

note the alias

unique values, number of – see number of unique values

unit conversion - Unit Conversion database for MS Access 97.(tropi.zip) Free

unprintable hexadecimal characters, replace with empty string – see hexadecimal unprintable characters, replace with empty string

update inner join

This works in MS Access but not in SQL Server:

update TableOne
    inner join TableTwo on TableOne.commonID = TableTwo.commonID
    set TableOne.field1 = TableTwo.field2

or this also works in MS Access but not in SQL Server (better for when you have extra “and” restrictions you need to add to the “where”):

update TableOne, TableTwo
    set TableOne.field1 = TableTwo.field2
    where TableOne.commonID = TableTwo.commonID

or this works in SQL Server but not in MS Access

update tableOne
set tableOne.field1=tableTwo.field2
from tableOne, tableTwo
where tableOne.commonID=tableTwo.commonID

updates - see patches

upsizing wizard

SSWUpsizing PRO! - Access to SQL Server Upsizing Utility

user, which user is using the database right now – see also machine ID,which machine is using the database right now

Private Declare Function GetUserNameA _
    Lib "advapi32.dll" (ByVal lpBuffer As String, _
    nSize As Long) As Long
Function WhoIsIt()
    Dim lRet As Long
    Dim szName As String
    szName = String(256,Chr$(0))
    lRet = GetUserNameA(szName, 255)
    If lRet <>0 Then
        MsgBox Left(szName, InStr(szName, Chr$(0)) - 1)
        MsgBox Error
    End If
End Function

users. kick out – see automatically logout users for DB maintenance

users, see which are in the database – also UserID currently logged on a remote machine

Sub ShowUserRosterMultipleUsers()

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Set cn =CurrentProject.Connection

Set rs =cn.OpenSchema(adSchemaProviderSpecific, _


'Output the list of all users in the current database.

Debug.Print rs.Fields(0).Name,"", rs.Fields(1).Name, _

"", rs.Fields(2).Name,rs.Fields(3).Name

While Not rs.EOF

Debug.Print rs.Fields(0), rs.Fields(1), _

rs.Fields(2), rs.Fields(3)



End Sub

utilities - see also libraries



DbTools - link to download doesn't work 12/30/02

Utter Angel's MS Access Downloads


version of access – SysCmd(acSysCmdAccessVer)

version of operating system – see operating system, which version

vertical printing – see rotate text – also View menu, click Properties, and then click the Other tab.  Set the Vertical property to Yes.  But often problems

visible, make column in datasheet view,– see column, hide in datasheet view

Visual Basic, Convert Access 2000 Databases to - see convert Access 2000 Databases to Visual Basic


web, display – GDB, enhanced GDB

week –

=DatePart("ww",[ShippedDate]) Week of year
=DatePart("w",[ShippedDate]) Weekday

week, subtract - DateAdd("ww",-1,[TransDate]) where “TransDate” is an input field

white space, remove

    Dim rstSource As New ADODB.Recordset
    Dim rstTarget As New ADODB.Recordset
    Dim cn As ADODB.Connection
    cn = CurrentProject.Connection
    Dim var_txt As String
    ' Start with the full source ...
   rstSource.Open("start", cn, adOpenForwardOnly, adLockReadOnly)
    ' ... and an empty copy of the target
   rstTarget.Open("end", cn, adOpenDynamic, adLockOptimistic)
    For i = 0 To rstSource.RecordCount - 1
        With rstTarget
            .Fields(0) = rstSource.Fields(0)
            var_txt = Nz(rstSource.Fields(10))
             'Remove InternalExtra White Spaces
             While var_txt <> Replace(var_txt,"  ", " ", 1, -1, vbTextCompare)
                var_txt = Replace(var_txt, "  ", " ", 1, -1, vbTextCompare)
             End While
            .Fields(2) = var_txt
        End With
    Next i
End Sub

Or this function just gets rid of CR, LF, Tab

Function StripChars( ByVal Str2clean As String) As String
    Dim A As Long
    Dim b As Long
    Dim C As String
    Dim D As String
    b = Len(Str2clean)
    D = ""
    For A = 1 To b
       C = Mid(Str2clean, A, 1)
        'Do nothing CR, LF or Tab
        If C = Chr(13) Or C = Chr(10) Or C = Chr(9) Then
             'Char Ok
            D = D & C
        End If
    StripChars = D
    End Function

Windows version – see operating system, which version

write table to a file

Dim strOutput As String
Dim blnStarted As Boolean
Dim cn AsADODB.Connection
Dim rs AsADODB.Recordset
Dim fso As Object
Dim ts As Object
blnStarted = False
Set cn = CurrentProject.AccessConnection
Set rs = NewADODB.Recordset
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\whitelist.txt", True)
sSQL = "SELECT TOP 100 PERCENT email "& _
        " FROM dbo.WhiteList" & _
        " ORDER BYemail"
With rs
   Set .ActiveConnection= cn
   .Source = sSQL
   .LockType = adLockOptimistic
   .CursorType = adOpenKeyset
End With
strOutput = ""
With rs
    Do While Not .EOF
        IfblnStarted Then
            strOutput = strOutput & ", " & rs.Fields(0)
            strOutput = rs.Fields(0)
            blnStarted = True
        End If
End With
MsgBox strOutput
ts.WriteLine strOutput
Set ts = Nothing
Set fso = Nothing



year, subtract - DateAdd("yyyy",-1,[TransDate]) where “TransDate” is an input field


zeroes, pad with– see padwith zeroes

zip code, split on a space –

split based on the last instance of a space usingInStrRev:

Zip: Mid([CityStateZip],InStrRev([CityStateZip],"")+1)

zip code, split on dash –

zip code   zip:IIf(InStr([postal_Code],"-")>0,Left([postal_Code],InStr([postal_Code],"-")-1),[postal_Code])

plus 4     plus4:IIf(InStr([postal_Code],"-")>0,Mid([postal_Code],InStr([postal_Code],"-")+1),"")


10060 error in odbc – see odbc connection fails – SQL Server Error 10060

2 or more spaces, split a string on but leave single spaces alone – see split a string on 2 or more spaces but leave single spaces alone

2nd last instance of a character in a string – see nth last instance of a pattern in a string