I have come across www.fontstuff.com by Martin Green.

He seems to be a gifted teacher (so I recommend the site for some
things like using SQL in the recordSource property of a form or using
SQL from within VBA).

I am inclined to send the website $10 for the free materials I've used
via PayPal.

However, I don't trust websites without a recommendation from some
neutral source.

Do you trust the www.fontstuff.com site?

Is there some web site verifier that you know about?

Thanks in advance.
Strasser

On Thu, 12 Nov 2009 03:45:54 -0800 (PST), Strasser
<wsfstrasser (AT) yahoo (DOT) com> wrote:

You could google him and find out more.

-Tom.
Microsoft Access MVP

Strasser,

I have met Martin, and I know that this is a genuine site, and it is his.

As you say, he is a gifted teacher, and I am sure he would appreciate your
acknowledgement of his capabilities.

Bob

"Strasser" <wsfstrasser (AT) yahoo (DOT) com> wrote

On Nov 12, 8:39*am, "Bob Phillips" <Bob... (AT) somewhere (DOT) com> wrote:
Thank you both!
Strasser

Strasser <wsfstrasser (AT) yahoo (DOT) com> wrote:

I very briefly checked out his page on the NotInList event. While exhaustive he did
one thing in his code that highly irritates me. He used docmd.setwarnings which we
should never, ever use.

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The problem with DoCmd.RunSQL is that it ignores any errors. Either of the
following will display any error messages received by the query. If using DAO, use
Currentdb.Execute strSQL,dbfailonerror.. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText You
can then remove the docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. Otherwise weird things may happen later on
especially while you are working on the app. For example you will no longer get the
"Do you wish to save your changes" message if you close an object. This may mean
that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As
always YMMV.

Sure, send him the $10. Seems decent enough.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote:

I sent him an email on this topic.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote in
news:8c5pf511kb8ojgjnlen422nio652sns9m7 (AT) 4ax (DOT) com:

Anyone who is using DoCmd.RunSQL can easily replace it with my
SQLRun() function (posted after my sig). It was written for that
purpose, but was an adaptation of code I was already using so that I
didn't have to repeatedly write error handlers in every sub where I
used:

db.Execute strSQL, dbFailOnError

I moved the whole thing to a sub so that any errors would be
handled, and then when I kept seeing people using DoCmd.RunSQL, I
rewrote it and renamed it so that it could replace RunSQL. It also
does a lot more than DoCmd.RunSQL, since it can execute SQL on a
database other than the CurrentDB (without requiring complex IN ''
in the SQL's FROM clause), and it also returns the number of records
affected. It can be used as a sub or as a function.

(the code version below has the transactions commented out because
in the most recent app I've been using, it was causing problems)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler

If db Is Nothing Then Set db = CurrentDb
'DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
'DBEngine.Workspaces(0).CommitTrans

exitRoutine:
SQLRun = lngRecordsAffected
'Debug.Print strSQL
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & err.Number & ": " _
& err.Description, vbExclamation, "Error in SQLRun()"
Debug.Print "SQL Error: " & strSQL
'DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function

Tony, David,

In this respect, let's say I have a maketable query. With setwarnings set to
False I won't get the message that the table already exists, which in some
cases is what I want. In this case, when using 'Currentdb.Execute
strSQL,dbfailonerror', you do get that error. Do you suggest that I should
trap for that specific error and let Access ignore it?

I've noticed that in an UPDATE INTO query, with 'Currentdb.Execute
strSQL,dbfailonerror', you won't get the question if you want to proceed
with adding x number of records, like with setwarnings set to false. Those
messages do show up if you use docmd.runsql without the setwarnings. Am I
correct?

Last question. Let's say I have a long sub in which I use David function's
more than once. The function doesn't return that the query encountered
errors. If I want to stop the sub when the function encountered an error, I
will have to rewrite this function to be able to use it, right?

Thanks,
Lars


"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> schreef in bericht
news:8c5pf511kb8ojgjnlen422nio652sns9m7 (AT) 4ax (DOT) com...

"Lars Brownies" <Lars (AT) Browniew (DOT) com> wrote in
news:hh3da2$i0l$1 (AT) textnews (DOT) wanadoo.nl:

No. Handle the error, not ignore it. If you want to replace the
existing table, then delete it before you execute your SQL. In other
words, make sure the error conditions are taken care of before you
run your MakeTable.

At least, that's my philosophy, i.e., don't raise an error you can
anticipate.

Yes. If you want to prompt your user, you have to do it yourself
(though you won't know how many records were involved unless you
wrap it in a transaction and prompt before it's committed; that's
actually what is happening with DoCmd.RunSQL with SetWarnings ON).

In its unaltered form, my SQLRun() function will handle the error,
telling the user what error occurred, and not completing the SQL.
You'd have to deal with each error with each call to SQLRun().

My function will stop with any error at all and pop up a MsgBox
informing the user of the error number and description. It's right
there in the code. If that's not what you want, you'd have to
describe it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks for the insight David. I will have some rewriting to do :-)

What I meant was that in some occasions, if your function gives an error, I
want the calling sub to stop as well. I think the only way to do that, is to
give a False or True back to the calling sub.

Lars


"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> schreef in bericht
news:Xns9CED9D0E4C05Bf99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.82...