Access Hints

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
       rstSource.MoveFirst
   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)
           rstTarget.AddNew
           With rstTarget
                 For k = 0 To 3
                     Select Case k
                     Case 0 ' full name
                         .Fields(k) = outputRec(k)
                     Case 1 ' streetaddress, which may or may not contain 2nd address
                         .Fields(k) = outputRec(k)
                     Case 2 ' 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)
               .Update
           End With
           i = -1
        End If
        rstSource.MoveNext
    Loop
    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.

Open the sample project NorthwindCS.adp.
Open the Customers form in Design view.
Clear the RecordSource property of the form to unbind the form.
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
      .Open
   End With

   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Setrs = Nothing
   Setcn = Nothing
End Sub

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:

PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=Database_Name;DATA SOURCE=Server_Name;Use Procedure for Prepare=1;Auto Translate=True;Workstation ID=WorkSta_Name

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:

CurrentProject.CloseConnection

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

CurrentProject.OpenConnectionstrConnect

.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
Red
Green
Blue

SubWorker(a() As Integer)
    Dim i As Integer
    For i = 1 To 5
       a(i) = i * 10
    Next
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)
    Next
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:

Open and hide a form that periodically checks for the logout flag
When flag is true, open and show the countdown form
Continue scanning to verify flag was not reset to false
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

1^st 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"
rs.Open

'Add the new record
rs.AddNew
rs("field1") = "value"
rs("field2") = "value"
rs.Update

rs.MoveLast

'get the newly created autonumber from the recordset
AutoNumber = rs("Auto-Number-Field")

'close therecordset and kill the object
rs.close
set rs = nothing

2^nd way

rs.AddNew
rs!LastName = "Abraham"
rs!FirstName = "Lincoln"
rs.Update

rs.Bookmark = rs.LastModified
Autonumber = rs.EmployeeID

3^rd way

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

—B—

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()
    Close(iFileNum)
    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)
        Debug.Print(xName)
    Next
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
    BinaryStream.Open
    BinaryStream.Write(Binary)

    '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
    Else
        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

—C—

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

ceiling

' 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
    Me.subTestForm.SetFocus
    DoCmd.RunCommand acCmdHideColumns
    cmdHideColumn.Caption = "UnhideColumns"
Else
    DoCmd.Echo False
    cmdHideColumn.Caption = "Hide Column"
  strForm = Me.subTestForm.SourceObject
    DoCmd.RunCommand acCmdDesignView
    DoCmd.OpenForm strForm, acFormDS
    DoCmd.RunCommand acCmdUnhideColumns
    DoCmd.Close
    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

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!COMPANY_NAME.SetFocus
Me.Parent!subRestaurantCustomer.Form.Dirty= False
Me!FirstName.SetFocus
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].* " & _
        "FROM[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
    Next

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

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

—D—

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

CREATE TABLE #reIdentify (JUNKID int IDENTITY (1, 1) NOT NULL)) SET IDENTITY_INSERT #reIdentify ON INSERT INTO #reIdentify(JunkID) SELECT MyTablePKID FROM INSERTED SET IDENTITY_INSERT #reIdentify OFF

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 AS BEGIN CREATE TABLE #NotPaid (JUNKID int NOT NULL) INSERT INTO #NotPaid (JunkID ) SELECT ID FROM dbo .Project WHERE (PAID = 0 ) AND (DATE_RECORD_ENTERED > CONVERT ( DATETIME , '2012-01-01 00:00:00' ,102 )) AND (HOLD_CANCELLED IS NULL OR HOLD_CANCELLED = N'' AND HOLD_CANCELLED <> N'CANCELLED and <> NHOLD' ) SELECT PROJECT_NAME ,CustomerFK , PAID ,OKToSendOut , DATE_RECORD_ENTERED , HOLD_CANCELLED , ID , DATE_PAID , DATE_PAIDWho from Project as pr inner join #NotPaid as np on pr .ID =np .JUNKID END GO

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
Loop
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

DELETE DISTINCTROW target.*

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”.

Open the delete query in Design view.
On the View menu, click Properties.
Set the UniqueRecords property to Yes.
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.

