jwandmrsquared
asked on
I cannot get the results of an update query to appear on my Access form
I have a form that is actually a main form, a tabbed form, a subform on a tab, and a subform within that tab. The subform pulls from its own table and is populated by selecting one option from a provided dropdown box. I attached a macro to choose whether a new record needs to be added using the dropdown selection or whether an existing one needs to be updated.
Once the update has been made to the record, I'm trying to reflect the change on the form. I keep getting a conflict message. I've attached a copy of the subform and the error message. help.
Once the update has been made to the record, I'm trying to reflect the change on the form. I keep getting a conflict message. I've attached a copy of the subform and the error message. help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Guided me in the right direction.
The requery is forcing the current record to be saved and so since it is no longer dirty, the message went away. This gets you past the error message but I suggest that you rethink the entire process in light of my earlier comments.
I'm going to add another "never". Never rely on the byproduct of a statement. If you want to save the record, save it using the correct VBA command - DoCmd.RunCommand acCmdSaveRecord OR in some cases you might need to use
If Me.Dirty Then
Me.Dirty = False '''force save record
End If
If you ever have to use the Me.Dirty trick (and it is a trick since it obfuscates the actual action), always comment it because the obvious interpretation of the statement is that you are cancelling the update by resetting the dirty flag rather than forcing the update.
Both requery and refresh as a side effect, save the current record but they have awkward side effects that you don't get if you simply save the current record. I'm telling you this because you didn't know that the Requery was forcing the record to be saved and that was why the message went away and someday, you might run into trouble because you used Requery rather than the correct save instruction.
I'm going to add another "never". Never rely on the byproduct of a statement. If you want to save the record, save it using the correct VBA command - DoCmd.RunCommand acCmdSaveRecord OR in some cases you might need to use
If Me.Dirty Then
Me.Dirty = False '''force save record
End If
If you ever have to use the Me.Dirty trick (and it is a trick since it obfuscates the actual action), always comment it because the obvious interpretation of the statement is that you are cancelling the update by resetting the dirty flag rather than forcing the update.
Both requery and refresh as a side effect, save the current record but they have awkward side effects that you don't get if you simply save the current record. I'm telling you this because you didn't know that the Requery was forcing the record to be saved and that was why the message went away and someday, you might run into trouble because you used Requery rather than the correct save instruction.
ASKER