I have implemented a Navigation Form for a friend’s self-written legal accounts application to give him a multi-user version uniting his three small offices. I also split the database into a set of front ends with the NF and a backend that has all the data. The data server on which the application resides has full Office 2010 installed, as do the various workstations, but the application is run in runtime format with the /runtime modifier at the end of the command line of the CMD files that open each of the user front ends. Once running, the application presents the user with a Navigation Form that has a top row of function groups each revealing a column of functions down the LHS – For example. MACROS on the top shows various macros down the left that give access to things like data entry to solicitors’ timesheets, while REPORTS on the top shows a menu with stuff like a report listing all current client files.
Each of the buttons in the LHS sub-menus calls an embedded macro 'On click' with simple instructions like Open Report – Current files – in Report View. Each clicked function displays its output in a tab within the NF.
There is no VBA anywhere in the system. I wouldn’t know where to start and the application is so simple overall that it works very well with the simple design.
I made two errors..
Error 1. Not realising that Queries are live views and lock the whole backend database so that all the users are locked out until the query is closed. That one was solved by substituting the Queries with Reports: BUT it revealed another problem that I had not expected and it’s this that I need some advice about…
Error 2. When a user clicks on a function with a data lock already instituted by another user – or by herself – the application puts up the standard response: “You tried to lock table “ while opening it, but the table cannot be locked because it is currently in use. Wait a moment, then try the operation again.” The box has OK and CANCEL buttons. CANCEL crashes the user out of the system which is a pain and OK opens a Macro Single Step window which is also a pain. In that window the Stop All Macros button returns the user to the Navigation Form while closing the window with its X crashes the user out of the system.
Can I prevent all of this behaviour and simply return the user to the NF on both OK and CANCEL? Even though I've removed the initial reason for such a lock by substituting Reports for Queries, I expect that data lock collisions will still occur in daily use and need to trap out this unfortunate outcome.
Ed Form
Each of the buttons in the LHS sub-menus calls an embedded macro 'On click' with simple instructions like Open Report – Current files – in Report View. Each clicked function displays its output in a tab within the NF.
There is no VBA anywhere in the system. I wouldn’t know where to start and the application is so simple overall that it works very well with the simple design.
I made two errors..
Error 1. Not realising that Queries are live views and lock the whole backend database so that all the users are locked out until the query is closed. That one was solved by substituting the Queries with Reports: BUT it revealed another problem that I had not expected and it’s this that I need some advice about…
Error 2. When a user clicks on a function with a data lock already instituted by another user – or by herself – the application puts up the standard response: “You tried to lock table “ while opening it, but the table cannot be locked because it is currently in use. Wait a moment, then try the operation again.” The box has OK and CANCEL buttons. CANCEL crashes the user out of the system which is a pain and OK opens a Macro Single Step window which is also a pain. In that window the Stop All Macros button returns the user to the Navigation Form while closing the window with its X crashes the user out of the system.
Can I prevent all of this behaviour and simply return the user to the NF on both OK and CANCEL? Even though I've removed the initial reason for such a lock by substituting Reports for Queries, I expect that data lock collisions will still occur in daily use and need to trap out this unfortunate outcome.
Ed Form
