Move Records to Recyclebin Operation Flow (1 Viewer)

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
Hi Friendz !

Thank you in Advance as I know I'm asking experts and they know the answers already.


I need Recyclebin operation flow like :

a Form will open through Macro (So that I can make it dialog)
when I put value in it and press OK Button,
it will filter my table by Query based on value entered in that Formand then copy that record paste in another Table(name: Recyclebin) and then Deletethat filtered record from main Table.


The Field which is base for all this has a dataType as Date/Time but in "yyyymmddhhnnss" format and I cannot enter it in that format, it takes input as "dd/mm/yyyy hh:nn:ss"
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,683
Why 'move' records and not just flag record as "Inactive".

Formatting in table (I never do that in table) does not change actual stored value.

Access will expect criteria in mm/dd/yyyy hh:nn:ss date/time structure.
 
Last edited:

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
I want to move it as they are duplicate records and reason "Recyclebin like flow" if user enter wrong value, he can able to restore it from other table.

"Format" because I need a unique number for my records by automatic, Auto-Number will not serve better, My this field is serving as unique number as well as exact time and date of entry so l can get which records is latest... and if latest record also has same value as old one than it should be deleted...
 

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
Dear Friendz !

Thank you very much for your suggestions, I did it by the following way


1) Created a Find duplicate Query

2) a Form based on Find Duplicate Query, showing Records in continuous Form, I mean multiple records at a time, So that user can compare first record with adjacent record and Delete latest one.

3) for Deleting record a Button given which actually runs a Macro

4) a Macroruns 2 Queries a) Append Query for Appending current record to a Table named Recyclebin b) and a Delete Query will delete current record from Main Table

5) if record deleted by mistake, same mechanism for restoring it from Recyclebin to main Table

and tada...

Now all works fine but it will look more professional If I can able to show Two Duplicate Records at a time in my Form, but it either shows Single Record or All Records... Suggest if anyone have some Idea
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,683
What makes record 'duplicate' - they have exact same date/time down to the second?
 

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
Hi June7

No They cannot have same date and time. They will Duplicate if they have all same data except date and time... How, I am explaining here-under...

I am making a simple program for Indian GST Filling, there is a report in we have to submit while filling "GSTR3B Tax Return" in which we have to declare

figure of sales & tax inclusive in Sales as well as Purchases and tax inclusive in purchases

and then sales tax - purchases tax = if positive number we have to pay it to government and if minus we will keep it as Input Tax Credit (ITC) for next months fillings

now there may sometime businesses doing changes in backdated vouchers after filling return

so if user entered data for Nov19 in after filling the return
and if all fields are same then that record should be deleted as there is no use of that records and if there is change in any field or fields that records will treated as update in return
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,683
If you want to show both records then have to apply filter criteria that would match to both records.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
I agree with June7. There is no point in moving a record in order to 'archive' it when you can just flag it in the main table. All you're doing is making things more difficult to work with either set of records. If for example you wanted to know something about the 'archived' records, you need another query because you have another table thus all you're really doing is adding overhead. Far easier and more efficient and more powerful to simply use a main table field to identify such records and leave them where they are. Usually a date field is best for this as it also tells you when and not just which.
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,683
Well, I wouldn't play "musical tables". If record is thoroughly validated and determined it is duplicate and should be deleted, then delete it. If it is determined that a record is actually needed with a minor change then do data entry, not go find 'duplicate' in archive table and move it back with minor edit.

But, it's your db, your decision.
 

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
Hi June7 and Micron,

I Got your point, but in that case I need a new field in table which automatically compares all fields (except that date & time field) with other adjacent records, and if found all data equal it should be autofill like Duplicate, Original or something like that...

I tried but I thing this in not possible in a Table
 

Jayessh

Registered User
Joined
Dec 30, 2019
Messages
13
I agree with June7. There is no point in moving a record in order to 'archive' it when you can just flag it in the main table. All you're doing is making things more difficult to work with either set of records. If for example you wanted to know something about the 'archived' records, you need another query because you have another table thus all you're really doing is adding overhead. Far easier and more efficient and more powerful to simply use a main table field to identify such records and leave them where they are. Usually a date field is best for this as it also tells you when and not just which.


Hi Micronand June7,

I Got your point, but in that case I need a new field in table which automatically compares all fields (except that date & time field) with other adjacent records, and if found all data equal it should be autofill like Duplicate, Original or something like that...

I tried but I thing this in not possible in a Table
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,683
Might be possible with table AfterInsert data macro - it would likely have to call a VBA routine. I never use data macro, I would use code behind a form.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom