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
Public Sub loop1()
' addresses start out clumped together in 3 or 4 lines each
' 3 lines
' Davy Jones
' 726 Briney St Apt A
' Bloomfield, NJ 07005
' Or 4 lines
' Sam Snead
' 7300 US Highway 55
' MS#SL430 AX 12345
' Princeton, NJ 08540
Dim i, k
As Integer
Dim thisRec,thisStateZip
As String
ReDim outputRec(5)
As String
strSQL = "SELECT * FROM CPAMailingListKatzRaw"
Dim db
As DAO.Database
Dim rstSource
As Recordset
Dim rstTarget
As Recordset
Set db =DBEngine(0)(0)
Set rstSource =CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
' ... and an emptycopy of the target with 6 fields
Set rstTarget =db.OpenRecordset("output", dbOpenDynaset)
If rstSource.RecordCount > 0
Then
rstSource.MoveFirst
i = -1
thisRec = ""
Do While Not rstSource.EOF
i = i + 1
thisRec = rstSource(1)
outputRec(i) = rstSource(1)
If InStr(thisRec, ",") > 0
Then
' we're lucky in this example 'cause commas
ONLY show upbetween city and state (once per record)
rstTarget.AddNew
With rstTarget
For k = 0
To 3
Select Case k
Case 0
' full name
.Fields(k) = outputRec(k)
Case 1
'
streetaddress, which may or may not contain 2nd address
.Fields(k) = outputRec(k)
Case 2
' either2nd address or city/state/zip
If outputRec(3) = ""
Then
' we onlyhave 3 fields, so 3rd field only contains city/state/zip
.Fields(3) = Left(outputRec(2), InStr(outputRec(2), ",") -1)
' split out city
thisStateZip = Mid(outputRec(2), InStr(outputRec(2), ",") + 1)
' stash state/zip
Else
'we have 4 fields, so 3rd field has 2nd addr, 4th field has city/state/zip
.Fields(2) = outputRec(2)
.Fields(3) = Left(outputRec(3), InStr(outputRec(3), ",") -1)
' split out city
thisStateZip = Mid(outputRec(3), InStr(outputRec(3), ",") + 1)
' stash state/zip
End
If
End Select
outputRec(k) = ""
Next k
thisStateZip = LTrim(thisStateZip)
' stashedstate/zip
' split out state
.Fields(4) = Left(thisStateZip, InStr(thisStateZip, " ") - 1)
' split out zip
.Fields(5) = Mid(thisStateZip, InStr(thisStateZip, " ") + 1)
.Update
End
With
i = -1
End If
rstSource.MoveNext
Loop
End If
End Sub
ADO recordsets, how to bind Microsoft Access forms
The following example demonstrates how to bind a form to an ADO recordset that is based on SQL Server data that shares an ADO connection with Microsoft Access.
- Open the sample project NorthwindCS.adp.
- Open the Customers form in Design view.
- Clear the RecordSource property of the form to unbind the form.
- Set the OnOpen property of the form to the following event procedure:
Private Sub Form_Open(Cancel As Integer)
Dim cn
As ADODB.Connection
Dim rs
As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs =
New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Setrs =
Nothing
Setcn =
Nothing
End Sub
- and then close the form.
.adp file, change source database for
Through GUI (Access 2007)
Windows button / Server / Connection
Select or enterserver name, select the database on the server
Using commands
in the Debug window (ctl-g):
? currentproject.BaseConnectionString
It will return something like this:
PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=Database_Name;DATA SOURCE=Server_Name;Use
Procedure for Prepare=1;Auto Translate=True;Workstation ID=WorkSta_Name
Just replace the Database_Name 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
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:
- Open and hide a form that periodically checks for the logout flag
- When flag is true, open and show the countdown form
- Continue scanning to verify flag was not reset to false
- Quit application at end of countdown.
automatically open a form on start up - see form, automatically open upon start up, hide ribbon when access starts
autonumber, convert to - Copy the table (structure only), add a autonumber field to the new table, and then run an append query to move all the fields--except append the old key field to the new 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�
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
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
' 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
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
data access pages read only – make a primary key
data types
" 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
Because dates and times are stored together as double-precision numbers, you may receive unexpected results when you compare Date/Time data. For example, if you type the following expression in the Immediate window, you receive a False result even if today's date is 3/31/2012:
? Now()=DateValue("3/31/2012")
The Now() function returns a double-precision number that represents the current date and the current time. However, the DateValue() function returns an integer number that represents the date but not a fractional time value. Therefore, Now() equals DateValue() only when Now() returns a time of 00:00:00 (12:00:00 A.M.).
To receive accurate results when you compare date values, use one of the following functions. To test each function, type the function in the Immediate window, substitute the current date for 3/31/2012, and then press ENTER:
� To return an integer value, use the Date() function:
? Date()=DateValue("3/31/2012")
� To remove the fractional part of the Now() function, usethe Int() function:
? Int(Now())=DateValue("3/31/2012")
dates, return consecutive
Public Function GetDates(StartDate
As Date, EndDate
As Date)
Dim InterimDate
As Date
InterimDate = StartDate
Do Until InterimDate = EndDate
InterimDate = DateAdd("d", 1,InterimDate)
Debug.Print InterimDate
Loop
End Function
days elapsed – see elapsed days
debug window, to bring up -- ctl-g
step through – shift-F8
decompile – see recompile, force – /decompile switch, decompile
Example: C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE C:\Users\Joe\Documents\myapp.mdb /decompile
delete using inner join – handy to delete all rows of a targettable if they‘re present in a source table
DELETE DISTINCTROW target.*
FROM target INNER JOIN source ON target.email =source.email;
Make sure you set the UniqueRecords property to Yes or you might very well get “Could not delete from specified tables”.
- Open the delete query in Design view.
- On the View menu, click Properties.
- Set the UniqueRecords property to Yes.
- Save the query, and then close it.
When you set the UniqueRecords property to Yes, Microsoft Access includes the DISTINCTROW predicate in the SQL statement of the query. The DISTINCTROW predicate retrieves unique records in a multi-table query where fields have only been selected from the one-sided table in the query.
For example, if you add both the Customers and Orders tables to a query, but only select fields from the Customers table, the query returns multiple rows for each customer who placed multiple orders. When you set the UniqueRecords property to Yes, the query returns only one occurrence for each customer as long as that customer placed at least one order.
device Attached to the System Is Not Functioning – Answer found on http://support.microsoft.com/support/kb/articles/Q139/4/34.asp and involves the following 2 files: Vbrun300.dll, Winoa386.mod
decompile from context menu
When using a shortcut to run the decompile switch on our databases, here's how you can right-click on an mdb in explorer view and decompile from the context menu that pops up.
- Open an Explorer window.
- Select "View|Folder Options"
- Select "File Types" and then select "Microsoft Access Database" from the list of file types.
- Click "Edit", then click "New" (if you also have Access 2.0 installed, you will need to do steps 4-6 for each version separately)
- In the "Action" box type what you want to appear in the context menu- e.g. "Decompile"
- In the "Application used to perform actions" box type the full path to MSAccess.exe and the rest of the command line, for example
"C:\ProgramFiles\Microsoft Office\Office\MSACCESS.EXE" /decompile "%1"
(That's all on one line and all the quotes are required)
- It‘s rumored that after this, when you right-click on an mdb you should see Decompile as one of your options in the context menu. But I‘ve not seen this at all.
dialog box, create
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
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 = "[email protected]"
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, '[email protected];[email protected]' 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("[email protected]","","","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("[email protected]", _
"My Message Subject", _
strHTML)
'A more complex example...
'blnSuccessful =FnSafeSendEmail("[email protected];[email protected]", _
"My Message Subject", _
strHTML, _
"C:\MyAttachmentFile1.txt; C:\MyAttachmentFile2.txt", _
"[email protected]", _
"[email protected]")
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?
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 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
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
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 –
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.
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.
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.
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
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
join, outer full – see outer join, full
kick users out – see automatically logout users for DB maintenance
label, associate with text box (which previously was 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
line feed – chr$(13) – for both carriage return and line feed:
cr =chr$(10) + chr$(13)
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
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
meter – SysCmd
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
naming convention – see Hungarian naming convention
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
select count(*) from
(select distinct field1 from TableName)
numbering in SQL – see rank in sequence
numbering rows in a report – use a calculated control and the RunningSum property. First, create a text box and set its ControlSource property to =1. Then set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All. The expression sets the value of the text box control to 1. Because the RunningSum property is used to accumulate the value, the text box is increased by one for every row. Entering a period (.) in the Format property box appends a period to the end of the number.
object qualifier error when testing for null – use 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
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
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.
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 support FULL OUTER JOIN:
SELECT
employee.LastName,
employee.DepartmentID,
department.DepartmentName,
department.DepartmentID
FROM employee
LEFT JOIN department
ON employee.DepartmentID
= department.DepartmentID
UNION
SELECT
employee.LastName,
employee.DepartmentID,
department.DepartmentName,
department.DepartmentID
FROM employee
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
pad with spaces -
Right(Space$(1)& Month([MAILED]),2)
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.
- Create or open the query that will collect its criteria values from the dialog box.
- Make sure that the query includes the tables you want to use.
- Add to the query design grid the fields associated with each of the dialog box controls where you'll enter criteria values. For each of these fields, enter an expression in the Criteria cell that tells Microsoft Access to refer to the control on the dialog box for the criteria values. If you need help creating the expression, use the Expression Builder. For more information, click
- Add to the query design grid the fields whose values you want returned.
For example, you could 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
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
ping IP address – see here
open
DoCmd.OpenForm stDocName, acFormPivotChart, ,stLinkCriteria
presence of form, detect - see form open?
PowerShell, open/read/write MS Access database (.accdb)
$mytemp
= [environment]::getfolderpath("mydocuments")
$filename
=
"$mytemp\companies.accdb"
$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.16.0;Data
Source=$filename;Persist Security
Info=False")
$conn.Open()
# read
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select company.* from company;"
"connection is: $($conn.State)"
$rdr
=
$cmd.ExecuteReader()
$dt
=
New-Object
System.Data.Datatable
$dt.Load($rdr)
$dt
# write
$cmd2
=
$conn.CreateCommand()
$cmd2.CommandText
="INSERT into Company (CompanyName)
values('Jumbotron')"
$cmd2.ExecuteNonQuery()
$conn.Close()
"connection is: $($conn.State)"
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
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"
If a string contains a single quote, put it inside double quotes.
If a string contains a double quote, put it inside single quotes.
If is string contains both, you can use the Chr() function to generate (asc() does the inverse of chr())
whichever kind of quote you used outside, like:
this is the " in a string
can be represented with:
this is the " & Chr(34) &" in a string"
rank in sequence – see also How to dynamically number rows in a SELECT Transact-SQL statement
select rank=count(*), t1.timeStamp
from timesheet t1, timesheet t2
where t1.timeStamp>= t2.timeStamp
group by t1.timeStamp
order by 1
read all files in a directory – see list all files in a directory
recompile, force – /decompile switch - see also decompile
With Access, sometimes even when you recompile, Access doesn't REALLY recompile everything. You have to forcibly "decompile" everything and then recompile. We do this with the/decompile "switch". The easiest way is to run “msaccess/decompile” from the “Start/Run” menu. Otherwise, here's a more complicated way how to do this which doesn‘t seem to work so well now in ME.
- 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.
- 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.
- 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".
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
- Create a new database for the program
- Import all the objects from the old into the new database. (May have to do one object type at a time to get it to work. Also, make sure “preview” is turned off)
- Repair and Compact the new file
- Set the start-up properties in the new file (Tools/Startup)
- Add the required references to the HrzEC32_new file (Microsoft Office 8.0, Hrz supporting objects, etc)
- Attach to the Meta and Data-databases
replace some arbitrary string with another (say with a carriage return) –
UPDATE History SET History.memo = Replace (History.memo,"//xx??",Chr(10));
report – also see parameterized reports, multiple arguments, pass
report footer, suppress conditionally– see footer, conditionally suppress for a report
report off a crosstab – see crosstab report
report recordset, problems setting or retrieving – you can‘t set or retrieve a report‘s recordset. See "Run-time error 2593" error when you set or you retrieve the Recordset property of a report
resize forms - see form resize
Return without GoSub - decompile – see more
ribbon, hide on startup – see hide ribbon when access starts
rotate text – see http://www.lebans.com/toc.htm – also View menu, click Properties, and then click the Other tab. Set the Vertical property to Yes. But often problems�
round - Round([UnitPrice],2).
See also
ceiling,
floor
routines – see libraries,utilities
rows, default maximum returned – see records, default maximum returned
running sum in a report – create a text box and set its ControlSource property to the field or expression that you want to sum. Then set the RunningSum property of the text box.
running total, query – dsum
runtime install problems– see install problems with runtime
screen resolution, resize forms to fit - see form resize
script - generate SQL for Access tables - see SQL for Access tables, generate
"search key not found in any record" error when importing – does one of your fields you're trying to import from an Excel spreadsheet where you select the first row to have field names have a blank name?
security .mdw, change default – c:\windows\system\Wrkgadm.exe
security warning setting, change – tools, macros, security
sequence numbers, add – see rank in sequence
setfocus for an element on a subform, force – see focus, force an element on a subform to have
shared (vs. exclusive) database – tools, options, advancedtab
sideways printing – see rotate text
single quotes – see quotes
slow performance - If all the clients have experienced a slowdown (after, say, rebuilding or compacting the database):
- If the application is client/server with the server portion on an NT box, try rebooting the NT server.
- Novell network?
SET MAXIMUM RECORD LOCKS PER CONNCECTION = 10000
SET MAXIMUM RECORD LOCKS = 200000
snaking columns in a form – can‘t do, but set up several subforms and then see next n after the top n
source of called function, to find– shift-F2
spaces, pad with– see padwith spaces
space, remove extra – see white space, remove, strip out characters, string, replace some arbitrary string with another
split a string based on a space – see also middle name, split out along with 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
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 your 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 Mid, InStr, Left, Right
suppress ability to add new records in a form – see record, new, suppress or prevent in a form
suppress existing records on a datasheet form – see record, new,show only new record (suppress existing records on a datasheet form)
suppress report footer conditionally – see footer, conditionally suppress for a report
synchronize two subforms – create common invisible field on master form
synchronize forms – see forms, synchronize
tab within a record or go to new record – form properties, “Other”, Cycle
tabs vs. tile in Access 2007 – Microsoft OfficeButton/Access Options/Current Database. In the Application Optionssection, under Document Window Options, click Overlapping Windows.
table, copy one record, field at a time. Obviously,there are easier ways to do this, but this gives basic outline to build on to customize. For an example, see mail merge, both sides
Sub readWriteTable()
' Before running,
import your data into "input" table. Copy that table
' (structure only)
into "scro_merge3".
Dim db
As DAO.Database
Dim rstSource
As Recordset
Dim rstTarget
As Recordset
Set db =DBEngine(0)(0)
' Starts with the full source ...
Set rstSource =db.OpenRecordset("input")
' ... and an emptycopy of the target
Set
rstTarget =db.OpenRecordset("output", dbOpenDynaset)
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: [email protected]")
.SendKeys(
"{Enter}")
Pause(1)
.SendKeys(
"rcpt to: [email protected] ")
.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.
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 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.
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
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
DbTools - link to download doesn't work 12/30/02
Utter Angel's MS Access Downloads
version of access – SysCmd(acSysCmdAccessVer)
version of operating system – see operating system, which version
vertical printing – see rotate text – also View menu, click Properties, and then click the Other tab. Set the Vertical property to Yes. But often problems�
visible, make column in datasheet view,– see column, hide in datasheet view
Visual Basic, Convert Access 2000 Databases to - see convert Access 2000 Databases to Visual Basic
web, display – GDB, enhanced GDB
week –
=DatePart("ww",[ShippedDate]) Week of year
=DatePart("w",[ShippedDate]) Weekday
week, subtract - DateAdd("ww",-1,[TransDate])
where “TransDate” is an input field
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
year, subtract - DateAdd("yyyy",-1,[TransDate])
where “TransDate” is an input field
zeroes, pad with– see padwith zeroes
zip code, split on a space –
split based on the last instance of a space usingInStrRev:
Zip: Mid([CityStateZip],InStrRev([CityStateZip],"")+1)
zip code, split on dash –
zip code zip:IIf(InStr([postal_Code],"-")>0,Left([postal_Code],InStr([postal_Code],"-")-1),[postal_Code])
plus 4 plus4:IIf(InStr([postal_Code],"-")>0,Mid([postal_Code],InStr([postal_Code],"-")+1),"")
–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