Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    7

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

    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 through ODBC driver.

    My problem is that I try to make a simple button which I wrote VBA code with a simple command like:

    field.setfocus
    field.text = "whatever"

    The idea is to insert some text into that field. At that point I dont have any problems. The warning message appears when I try to change to another record, or if I add a Me.Refresh line into that code.

    I have no problem adding, editing or deleting fields. The only way this windows pops up is when I try to edit or add text to an existing field using VBA on a button.

    Furthermore if I use this button on a new record it works fine, at this point if a go to the first record, it works once more, from that point on, every time I click the button the popup appears

    ...and yes I added the TIMESTAMP field on the table using CURRENT_TIMESTAMP as default.

    I would really appreciate some help on this one.

  2. #2
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    How about trying Me.Dirty=False in place of Me.Refresh?

  3. #3
    Join Date
    Sep 2011
    Posts
    7
    Quote Originally Posted by LisaChow View Post
    How about trying Me.Dirty=False in place of Me.Refresh?
    Nope... same little window from hell pups up

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This usually happens when data is sent back to the server by another process than the form recordset, but this does not seem to be the case here. Does this also happen if you use:
    Code:
    Me.Field.Value = "whatever"
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    7
    Yes it happens just the same

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've run into this before too. It may or may not be the same solution though.

    I managed to figure out that a calculation I was doing in VBA was placing a value with significant precision in a field in the MySQL server that would cause the record to be forever locked due to that error message.

    I solved it by making sure the calculation was rounded to two decimals before attempting to store it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Sep 2011
    Posts
    7
    Quote Originally Posted by StarTrekker View Post
    I've run into this before too. It may or may not be the same solution though.

    I managed to figure out that a calculation I was doing in VBA was placing a value with significant precision in a field in the MySQL server that would cause the record to be forever locked due to that error message.

    I solved it by making sure the calculation was rounded to two decimals before attempting to store it.
    Well I dont think is the same case, It doesnt matter what I assign to that field, text or numbers its just the same.

    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.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can manually enter text into the field without problems?

    Have you tried

    Me.ControlName = "Text to insert"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Sep 2011
    Posts
    7
    Quote Originally Posted by StarTrekker View Post
    You can manually enter text into the field without problems?

    Have you tried

    Me.ControlName = "Text to insert"
    Yes I can manually enter text, edit text, delete text...
    ...and tried the me.field... same thing

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just a wild guess: Rename the control so that its name is different from the field name (e.g. Txt_Field1 for Field1), then retry.
    Have a nice day!

  11. #11
    Join Date
    Sep 2011
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    Just a wild guess: Rename the control so that its name is different from the field name (e.g. Txt_Field1 for Field1), then retry.
    Yes I did, same result

  12. #12
    Join Date
    Sep 2011
    Posts
    7

    Case solved!

    Finally I figured it out

    It seems that something (access, or mysql) does not accept assigning a value through VBA to a field if the same value already exist on that field.

    To prove this I wrote this code behind the button:

    Private Sub button2_Click()
    If Me.myfield = "whatever" Then
    Me.myfield = "other"
    Else
    Me.myfield = "whatever"
    End If
    Me.Refresh
    End Sub

    This way I can toggle the text and press the button as many times I want and works perfect. So the only thing I have to do is to handle the text that the field contains before I try to assign with the code.

    Thank you very much for all your help and time, I really appreciate it.

    Case solved

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is very strange and the first time I hear of such a problem. Glad you were able to find a solution, and thanks for sharing it.
    Have a nice day!

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow... I've never run into that one before either. Glad you got it sorted :-)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •