1 Attachment(s)
Un-Updateable queries and IIf if records exist in the queries
Goal: Update the table t_Scan_Errors, if there are errors, with the values obtained in queries “q_Missing_Folders” and q_Missing_MCRs_In_Folders
t_Runs is a One to Many relationship with t_Scan_Errors
Sign in under Warren Nelson, password is “Welcome” without the quotes.
To see the report, select the only report available in the combobox on the Report Parameters Form, and the date Oct 26 2017 from the date selector.
Process Overview:
Scanner fills in their day using the form Scan_Operator_Details_Center.
Once they have filled it all in they save and close it.
Having finished the scanning for the day, they run the Import Files form to import all of the files, and their respective directories
Then they run the Report Parameter form for the days summary. I have purposely deleted some folders and files, and you will notice in the Errors area, under Missing Folder #, that the folders 16 and 23 have been missed by the scan operator. This means when they were creating the sequential folder numbers to store the scans in, they skipped these two numbers.
You can also see that there are two folders listed in the “No MCR’s in Folder”, Folder 12 and 24. MCR is a particular file named 01.pdf. Every folder should have one of them, however, 12 and 24 are missing it.
t_Runs records information about each scanning of documents run.
t_Runs:
Runs_ID Primary Key, Long Integer
t_Scan_Errors is a table that holds any errors found by queries after a scanning run.
t_Scan_Errors:
Scan_Errors_ID Primary Key, Long Integer
Runs_ID_FK Foreign Key to t_Runs, Number
Error_Types_ID_FK Foreign Key to lt_Scan_Errors, Number
t_Error_Types:
Error_Types_ID Primary Key, Long Integer
Error_Name (One of Two Choices: Missing Folder or Missing File In Folder)
q_Missing_Folders is an unupdateable query that returns missing sequential folder number(s) if there are any
q_Missing_MCRs_In_Folders provides the folder number if there are any missing files named “01.pdf”
The goal is to create and then append all of the summary data to a table in a Master Database that records all of the final statistics… I have not created this until I hear advice on how I should design this. Both databases will be split and multi-user…
The report is a summary of the information I want to record as a summary table in the new database.
Also, is there a way to copy the database in VBA and compress it and move it to a folder on the server in another drive? Would anyone have a sample and/or advice?
Thanks for your help as always, VBA Express !
Oblio
