Find/Replace code - line wrap problem

[Les Isaacs]

Hi All
We have an accdb with lots and lots of modules, subs and functions, most of which have decent error trapping: so typically at the end of a sub I have

uploadEIPs_Error:

420       MsgBox "There has been an error!" & vbNewLine & _
                 "Line Number: " & Erl & vbNewLine & _
                 "Error number " & Err.number & vbNewLine & _
                 "Error description " & Err.Description & vbNewLine & _
                 "Procedure uploadEIPs of VBA Document Form_frm_EIPs"

What I want to do now is to write the errors to a table, so that I can audit them etc. I have written a sub to write the details to a table, which works fine, so now I want to call the sub whenever there is an error. I thought I could do this by replacing

_Error:

with

_Error:
Call WriteErrors(Now(),Erl,Err.number,Err.Description,{PROCEDURE_NAME} ,{MODULE_TYPE} ,{MODULE_NAME})

- and doing this globally, but in the VB window and doing Ctrl-F, in the 'replace' I can't seem to get the new line after the _Error:
Can this be done? If so I'm blowed if I can see it :banghead:
Thanks for any help
Les

 

[MSAccessRookie]

While is it possible to do what you want by adding a NewLine Insert Character <p> to the replacement string, it might not be practical, since the Replace All Feature is not completely intelligent and will replace any matches that it thinks it finds. Another approach would be to replace one at a time and approve each one as it is presented to you. That way, you will get no unexpected modifications.

-- Rookie

 

[Les Isaacs]

Ho Rookie
I absolutely take your point - I know how 'powerful' (= undiscerning!) the Replace All feature is! Having thought quite long and hard though about the code we have, I decided it would be safe to insert the call to WriteErrors between the error handling label

_Error:

and the first line of the handler itself - which is always

