Refreshing a Form

[AccessHelp]

Hello all,

I have a form to delete records (using a delete query) from a table. Below
is a sample code that I use to append the records to another table, delete
them and refresh the form to remove the deleted records off the form when the
user click on the button on the form.

docmd.setwarnings false
docmd.openquery "Append_Query",acViewNormal, acEdit
docmd.openquery "Delete_Query",acViewNormal, acEdit
Me.Refresh

For some reason, when I click on the records, the records are appended and
deleted. However, the records are still showing on the form with "#Delete"
in the fields. I thought when I use the code "Me.Refresh", it would remove
the deleted records from the form.

Am I missing something? How can I do to remove the deleted records from the
form, instead of showing on the form with "#Delete"?

Thanks.

 

[Klatuu]

Use a Requery rather than a Refresh
But, what is the purpose of moving records from one table to another?
There are rarely reasons to do this. If there is a change in status to
records, the more common technique is to have a field in the table denoting
that status.
About the only reason for what you are doing is if you are importing
external data and you need to clean out the old data first; however, another
way to do that is to link to the external data (if possible) as a table, then
use the append query to update the Access table, then destroy the link.
This will result in faster execution and is less likely to contribute to
bloat.

 

[AccessHelp]

Hi Klatuu,

I am appending the records that users will be deleting permanently to a
table in another BE database for audit trial. I told the users that it is
not a best practice to have a feature to delete the records permanently. The
users insisted to have a feature to delete the records. So I created a BE
database for those deleted records.

Thanks for your helps. If you have any suggestions or ideas, I would be
appreciated.

 

[Klatuu]

God bless users. Where would we be without them?
In reality, the audit trail should have been created at the time of any
change to the records. Also, this tells me this is not really a relational
database.
I feel your pain.

 

[AccessHelp]

Hi Klatuu,

Please expand your thought on why you think "this is not really a relational
database". In addition, what would you do if you are in my shoe? Thanks.

 

[Klatuu]

In a true relational database, a major table would most likely have related
tables. when you have related tables and delete the parent record, it will
leave orphans floating around. If you turn on referential integrity, but do
not allow cascading deleted, it will trow an error if you try to delete the
parent. If you allow cascading deletes, you run the danger of deleting a
child that belongs to more than one parent.
Now, I could be wrong and you have everything established correctly, but the
evidense is you have only a loose collection of tables. But even with that,
if another table depends on data in this table, there can be problems.

As to what to do, I don't know. If the user will not listen to your
professional advise, your only options are to change jobs or comply. It is
your duty to point out the issues regarding the flaws in the design (as
diplomatically as possible), then do what they want even if it is wrong.
Then if problems arise, you can cover yourself with the reminder you
suggested a different design.

 

[AccessHelp]

Hi Klatuu,

Thank you very much for taking the time explaining to me. Sometime, I don't
understand if the users want to do freely to the data, why they even bother
asking us to design a database.

Thanks again.

 

[Klatuu]

A universal problem. "Let me do whatever I want, but be sure my data is
always accurate and update to date, and oh, by the way I have never designed
a database, but I will tell you exactly how you should do it"