Warning msg --> The record has been changed by another user...

[azirion]

Hi

I'm using MS Access as a front end program, and I have a form based on a query which obtains the data from a MySql database on a server.

My problem is that I try to make a simple button which I wrote VBA code with a simple command like field.setfocus and field.text=whatever to insert some data into that field. At that point I dont have any problems. The warning message appears when I try to change into another subform, then the little window pops up and says: The record has been changed by another user since you started editing it...etc. ...and I am the only user

I would really appreciate some help on this one.

 

[GinaWhipp]

You will get this message if you try to change data on one form based on the same table that your subform is based on. You can try putting a Save command on the Lost_Focus of the main form and seeing if that helps remove the message.

 

[azirion]

Thank you for replying.

They are not based on the same table, each sub form is based on its own table, and the problem occurs when I click to open a sub form (doesn't matter which one). But this only happens when I run the code on this button (described above). If I edit the field manually everything works fine.

I tried writing docmd.requery or me.refresh at the end of the code, but the only difference is that now the warning window pops up as soon as I press (run) the button.

 

[GinaWhipp]

Can you please cpoy/paste the exact code in the On_Click event of the button...

 

[azirion]

I did a test: Created a new Access 2007 file (accdb) linked only one table to the mysql server via ODBC and created only one form based on that table.

I added a button and wrote into it this simple code:
field.setfocus
field.text="whatever"
me.refresh -->same with docmd.requery

Results: The same popup!

So now Im really confused

 

[GinaWhipp]

I see I'm not going to get the exact code... so let's try this. What happens when you open the table and try to add a record? What happens when you try to change something in a record?

 

[azirion]

Im sorry Gina if you feel Im not giving you enough information. The thing is that I simplified the code as much as possible to pinpoint the error and the message is still popping up.

As I said before I am able to edit, add, erase everything I want in any field and it works just fine. The only problem is when I try to assign a text or value to a field using code.

The code Im using is extremly simple so the only thing I didnt show you was the Private Sub line and the End Sub line, the rest is exactly as I tested, here´s an exact copy of the code:

Private Sub button_Click()
field.SetFocus
field.Text = "Whatever"
Me.Refresh
End Sub

Extra information on testing:

Something weird happens, if I go to a new record, the button works perfectly without any popups, the curious thing is that if I go back to the first record press the button again and it works fine!!!, but just once, if I try to press it again, the little $%# window appears and will still appear every time I press it until I create a new record. WHY?

Should it be something with my ODBC? ...I dont have a clue on what to do now

 

[GinaWhipp]

Sounds like a timming issue... It's not commiting to the table fast enough for you to make a second change. Try putting this line...

DoCmd.RunCommand acCmdSaveRecord

...here

Private Sub button_Click()
DoCmd.RunCommand acCmdSaveRecord
field.SetFocus
field.Text = "Whatever"
End Sub

...and I removed the Me.Refresh as it really does not help. And yes, it could be your connection but if works and then doesn't work I would say it's more likely a timming issue.

 

[azirion]

Gina!!!! if I could I would give you a HUGE Kiss!!! You hit the nail!!!

Thank you very much!!! You resolved the puzzle no one could!!

thank you...thank you... thank you!!!

 

[azirion]

Ohhh Gina Im so sad.... I thought everything was ok... but no...
I deleted the line Me.Refresh as you said and so the popup didnt came up, so I thought the problem was solved with your code, but when I tried it out with the main program the problem still persists.

 

[GinaWhipp]

I read the first post and was happy... then the next one and then sad. Okay, what happens if you leave the Me.refresh in the live database?

 

[azirion]

I read the first post and was happy... then the next one and then sad.

tell me about it...

Okay, what happens if you leave the Me.refresh in the live database?

I don't know if I understood correctly by "live database", but if you mean to leave it behind the button´s code as it was? then yes... the same little window from hell pops up

 

[GinaWhipp]

What I meant by *live* was the Main database. You said that is where the error came back. So I wanted you to put in the DoCmd line AND the Me.Refresh line. I wanted to see if having both prevents the error in the main one.

 

[bparkinson]

I get this same problem. Only difference is that I am using SQL Server. Usually, deleting the record in the back end (not from Access) and then recreating it solves the problem. It's clearly not another user, it has to be some edit lock placed on the record in the back end and then not released. Unfortunately, I have not been able to find an explanation for this.

My point is though, it's probably a back end issue.

 

[GinaWhipp]

@bparkinson

Have you tried the *DoCmd.RunCommand acCmdSaveRecord* to see if that works? And perhaps you could post the code section that produces the error?

 

[azirion]

Check this test out: I created a brand new little MySql database which holds only one table, and this one has only 3 fields: the ID field, the text field, and the timestamp field (using default current_timestamp).

I linked the table on MS Access front end, using the ODBC 5.1 driver/connector which I downloaded from MySql site. Created a form based on that linked table, added a button with the code mentioned before, and guess what... the same little warning window poped up when I try to assign a string or a value to the text field and then< me.refresh> using code behind the button.

Should it be the ODBC driver? the configuration has been left as default so I really dont know.

 

[GinaWhipp]

I have not used MySQL, I use SQL Server and use the wizard to upsize (and then fix any boo-boos the wizard makes)... My point is did you try to connect to MySQL using the Upsizing Wizard instead of the ODBC driver from MySQL?

 

[azirion]

I have not used MySQL, I use SQL Server and use the wizard to upsize (and then fix any boo-boos the wizard makes)... My point is did you try to connect to MySQL using the Upsizing Wizard instead of the ODBC driver from MySQL?

I can´t use the Upsizing Wizard for MySql, if I chose "Use existing database" the moment I chose the ODBC connection an error pops up saying <Type mismatch>. And if I chose the option "create new database" there´s no option other than an SQL server.

 

[GinaWhipp]

Okay, I learned something new! I will have to bear that in mind for MySQL databases.

Have you tried editing the table and seeing if you get an error? Just open and try to change anything.

 

[azirion]

I can do pretty much everything I want, edit, add, delete, directly on the table or trhough the form, as long as I dont use VBA.