Editing tables but also keep original data (1 Viewer)

killerflappy

Registered User.
Local time
Today, 19:25
Joined
Aug 23, 2017
Messages
50
Hi,

I have a complicated Access file with a lot of tables, query’s and VBA.
It works roughly like a Warehouse Management System.
The Access file is used to import orders from a couple of customers and then use barcodes scanners to collect this orders.

In the Access file the scans (for invoicing) are stored in 3 different tables:
Scans of the day
Scans of the day that can’t be deliverd (for instance if a part of the order can’t fit in the truck)
History of scans of the day and Scans of the day that can’t be delivered.

When a new day is created the scans of the day are stored in the history table for scans.
The records of these tables are also added to a MariaDB/MySQL server every morning before a new scan day. Access holds 50 days of data. MariaDB/SQL is holding all the data.
Access has some ODBC connections to MariaDB/SQL for searching history.
The scans of the day and scans of the day that can’t be delivered tables is overwrited every day on the server.

Now I want to make a better invoicing system. It’s easy for me to get the amounts per customer per product of the scans in a crosstable-query.
But the administration-department want to add/remove/edit records of the scans and history.
They want to be able to make changes every day. Also for the table of the scans of the day.
At the end of the week they can make the invoices.

To make changes in the original data is creaping me out, because the original scans are made in the warehouse and the editing will be done by the administration-department.
I reather don’t want the administration-department use the same Access file. There must be some segregation of duties.
Also adding records can cause problems with autonumbers with synchronization and making history.

I need some ideas to get an easy way to implement the requirements of the administration-department.
 

isladogs

MVP / VIP
Local time
Today, 18:25
Joined
Jan 14, 2017
Messages
18,186
Do not allow any user access to the tables or queries. Hide the navigation pane.
All interaction should be via forms.

Up to you whether you let admin add or edit existing records.
Don't allow deletion. Instead add a Boolean field Active to the table.
You could then allow admin to deactivate records if necessary.
The form can befiltered to only show active records.
The record will still exist and can be reactivated if needed.

As for autonumbers, they are only intended an a unique ID and should have no meaning.
If you add referential integrity to any tables with relationships, synchronisation should happen automatically.
 

Users who are viewing this thread

Top Bottom