Delete Record from table

[lilmark]

I am trying to delete specific records from a table using VBA.

I believe I have the SQL part of the coding, but I am not sure how to do the loop part of the coding.

strSQL = "DELETE FROM tblAcct WHERE date = '1/1/06'"
DoCmd.RunSQL

I'm not sure how to make the coding so it goes through every record. I know I need a loop, but not sure what to use for the condition.

Do Until ?????
code
Loop

On a side note the tblAcct on this access form is linked to another table on another access form. I don't know if that will effect anything, but thought I should mention it.

[sevenhalo]

That command will delete ALL of the records where the where clause is meet. I guess I don't understand why you would need a loop? If there more detail to this problem?

As for your side note, if they both are linked to the same table, you won't have an problem. You will, however, have a problem with asyncronization between the different forms. If one form is open and the other form deletes; the first form won't reflect the changes until it's requeried (this is a whole new beast to deal with).

Ohh, and the syntax is:
DoCmd.RunSQL strSQL

[Ranthalion]

lilmark,

I don't think the syntax on the SQL string is correct.

strSQL = "DELETE * FROM tblAcct WHERE [date] = #1/1/06#"
DoCmd.RunSQL strSQL

I use Access 2003 and I would need the * after DELETE for it to work. Also, if date field is a string, then you had the last part right, but probably it is a Date datatype, so I put # symbols around it in stead of single quotes.

Hope this helps.

-Ranthalion

[lilmark]

Oh ok. I thought I had to loop it through each record one by one and delete them. I don't use SQL a lot and have never used it to delete so it's still new to me. I guess I'm just used to using loops to go through all the records to search for a specific record with VB 6.0.

This program is simple. It is just a SINGLE form that the user will use to input data. They will not be able to view any other data. This SQL delete is just to be run when the user opens the program so it will delete any record that is a month old. So, I don't think I'll have any problems unless there's something else you can think of.

Ya, sorry, forgot to add the strSQL to end of that command.