BigJ
asked on
"The form was changed by another user since you opened it"
I am looking after a database that has a small number of users. The users are getting the error "The form was changed by another user since you opened it", which is strange because they are not making changes to any of the forms. I don't know why this is happening.
I tried setting the code that opens the form to open a new instance for each user - eg
Public frm as form (declared in separate module)
set frm = new Form_Main_menu
frm.visible=true
This opens each form as a new instance to avoid the error, BUT this has caused 2 problems:
1. Now it is impossible to refer to the form. The code
Forms("Main_menu").Visible
2. A related problem is that when other forms (with controls that take information from the "Main_menu" form) are opened, the same error appears.
So the question is: how can I allow the users to open the Main_menu form without errors? I would prefer to do this without having to worry about instances, just find a way avoid the error "another users has changed the form since you opened it..." If this is not possible then how can I do it using instances, in a way that lets the code, and controls on other forms, refer to the form "Main_menu" by name.
I tried setting the code that opens the form to open a new instance for each user - eg
Public frm as form (declared in separate module)
set frm = new Form_Main_menu
frm.visible=true
This opens each form as a new instance to avoid the error, BUT this has caused 2 problems:
1. Now it is impossible to refer to the form. The code
Forms("Main_menu").Visible
2. A related problem is that when other forms (with controls that take information from the "Main_menu" form) are opened, the same error appears.
So the question is: how can I allow the users to open the Main_menu form without errors? I would prefer to do this without having to worry about instances, just find a way avoid the error "another users has changed the form since you opened it..." If this is not possible then how can I do it using instances, in a way that lets the code, and controls on other forms, refer to the form "Main_menu" by name.
BigJ,
Is this
A) a new error (in other words, have there have been no changes made but this error just started occuring),
B) or has it always done this and you are trying to figure out how to get rid of it,
C) or did someone make a change somewhere and this error began to occure?
Mac
Is this
A) a new error (in other words, have there have been no changes made but this error just started occuring),
B) or has it always done this and you are trying to figure out how to get rid of it,
C) or did someone make a change somewhere and this error began to occure?
Mac
have you considered the option of splitting up the db into a front-end / back-end solution with each user having his/her own copy of the front-ent portion?
In my experience you can never really rely upon Access to run without problems when you have users simultaneously using the same shared file for anything other than straight table editing.
Also, be sure the form's "Record Locks" property is set to "No Locks"
ASKER
Ummmm.... OK I'll admit it - it's a new problem caused by yours truly, afterI recombined a split database. This move was UNAVOIDABLE due to network issues that I am not going to go into here. Suffice to say, the single database is here to stay.
Still, a small handfull of users should be able to use the same front end, SURELY!!
MacRena, it's the form that changed, I am not sure how setting no record locking will help. Won't that just cause record locking conflicts when two users try to edit the same record?
Still, a small handfull of users should be able to use the same front end, SURELY!!
MacRena, it's the form that changed, I am not sure how setting no record locking will help. Won't that just cause record locking conflicts when two users try to edit the same record?
>>"Still, a small handfull of users should be able to use the same front end, SURELY!!"<<
First, don't call me Shirley.
Second, If the tables are in the db, then it's not really a "front end". And if there are several instances of a unified database running on different computers, you are bound to get conflicts. Access is not designed to share several instances of a database. That's why the "split db" design is there.
Third, "Won't that (No Locks) cause record locking conflicts when two users try to edit the same record?"<<
Yea, in this situation it will. The reason I suggested that is because I doubt there is a likely hood of 2 users trying to edit the same record at the same time. And in the once a year instance that this does happen, they can talk to each other and resolve the problem. The rest of the year, you get no problems. But the implementation is a bad idea.
But let's see if we can try different things.
First, you have to decide if "No Locks" will resolve it to a workable level.
Second, look at other options besides a split db design. Maybe a replication model might work here... you have several instances of a db in use, then synchronize at periodic intervals. Or, you might want to have a Master db seperate from the others that you would simulate a replication in which you would write a function that would update the Master Tables from the individual dbs and (in the event that 2 records are modified on the same day) report differences to an Admin for a final decision; an Admin would see both instances of the record and would create the record to be entered into the Master db at his/her discression.
I'll try think of other options.
Mac
First, don't call me Shirley.
Second, If the tables are in the db, then it's not really a "front end". And if there are several instances of a unified database running on different computers, you are bound to get conflicts. Access is not designed to share several instances of a database. That's why the "split db" design is there.
Third, "Won't that (No Locks) cause record locking conflicts when two users try to edit the same record?"<<
Yea, in this situation it will. The reason I suggested that is because I doubt there is a likely hood of 2 users trying to edit the same record at the same time. And in the once a year instance that this does happen, they can talk to each other and resolve the problem. The rest of the year, you get no problems. But the implementation is a bad idea.
But let's see if we can try different things.
First, you have to decide if "No Locks" will resolve it to a workable level.
Second, look at other options besides a split db design. Maybe a replication model might work here... you have several instances of a db in use, then synchronize at periodic intervals. Or, you might want to have a Master db seperate from the others that you would simulate a replication in which you would write a function that would update the Master Tables from the individual dbs and (in the event that 2 records are modified on the same day) report differences to an Admin for a final decision; an Admin would see both instances of the record and would create the record to be entered into the Master db at his/her discression.
I'll try think of other options.
Mac
Are you in access 2k or 97? Reason I ask is because 2K won't allow actual modifications to a form without exclusive access, so you can pretty much nail down if it's corrupted or not if you are in 2K.
Do you have code that: Changes formatting, changes position, modifies a record/control source, any type of property change to any control on that form? If so, this could also be the cause of your modified form error.
-Craig
Do you have code that: Changes formatting, changes position, modifies a record/control source, any type of property change to any control on that form? If so, this could also be the cause of your modified form error.
-Craig
ASKER
By front end I am simply referring to the mdb file that the user physically opens, as opposed to a file that is accessed remotely vialinked tables. Sure, in this case it happens to contain data tables, but you know what I meant :-)
Shirley: Can you please explain why record locking will stop the error message appearing regarding the fact that a form has been changed. I'm not challenging you, I just don't understand.
Also, I know the advantages of a split database, and of having only 1 instance of the front-end or combined database per user... but all the same, doesn't multi-user mean that a bunch of people should be able to run the same front-end or combined database?
Gudare: 1. I'm in A97. None of the users have A2K installed so this is not an option. 2. No, no controls or properties are changed on the form in runtime=, or control source, formatting or position.
Thanks guys.
Shirley: Can you please explain why record locking will stop the error message appearing regarding the fact that a form has been changed. I'm not challenging you, I just don't understand.
Also, I know the advantages of a split database, and of having only 1 instance of the front-end or combined database per user... but all the same, doesn't multi-user mean that a bunch of people should be able to run the same front-end or combined database?
Gudare: 1. I'm in A97. None of the users have A2K installed so this is not an option. 2. No, no controls or properties are changed on the form in runtime=, or control source, formatting or position.
Thanks guys.
>>"Shirley: Can you please explain why record locking will stop the error message appearing regarding the fact that a form has been changed"<<
I have given up on the dream that Access Error Messages will tell you what is really wrong - much like my ex-wife on a bad week, you have to look at the message, then try various other things to figure out what is REALLY bothering it. (BTW - men should NEVER diagnose PMS for at least 2 weeks after the actual event!)
The fact that your form has not changed is clear. The fact that Access is telling you that the form has changed is also clear. So I am trying to see if it's bothered by one problem and crying about something else.
I would also like to know all of the wording of the error message - including error number(s).
I will wait for your reply. (and at least several from women in the gallery).
Mac
I have given up on the dream that Access Error Messages will tell you what is really wrong - much like my ex-wife on a bad week, you have to look at the message, then try various other things to figure out what is REALLY bothering it. (BTW - men should NEVER diagnose PMS for at least 2 weeks after the actual event!)
The fact that your form has not changed is clear. The fact that Access is telling you that the form has changed is also clear. So I am trying to see if it's bothered by one problem and crying about something else.
I would also like to know all of the wording of the error message - including error number(s).
I will wait for your reply. (and at least several from women in the gallery).
Mac
ASKER
Dear Macarena :-)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
ASKER
Dear Macarena :-)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
ASKER
Dear Macarena :-)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
ASKER
Dear Macarena :-)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
ASKER
Dear Macarena :-)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
Message is: "Main_menu has been changed since the last time you opened it, either by another user or because another instance of it was opened on your own machine. Do you want to rplace the changes that you or another user made?"
PS In my experience, the worst times to bring up the topic of PMS are 2 weeks prior to the event, and 2 weeks after the event. Any other time is fine (apart from during the event, which is of course a suicidal act)
ASKER
Whoops, I just noticed that every time I hit 'Refresh' in my browser to see if any answers have been posted, Experts Exchange adds my last comment, over and over again. Guess I'll stop refreshing my browser window...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Record Changes", False
is what I have here. I don't know if either of these will work for this message, or there is another message that you can turn off. I looked up the Application.SetOptions command at www.msdn.Microsoft.com but it didn't return anything.
Sorry that I can't study this more right now - too much on my plate here at work.
Mac
Application.SetOption "Confirm Record Changes", False
is what I have here. I don't know if either of these will work for this message, or there is another message that you can turn off. I looked up the Application.SetOptions command at www.msdn.Microsoft.com but it didn't return anything.
Sorry that I can't study this more right now - too much on my plate here at work.
Mac
ASKER
Mac, I have an idea. After I unsplit the database I renamed the .mdb file as .mde to preserve all the users' links to the F.E. to minimise disruption. I tested it and it seemed to work fine so I assumed it didn't matter mde or mbd. But maybe one unforeseen consequence of that is this message that is coming up. I will rename the F.E. file back to .mdb and will see if this stops that particular message coming up.
The problem occurs when they exit the app. The form in question is always open while the app is in use.
I don't want to disable record change confirmations across the board because the users might lose data and I don't have time to add all the delete message boxes that would be needed. Also my guess is that if it thinks the form has changed then it will still want the user to decide whether to save the changes or not.
Cheers,
BJ
The problem occurs when they exit the app. The form in question is always open while the app is in use.
I don't want to disable record change confirmations across the board because the users might lose data and I don't have time to add all the delete message boxes that would be needed. Also my guess is that if it thinks the form has changed then it will still want the user to decide whether to save the changes or not.
Cheers,
BJ
BigJ,
>>"After I unsplit the database I renamed the .mdb file as .mde to preserve all the users' links to the F.E. to minimise disruption"<<
I don't understand two things here...
1) Did you 'rename' the .mdb to .mde, or did you perform the "Make .MDE" procedure on the 'Database Utilities'? (renaming doesn't really make an .mde, but I'm sure you know that)
2) What does >>"preserve all the users' links to the F.E. to minimise disruption"<< mean?
In your original question, you said
Public frm as form (declared in separate module)
set frm = new Form_Main_menu
frm.visible=true
but the app. can't find the form "Form_Main_menu".
Maybe that's because you opened a form called "frm", not "Form_Main_menu". So you would need the code that refers to the form to say
Forms("frm").Visible =True
Just a thought. I never went about doing this as you have done.
Since it's just us guys here anymore, can you explain what you are talking about when you say
>>"This move was UNAVOIDABLE due to network issues that I am not going to go into here."<< unless you really don't want to into them here. Maybe we can think of another solution that will suit the "network issues".
Mac
>>"After I unsplit the database I renamed the .mdb file as .mde to preserve all the users' links to the F.E. to minimise disruption"<<
I don't understand two things here...
1) Did you 'rename' the .mdb to .mde, or did you perform the "Make .MDE" procedure on the 'Database Utilities'? (renaming doesn't really make an .mde, but I'm sure you know that)
2) What does >>"preserve all the users' links to the F.E. to minimise disruption"<< mean?
In your original question, you said
Public frm as form (declared in separate module)
set frm = new Form_Main_menu
frm.visible=true
but the app. can't find the form "Form_Main_menu".
Maybe that's because you opened a form called "frm", not "Form_Main_menu". So you would need the code that refers to the form to say
Forms("frm").Visible =True
Just a thought. I never went about doing this as you have done.
Since it's just us guys here anymore, can you explain what you are talking about when you say
>>"This move was UNAVOIDABLE due to network issues that I am not going to go into here."<< unless you really don't want to into them here. Maybe we can think of another solution that will suit the "network issues".
Mac
ASKER
Hi Mac,
1. I renamed the file. I know this does not actually create an MDE file but I thought I would bluff it (if the users see the mde file they may assume they cannot access the forms and modules so they won't try). There is no user level security yet...
2. More importantly, all the users already have a link on their desktops to the FrontEnd.mde file. I didn't want to ahve to go through and get them to change the link to the new .mdb file so I just renamed it to .mde!
Anyway I have now renamed the file back to .mdb and the problem is still occurring.
3. I wrote some code that says
Public frm As New Form_Main_menu (decl. elsewhere)
Set frm = New Form_Main_menu
frm.Visible = True
This opens the form, but I cannot seem to write code that, say, refers to a control eg ID on the form. I have tried referring to it with
Forms![Form_Main_menu]!ID
Forms![Main_menu]!ID
Forms![frm]!ID
Forms("Main_menu").Form!ID
In each case I get "Access cannot find the form 'formname'referred to..."
The Network issues are described in my other question posted a few days ago, called "Use(r)s can access back end directly, but not via front end".
Thanks Mac I appreciate your assistance.
1. I renamed the file. I know this does not actually create an MDE file but I thought I would bluff it (if the users see the mde file they may assume they cannot access the forms and modules so they won't try). There is no user level security yet...
2. More importantly, all the users already have a link on their desktops to the FrontEnd.mde file. I didn't want to ahve to go through and get them to change the link to the new .mdb file so I just renamed it to .mde!
Anyway I have now renamed the file back to .mdb and the problem is still occurring.
3. I wrote some code that says
Public frm As New Form_Main_menu (decl. elsewhere)
Set frm = New Form_Main_menu
frm.Visible = True
This opens the form, but I cannot seem to write code that, say, refers to a control eg ID on the form. I have tried referring to it with
Forms![Form_Main_menu]!ID
Forms![Main_menu]!ID
Forms![frm]!ID
Forms("Main_menu").Form!ID
In each case I get "Access cannot find the form 'formname'referred to..."
The Network issues are described in my other question posted a few days ago, called "Use(r)s can access back end directly, but not via front end".
Thanks Mac I appreciate your assistance.
BJ,
>>"if the users see the mde file they may assume they cannot access the forms and modules so they won't
try"<<
1) If I were looking at the screen and saw that the blue triangle at the top left was blue, then I would know that I had the design version of the program. Doesn't matter what the extension was. Perform the "Make .MDE" procedure on the 'Database Utilities' and then see if the error goes away. (It probably won't, but I'm throwing a 'hail-mary' now)
2) In the OnOpen event of the frm Form, put a MessageBox like this...
Private Sub Form_Open(Cancel As Integer)
MsgBox Me.Name
End Sub
let's see what it thinks itself is called.
>>"if the users see the mde file they may assume they cannot access the forms and modules so they won't
try"<<
1) If I were looking at the screen and saw that the blue triangle at the top left was blue, then I would know that I had the design version of the program. Doesn't matter what the extension was. Perform the "Make .MDE" procedure on the 'Database Utilities' and then see if the error goes away. (It probably won't, but I'm throwing a 'hail-mary' now)
2) In the OnOpen event of the frm Form, put a MessageBox like this...
Private Sub Form_Open(Cancel As Integer)
MsgBox Me.Name
End Sub
let's see what it thinks itself is called.
ASKER
To quote Alice in wonderland, "Things are getting curiouser and curiouser..."
OK I ran your code and the form itself thinks it is called "Main_menu". But the following code has the following results:
THIS WORKS:
If SysCmd(acSysCmdGetObjectSt
THIS DOES NOT (ie Access cannot find the form with this name):
If Forms("Main_menu").Form.Vi
Also I don't have time to convert it to an MDE file because I need to de-replicate it first which will take a bit of time, so I will have to do it on the weekend when no-one is waiting to use it.
OK I ran your code and the form itself thinks it is called "Main_menu". But the following code has the following results:
THIS WORKS:
If SysCmd(acSysCmdGetObjectSt
THIS DOES NOT (ie Access cannot find the form with this name):
If Forms("Main_menu").Form.Vi
Also I don't have time to convert it to an MDE file because I need to de-replicate it first which will take a bit of time, so I will have to do it on the weekend when no-one is waiting to use it.
ASKER
Mac, what do you make of my findings, as posted in my previous message?
This problem could be related to wrong versions of jet,vba etc on different machines. First when users open the forms they have their own copy of these forms in the memory.
However, the tables if used simultaneously may give you a similar message. So first check if all your users have same version of the following files.
MSJET35.dll
VBA332.dll
DAO350.dll
Once done synchronising do a decompile by having
c:\.....\msaccess.exe fullmdbpath /excl /decompile on the command line.
After this open the db and do a compile and save from tools (module design mode)
Then do a compact from tools menu.
This gotta solve it...
However, the tables if used simultaneously may give you a similar message. So first check if all your users have same version of the following files.
MSJET35.dll
VBA332.dll
DAO350.dll
Once done synchronising do a decompile by having
c:\.....\msaccess.exe fullmdbpath /excl /decompile on the command line.
After this open the db and do a compile and save from tools (module design mode)
Then do a compact from tools menu.
This gotta solve it...
BigJ,
Sorry for not returning.
Went on vacation and just returned late last night.
Will look into this again asap if the above Comment by devtha doesn't help (I hope it does)
Mac
Sorry for not returning.
Went on vacation and just returned late last night.
Will look into this again asap if the above Comment by devtha doesn't help (I hope it does)
Mac
ASKER
Thanks Devtha,
Sounds like a good idea, but is there a simple way to check the version of MSJET35.dll, VBA332.dll and DAO350.dll??
BJ
MacRena, hope you had a good vacation. Where did you go, you lucky devil??!!
Sounds like a good idea, but is there a simple way to check the version of MSJET35.dll, VBA332.dll and DAO350.dll??
BJ
MacRena, hope you had a good vacation. Where did you go, you lucky devil??!!
Only way I kno is manually go to each machine and find it out. Unless if you can write or some one has written it then it would be lot easier. You can also ask users to do that for you. Find each file and check its properties by right mouse click on it and check the version.
I meant written a program to find the version...
ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
http://support.microsoft.com/support/kb/ARTICLES/Q172/7/33.asp
http://support.microsoft.com/support/kb/ARTICLES/Q172/7/33.asp
ASKER
Hi Guys,
I'm trying to find some time to "un-replicate" the database so I can save the file as an MDE. Hopefully this will alleviate the problem.
I will advise when I have made some progress.
I'm trying to find some time to "un-replicate" the database so I can save the file as an MDE. Hopefully this will alleviate the problem.
I will advise when I have made some progress.
ASKER
Hi guys, I haven't forgotten - I'm still waiting for an opportunity to create an .mde file...
ASKER
I finally had an opportunity to compile the front end as an MDe file and I think this has solved the problem.
BigJ,
I hope you're right. If not, let me know and we can continue.
You might have to email me the program for me to look at it up close, because I was really getting stumped from this far away.
I am extremely happy that this solved the problem.
Access can be a funny bird sometimes.
Thanks for the points. This was one of the toughest I have worked on, and I'm really gratified that it did get solved!
Mac
I hope you're right. If not, let me know and we can continue.
You might have to email me the program for me to look at it up close, because I was really getting stumped from this far away.
I am extremely happy that this solved the problem.
Access can be a funny bird sometimes.
Thanks for the points. This was one of the toughest I have worked on, and I'm really gratified that it did get solved!
Mac
I've had a similar situacion before, and (in my case) my mdb was corrupted. I had to:
1-MAKE A BACKUP COPY OF YOUR DATABASE
2-Make sure u dind't miss step 1, and
3-Use the /decompile option to open the mdb (don't forget to visit http://www.trigeminal.com/usenet/usenet004.asp?1033)
HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela