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

–A–

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' streetaddress, which may or may not contain 2nd address
                         .Fields(k) = outputRec(k)
                     Case' either2nd address or city/state/zip
                          If outputRec(3) = "" Then   ' we onlyhave 3 fields, so 3rd field only contains city/state/zip
                             .Fields(3) = Left(outputRec(2), InStr(outputRec(2), ",") -1)   ' split out city
                             thisStateZip = Mid(outputRec(2), InStr(outputRec(2), ",") + 1)  ' stash state/zip
                          Else    'we have 4 fields, so 3rd field has 2nd addr, 4th field has city/state/zip
                             .Fields(2) = outputRec(2)
                             .Fields(3) = Left(outputRec(3), InStr(outputRec(3), ",") -1)   ' split out city
                             thisStateZip = Mid(outputRec(3), InStr(outputRec(3), ",") + 1)  ' stash state/zip
                          End If
                     End Select
                    outputRec(k) = ""
                 Next k
                thisStateZip = LTrim(thisStateZip)  ' stashedstate/zip
                 ' split out state
                .Fields(4) = Left(thisStateZip, InStr(thisStateZip, " ") - 1)
                 ' split out zip
                .Fields(5) = Mid(thisStateZip, InStr(thisStateZip, " ") + 1)
               .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.

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

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

  1. and then close the form.

.adp file, change source database for

Through GUI (Access 2007)

Windows button / Server / Connection

Select or enterserver name, select the database on the server

Using commands

in the Debug window (ctl-g):

? currentproject.BaseConnectionString

It will return something like this:

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 and the 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

1.

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

2.

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:

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

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

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

[newAutoNumberField] = CLng([oldKeyField])

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

autonumber, determine number of just added

1st way

'create a connection
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 the recordset and kill the object
rs.close
set rs = nothing

2nd way

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

3rd way

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

–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 binary data - 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 And write text/string data To the object
    BinaryStream.Open
    BinaryStream.Write(Binary)
 
    'Change stream type To binary
    BinaryStream.Position = 0
    BinaryStream.Type = adTypeText
 
    'Specify charset For the 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 open database, 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 a string – see how many times specific character appears in a string

chart, pivot – see pivot chart

checkbook – see general ledger

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

child/master relationship between form and subform breaks - check to make sure a filter wasn't set on the child form

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 each row - use Conditional Formatting to solve your problem. If you need more flexibility, you can programmatically control Conditional Formatting at runtime.

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

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

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

Me.Parent!linkCustomer = Me!ID

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

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

Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME.SetFocus ' "Parent" on "Grandparent"
If Nz( Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME) = "" Then
    Me.Parent!subRestaurantCustomer.Form!COMPANY_NAME = Null ' a silly statement, but seemingly necessary!
End If
Me.Parent!subRestaurantCustomer.Form!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”.

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

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

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

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

decompile from context menu

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

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

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

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

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

dialog box, create

how tocreate

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

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

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

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

