Microsoft Access 2010 VBA Programming Inside Out

Errata for Microsoft Access 2010 VBA Programming Inside Out




The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page XXVIII
Errata & Support

Link http://go.microsoft.com/FWLink/?LinkId=223727 leads to http://examples.oreilly.com/9780735659872-files/ But there is no link from the oreilly pages to the Submit Errata page which I am using now.

Note from the Author or Editor:
Yes I can confirm that the link does not take you to anywhere with a link to register errata.

Mayo Marriott  Dec 12, 2011  Oct 12, 2012
Other Digital Version
I
Kindle has no page #s, Book Location = 15095

The "Inside Out" that is after Fig. 18-2. CurserLocation has Default setting and alternative setting the same "adUseServer". Is this correct? Please excuse the lack of correct page number, just wanted to submit my question w/o being given an error message. Thanks.

Note from the Author or Editor:
Page 662 INSIDE OUT Ine text which describes the CursorLocation make the following change :- "...the alternative setting is adUseServer..." should read "...the alternative setting is adUseClient..."

Anonymous  Jun 06, 2012  Oct 12, 2012
Printed
Page 000
Accompanying code

There is no trap in the cmdPick_Click() sub in the form class module for frmListBoxMultipleSelections2 if no item is selected. IsNull(Me.lstProducts) Then MsgBox "No product selected": Exit Sub added as first line of the sub would be neater. By default no item is selected in the products list so error is fairly inevitable for the click happy user. The reverse routine of deselecting from the selected products list seems OK. I would personally prefer an explicit test of an item being selected rather than reliance on replacement of an empty string with an empty string in the rowsource for the combo.

Note from the Author or Editor:
On Page 291, after the comment add the following line of code (note this all needs to be on a single line) If IsNull(Me.lstProducts) Then MsgBox "No product selected": Exit Sub The code will then look as follows :- Private Sub cmdPick_Click() ' add current item to selected list If IsNull(Me.lstProducts) Then MsgBox "No product selected": Exit Sub

johnbirt  Jul 24, 2012  Oct 12, 2012
Printed, Safari Books Online
Page Section 2.4.6
for loop in code section

syntax error the procedure, sub mod_Loop_FOR() is missing the next statement in the for loop. It is present in the code sample for the book, but missing on the page.

Note from the Author or Editor:
On Page 81 at the bottom the the code section at the bottom of the page it reades :- If lngCount > lngMaxRecords Then Exit For End If rst.MoveNext End If This should read as :- If lngCount > lngMaxRecords Then Exit For End If rst.MoveNext Next End If Before the End If there is a missing Next statement.

Terry Silveus  Sep 19, 2012  Oct 12, 2012
Printed, Safari Books Online
Page Section 3.3
SQL and Embedded Quotes

The comment is incorrect - "replace an embedded single quote with two single quotes" is repeated for the section of code that should be commented "use double quotes as delimiters"

Note from the Author or Editor:
Page 99 in the sample code. The following comment occurs twice :- ' replace an embedded single quote with two single quotes The second time it occurs it is wrong and should be replaced with :- ' use double quotes as delimiters

Terry Silveus  Sep 20, 2012  Oct 12, 2012
Printed
Page 28
Figure 1-43

Accompanying text on page 28 and in figure 1-43 reference an intentional error that is supposed to be included in the companion code. The intentional error in fact does not exist in the companion code. The intentional error was supposed to be: If lngPosSpace <>0 The accompanying code contains the correct code: if lngPosSpace = 0 As a result, the reader is unable to duplicate the results in the book.

Note from the Author or Editor:
The required correction has been made to the sample download database (no correction is required in the book). The revised file to download is located at http://www.upsizing.co.uk/VBAInsideOut.aspx.

Tom Fulmer  Sep 16, 2011 
Printed
Page 28
3rd paragraph

The page refers to the downloaded database, and anticipates that the code will not work. In the example db the code DOES have the "=" and not the "<>" as described in the book result >>> I got even more confused Simon

Note from the Author or Editor:
Hi Simon, My apologies, it looks like the current download is missing a corrected file, which includes the line lngPosSpace <> 0 rather than the corrected code which reads lngPosSpace = 0. I will get the download updated for this. Many thanks for bringing this to my attention. Regards Andy Couch

simon fenna  Sep 10, 2012 
Printed, Other Digital Version
Page 46
First paragraph

