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.
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.
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"
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.