Delete Record question

[Stockwell43]

Hello,

There is a database used by one user daily. Not big maybe at most maintain
about 50 records. Every month some records need to be closed so they don't
show on a report and the database has over 200 records that are not needed
for anything.

So my question is this:

Is it safe to place a button on the form to delete ALL records where the
Balance Change cbo reads "Closed"? I normally don't like to delete records
but they are taking up space and will never need to go back to them for
anything. If there is a safe method for doing this, I would greatly
appreciate any help.

Thanks!!!!

 

[Stockwell43]

Hi Clifford,

Thank you for your response. Ok, I can do that and that will be safe and not
mess update the data in the other records? Normally I delete the rows from
the table itself but I thought if I can do it in one easy step, that would be
even better!!

Thank you again!!!

Hi,

Yes there is. 200 records is a miniscule number of records. However,
if there really is no need for the data, historical, legal or otherwise, you
can use this:

DoCmd.RunSQL "delete from [tblYourTableName] where [Balance Change] =
""Closed""

If your Balance Change actually holds a code instead of "Closed", use
the code instead.

Clifford Bass

Hello,

There is a database used by one user daily. Not big maybe at most maintain
about 50 records. Every month some records need to be closed so they don't
show on a report and the database has over 200 records that are not needed
for anything.

So my question is this:

Is it safe to place a button on the form to delete ALL records where the
Balance Change cbo reads "Closed"? I normally don't like to delete records
but they are taking up space and will never need to go back to them for
anything. If there is a safe method for doing this, I would greatly
appreciate any help.

Thanks!!!!

 

[Clifford Bass]

Hi,

You are welcome. Just noticed that I missed the final quote; it should
be ...Closed""".

Clifford Bass

Hi Clifford,

Thank you for your response. Ok, I can do that and that will be safe and not
mess update the data in the other records? Normally I delete the rows from
the table itself but I thought if I can do it in one easy step, that would be
even better!!

Thank you again!!!

Hi,

Yes there is. 200 records is a miniscule number of records. However,
if there really is no need for the data, historical, legal or otherwise, you
can use this:

DoCmd.RunSQL "delete from [tblYourTableName] where [Balance Change] =
""Closed""

If your Balance Change actually holds a code instead of "Closed", use
the code instead.

Clifford Bass

Hello,

There is a database used by one user daily. Not big maybe at most maintain
about 50 records. Every month some records need to be closed so they don't
show on a report and the database has over 200 records that are not needed
for anything.

So my question is this:

Is it safe to place a button on the form to delete ALL records where the
Balance Change cbo reads "Closed"? I normally don't like to delete records
but they are taking up space and will never need to go back to them for
anything. If there is a safe method for doing this, I would greatly
appreciate any help.

Thanks!!!!

 

[Stockwell43]

Oh, I see what it's doing, it says the word Delete in all the fields on the
records that are marked closed. I was more looking to remove those records
from the table so if I started with 325 records and had 100 that are closed,
after I hit the delete button it would show 200 records in the records
selector at the bottom.

Hi,

You are welcome. Just noticed that I missed the final quote; it should
be ...Closed""".

Clifford Bass

Hi Clifford,

Thank you for your response. Ok, I can do that and that will be safe and not
mess update the data in the other records? Normally I delete the rows from
the table itself but I thought if I can do it in one easy step, that would be
even better!!

Thank you again!!!

Hi,

Yes there is. 200 records is a miniscule number of records. However,
if there really is no need for the data, historical, legal or otherwise, you
can use this:

DoCmd.RunSQL "delete from [tblYourTableName] where [Balance Change] =
""Closed""

If your Balance Change actually holds a code instead of "Closed", use
the code instead.

Clifford Bass

:

Hello,

There is a database used by one user daily. Not big maybe at most maintain
about 50 records. Every month some records need to be closed so they don't
show on a report and the database has over 200 records that are not needed
for anything.

So my question is this:

Is it safe to place a button on the form to delete ALL records where the
Balance Change cbo reads "Closed"? I normally don't like to delete records
but they are taking up space and will never need to go back to them for
anything. If there is a safe method for doing this, I would greatly
appreciate any help.

Thanks!!!!

 

[Clifford Bass]

Hurrah! You are welcome.

Clifford Bass

 

[Brian Smith]

Oh, I see what it's doing, it says the word Delete in all the fields on
the
records that are marked closed. I was more looking to remove those records
from the table so if I started with 325 records and had 100 that are
closed,
after I hit the delete button it would show 200 records in the records
selector at the bottom.

You are seeing the "Deleted" because the table was open when you deleted the
records. If you close it and then reopen it those records will be gone.

Brian

 

[Stockwell43]

Clifford, may I ask one more question please?

After the button is clicked, is there a way to place a message to pop up to
say Are you sure you want to Delete these records? Yes, No.

Just as a safety measure! )

 

[Clifford Bass]

Hi,

Sure you can and sure you can

Actually, did the RunSQL not cause a similar question to pop up? If
warnings are turned off, it may not have. You can make sure they are turned
on with this before the RunSQL statement:

DoCmd.SetWarnings True

Or, you can do your own box:

If MsgBox("Really delete???", vbQuestion + vbYesNo + vbDefaultButton2) =
vbYes Then
' Turn off any Access generated confirmation boxes
DoCmd.SetWarnings False

....

' Turn warnings back on
DoCmd.SetWarnings True
End If

The vbDefaultButton2 makes the No button the default so if the user
just automatically hits return without thinking, it will choose No.

Clifford Bass

 

[Stockwell43]

Works great, thanks again!!!!!

Hi,

Sure you can and sure you can

Actually, did the RunSQL not cause a similar question to pop up? If
warnings are turned off, it may not have. You can make sure they are turned
on with this before the RunSQL statement:

DoCmd.SetWarnings True

Or, you can do your own box:

If MsgBox("Really delete???", vbQuestion + vbYesNo + vbDefaultButton2) =
vbYes Then
' Turn off any Access generated confirmation boxes
DoCmd.SetWarnings False

....

' Turn warnings back on
DoCmd.SetWarnings True
End If

The vbDefaultButton2 makes the No button the default so if the user
just automatically hits return without thinking, it will choose No.

Clifford Bass

 

[Clifford Bass]

You are welcome!

Clifford Bass