Form based on query does not always update data

[lios1984]

Hello to everyone!!!

I have created a form based on a query. The two tables in this query are joined correctly and up until recently I could update data with no apparent problem. However, I came to notice that there are certain times that the data is not updated. Only after two or three tries.

Why is that? Should I recreate the form?

[June7]

Try it and see if that corrects the issue. Could provide the db for analysis. Follow instructions at bottom of my post.

[Missinglinq]

...I came to notice that there are certain times that the data is not updated. Only after two or three tries.

One scenario in which Updates are sometimes but not always committed to the underlying Table involves the use of a custom Command Button to Close a Form and a bug that has been known from Access Day 1 but never fixed.

It has long been recommended that the code

DoCmd.RunCommand acCmdSaveRecord

Or

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a form because of a quirk in Access. When DoCmd.Close is used, Access closes the form regardless of whether or not a PK field or other required field has been left blank or validation rule has been violated! If one of these things occur, Access will simply dump the Record, close the Form, and not tell the user that the Record has been dumped!

If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord

forces Access to attempt to save the Record, and if a violation has occurred, will throw up a warning message allowing correction to be made before closing the Form.

So, if you have such a Command Button and Fields that are made Required, in one fashion or another, as outlined above, this could be why sometimes the Updates aren't saved.

Linq ;0)>

[June7]

Interesting, I have never encountered issue with DoCmd.Close. Could be because of my db design. Will have to keep that in mind. Thanks Linq.

[Missinglinq]

It was recognized and reported as a bug as far back as 2000, I believe. Rather than fix the bug, after all these years, they simply added a line to the 'Help' entry for the Command, in v2007, stating that it the problem existed and would dump non-conforming Records, without warning, unless the Record was saved first!

And then newbies started using the line

If Me.Dirty Then Me.Dirty = False

even on Unbound Forms, which popped its own error, since Unbound Forms have no 'Dirty' Property!

It may not be the culprit, here, but it is one possibility.

Linq ;0)>

[lios1984]

I must say that you really surprised me. That is exactly what I have done. I have created the form with an adjusted command button that executes DoCmd.Close the specific form when clicked on. However I tried both ways you suggested and they still don't work. When I try updating the data in the query, it works fine but in the form nothing.

[June7]

Want to provide project for analysis? Follow instructions at bottom of my post.

[lios1984]

Now I noticed that when I close the form, it's not the current record that is updated but the previous record (Current record-1). And that seems to occur only for certain area of records. I really don't know what is happening. The rest forms work just fine (after several trials).

It will take some time to attach an emptied database.

[lios1984]

I attach for you the test database. I hope this file offers enough to analyze.

Test.zip

[June7]

Hard to test. My English version can't deal with the Greek naming. Why is there a mix of English and Greek? The RowSource SQL of ProjectNumber combobox references Greek field names but the table has English field names. Controls have Greek names.

I renamed everything to English. Even after edits, I get error:

"The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control."

I tried importing all to Access 2007 db and still get the error.

Sorry, can't help.

[Missinglinq]

Having comments as well as field/object/control names in a foreign language does make a difficult job, wading thru someone else's app, even tougher! I've downloaded it, as well, but while I usually fit responding to threads in between my other work, I'll have to look at this later when can lock my door and concentrate on it!

It would probably help if we had a concise step-by-step on how to use the Form, in order to attempt to reproduce the error.

Linq ;0)>