Open an Explorer window.
Select "View|Folder Options"
Select "File Types" and then select "Microsoft Access Database" from the list of file types.
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)
In the "Action" box type what you want to appear in the context menu- e.g. "Decompile"
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)

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)
        Loop
    Next FileItem
    IfIncludeSubfolders Then
        For Each SubFolder InSourceFolder.SubFolders
           StuffFilesInFolderToTable(SubFolder.Path, True)
        NextSubFolder
    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

SELECT DISTINCTROW Individ.*,EVAL.WP_CPBLTYCURRENTCODE, EVAL.WP_CPBLTYFUTURECODE, EVAL.WP_CPBLTYCURRENTDESC,EVAL.WP_CPBLTYFUTUREDESC, EVAL.WP_COMMENTS, EVAL.EVALDATE,DLookUp("[MOBILITY]","CAREER","id = '" &[individ].[id] & "'") AS Mobil

The trick isgetting the single quotes right!

FROM IndividLEFT JOIN EVAL ON Individ.ID = EVAL.ID

ORDER BYIndivid.LASTNAME;

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

rs.MoveFirst
Do While Not rs.EOF
   strText = strText &"""" & rs(0) & """" &","
   strText = strText & """" &rs(1) & """" & ","
   strText = strText & """" &rs(2) & """"
   strFileText = strFileText & strText & vbCrLf
   strText = ""
   rs.MoveNext
Loop

Set fs =CreateObject("Scripting.FileSystemObject")
Set a =fs.CreateTextFile("C:\Program Files\xx\xx.csv", True)
a.WriteLine (strFileText)
a.Close
rs.Close
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

GenericDB

—E—

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
    Err.Clear
    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

    mItem.Save
   mItem.Send
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: 'http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning ' ' 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: 'http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning ' 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 Else .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 MAPISession.Logoff End If 'If we got to here, then weshall assume everything went ok. blnSuccessful = True ExitRoutine: Set MAPISession = Nothing FnSendMailSafe = blnSuccessful Exit Function ErrorHandler: 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:
'http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning
'
' 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>"& _
             "</html>"

    blnSuccessful =FnSafeSendEmail("myemailaddress@domain.com", _
                                          "My Message Subject", _
                                          strHTML)

    '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", _
                                       "bcc_recipient@domain.com")

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

'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

        objExplorer.Close

        Set objNameSpace = Nothing
        Set objExplorer = Nothing

    End If

    blnSuccessful = objOutlook.FnSendMailSafe(strTo,strCC, strBCC, _
                                                   strSubject, strMessageBody, _
                                                   strAttachmentPaths)
    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
Wend

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?

—F—

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
    Next
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
       Debug.Print(sText)
    Loop
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
Loop
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
    Else
       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
Next

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
nextiteration:
Next

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

—G—

general ledger

CSA-net

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 –

DBNetGrid

HierarchicalData Grid in ASP

—H—

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)
Next
'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

—I—

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

—J—

join, outer full – see outer join, full

—K—

kick users out – see automatically logout users for DB maintenance

—L—

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

Highlight the label

Cut it

Highlight the text box

Paste

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.

InStrRev(
	string1, 
	string2 [, 
	start] [, 
	compare])

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

Lesandrini

ProgrammingMSAccess.com

Roger's Access

Walker

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, _
       fileName))
       nFiles = nFiles + 1
       Debug.Print(fileName)
       fileName = Dir() ' Get next file
       DoEvents()
    End While
    nDirs = nDirs + 1
    If fld.SubFolders.Count > 0 Then
        For Each tFld In fld.SubFolders
           DoEvents()
           FindFile = FindFile + FindFile(tFld.Path, sFile, nDirs, nFiles)
        Next
    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

—M—

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 1^st and 2^nd,3^rd and 4^th, 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)

    rstSource.MoveFirst
    k = 0   'our counter to 8
    For i = 0 To rstSource.RecordCount - 1
        k = k + 1
        rstTarget.AddNew
        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
           .Update
        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
                rstTarget.AddNew
                 With rstTarget
                    .Fields(1) = temp(2 * l, 1)
                    .Fields(2) = temp(2 * l, 2)
                    .Update
                 End With
                rstTarget.AddNew
                 With rstTarget
                    .Fields(1) = temp(2 * l - 1, 1)
                    .Fields(2) = temp(2 * l - 1, 2)
                    .Update
                 End With
           Next l
           k = 0   ' clear our counter to start allover again with the next 8
        EndIf

        rstSource.MoveNext

        '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
        EndIf

    Next i
    rstSource.Close
    rstTarget.Close
    db.Close
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

