Microsoft KB Archive/173975: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - """ to """) |
||
Line 76: | Line 76: | ||
</pre> | </pre> | ||
<br /> | <br /> | ||
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the | This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual. | ||
</div> | </div> | ||
Line 120: | Line 120: | ||
' Print error to Debug window and don't interrupt query. | ' Print error to Debug window and don't interrupt query. | ||
err_ReplaceString: | err_ReplaceString: | ||
Debug.Print | Debug.Print "Error Replacing String """ & _ | ||
strSearchFor & | strSearchFor & """ with """ & _ | ||
strReplaceWith & | strReplaceWith & """ in text """ & _ | ||
strSearch & | strSearch & """" | ||
' If there is an error, return original string | ' If there is an error, return original string | ||
' and exit the function. | ' and exit the function. | ||
Line 130: | Line 130: | ||
End Function | End Function | ||
</pre></li> | </pre></li> | ||
<li>On the Debug menu, click | <li>On the Debug menu, click "Compile and Save All Modules."</li> | ||
<li><p>Create a new query and add the table containing the Memo field:<br /> | <li><p>Create a new query and add the table containing the Memo field:<br /> | ||
</p> | </p> | ||
Line 194: | Line 194: | ||
With rs | With rs | ||
.AddNew | .AddNew | ||
.Fields(strFieldName) = | .Fields(strFieldName) = "abc" & String(2050, "x") | ||
.Update | .Update | ||
End With | End With | ||
Line 202: | Line 202: | ||
Err_FillMemo: | Err_FillMemo: | ||
MsgBox CStr(Err) & | MsgBox CStr(Err) & " " & Err.Description | ||
Resume Exit_FillMemo | Resume Exit_FillMemo | ||
End Function | End Function | ||
Line 208: | Line 208: | ||
<li>Type the following line in the Debug window, and then press ENTER:<br /> | <li>Type the following line in the Debug window, and then press ENTER:<br /> | ||
<br /> | <br /> | ||
<span class="kbd userinput"> ?FillMemo( | <span class="kbd userinput"> ?FillMemo("tblMemoTable","MemoText")</span></li> | ||
<li>Open tblTableMemo.</li> | <li>Open tblTableMemo.</li> | ||
<li>On the Edit menu, click Replace.</li> | <li>On the Edit menu, click Replace.</li> | ||
<li>In the Replace In Field dialog box, type <span class="kbd userinput"> abc</span> in the Find What text box.</li> | <li>In the Replace In Field dialog box, type <span class="kbd userinput"> abc</span> in the Find What text box.</li> | ||
<li>Type <span class="kbd userinput"> hij</span> in the Replace With text box, and then click to clear the Match Whole Field check box.</li> | <li>Type <span class="kbd userinput"> hij</span> in the Replace With text box, and then click to clear the Match Whole Field check box.</li> | ||
<li>Click Replace. Note that you receive one of the errors described in the | <li>Click Replace. Note that you receive one of the errors described in the "Symptoms" section.</li></ol> | ||
Line 221: | Line 221: | ||
== REFERENCES == | == REFERENCES == | ||
For more information about creating update queries, search the Help Index for | For more information about creating update queries, search the Help Index for "update queries, creating" and select the Help Topic "Change records as a group using an update query," or ask the Microsoft Access 97 Office Assistant "How to create an update query."<br /> | ||
<br /> | <br /> | ||
For more information about writing recursive procedures, search the Help Index for | For more information about writing recursive procedures, search the Help Index for "recursive procedures" and select the Help topic "Creating Recursive Procedures." | ||
</div> | </div> |
Revision as of 11:07, 21 July 2020
Article ID: 173975
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q173975
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
When you perform a search and replace in a Memo field containing more than 2052 characters, you receive one of the following error messages.
In Microsoft Windows 95
If you click the Details button, you see the following information:
MSACCESS caused an invalid page fault in MSACCESS.EXE.
In Microsoft Windows NT
An application error has occurred and an application error log is being generated. MSACCESS.exe
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.
RESOLUTION
The following example uses an update query that calls a user-defined function to do the search and replace:
Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit
Type the following procedure:
Function ReplaceString(strSearch As String, strSearchFor As String, _ strReplaceWith As String) On Error GoTo err_ReplaceString ' Searches the strSearch variable for strSearchFor ' and replaces it with strReplaceWith. Dim lngFoundLoc As Long ' Location of match. Dim lngLenRemove As Long ' Length of string being replaced. ' Set length of original text to skip. lngLenRemove = Len(strSearchFor) ' Set location of match. lngFoundLoc = InStr(1, strSearch, strSearchFor) ' If strSearchFor isn't found in strSearch ' just return the original string. If lngFoundLoc = 0 Then ReplaceString = strSearch ' If match is found, return original string up to match ' location, concatenate new text, and search the rest of ' the string recursively for additional matches. Else ReplaceString = Left(strSearch, lngFoundLoc - 1) & _ strReplaceWith & _ ReplaceString(Mid(strSearch, lngFoundLoc + _ lngLenRemove), strSearchFor, strReplaceWith) End If exit_ReplaceString: Exit Function ' Print error to Debug window and don't interrupt query. err_ReplaceString: Debug.Print "Error Replacing String """ & _ strSearchFor & """ with """ & _ strReplaceWith & """ in text """ & _ strSearch & """" ' If there is an error, return original string ' and exit the function. ReplaceString = strSearch Resume exit_ReplaceString End Function
- On the Debug menu, click "Compile and Save All Modules."
Create a new query and add the table containing the Memo field:
Query: qryUpdateMemos -------------------------------------------------------------------- Type: Update Query Field: <Name of Memo Field> Table: <Name of Table> Update To: ReplaceString([<Name of Memo Field>],[Search for],[Replace with]) Criteria: Field: Instr(1,[<Name of Memo Field>], [Search for]) Update To: Criteria: >0
- On the Query menu, click Parameters.
- On the first line of the grid, type [Search for] in the Parameter field, and select Text from the Data Type box.
- On the second line of the grid, type [Replace With] in the Parameter field, and select Text from the Data Type box.
- Run the query. Enter the text to search for and the text to replace with when prompted.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97.
MORE INFORMATION
Steps to Reproduce Problem
- Start Microsoft Access and open a new blank database.
Create the following table:
Table: tblMemoTable -------------------- Field Name: MemoText Data Type: Memo Indexed: No
Close the table and save it as tblMemoTable. When prompted if you want to create a primary key, click No.Create a new module and type the following procedure:
Function FillMemo(strTableName As String, _ strFieldName As String) Dim db As Database Dim rs As Recordset On Error GoTo Err_FillMemo Set db = CurrentDb Set rs = db.OpenRecordset(strTableName) With rs .AddNew .Fields(strFieldName) = "abc" & String(2050, "x") .Update End With db.Close Exit_FillMemo: Exit Function Err_FillMemo: MsgBox CStr(Err) & " " & Err.Description Resume Exit_FillMemo End Function
- Type the following line in the Debug window, and then press ENTER:
?FillMemo("tblMemoTable","MemoText") - Open tblTableMemo.
- On the Edit menu, click Replace.
- In the Replace In Field dialog box, type abc in the Find What text box.
- Type hij in the Replace With text box, and then click to clear the Match Whole Field check box.
- Click Replace. Note that you receive one of the errors described in the "Symptoms" section.
REFERENCES
For more information about creating update queries, search the Help Index for "update queries, creating" and select the Help Topic "Change records as a group using an update query," or ask the Microsoft Access 97 Office Assistant "How to create an update query."
For more information about writing recursive procedures, search the Help Index for "recursive procedures" and select the Help topic "Creating Recursive Procedures."
Additional query words: pra searching replacing
Keywords: kbbug kberrmsg KB173975