It's not clear how the code in the paragraph would operate (no code to establish which version of Access was running) or how the compiler directive # would change the logic flow versus the same code without the compiler directive. The example might be more useful if it included code to identify the Access version before the compiler directive code; e.g. calling something like: Public Function AccessVersionID() As String Select Case SysCmd(acSysCmdAccessVer) Case 7: AccessVersionID = "95" Case 8: AccessVersionID = "97" Case 9: AccessVersionID = "2000" Case 10: AccessVersionID = "2002" Case 11: AccessVersionID = "2003" Case 12: AccessVersionID = "2007" Case 13: AccessVersionID = "Pirated!" Case 14: AccessVersionID = "2010" Case Else: AccessVersionID = "Unknown" End Select End Function 'AccessVersionID()

Note from the Author or Editor:
This would be a low priority, but we could add in the text that "In this example you would manualy set the constant Pre2007Version to True or False". This would be inserted after the code shown on page 46, and just before the next topic "References" The proposed example by the reader is a nice idea, but it will not work because compiler constants can not be functions, variables or anything other than a constant.

Bob Schilling  Jan 13, 2012  Oct 12, 2012
Printed, PDF
Page 46
1st paragraph - code example

In the code example on page 46, the the compiler directive constant "Pre2007Version" is declared and initalized to "False" in the first code statement; however, it appears that the "rst" object variable is subsequently initialized to a DAO.Recordset2 object if the "Pre2007Version" constant is true, otherwise "rst" is intiialized to a DAO.Recordset object. Assuming that the implication is that the "Pre2007Version" constant declaration statement would first be modified to be intialized to "True" for a version that was released PREvious to the 2007 Verision, and considering that the DAO.Recordset2 object is not available in versions PREvious to Access 2007, isn't the logic in the directive "#IF...#ELSE" block actually a reversal of the intended logic (or is my mental process falling victim to some sort of "perception of set" fallacy?)

Note from the Author or Editor:
The following code lines on page 46 should be changed :- Existing #If Pre2007Version Then Dim rst As DAO.Recordset2 #Else Dim rst As DAO.Recordset #End If This should be changed to #If Pre2007Version Then Dim rst As DAO.Recordset #Else Dim rst As DAO.Recordset2 #End If

Anonymous  Jan 27, 2012  Oct 12, 2012
Printed, PDF
Page 57
2nd paragraph

The text explains how to avoid having global variables lose their values when an unhandled error occurs in the code, through the use of a function call. However the code snippet that follows shows a Sub procedure, not a function. Existing: Sub modGlobal_GetUsername2() If userName = "" Then modGlobal_SetUsername End If Debug.Print userName End Sub should be: Function modGlobal_GetUsername2() If userName = "" Then modGlobal_SetUsername End If modGlobal_GetUsername2 = userName Debug.Print userName End Function

Note from the Author or Editor:
The reader has a point here, but the suggested resolution is not correct. On page 57 change the text on line of text :- "You can also have the function test..." to "You can alternatively use a subroutine to test..."

Anthony Croft  Sep 24, 2013 
PDF, ePub
Page 75
Last section - The ParamArray Qualifier

This section on using a ParramArray states: "If you are planning to pass an array as one of these parameters, you need to be quite careful about the syntax; the array will be passed as a single variant parameter array, as shown in the following:" However, it then goes on to display the same procedure definition as the preceding example (unless my eyes deceive me). I think Andy is trying to illustrate that an array can be passed as one of the paramArray elements, but this procedure does not do this it simply replicates the more simple example above it. Tony

Note from the Author or Editor:
Replace the code on this page is identical, replace the existing two procedures at the bottom of the page with the following. Sub modProcParam_ParamArray2(ParamArray varParameterArray()) ' ParamArray must be the last defined parameter ' Because we are passing an array we have an array Dim lngCount As Long For lngCount = 0 To UBound(varParameterArray(0)) Debug.Print lngCount, varParameterArray(0)(lngCount) _ ; VarType(varParameterArray(0)(lngCount)) Next End Sub Sub modProcParam_ParamArray_Test2() Dim db As Database Dim varRowArray(2) As Variant varRowArray(0) = Date varRowArray(1) = 100 varRowArray(2) = "A text string" modProcParam_ParamArray2 varRowArray End Sub

Anthony Croft  Sep 27, 2013 
PDF, ePub
Page 93
5th indent

The text refers to "upper" and "lower" string functions, but I think this should be "ucase" and "lcase".

Note from the Author or Editor:
Change the following text on the 5th point from :- Upper, Lower and To :- UCase, LCase and

Anthony Croft  Oct 01, 2013 
Printed, PDF, Safari Books Online
Page 189
Top half

In the modRST_TableRecordset() code, the With rst sections have rst.Seek statements. Shouldn't the rst be omitted ? Also, top quarter of p. 190, the With rst section has an rst.MoveNext statement.

