none
RESOLVED: Combo box in Access with VBA code works on only one of three customer computers with same set up--please help! RRS feed

  • Question

  • 29 Jan 16 Update: This is RESOLVED as of 27 Jan--THANK YOU!!

    Hello! I am new to this forum, so forgive me if I don't know the protocol just yet. And thank you in advance to anyone who can get me to a point where I can get my customer's machines all responding the same way!

    Scenario:
    I have a customer who has three Windows 7 64-bit computers running Office 2010. They share an Access database in which there are numerous forms that use combo boxes to find records. Generally, these work just fine, but...

    ONE of the combo boxes (copied onto multiple forms) required VBA code to allow for a search on a CompanyName field that includes apostrophes. 

    The combo box works beautifully on ONE machine only (?!), but doesn't work at all on the other two. This happens whether all three employees are in the database at once, if only one person is in but on one of the wonky machines, etc.

    We've tried updating the code, but that didn't work. 

    I had them do an Office "repair" just in case (that has helped on occasion), but it didn't help.

    I haven't been able to find any answer on discussion boards just yet, and thus I ask the question here. I appreciate any advice that can help me get my customers to get their computers to work the same. Thank you!



    • Edited by YooperGal Monday, February 8, 2016 6:14 PM
    Monday, January 25, 2016 6:48 PM

All replies

  • I would advise a sanity check in regard to a set up or cock pit error.

    You imply that this is a split database, because it is multi user.  Verify that.

    Delete or archive the db front end of one bad computer - then - copy/paste the db front end of the good computer to the bad computer.  When sitting at the bad computer also of course link to the common back as well as go thru the Trust Center set up to trust the folder of the front file and also the folder of the back file.

    Then see what happens.

    Monday, January 25, 2016 9:08 PM
  • Try compiling the front-end code on the 'wonky' computers, You could be missing a reference.

    Open up a code module and do 'Debug -- Compile'

    Monday, January 25, 2016 9:38 PM
  • it could be that the other two computers don't have the folder as a Trusted Location. Code won't run in non-trusted locations. Check File tab>Options>Trust Center.

    I bet the one that it works on either has the file in a trusted location or has its macro settings to the lowest setting "Enable all macros". 


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, January 25, 2016 9:44 PM
  • Actually, it is NOT a split database; they indicated they didn't want that. But, if that'd be the best solution, I'll offer it up to them.

    I will take another look at the Trust Center as well. 

    Thank you!

    Monday, January 25, 2016 10:35 PM
  • Thanks! I believe we tried that already, but it's worth another shot (especially if my memory is failing me and we actually didn't!). 
    Monday, January 25, 2016 10:36 PM
  • Will do.  I'm seeing them again on Wednesday and will first try the Trust Center options as you and the person above suggested, then a Debug-compile, then... the customer-dreaded splitting if need be.  

    I think I have enough to work with for now. If I'm still having trouble after trying all of the above (if necessary), I'll post again. Thanks again!

    Monday, January 25, 2016 10:40 PM
  • >>>I'm seeing them again on Wednesday and will first try the Trust Center options as you and the person above suggested, then a Debug-compile, then... the customer-dreaded splitting if need be. 

    According to your description, as far as I know that if the database file is stored on a shared network drive, since all database objects are shared, reliability and availability can become issues if there are multiple simultaneous users changing data. So I suggest that you could split database or use SharePoint site or use database to share an Access database.

    For more information, click here to refer about Ways to share an Access database

    Friday, January 29, 2016 6:31 AM
  • Actually, it is NOT a split database; they indicated they didn't want that. But, if that'd be the best solution, I'll offer it up to them.


    It's more than the best solution, it's the only sensible one.  Having multiple users access a single database file is an open invitation to corruption.  Why your users should be averse to splitting the database is hard to understand.  It's the way countless Access databases around the world are installed successfully.  Even in a single user environment, any but the most trivial database should be split.  It makes it very simple to back up the data regularly (they are doing this at least daily, aren't they?), and should a user's copy of the front end become corrupted it's simply replaced with a copy of a 'master' version kept in a secure location.  You can also undertake development work on the front end independently of the operational database, replacing the user's copies of the front end with the new version when the development work has been completed.

    Ken Sheridan, Stafford, England

    Friday, January 29, 2016 12:55 PM
  • Thanks for your input. The group is just three people. It turns out even though they all had the same trusted locations to begin with, adding another to the two strangely-acting computers took care of the issues within a couple of minutes. Lesson learned! 
    Saturday, January 30, 2016 12:18 AM
  • Thanks for the input. "Sensibility" isn't always as clear-cut as one might think.  Perhaps you can give me ideas as to how it's best to make changes to the back-end structure of a client's db when I don't have access to their servers or computers; I'm essentially relegated to working on back-end changes to their table structure offsite. I plead ignorance on that one. It's been frustrating as hell to inherit an old Access database whose structure needs to be updated, yet I am not provided remote access to the split tables' location. It's all new for me. But seeing as you have >20,000 points, I'm all ears... (not to negate anyone else's responses; their input is what led me to take care of my issue I posted about). Ta.
    Saturday, January 30, 2016 12:30 AM
  • If a database is not split - then it must be used round-robin; which is to say 1 person at a time.

    The standard split installation is to put the front file on the user's local PC (and link to the common back folder).  But I know in some companies there a preference to put the font file(s) on a server - this can be a legitimate installation as long as each user still as their own front file (on the server) and one does not attempt multiple users inside the same front file.

    None of this changes the trust center - - when in the front file one must be sure the folder holding the front and holding the back file is trusted.

    Saturday, January 30, 2016 3:39 AM
  • Firstly don't pay much regard to those points.  Anyone who has answered questions here for a while will soon build up points provided their answers aren't totally stupid.  Personally I don't like them, and would prefer to be judged by others solely on the quality of my responses.  I fought a prolonged and partially successful battle with Microsoft some years ago to stop them sticking letters after my name in forums.

    If you have to make extensive changes to the back end then it is probably best to get the end user to send you a copy of it and for you to make the changes to that and return it to them.  This of course has implications:

    1.  The end user will have to pause any data input until you give them the new front end.  Or….

    2.  They can continue to input data, but you'll need to provide them with the means to transfer the data into an empty new front, e.g. by including the necessary 'append' queries in the new back end file and a simple interface for them to be able to browse to, and link to the old back end, and then execute the queries in the correct order so that referential integrity is not violated.  Or….

    3.   Instead of sending them a new back end, send them a file in which a reference to the existing back end is returned by browsing to it, and then executing routines which execute the necessary CREATE, DROP or ALTER TABLE statements to update the old back end.  It might also be necessary to move data around once the table definitions have been amended.

    Over the years I've done all three of the above, depending on the context in which the back end is being changed.

    Whatever you do, I really can't emphasise too much how unwise it would be to stick with the present set-up where the three users are accessing the same single file.  Sooner or later the brown stuff will make contact with impeller.

    Ken Sheridan, Stafford, England

    Saturday, January 30, 2016 12:52 PM
  • Thanks for your input. The group is just three people. It turns out even though they all had the same trusted locations to begin with, adding another to the two strangely-acting computers took care of the issues within a couple of minutes. Lesson learned! 
    I'm glad you were able to set up the trusted locations as I suggested. Sometimes it's little things that go unvarified that cause the biggest problems.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 1, 2016 4:09 PM