none
Can you use a separate instance of Access to automate entries on forms in a standalone Access app? RRS feed

  • Question

  • My work has a bespoke Access app that records the outputs of our tests, of which there are thousands. There are a few things that need to be the same for every test, but actually require manual input, which, over thousands of tests, really impacts efficiency. 

    Unfortunately we don't have access to the underlying code (hidden by managers), and I would be loath to change it anyway, as it is a work piece of software, and anything I do could impact the work of others. 

    So my question, is it possible to create something in another instance of access that will enter these recurring bits of information.

    I know I haven't included much coding info here, but would be happy to provide it for any questions that may lead to a solution.

    Many thanks!

    Monday, February 1, 2016 9:47 AM

Answers

  • Is the database split into separate front and back ends?  For anything but the most trivial application it should be, and in a multi-user environment it's de rigueur; having multiple users access a single file is a recipe for corruption.

    Assuming the database is split, there is no reason why you cannot create a separate front end file linked to the same back end as the operational front ends.  You can then include whatever functionality you wish to overcome your problem, leaving the operational front ends unaltered.

    Having said that, this might well be an instance of moving the deckchairs on the Titanic, as the fact that you have 'a few things that need to be the same for every test' suggests that the table(s) contain significant redundancies as a result of not being correctly normalized.  From what you say, the possibility of correcting the model might not be available, though.  

    Without more detail it is difficult to suggest how you might overcome the problem through the additional front end, but setting the DefaultValue property of bound controls in a data input form would seem a possible approach.  If the values are always the same then the default values could be set in the controls' property sheets as part of the form design.  If they need to be set electively, then you'll find an example as Defaults.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the default values of a couple of controls in the form can be set to the last entered value by means of check boxes, carrying them forward to subsequent records inserted via the form.  Two options are illustrated, one which does this for the current session of the form only, the other which does it for this and all subsequent sessions.  In the latter case the defaults are stored in a separate local table.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, February 1, 2016 11:18 AM Typo corrected.
    • Proposed as answer by André Santo Monday, February 1, 2016 12:22 PM
    • Marked as answer by AB19842 Monday, February 1, 2016 4:45 PM
    Monday, February 1, 2016 11:17 AM

All replies

  • Is the database split into separate front and back ends?  For anything but the most trivial application it should be, and in a multi-user environment it's de rigueur; having multiple users access a single file is a recipe for corruption.

    Assuming the database is split, there is no reason why you cannot create a separate front end file linked to the same back end as the operational front ends.  You can then include whatever functionality you wish to overcome your problem, leaving the operational front ends unaltered.

    Having said that, this might well be an instance of moving the deckchairs on the Titanic, as the fact that you have 'a few things that need to be the same for every test' suggests that the table(s) contain significant redundancies as a result of not being correctly normalized.  From what you say, the possibility of correcting the model might not be available, though.  

    Without more detail it is difficult to suggest how you might overcome the problem through the additional front end, but setting the DefaultValue property of bound controls in a data input form would seem a possible approach.  If the values are always the same then the default values could be set in the controls' property sheets as part of the form design.  If they need to be set electively, then you'll find an example as Defaults.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the default values of a couple of controls in the form can be set to the last entered value by means of check boxes, carrying them forward to subsequent records inserted via the form.  Two options are illustrated, one which does this for the current session of the form only, the other which does it for this and all subsequent sessions.  In the latter case the defaults are stored in a separate local table.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, February 1, 2016 11:18 AM Typo corrected.
    • Proposed as answer by André Santo Monday, February 1, 2016 12:22 PM
    • Marked as answer by AB19842 Monday, February 1, 2016 4:45 PM
    Monday, February 1, 2016 11:17 AM
  • PS:  Even if the file is unadvisedly not split, you could of course still create a separate front and link to its tables.

    Ken Sheridan, Stafford, England

    Monday, February 1, 2016 11:56 AM
  • Hi Ken, 

    Thanks for the advice there. I don't think the database is split, although I'm not sure how to find out. I copied it to my local system and can have a look at the underlying tables if I open it while pressing shift. I'm currently trying to get something working on my local system that can populate the needed fields automatically, then if it works locally, I'll ask if I can have it implemented higher up.

    I'll mark your reply as the answer as it's obvious you've put a bit of thought into this. 

    Many thanks!


    • Edited by AB19842 Monday, February 1, 2016 4:45 PM
    Monday, February 1, 2016 4:45 PM