Thanks Boyd. The vba code you helped with is for the addition form for a table. There is another table with the same form and fields. The first table is for actual payment while the second table is for pending payments. Now, records in pending payments are moved to payment table with an append query when payment is made, but there will always be a duplicate PVNo in the Payment table with the append query action. this is what i want to prevent. Hope it makes sense.
The real issue is that you are moving record between table. This should be avoided and never done. I NEVER do this. In 30+ years creating accounting system I have never needed to move records between table.
It actual violates the rules off normalization when you use a table name as data for the record. In yur case the status of pendin should come from a field not the table name.
I see it liek this:
Payments are payment. Pending or not. The status of pending does not change what table the record should be in. All payment belong in the same table.
Do you have a better idea of how to go about this?
YES! There is a much better and simpler solution that used the power of a Relational Database and queries. Use a status field or calculate it as needed.
I see this a lot with people that come from an Excel background and not a relational database background.
When the status changes you want the record to move to a different report page. In Excel this was done my moving the data to the different section.
Moving data is ALWAYS DANGEROUS and ERROR prone.
In a well designed database you simple change the status field. You are done. Even better it is calculated bases on other data.
The report's query can filter that data to the correct place. same for forms. It also makes changing the status back to pending very simple. No meed to move records around again.
By using the power of queries to filter data, to the user/form is can look like the records are in different tables while in reality they are all in the same table.
