Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 16th, 2004, 12:31 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with DoCmd.RunSQL command

I need help please. I'm new at VBA and don't understand what RunSQL mead or does. Also have a problem with a some code. Sometimes it works and sometimes it does not work.

The first thing I do is create a string :

SQL ="Delete * from tblpatset_Temp where (testnumber='11.2.3R');"

This is the string that is sent to a routine call RunQuery: ie;

Sub runQuery(sql As String)
   On Error GoTo Error_RunQuery

   DoCmd.SetWarnings False
     MsgBox sql 'I verify the correct sting
     DoCmd.RunSQL sql
   DoCmd.setWarnings true

Exit RunQuery:
  Exit Sub

Error_RunQuery:
   more error code

Sometimes when the string returns from RunSQL it goes to DoCmd.SetWarnings True then exit sub.

Sometimes when the string returns from RunSQL it goes to Error_RunQuery and the delete does not happen.

Why??? What does RunSQL return? A true/false statement?

Any ideas?


  #2 (permalink)  
Old July 16th, 2004, 01:16 PM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The RunSQL method does just that, it runs the SQL query you've setup with the variable called "SQL". And because you've turned off the warnings prior to RunSQL, nothing will be returned. The query will run, and in the case of your example, delete the records that match the criteria from the specified table. If you comment out the line 'DoCmd.SetWarnings False', you should receive a message telling you the number of records that will be deleted and that if you proceed, the action cannot be undone. But it is possible the database properties may be set to not display this warning. To check this go to Tools|Options...|Edit/Find and check the settings in the Confirm group.

Darrell L. Embrey
  #3 (permalink)  
Old July 16th, 2004, 03:18 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I commented out the 'DoCmd.SetWarnings False' and when the program returns and works correctly it will display a message telling me the number of records that will be deleted. However, it still comes back and jumps over the 'DoCmd.SetWarnings False' and goes to the Error_RunQuery: and not delete the record.


  #4 (permalink)  
Old July 21st, 2004, 03:32 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found out why the items are not getting deleted when I run a DoCmd.runSQL DELETE * query. The table that it is using is called "tblTemp" and is used to temporarily store items until done then the items will tranfer to a static table. It turns out that if there is more the one item of the same name the system gets confused and does not know which one to delete. I don't know why this is. Also, the table is linked and don't know how to find out who it's linked to. I tried deleting all 800 records but it will not let me delete 34 of them. I suspect this 34 are linked to another table. I don't know.

Any help will be appreciated.

Rick



Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DoCmd.RunSQL to return values jscully Access VBA 10 August 21st, 2017 12:58 PM
Docmd.runsql anne.burrows VB How-To 2 October 25th, 2006 08:21 AM
DoCmd.RunSQL dates and time TarkaDahl Access VBA 3 May 11th, 2006 11:19 AM
Removing the MsgBox in DoCmd.RunSQL Update arfa Access 2 March 24th, 2006 08:05 PM
docmd.runsql "select RodMead Access VBA 2 July 31st, 2004 02:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.