Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-13-2020, 12:32 AM   #1
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Move Records to Recyclebin Operation Flow

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"

Jayessh is offline   Reply With Quote
Old 01-13-2020, 02:00 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Move Records to Recyclebin Operation Flow

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Last edited by June7; 01-13-2020 at 09:56 AM.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Jayessh (01-13-2020)
Old 01-13-2020, 02:12 AM   #3
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Re: Move Records to Recyclebin Operation Flow

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 is offline   Reply With Quote
Old 01-13-2020, 05:45 AM   #4
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Re: Move Records to Recyclebin Operation Flow

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
Jayessh is offline   Reply With Quote
Old 01-13-2020, 10:00 AM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Move Records to Recyclebin Operation Flow

What makes record 'duplicate' - they have exact same date/time down to the second?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Jayessh (01-13-2020)
Old 01-13-2020, 10:01 PM   #6
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Re: Move Records to Recyclebin Operation Flow

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
Jayessh is offline   Reply With Quote
Old 01-14-2020, 11:09 AM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Move Records to Recyclebin Operation Flow

If you want to show both records then have to apply filter criteria that would match to both records.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Jayessh (01-14-2020)
Old 01-14-2020, 11:34 AM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,518
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Move Records to Recyclebin Operation Flow

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Jayessh (01-14-2020)
Old 01-14-2020, 12:13 PM   #9
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Move Records to Recyclebin Operation Flow

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Jayessh (01-14-2020)
Old 01-14-2020, 10:57 PM   #10
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Re: Move Records to Recyclebin Operation Flow

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 is offline   Reply With Quote
Old 01-14-2020, 10:59 PM   #11
Jayessh
Newly Registered User
 
Join Date: Dec 2019
Location: India
Posts: 13
Thanks: 12
Thanked 0 Times in 0 Posts
Jayessh is on a distinguished road
Re: Move Records to Recyclebin Operation Flow

Quote:
Originally Posted by Micron View Post
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
Jayessh is offline   Reply With Quote
Old 01-14-2020, 11:20 PM   #12
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Move Records to Recyclebin Operation Flow

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Jayessh (01-15-2020)
Reply

Tags
field , form , macro , query , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Move records to next syedadnan Tables 3 08-24-2013 12:07 AM
Move around records in a form FuzMic Forms 4 08-07-2011 07:17 PM
Automatically Move Records i.am.lost Modules & VBA 3 09-06-2008 02:12 PM
move records still_rookie General 5 05-06-2005 02:18 PM
Move records to another DB bmwilson Modules & VBA 1 12-14-2001 07:17 AM




All times are GMT -8. The time now is 04:10 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World