Solved

I cannot get the results of an update query to appear on my Access form

Posted on 2014-11-11
4
161 Views
Last Modified: 2014-11-11
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.

  the subformthe error
0
Comment
Question by:jwandmrsquared
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40436256
You are conflicting with yourself.  The form is bound and something made it "dirty" so Access knows the current record needs to be saved.  You then ran a macro to update the bound record.

It is never a good idea to use an update query to update the record you are looking at.

You can start by figuring out what is dirtying the current record.  Is your combo bound?  If so, that would do it.  You should not be using a bound combo to enter selection criteria.  If you can keep the current record from becoming dirty before you run the update, you may be able to prevent the scary message or it may just move to the point in time where you try to save the current record using the form.

I don't have a clear picture of what you are trying to do so I can't be too specific. Usually an "add new" button just moves the current record to a "new" record.  It sounds like you are having the user enter some value BEFORE you move to the "new" record and that could be the issue.
0
 

Author Comment

by:jwandmrsquared
ID: 40436317
I ended up converting the macro to vba, then inserting a requery statement. I've tested numerous times and it seems to be working.  I am keeping your notes above about not updating the record you are on, I didn't know that.
0
 

Author Closing Comment

by:jwandmrsquared
ID: 40436318
Guided me in the right direction.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40436357
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA pass value between two fields different tables 10 39
Sub Reports 8 23
MS Access Order Smallest to Biggest Query Help 13 43
Continuous form : Prefilled Data 7 19
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now