Sub StuffFilesInFolderToTable( ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    ' make sure you add"Microsoft Scripting Runtime" as a reference!
    ' example:StuffFilesInFolderToTable "C:\Users\joe\Documents\ACH\", True
    Dim db As Database
    Dim strSQL As String
    db = CurrentDb()
    Dim oFSO As New FileSystemObject
    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 = New Scripting.FileSystemObject
    SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.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
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            StuffFilesInFolderToTable(SubFolder.Path, True)
        Next SubFolder
    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 not selected, 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 is getting the single quotes right!

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

ORDER BYIndivid.LASTNAME;

Docmd.TransferText – convert this command 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 made it 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 file number
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 to the 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 the outbox 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 we shall 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 message sent successfully!"
    Else
        MsgBox "Failed to send e-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 often involves at least one line of code like this:

Dim mobjSession As MAPI.Session

Which might fail 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 man's record 'paint' event. However what *you*will get may be an image control with wildly half drawn images flickering as it is 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, advanced tab

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 through fields

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 to work 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.txt for input. App.Path returns the path your app is saved in
Open App.Path & "Test.txt" For Input 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

filtering fails with a run time error 2101 - usually in conjunction with a complicated "select" statement - like a subselect or "in"

Private Sub cboFindLicense_AfterUpdate()

Dim sSQL As String
If Nz(Me!cboFindLicense) <> "" Then

Me.FilterOn = True
' The simplest approach below has the "select" statment as a sub-select to the "in" clause
' But this simple approach returns run time error 2101
'sSQL = "HardwareID in " & _
' "(SELECT h.HardwareID " & _
    ' "FROM dbo.Hardware AS h INNER JOIN " & _
    ' "dbo.HardwareSoftwareKey AS hsk ON h.HardwareID = hsk.HardwareID INNER JOIN " & _
    ' "dbo.SoftwareKey AS sk ON hsk.SoftwareKeyID = sk.SoftwareKeyID " & _
    ' "WHERE (sk.SoftwareKey = N'" & Me!cboFindLicense & "'))"
' Because of this, we have to go through this whole rigamarole below to get a string for the "in" clause
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
cn = CurrentProject.AccessConnection 'Use the ADO connection that Access uses
rs = New ADODB.Recordset 'Create an instance of the ADO Recordset class...
sSQL = "SELECT h.HardwareID " & _
    "FROM dbo.Hardware AS h INNER JOIN " & _
         "dbo.HardwareSoftwareKey AS hsk ON h.HardwareID = hsk.HardwareID INNER JOIN " & _
         "dbo.SoftwareKey AS sk ON hsk.SoftwareKeyID = sk.SoftwareKeyID " & _
    "WHERE (sk.SoftwareKey = N'" & Me!cboFindLicense & "')"
With rs ' ... and set its properties
    Set .ActiveConnection = cn
    .Source = sSQL
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open()
End With
If (rs.EOF = True And (rs.BOF = True Then ' No Records Found
    MsgBox("No hardware has this key installed!") ' don't try to apply a null filter, but let user know
Else
    Do While Not rs.EOF ' build the results of the sub-select we want
        strText = strText & Nz(rs(0)) & ","
        rs.MoveNext()
    Loop
    strText = Left(strText, Len(strText) - 1) ' trim the last comma
    sSQL = "HardwareID in (" & strText & ")" 'now we can finally build our SQL that won't bust things
    Me.Filter = sSQL
    Me.FilterOn = True
End If
rs = Nothing
cn = Nothing

End If

End Sub

filtering fails with an "Enter a valid value" - if this is an .adp access project connected to a SQL Server back end, check to make sure the SQL Server back end type of this field is varchar and NOT nvarchar!

FindFirst causes “Object doesn‘t support this property or method” - 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 never will!  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, how to 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 outer join, 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

global variable, 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 access starts

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 built in 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 naming convention 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 the choices available– see .txt files won‘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 not associated) –

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"

link between master/child breaks - check to make sure a filter wasn't set on the child form

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 a reference!
    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 1st and 2nd,3rd and 4th, etc. so the columns line up right on the other side.  Here‘s code to do this:

Sub duplicate8recordsAtaTime()
    ' Before running, import the Excel into "input" table.  Make sure you have
    ' Access create an autoincrement index field.  We need that when we export 'cause
    ' the order isn't always preserved right when we export and we'll use that to
    ' sort in Excel.  Copy that table (structure only) into "output".
   
    ' We want to do a mail merge.  Full info on one side where we're putting address.
    ' Only full name and 1st name on 2nd side.  8 postage cards to a sheet.
    ' Takes incoming records and makes a copy of each 8 in turn with only the
    ' full name (field (1)- the 2nd field) and first name (field (2) - the 3rd field)
    ' copied in that 2nd 8
   
    ' Once you're done,export "output" back into Excel.  You might have to sort the
    ' exported file by thefirst field and then delete that first field, which is
    ' extraneous to themail merge.
    Dim db As DAO.Database
    Dim rstSource As Recordset
    Dim rstTarget As Recordset
    Set db =DBEngine(0)(0)
 
    ' first dimension isrecord #, 2nd field #.  We don't actually need the 0th field,
    ' but it's easier to declare and discard rather than shifting things over.
    ReDim temp(8, 3)
   
    ' Starts with the fullsource ...
    Set rstSource = db.OpenRecordset("input")
   
    ' ... and an emptycopy of the target
    Set rstTarget = db.OpenRecordset("output", dbOpenDynaset)
 
    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

master/child relationship between form and subform breaks - check to make sure a filter wasn't set on the child form

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

meterSysCmd

Mid (text, start_position, number_of_characters)

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

Function SplitFirstMiddleLast( ByVal strComingIn As String, ByValstrWhich As String) As String
    howManyBlanks = Len(strComingIn) -Len(Replace(Trim(strComingIn), " ", ""))
    ' First Name
    If strWhich = "First" Then
        If howManyBlanks = 0 Then
            SplitFirstMiddleLast = strComingIn
        ElseIf howManyBlanks > 0 Then
            SplitFirstMiddleLast = Left(strComingIn, InStr(strComingIn, " ") - 1)
        End If
        'Middle name
    ElseIf strWhich= "Middle" Then
        If howManyBlanks = 0 Or howManyBlanks = 1 Then
            SplitFirstMiddleLast = ""
        ElseIf howManyBlanks > 0 Then
             ' what if we have a suffix like Jr or Sr?
             If Len(strComingIn) - InStrRev(strComingIn, "Jr") < 3 _
             Or Len(strComingIn) - InStrRev(strComingIn, "Sr") < 3 Then
                SplitFirstMiddleLast = _
                    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 be possible 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 Windows registry (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 of collection, 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

Option Explicit
Private Type SHITEMID
   cb As Long
   abID As Byte
End Type
Private Type ITEMIDLIST
    mkid As SHITEMID
End Type
Private Const CSIDL_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 add children – 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

nth instance of a pattern in a string

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

nth last instance of a pattern in a string

Public Function fFindNthLastOccur( ByVal pStr As String, _
                                       ByVal pFind As String, _
                                       ByVal pNth As Integer) As Integer
    '------------------------------------------------------------------
    ' Purpose:  Return location of nth last occurrence of item in a string.
    ' Arguments: pStr: The string 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 Nz function

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

SELECT * 
FROMemployee
     FULL OUTER JOIN
     department
       ON employee .DepartmentID = department .DepartmentID
+-----------+--------------+----------------+--------------+
| LastName  | DepartmentID |DepartmentName | DepartmentID |
+-----------+--------------+----------------+--------------+
| Smith    |           34 |Clerical      |           34 |
| Jones    |           33 |Engineering   |           33 |
| Robinson |           34 |Clerical      |           34 |
| Jasper   |           36 |NULL           |         NULL |
| Steinberg|           33 |Engineering   |           33 |
| Rafferty |           31 |Sales         |           31 |
| NULL     |         NULL |Marketing     |           35 |
+-----------+--------------+----------------+--------------+

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

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 spacesRight(Space$(1)& Month([MAILED]),2)

pad with zeroes -

1. Simplest

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

2. If you want a function

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

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

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

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

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

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

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

    On Error GoTo Err_SwitchtoLegal
    Dim DevString As gtypStr_DEVMODE
    Dim DM As gType_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report
    DoCmd.Echo(False, "Checking default printer settings...")
    DoCmd.OpenReport(strName, acDesign) ' Opens report in Design view.
    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 SubReport_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.

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

For example, you could create a customer'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-child relationship between form and subform breaks - check to make sure a filter wasn't set on the child form

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 number of 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 will convert a string to ProperCase           -
    '- The initial letter of each word is capitalised.             -
    '- It will also handle special names such as O', Mc and        -
    '- hyphenated names                                            -
    '- if intChangeType =1, all text is converted to proper case.  -
    '- e.g. 'FRED' is converted 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 in string - nothing to do                -
    '----------------------------------------------------------
    If Len(strOneLine) = 0 Then
        ProperCase = ""
        Exit Function
    End If
 
    '----------------------------------------------------------
    '- Always set first letter to upper case                  -
    '----------------------------------------------------------
    strResult = UCase$(Left$(strOneLine, 1))

    '----------------------------------------------------------
    '- Now look at the rest of the string                     -
    '----------------------------------------------------------
    For I = 2 To Len(strOneLine)

        '----------------------------------------------------------
        '-If the previous letter triggered a capital, change      -
        '-this letter to upper case                               -
        '----------------------------------------------------------
        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 handling for 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.

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

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

Change it to:

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

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

recompile, normal

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

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

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

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

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

record locking – tools, options, advanced tab

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

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

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

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

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

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

record save, force – Me.Dirty = True

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

recordset count – ? Me.Recordset.Recordcount

recordset, loop through fields

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

recordset, loop throughrecords

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

or

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“s wrong and how to fix it:

Even though you may have checked the options

"Show all files"

"Hidden Files - Show all files"

and unchecked

“Hide file extensions for known file types"

in Windows Explorer under View/FolderOptions/View and clicked

"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 entries that look like:

ACBCustom Popup 521

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

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

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

remove characters – see strip out characters

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

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

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

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

report – also see parameterized reports, multiple arguments, pass

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

report off a crosstab – see crosstab report

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

resize forms - see form resize

Return without GoSub - decompile – see more

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

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

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

routines – see libraries,utilities

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

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

running total, query – dsum

runtime install problems– see install problems with runtime

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

  1. If the application is client/server with the server portion on an NT box, try rebooting the NT server.
  2. 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 1st and last names

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

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

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

See also here

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

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

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

Add a function:

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

Next, invokethis function in a query:

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

FROM some_table as s;

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

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

Add a function:

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

Next, invoke this function in a query:

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

FROM some_table as s;

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

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

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

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

SQLcrib sheet

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

SQL for Access tables, generate

Access2MySQL- $35, 30 free uses

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

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

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

ExportSQL version 2.1, 3.2

SSW'sUpSizing Pro .exe file - $99

untested script from a Google message

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

start-up properties of a database –

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

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

step through -- shift-F8

stored procedures in SQL Server with parameters

First, create stored procedure with a parameter:

CREATE PROCEDURE activeProjectsForAcctMgr (@acctMgr  char (20))
AS
BEGIN
SELECT
     PROJECT_NAME , ACCOUNT_MANAGER
FROM          dbo .Project
where ACCOUNT_MANAGER = @acctMgr
END
GO

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

Private Sub Form_Open(Cancel As Integer)

    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command

    Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset

 

    With cmd

       .ActiveConnection = CurrentProject.Connection

       .CommandType = adCmdStoredProc

       .CommandText = "activeProjectsForAcctMgr"

       'Dim par As ADODB.Parameter

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

       '.Parameters.Append par

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

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

    End With

 

    rs.Open cmd, ,adOpenStatic    ' retrieve data

    Set Me.Recordset = rs

    Set rs = Nothing

    Set cmd = Nothing

End Sub

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

Private Sub Report_Open(Cancel As Integer)

    Dim strRecordSource As String

    strRecordSource = "Exec[activeProjForAcctMgr] 'Bob'"

    Me.RecordSource = strRecordSource

End Sub

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

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

     @startDateString  varchar (40 )

AS

BEGIN

      DECLARE @startDate DATETIME

      set @startDate = CONVERT ( datetime, @startDateString)

 

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

     dbo .Customer .SALES_REP , @startDate asstartDateString      

This last parameter is solely for the purpose of passing to the child report – spell it the sameway!

FROM          dbo.RestaurSeminar

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

And then set it in the child:

If Not myOpenFlag Then

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

    myOpenFlag = True

End If

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

Dim cn As ADODB.Connection

Set cn = CurrentProject.AccessConnection<

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

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

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

Private Sub cmdProjectsNeeded_Click()

On Error GoTo Err_cmdProjectsNeeded_Click

    Dim TSQL As String

    On ErrorResume Next

    CurrentProject.Connection.Execute"DROP Procedure TempProcedure"

    On ErrorGoTo Err_cmdProjectsNeeded_Click

    TSQL =GetSQLStringFromSP("Management-ProjectsNeededForEmployee")

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

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

    'DoCmd.RunCommandacCmdViewStoredProcedures

   Application.RefreshDatabaseWindow

    DoCmd.OpenStoredProcedure"TempProcedure"

    CurrentProject.Connection.Execute"DROP Procedure TempProcedure">

Exit_cmdProjectsNeeded_Click:

    Exit Sub

 

Err_cmdProjectsNeeded_Click:

    MsgBox Err.description

    ResumeExit_cmdProjectsNeeded_Click

End Sub

 

Public Function GetSpTSQL(ByVal SpName AsString) As String

    DimTSQL As String

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

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

End Function

 

Public Function GetSQLStringFromSP( ByVal SpName As String) As String

    Dim spTSQL As String

    Dim Position As String

    spTSQL = GetSpTSQL(SpName)

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

    GetSQLStringFromSP = Mid$(spTSQL,Position)

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

    GetSQLStringFromSP =Trim(GetSQLStringFromSP)

End Function

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

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

Val("4.5")

returns the number 4.5 and:

Val("1234 Main Street")

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

Val($12.00)

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

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

sText = String(25, "A")

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

sText = Space(25)

sets sText to a string consisting of 25 spaces.

string, find location of –

this works in VBA

InStr([name]," ")

this works in VB.NET

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

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

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

string, remove characters from beginning, end –

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

Or, easier for blanks

Addr: LTrim([Address])

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

string, remove characters from –see strip out characters

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

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

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

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

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

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

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

   ' Pass back corrected string.
   StripString = strHoldString

StripStringEnd:
   Exit Function

StripStringError:
   MsgBox Error$
   Resume StripStringEnd
End Function

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

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

Me!subProjectTabular.SetFocus

Me!subProjectTabular.Form.Recordset.AddNew

If you do want to save it, easier:

With Me!subProjectTabular.Form.Recordset

    .AddNew

   !CustomerFK = Me!ID

    .Update

End With

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

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

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

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

select * from table1

where field1 not in (select field2 from table2)

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

select table1.* from table1

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

where table2.field2 is null;

subselect example

SELECT DISTINCT t1.employeeFK, t1.timeStamp

FROM timesheet AS t1

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

 

See also timeclock example for a fairly horrible example

substring – see InStr, Left, Right

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

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

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

synchronize two subforms create common invisible field on master form

synchronize forms – see forms, synchronize

–T–

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

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

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

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

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

table, read one record at a time

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

Dim strRecip As String

Dim rs As New ADODB.Recordset

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

Do While Not rs.EOF

    strRecip = rs!namedField1

    rs.MoveNext

Loop

rs.Close

Set rs = Nothing

If linked, however, a little different:

Dim rs As NewADODB.Recordset

Dim strsql As String

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

 

With rs

    .ActiveConnection =CurrentProject.Connection

    .CursorType = adOpenStatic

    .Source = strsql

    .LockType = adLockPessimistic

    .Open

End With

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

table, update one record at a time

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

tables, list

SELECT Name

FROM MSysObjects

WHERE [Type]=1 AND Flags=0;

telnet

Sub telnet1()
    'Create the shell object
    oShell = CreateObject( "WScript.Shell")
    'Start up command prompt
    With oShell
        'Start up command prompt
        .Run( "cmd.exe")
        'Send keys to active window; change the
        '    ip address as needed.
        Pause(1)
        .SendKeys( "telnet mail2 25")
        'Emulate the enter key
        oShell.SendKeys( "{Enter}")
        Pause(1)
        'write the user name to the cmd window
        'oShell.SendKeys"USERNAME"
        'oShell.SendKeys("{Enter}")
        'write the password to the cmd window
        'oShell.SendKeys"PASSWORD"
        'oShell.SendKeys("{Enter}")
        .SendKeys( "helo mail2")
        .SendKeys( "{Enter}")
        Pause(0.5)
        .SendKeys( "mail from: you@yourdomain.com")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( "rcpt to: you@yourdomain.com ")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( "Data")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( "subject: This is a test mail")
        .SendKeys( "{Enter}")
        .SendKeys( "to: Joe")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( "This is the text of my test mail.")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( ".")
        .SendKeys( "{Enter}")
        Pause(2)
        'Exitthe program
        .SendKeys( "% ")
        .SendKeys( "{Enter}")
        Pause(1)
        .SendKeys( "quit")
        .SendKeys( "{Enter}")
    End With
End Sub

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

=DlookUp("[NameOfField]","[qryNameOfQuery]")

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

"The 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." - set the UniqueTable property of the form to see if Access can create aResync Command string for you.  Otherwise, in the “BeforeUpdate” property:

Private Sub status_BeforeUpdate(Cancel As Integer)

sSQL = "SELECT * "& _

    " FROM tasks " & _

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

    " or status is null"

 

If IsNull(Me!ID) Then

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

Else

  Me.ResyncCommand = sSQL

End If

End Sub

thermometer -- see meter

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

time clock example

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

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

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

AND (t2.timeStamp =

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

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

AND t1.actionFK=1

AND t2.actionFK = 2)

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

 

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

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

time elapsed - see elapsedtime or elapsed months

timer

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

tool bar missing – compact, repair database

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

trailing characters, remove – see string, remove characters from

translate phone number - see convert phone number

transpose

transpose rows, columns

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

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

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

http://download.microsoft.com/download/office97pro/sp/1/win98/EN-US/Jet35sp3.exe

Details here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;172733

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

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

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

Then turn right around and installed them into the Registry:

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

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

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

–U–

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

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

union, insert into new table

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

note the alias

unique values, number of – see number of unique values

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

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

update inner join

This works in MS Access but not in SQL Server:

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

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

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

or this works in SQL Server but not in MS Access

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

updates - see patches

upsizing wizard

SSWUpsizing PRO! - Access to SQL Server Upsizing Utility

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

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

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

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

Sub ShowUserRosterMultipleUsers()

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Set cn =CurrentProject.Connection

Set rs =cn.OpenSchema(adSchemaProviderSpecific, _

,"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

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

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

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

While Not rs.EOF

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

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

rs.MoveNext

Wend

End Sub

utilities - see also libraries

diaglenet.com

Trigeminal

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

Utter Angel's MS Access Downloads

–V–

version of access – SysCmd(acSysCmdAccessVer)

version of operating system – see operating system, which version

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

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

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

–W–

web, display – GDB, enhanced GDB

week –

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

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

white space, remove

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

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

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

Windows version – see operating system, which version

write table to a file

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

–X–

–Y–

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

–Z–

zeroes, pad with– see padwith zeroes

zip code, split on a space –

split based on the last instance of a space usingInStrRev:

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

zip code, split on dash –

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

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

No's

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

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

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

2101 - see filtering fails with error 2101