DoCmd.RunSQL - Need help!

[Todd H]

I have a budget management application, and the budget information
goes into tblProjectLineItems. I have three “set up” records that I
want to automate. When I enter the budget (I have a form/query to do
this), I want to automatically populate the other two “set up”
records. In addition I have another table, tblPayments, that I track
the project/actual cost by cost code; I want to populate this record
as well.

I think the DoCmd.RunSQL is the proper function, but I can’t seem to
get the syntax correct. I also want to “silence” this operation (no
confirmation dialogs).

Private Sub CostCode_AfterUpdate()
'DoCmd.RunSQL "INSERT INTO tblProjectLineItems (CostCode, Amount,
BMNo, Contract) VALUES (" & Me.CostCode & ",0,9999,” N/A “);"
'DoCmd.RunSQL "INSERT INTO tblProjectLineItems (CostCode, Amount,
BMNo, Contract) VALUES (" & Me.CostCode & ",0,0000,” N/A “);"
'DoCmd.RunSQL "INSERT INTO tblPayments (CostCode,ActualCost,
ProjectedCost, BillingPeriodNo, Contract) VALUES (" & Me.CostCode & ",
0, 0,1,” N/A “);"
End Sub

tblProjectLineItems; CostCode is text, Amount is Currency, BMNo is
number, Contract is text
tblPayments; CostCode is text, Actual/ProjectedCost is currency,
BillingPeriodNo is number, Contract is text

Any help is appreciated. Thanks, Todd

 

[Tony Toews]

I think the DoCmd.RunSQL is the proper function, but I can’t seem to
get the syntax correct. I also want to “silence” this operation (no
confirmation dialogs).

Almost but not quite.

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.

Private Sub CostCode_AfterUpdate()
'DoCmd.RunSQL "INSERT INTO tblProjectLineItems (CostCode, Amount,
BMNo, Contract) VALUES (" & Me.CostCode & ",0,9999,” N/A “);"

My newsgroup reader is showing some strange characters around N/A.

You'll want to use single quotes on text values. So for the above it
would be

currentdb.execute "INSERT INTO tblProjectLineItems (CostCode, Amount,
BMNo, Contract) VALUES ('" & Me.CostCode & "',0,9999,'N/A');"

There is a single quote just after the ( and just after the & " as
well as around N/A.

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 convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/