Delete Record question

S

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!!!!
 
S

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!!!

Clifford Bass said:
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

Stockwell43 said:
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!!!!
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi,

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

Clifford Bass

Stockwell43 said:
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!!!

Clifford Bass said:
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

Stockwell43 said:
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!!!!
Click to expand...
Click to expand...
Click to expand...
 
S

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.

Clifford Bass said:
Hi,

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

Clifford Bass

Stockwell43 said:
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!!!

Clifford Bass said:
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!!!!
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Smith

Stockwell43 said:
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.
Click to expand...

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
 
S

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! :blush:)
 
C

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
 
S

Stockwell43

Works great, thanks again!!!!!

Clifford Bass said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete query doesn't delete records 8
Writing SQL code to delete duplicate records 0
jet + odbc sql 2005 expr- cannot delete record - complex question 2
Delete categories in a table based on a group query 3
Saving Data 4
Delete first record error with linked tables 7
History 5
Delete all records 2

Top