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
 
Old April 9th, 2005, 02:53 AM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default code conversion

i've never programmed in VBA before but have i can program in php and c. I'm trying to implement a simple query program but don't have a clue how to write or implement it in VBA.

for instance i have a table:

Id(PK) | Code(text) | Description (text)
----------------------------------------
1 | 00/1001 | Sensor ON
2 | 00/1001 | Sensor OFF
3 | 00/1003 | Relay ON
4 | 00/1001 | Sensor ON
5 | 00/1003 | Relay OFF

what i need to do is run a subroutine that finds pairs and deletes them, i.e. if Code[1] = Code[2] && Description[1] = "Sensor ON" && Description[2] = "Sensor OFF", delete both rows. Leaving me, using the test table:

Id(PK) | Code(text) | Description (text)
----------------------------------------
4 | 00/1001 | Sensor ON

essentially it is just 2 for loops and one conditional statement, any help would be appreciated.

thanks,

josh
 
Old April 11th, 2005, 08:17 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can't you delete the records with just a single SQL statement? Something like..

DELETE * from table Where (Description = "Sensor OFF") AND (ID in (Select ID from table where Code = "Sensor ON"))

If so, then your code can be as simple as...

Code:
DoCmd.RunSQL SQLStatement
If you don't want the user to be warned of the deletion, then...

Code:
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStatement
DoCmd.SetWarnings True
You will have to adequately quote the SQL statement. So...

DoCmd.RunSQL "DELETE * from Table Where (Description = ""Sensor OFF"") AND (ID in (Select ID from table where Code = ""Sensor ON""))"

Notice the double quotes ("") for the embedded quotes.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org




Similar Threads
Thread Thread Starter Forum Replies Last Post
conversion... ankur_icfai Flash (all versions) 1 November 9th, 2006 10:38 AM
conversion of vb to C# code KamalRaturi ASP.NET 2.0 Professional 6 September 13th, 2006 04:35 AM
Conversion anukagni Access 2 September 6th, 2006 12:22 AM
Code conversion and proper attribution qnr BOOK Beginning Linux Programming, 3rd Edition 1 April 5th, 2006 12:48 AM
conversion gbilios C++ Programming 6 August 6th, 2004 06:13 PM





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