Msgbox("There has been an error!"  & vbnewline & _

There is always one blank line between these two lines, so I think my search would be for
_Error: then 2 new lines then Msgbox("There has been an error!" & vbnewline & _
or
_Error:<p><p>Msgbox("There has been an error!")
and the replace would be
_Error:<p>Call WriteErrors(Now(),Erl,Err.number,Err.Description,{PROCEDURE_NAME} ,{MODULE_TYPE} ,{MODULE_NAME})<p>Msgbox("There has been an error!" & vbnewline & _

Might it work?!
Les

 

[MSAccessRookie]

It looks possible, but you might need two <p>. Testing is the only way to see what it will really look like. Good luck

-- Rookie

 

[Les Isaacs]

I can't seem to be able to use the NewLine Insert Character <p> in either the Find or the Replace. Should I be typing it exactly as

<p>
i.e.
the lessthan sign, then p, then then morethan sign

or am I being thick

 

[MSAccessRookie]

Somehow I got the rules for Word mixed up with the rules for Access. Try using the vba Equivalent vbCrLf instead.

-- Rookie

 

[Les Isaacs]

Tried all these (on their own) in the Search:

vbCrLf
<vbCrLf>
*vbCrLf*
<p>
*p*

still no joy :banghead:
Surely this shouldn't be so hard

 

[MSAccessRookie]

Tried all these (on their own) in the Search:

vbCrLf
<vbCrLf>
*vbCrLf*
<p>
*p*

still no joy :banghead:
Surely this shouldn't be so hard

I did some testing on my own and have come up with the following:

1. The marker that I was thinking of was ^p, not <p>, and before anyone goes off to try it, it does not work either.
2. I am no longer sure that you will be able to do what you want.
3. I am going to have to leave the next step to others with more experience, but I will be following to see if I can learn anything new.

Sorry I was not of any real assistance.

-- Rookie

 

[David R]

Google thinks

Chr(13) & Chr(10)

will work.

 

[pr2-eugin]

I am not sure if this wil work for you, just might be a bit of luck I should say.

Select the _Error until the Next line,

Then just drag your mouse to the start of the second line, do not go back to Line 1.

Then use Ctrl + H to replace, as you have made a selection do not touch Find What. Just add what you want to the Replace bit. I am not sure if this would work !

 

[Les Isaacs]

Hi to all 3 of you
Very many thanks for all the suggestions - especially to Rookie for persevering
Unfortunately none of the suggestions have worked
Just searching for Chr(13) & Chr(10) on its own doesn't find anything, and trying to capture the linebreak as pr2-eugin suggests seems to drop whatever is representing the linebreak - the search just finds the 1st line, irrespective of what's on the 2nd line
I hope someone out there has tried - and found a way - to do this before
Thanks again
Les

 

[David R]

No, what I'm saying is I think (without testing) that:

420       MsgBox "There has been an error!" & Chr(13) & Chr(10) & _
                 "Line Number: " & Erl & Chr(13) & Chr(10) & _
                 "Error number " & Err.number & Chr(13) & Chr(10) & _
                 "Error description " & Err.Description & Chr(13) & Chr(10) & _
                 "Procedure uploadEIPs of VBA Document Form_frm_EIPs"

should work for the version that is appending to the table (your displayed errors will still use vbCrLf).

 

[Les Isaacs]

Hi David
Sorry - I'm not sure I understand what you're suggesting I do
I know that I could have had Chr(13) & Chr(10) instead of vbNewLine in the MsgBox, but as far as I know that wouldn't change anything and in any case I don't think it helps me to do what I'm trying to do: which is to insert a line a code (a call to my new WriteErrors() procedure) on a new line between the error handler label (in this case, that's "uploadEIPs_Error:") and the MsgBox expression.

So I want to search for

_Error:

(and the string being searched for should include two new lines - so the search should NOT return any cases where line after _Error: is not blank)

and replace it with

_Error:
Call WriteErrors()

Does that make sense?

 

[David R]

Sorry, I'm not being entirely clear, because I'm working in aircode based on your code samples given.

Use the vbCrLf for your MsgBox. Use Chr(13) & Chr(10) in your APPEND query to log the error. The rest of the syntax should be almost entirely identical.

 

[Les Isaacs]

Hi David
Thanks for your further reply: but I've obviously not explained what I'm trying to do adequately

1. I don't want to change the MsgBox (I can't see what difference it would make - the message itself linewraps just as I want it already)
2. I won't be using an append query to log the errors - just some sql that is in my new procedure WriteErrors() anyway

The problem that I'm struggling with is doing a global Find/Replace to insert the call to WriteErrors() after the error handle and before the MsgBox: the difficulty is getting the Find, or the Replace, to include the new line (i.e. vbNewLine, or Chr(13) & Chr(10), or vbCrLf - each of these seem to be stripped away from the Find, or the Replace, or they are treated as literal strings rather than as new lines.
Hope that explains it better
Thanks again
Les

 

[David R]

You don't need a separate function to write the data to your error log table... though you said it is already written and works except for the vbCrLf part, so keep using it by all means.

Does WriteError use a recordset, then? Post what it says now.

 

[Les Isaacs]

Hi

I haven't actually written WriteErrors() yet , but it will be pretty trivial. It will not have a vbCrLf part - just a .Add .... .Update on a DAO.Recordset.

I know that I could insert the code itself (as opposed to the new function) to the error handling, but I think it would be neater to call a function and - more importantly - if/when I want to amend the error handling I would then just need to amend the function rather than amend each and every place where I am handling errors: but in any case, whether I insert the function or the code, I still need to work out how to do a global insert

Is this not a good idea
Les

 

[David R]

Your idea is fine, and your instinct for reusable code is fine.

ALL I am saying is that the equivalent of vbCrLf for your APPEND or .Update is those two ASCII codes. You MUST use them in that order, and together. Chr(13) is Carriage Return, and Chr(10) is a Line Feed.

vbCrLf will work fine in your MsgBox. SQL doesn't understand VBA constants, so you must use the ASCII codes directly.

 

[David R]

If you don't believe me, go to the VBA Immediate window and execute the following:

? "test1" & Chr(13) & Chr(10) & "test2"

? "test3" & vbcrlf & "test4"

 

[Les Isaacs]

Hi Dave
I know about Chr(13) & Chr(10) and about vbcrlf. The problem is not about how I can include 'new lines' in a Find and in a Replace - not about how I can create new lines in a MsgBox, or in an Append, or in an .Update !!
I'm sorry that I hadn't made this clear
Les