Need help with strSQL

  • Thread starter Thread starter nlburgess via AccessMonster.com
  • Start date Start date
N

nlburgess via AccessMonster.com

What is wrong with this code:

strSQL = "UPDATE Master_CASEMANAGER "
strSQL = strSQL & "SET Master_CASEMANAGER.CWFMSPLookup = 'Yes', "
strSQL = strSQL & "Master_CASEMANAGER.NotESRD = 'Yes', "
strSQL = strSQL & "Master_CASEMANAGER.cmUser = [forms]![Logon]![cboUserName]"
strSQL = strSQL & "WHERE hicn = " & Me.hicn & ";"

DoCmd.RunSQL strSQL
 
Line 5 needs a space before the WHERE. Also Line 5 assumes tha HICN is a
number value and not text.

Line 4 requires that the form be open. I think you are ok with using the
form reference since you are using DoCmd.RunSQL but if you still get an
error you might try changing the line to
strSQL = strSQL & "Master_CASEMANAGER.cmUser = """ &
[forms]![Logon]![cboUserName] & """"

Line 2 and 3 assume that the fields CWFMSP and NotESRD are text fields which
you are putting the string Yes into. If they are Yes/No (boolean) fields
then CHange 'Yes' to True or change it to -1

Line 1 look great.

strSQL = "UPDATE Master_CASEMANAGER "
strSQL = strSQL & "SET Master_CASEMANAGER.CWFMSPLookup = 'Yes', "
strSQL = strSQL & "Master_CASEMANAGER.NotESRD = 'Yes', "
strSQL = strSQL & "Master_CASEMANAGER.cmUser =
[forms]![Logon]![cboUserName]"
strSQL = strSQL & " WHERE hicn = " & Me.hicn & ";"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
nlburgess via AccessMonster.com said:
DoCmd.RunSQL strSQL
Click to expand...

You will get a warning message here.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

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.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top