Note from the Author or Editor:
This error occurs on the following pages and relates to program code:- In each case the prefix for example rst in rst.Seek needs to be removed to read .Seek (watch that you leave in the full stop .Seek as the full stop is very important). On page 189 Incorrect code on two lines:- rst.Seek "=", 9 rst.Seek "=", "Thomas", "Axen" Corrected Code: .Seek "=", 9 .Seek "=", "Thomas", "Axen" On top of page 190 Incorrect code is rst.MoveNext Correct code is .MoveNext On Page 192, near the bottom of the page in the INSIDE-OUT. Incorrect code is rst.MoveNext Correct code is .MoveNext On page 212 near the bottom of the page Incorrect code is bk = rst2.Bookmark Correct code is bk = .Bookmark

Robert Lundquist  Sep 19, 2011  Oct 12, 2012
Printed, PDF
Page 457
last code listing on page (modExcel_MapLetterToColumn fucntion) and carrying over to next page

The function "modExcel_MapLetterToColumn" does not work correctly when mapping to a cell column identified by any combination of two alphabetical characters (i.e.: cells "AA" through cells "ZZ"); however, the function does in fact provide the intended results when mapping to cell identified by only a single letter, as in the case of the sample data provided in the example table, tblExcelTemplateCellMap. Nonetheless, the fucntion was obviously meant to work in either situation. Consequently, it can be made fully functional by modifying the function's final "If...Then" statement (which appears at the top of page 458) as follows: If Len(strCol) > 1 Then strChar = UCase(Mid(strCol, 2, 1)) lngCol = (lngCol * 26) + (Asc(strChar) - 64) End If Please note that the above modification contains a subtle change to the second argument of the Mid statement along with the more obvious changes on the third line of code.

Note from the Author or Editor:
Near the top of page 458 on two lines of code should be changed, the second line of code on the page, change strChar = Ucase(Mid(strCol,1,1)) to strChar = UCase(Mid(strCol, 2, 1)) On the third line of code on the page, change lngCol = (lngCol + 1) * 25 + (Asc(strChar) - 64) to lngCol = (lngCol * 26) + (Asc(strChar) - 64) The code indentation on the page should remain unchanged

Dennis Lampley  Jul 01, 2013 
Printed, PDF
Page 460
The second code listing (mod_ExcelReadCell...) on the page

In the code listing for the mod_ExcelReadCell function, the second comment line appears as follows (quote): ' Note we are ensuring that by using an Explicit ByVal that this routine can modify the value (end of quote) Even though no functionality is impaired since it appears in a comment, shouldn't the reference in the comment to the ByVal keyword be a reference to the ByRef keyword instead so that the comment is accurate?

Note from the Author or Editor:
On page 460, on line 13, change the following, change the single word ByVal to ByRef ' Note we are ensuring that by using an Explicit ByVal that this routine can modify the value to read ' Note we are ensuring that by using an Explicit ByRef that this routine can modify the value

Dennis Lampley  Jul 16, 2013 
Printed, PDF
Page 474
First sample code listing at the top of the page

The modOutlook_ListSpecificFolder() procedure contains a minor error in the eighth line of the procedure which is printed as (quote): For lngItem = 1 To fldOutlook.Items.Count -1 (end of quote) Since Outlook collections are one-based, and thus not zero-based like the default basing of VBA arrays, the code as printed will miss one item while iterating through the items collection. The problem can be resolved by simply removing the subtraction of 1 at the end of the code line so that it appears as follows: For lngItem = 1 To fldOutlook.Items.Count

Note from the Author or Editor:
Page 474, line 10 Change the line of code For lngItem = 1 To fldOutlook.Items.Count -1 to For lngItem = 1 To fldOutlook.Items.Count

Dennis Lampley  Jul 27, 2013 
Printed
Page 478
2

A really picky typo, but the first comment in the code reads "Global varaibles" and should of course be "Global variables" (!)

Note from the Author or Editor:
The 8th line down on page 478 reads as follows ' Global varaibles Change this to ' Global variables

Duncan  Mar 02, 2013 
Safari Books Online
2617
Function modProc_VerySimpleByRef

in the kindle book and in the code the word refernce is spelt incorrectly Function modProc_VerySimpleByRef(ByRef lngFirst As Long, _ ByRef lngSecond As Long) As Long ' the ByRef, says pass a refernce to the parameter

Note from the Author or Editor:
At the top of Page 72, in the first line the word "refernce" should be spelt as "reference"

Ian Bennett  Jul 25, 2012  Oct 12, 2012