delete records query / macro?

[tradmusic]

Hi,

Is it possible to set up a query / macro that automatically deletes records
held in a particular query table?

For example, I wanted to have a Yes/No field in my database that would
basically be an Include / Delete option - if ticked, then that record would
be pulled into a query table of Deleted records.

Of course, they're not deleted, they are just marked as such - so is it
possible to then create a command that will delete out, maybe weekly or
something, all records held in the Deleted Records query table?

Hope that makes sense!
Nath.

 

[Scott McDaniel]

You could ... in the AfterUpdate event of your tick box, do something like
this:

If Me.YourCheckBox.Value Then
CurrentProject.Connection.Execute "INSERT INTO
YourDeleteTable(strTableName, varUniqueID, strUniqueIDFieldName) VALUES('" &
TableNameHere & "'," & Me!YourIDFieldHere & ",'" & YourFieldNameHere & ")"
Else
CurrentProject.Connection.Execute "DELETE * FROM YourDeleteTable WHERE
strTableName='" & TableNameHere & "' AND varUniqueID=" & e!YourIDFieldHere
End If

Note that you would need a table to store this information, and you would
need to add the three fields referenced in the first If statement ...

To delete those records, use code like this:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM YourDeleteTable",CurrentProject.Connection

Do Until rst.EOF
CurrentProject.Connection.Execute "DELETE * FROM " & rst("strTableName")
& " WHERE & " rst("strUniqueIDFieldName") & "=" & rst("varUniqueID")
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Personally, I believe your life would be easier if (a) you'd add a blnDelete
field to each table and (b) bind that control to the recordset of each form.
Then, whenever desired, you'd just issue this statement:

CurrentProject.Connection.Execute "DELETE * FROM YourTable WHERE
blnDelete=True"

You could easily build a loop that would iterate through all your tables (or
only those you select) and fire this code.

 

[tradmusic]

Hi Scott. Thanks for replying.

I don't have an AfterUpdate event happening. I am using really simple
tables and queries.

My tick box is called "include" and, when the value is False for a
particular record, it appears in my "deleted records" query table. I then
wanted to create something that would, at a set time / day, delete all
records held in this query table.

I'm not into the ASP/VBScript at this stage, I just wanted this all to
happen in Access, and then I can set up my pages seperately.

Does that make sense?

What is blnDelete and how do I build that loop? That sounds like what I'm
trying to do - basically getting it to say, "ok, that field is false, so I
can delete that record". Is this a macro or something?

Thanks for your help
nath.