Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    28

    Unanswered: Stand Alone Write Conflict after query from vba on form

    First of all thank you all for being here!

    Here is what my problem is, and I have searched a lot before posting here. I have a query that conbines quite a few tables and I have been using VB behind buttons to modify fields directly. Now, instead of that, and for added functionality I have changed to modifyign fields based on queries built from concating other fields and it works great!

    Except, I click the button, it askes me to ok the row change (which I know how to turn off) and it changes everything, but it doesn't update on the form and if I try to go to next record or anything, it says that the data has been changed by another user (my query) and asks me to drop changes or what not.

    From what I can find this usually only happens when you actually have other users, on a shared database or with an sql server and another user has actually changed the data. This on the other hand is an entirely stand alone db.

    I have tried Record menu -> Refresh, me.requery and docmd.requery . . . I'm at a loss and need to get this done...

    The data updates correctly if I select "Drop Changes" but I have about 500 records and I dont' want to hit drop changes on every one and I intend to click this button individually for each record. It is also going to be a shared database in the future and I need to get this resolved on the stand alone first obviously.

    Thanks in advance for any and all help!

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    whenever this happens stand alone and and one user it is usually because the underlying recordsource has the id from two different tables in the grid ie:

    table1.myid
    table2.myid


    see if this exists

  3. #3
    Join Date
    Aug 2003
    Posts
    106

    Refreshing the form

    If the form takes its data (i.e control source or record source) directly from a table or query, you may just need to Refresh or repaint the form.

    Try typing

    Me.Refresh

    at the end of the code you have run to add data to your table.

    sugarflux

  4. #4
    Join Date
    Dec 2003
    Posts
    28
    Thanks SOOO much for the responses.

    The form is based on a query that combines many tables. There are several Primary Keys included in the query. None of which have identical names, although they are linked on identical names, only one of the linked keys are in the query. I hope that is clear. I'm not sure if the issue you mentioned fits me or not. The only two fields that are both keys and are linked to each other and are in the query are named table1.item_no and table2.inventory_number.

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    and that is the reason for the error I believe. With data in both tables being "dirty" by virtue of keyfields this will occur. Try taking one of the id out of the query design and use a join to the ids in the design part of the query. Make sure the join is "where both filds are equal"

  6. #6
    Join Date
    Dec 2003
    Posts
    28
    Is it possible that I am getting this error simply due to changing data directly as in: field1 = "" & Field2 & field3 & "" and doing running sql in the same procedure?? This just kina hit me. I am currently moving from modifying the data directly to using queries. Let me know if this is the cause of my woes. I hope to at some point soon have only queries making the changes.

    Thanks again for ANY and ALL help!

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    any time two processes are editing data in the same row (record) this could happen

Posting Permissions

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