meter – SysCmd

Mid (text, start_position, number_of_characters)

middle name, split out along with 1^st 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 = _
                    Mid(strComingIn,_
                    InStr(strComingIn, " ") + 1, _
                    fFindNthLastOccur(strComingIn, " ",2) - InStr(strComingIn, " "))
           Else
                SplitFirstMiddleLast = _
                    Mid(strComingIn,_
                    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))
           Else
                SplitFirstMiddleLast = Mid(strComingIn, InStrRev(strComingIn, " ") + 1)
           End If
        End If
    Else
        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
Public 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

OptionExplicit
Private Type SHITEMID
   cb As Long
   abID As Byte
End Type
Private Type ITEMIDLIST
    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)
    Else
        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

—N—

naming convention – see Hungarian naming convention

next n after the top n -

SELECT e1.ID

FROM [select top 10 ID from

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

order by ID desc]. AS e1

ORDER BY e1.ID;

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

SET MAXIMUM RECORD LOCKS PER CONNCECTION = 10000

SET MAXIMUM RECORD LOCKS = 200000

n^th 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
        Else
           intSay = InStr(1, strHold, strFind)
           intKeep = intKeep + intSay
           n = n + 1
           strHold = Mid(strHold, intSay + Len(strFind))
           fFindNthOccur = intKeep
        End If
    Loop

End Function

n^th 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
         Else
           intSay = InStrRev(strHold, strFind)
           n = n + 1
           strHold = Left(strHold, intSay - 1)
           fFindNthLastOccur = intSay
        End If
    Loop

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.

—O—

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

{PreLoginHandshake()}.

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:

Private Type OSVERSIONINFO
   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"
                          Else
                             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

Solutions:

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):

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

SELECT
     employee .LastName ,
     employee .DepartmentID ,
     department .DepartmentName ,
     department .DepartmentID
FROMemployee
     LEFT JOIN
     department
       ON employee .DepartmentID = department .DepartmentID
UNION
SELECT
     employee .LastName ,
     employee .DepartmentID ,
     department .DepartmentName ,
     department .DepartmentID
FROMemployee
     RIGHT JOIN
     department
       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

—P—

pad with spaces - Right(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.SetWarnings(False)
    DoCmd.Save(acReport, strName)
    DoCmd.Close(acReport, strName)
    DoCmd.SetWarnings(True)
    DoCmd.Echo(True, "")

Exit_SwitchtoLegal:
    Exit Sub

Err_SwitchtoLegal:
    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 *, IIF(IsNumeric(Nz(Forms!frmMain!txtPct,1.1)), 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 = &_

"SELECT DISTINCTROWINDIVID.ID, EVAL.EJ_KeyAssoc, Year([EvalDate])" &_

"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.

Create or open the query that will collect its criteria values from the dialog box.
Make sure that the query includes the tables you want to use.
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
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

patches

MicrosoftAccess Fixes, Patches and Updates

pause

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
    Loop

Err_Pause:
    Exit Sub
End Sub

performance is slow – see slow performance

phone number, convert - see convert phone number

php

ping IP address – see here

pivot chart

open

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 -
           '----------------------------------------------------------
        Else
           If intChangeType = 1 Then
                strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
           Else
                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

—Q—

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"

QueryDef

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:
rs.MoveFirst
Ifrs.RecordCount > 0 then msgbox "Yep –it's got records alright"

quotes

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"

—R—

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.

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.
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.
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
Next

recordset, loop throughrecords

with rs
    do while not.eof
        'dosomething in here.
        .movenext
    loop
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
    rst.MoveFirst
    Do While Not rst.EOF
        'dosomething in here.
        Debug.Print(rst(1))
        rst.MoveNext
    Loop
    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.

HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Access\Settings\CommandBars

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

Create a new database for the program
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)
Repair and Compact the new file
Set the start-up properties in the new file (Tools/Startup)
Add the required references to the HrzEC32_new file (Microsoft Office 8.0, Hrz supporting objects, etc)
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

—S—

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):

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

SET MAXIMUM RECORD LOCKS PER CONNCECTION = 10000

SET MAXIMUM RECORD LOCKS = 200000

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 1^st 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)