Step #7 - Edit mcrRefreshDatabase Macro - Add MsgBox and StopMacro Actions
1. Open mcrRefreshDatabase Macro in Design View
2. In the Action column on Row 8, type: MsgBox
- Update the MsgBox's Condition Statement to:
Code:
DCount("[CUSIP Number]","tblImportStocks")=0
- Then, update the MsgBox's Action Arguments per the following screenshot:
Attachment 617
3. Next, in the Action column on Row 9, type: StopMacro
- Update the StopMacro's Condition Statement with three periods: ...
See StopMacro Action on Row 3 for example on updating the three periods
4. Save and close Macro
The attached Access 2007 database (inside zip file) is what you should have.
WHAT YOU SHOULD KNOW OR BE AWARE OF:
1. You must understand our Macro's objective from here. Its objective from here is to take ALL records (73 records) from our tblImportStocks table and update the tblMainStocks table (main table will be created in our next step). We need Access to: 1) SYNC fields of records that exist BOTH on the tblImportStocks table (weekly spreadsheet) and the tblMainStocks table, 2) APPEND any records from the tblImportStocks table into the tblMainStocks table that DO NOT already exist there, and 3) DELETE any records from our tblMainStocks table that DO NOT EXIST on the tblImportStocks table (I like to refer to these records as "dropped records").
2. Why add the above steps to our Macro?
Let's say your database is currently tracking 73 stocks based on the last database refresh. Your employer forwards you the new refresh file of the week. You immediately attempt to run the database refresh process using the new refresh file, not knowing the refresh file contains ZERO records (only field names). Something must have gone wrong when the refresh file was extracted from the online brokerage service. What are the repercussions? In accordance with the last paragraph above (3), the refresh process will DELETE ALL records from the tblMainStocks table due to the fact that ALL these DELETED records NO LONGER EXISTED on the weekly spreadsheet. In other words, you just lost ALL your data in your database which is now unrecoverable unless you backed up your database. Tough lesson to learn! Therefore, to prevent such data loss, we had to tell Access to stop the refresh process IF no records are found in the tblImportStocks table after the Macro's TransferSpreadsheet Action.
Feel free to take the Macro for a spin. But first, remove ALL RECORDS from the Excel spreadsheet EXCEPT FOR THE FIELD NAMES and run the Macro. Access should throw the critical pop-up window telling you there were NO records found. The Macro will stop running at this point in the refresh process. And, we can now sleep at night knowing that we will not lose any data during a database refresh.
Restore the previous Excel spreadsheet containing ALL records and run the Macro again. You should see ALL 73 records restored back in your tblImportStocks table.
We can now move on to creating our main tblMainStocks table.
Continued...
-RC