code conversion

tr8it0r · April 9th, 2005, 02:53 AM

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

rjweers · April 11th, 2005, 08:17 AM

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