Command buttons not working & code disappearing (1 Viewer)

[dfsabrown]

Hello

I have been developing a case management database over the past few years, every 6 months I make a few changes like new forms or reports and then a case team use it. Never had any trouble or issues.

On the last update I had to convert the file to a 2007 format. The uses all have Microsoft 2010 and all databases open fine. But for some reason after a while the database just stops working. The main form on opening is there but the command button doesn’t work. Also even if I open other forms from the navigation bar, random command buttons don’t work. When I then go to the design of a form & look at the “on click” for the command buttons “Event Proceedure” is still there but when I click on that & open the VBA, the code is gone, just get:

Private Sub open_case_dets_Click()
End Sub

Luckily I keep a blank master of the database so I have just taken a clean master & copied over all the data BUT I need to prevent this from happening again.

Trusted locations is fine & the “Enable Content” pop up is always selected. I tried “compile & compact” tool, but still command button not working & code gone.

I dont think anything can be done to the "Broken" database so i am wondering if I should take the current blank master back up & sort that to prevent this issue from happening again. Perhaps a "Debug & Compile" and/or perhaps convert to accde

I am completely self taught – so go gently on me!

Many thanks in advance for any advice

:banghead:

 

[CJ_London]

you haven't said how you have it set up but the db should a) be split, with tables only in the backend and everything else in the front end and b) each user should have their own copy of the front end. If not structured in this way then likely your db has become corrupted and only solution is to revert to a backup. This

Luckily I keep a blank master of the database so I have just taken a clean master & copied over all the data

implies the db is not split and therefore users do not have their own copy of the front end. If this is the case, you have been lucky. Next time, you many not be able to copy the data.

 

[dfsabrown]

yes, sorry forgot to say...its not split.

 

[dfsabrown]

Basically, every case team has there own database as each team need to record confidential & different case information. So if split each team would need own back end & front end. Every year i update the database & copy over exisisting data to the new version to make sure each team has all the recent reports & forms etc. I wasn't sure if spliting it would make this much harder. Can i split it now or would accde help?

many thanks

 

[Minty]

You are suffering from corruption - there is a very good article here http://allenbrowne.com/ser-25.html explaining some of the more common causes. But basically if you have multiple users you need to split it.

With regard to your multiple database user group set up, you should probably have some form of user access control and keep everything in the same database. And then control versions using an auto Front end update process. Google on here will provide some background.

 

[dfsabrown]

thank you all so much. i have looked at fixing a few minor errors & considered spiliting etc. Unfortunately due to the nature of users i can not split it - basically users take a copy and move it to a secured folder per case. so moving the front/back end breaks the links in the master. Due to the confidential nature of the work it is not possible to have one main back end or even have one folder location accessable by all the users. So i still need to allow the users to copy a master & move it to their own secure folder.


Quick questions to finish this off....im planning to debug/compile BUT should i then make it accde?


thank you all so much

S

 

[CJ_London]

you can still split it and have a routine to remake the connections.

Converting to .accde is a good idea in that it prevents users from messing with forms, reports and code. But it won't necessarily reduce the risk of a corruption if users are using the same front end on an unsplit database

If the db can be 'thrown away' then probably OK not to split, tho' personally, I wouldn't never provide an unsplit db to my clients except perhaps as a proof of concept for the design.

Bit confused by your comments

users take a copy and move it to a secured folder per case
...
...
it is not possible to have one main back end or even have one folder location accessable by all the users

If the latter - how do they copy it?

 

[dfsabrown]

Thank you CJ_London

So what i have is one open folder which all users can access. In there I store a blank no data master copy of the database. Users go to that folder & open the database then save a copy into a secure case members only folder. they then use to record confidential data. Its not ever large amounts of data it just needs to be very secure. I update every year or so & yes can 'throw away' the old version.

thanks for continued help

 

[The_Doc_Man]

into a secure case members only folder

If that "members" (plural) is correct AND each member (singular) has a separate workstation, then EACH MEMBER should have a separate copy of the FE and should access the BE that is shared by those members only.

It would get more complex if the members overlapped because they worked on different cases at different times.

This might represent a complicated logistics problem, though it is not impossible to have a database "find" its own BE files - at worst, through a File System Object "file picker" dialog box - and then fill in the "Connect" information for the BE tables. The persons who copy over this FE/BE pair to the "secured folder" would have to be trained a little bit on setting up a new case, but it is not impossible to set it up that way.

Or, everyone could have a FE copy (since you say it is always the same) and have them go through a dialog box to find the BE for their case. You really need to split the DB to avoid corruption - and while setting it up might be a pain, what you gain is that all of the Windows and Access locking gets split into FE/BE - and there is ZERO contention for locking anything in the FE file. Further, if the FE is on each workstation, there is no NETWORK traffic for opening the definitions of forms, reports, code, macros, queries... the only traffic is when someone hits a BE table. So better performance.

 

[dfsabrown]

Thank you The_Doc_Man. Unfortunately team members do work on multiple cases at any one time. Also its not just one person who copies over the file, again due to confidentiality issues & folder access restrictions. Most users are not competent with the details of access either. thank you though, i will continue to look into that as a future developement if i can get some users trained.

The annoying thing is that this database has been fine for years as it is. Its only the latest update that has had any problems. amongst other small table/form changes I was asked to upgrade to access 2010 and i wonder if thats caused a problem.

Whilst testing turning a copy into a accde, i have a fixed some errors that came up on the compile so im hoping that will help. Otherwise i just wonder if i should rebuild from scratch in 2010. Its not a big database & id rather be more confident that its ok (as much as i can be!).

thanks
S

 

[CJ_London]

The annoying thing is that this database has been fine for years as it is. Its only the latest update that has had any problems.

repeat - it is because you do not split your db. corruption as a result of multiple users same the same front end is insidious.

I could say it's annoying my car has stopped because I don't bother servicing the engine. If you are not prepared to take the advice, you'll just have to live with it..

Updating to 2010 can have issues, but all solvable. It is usually down to the earlier version using functionality that has been deprecated by 2010. Also 2010 is less forgiving of poorly designed code and will generate an error where before it just ignored it. Your error fixing to get a .accde will have helped with this

 

[dfsabrown]

If you are not prepared to take the advice, you'll just have to live with it..

i know, i know!!! i do want to split it, but i am a novice at all this so it will just take me a while to work it all out, work out what will work with all the restricted folders and make sure the copied over blank Master FE "finds" their correct BE etc, etc. its going to take me a while!!

Thanks for all your advice everyone

S