Solved

Ms Access SQL not updating when form is open

Posted on 2014-01-02
2
448 Views
Last Modified: 2014-01-03
I have form that prints an Invoice. When the invoice is printed it updates the inventory table to adjust for the number of items sold. I do this with the following statement.

    DoCmd.RunSQL "UPDATE tblInvoiceDetail INNER JOIN tblInventoryData ON " _
    & "tblInvoiceDetail.Partno = tblInventoryData.Partno SET " _
    & " tblInventoryData.QuanInStock = [tblInventoryData].[QuanInStock]-[tblInvoiceDetail].[OrderQuan] " _
    & "WHERE (((tblInvoiceDetail.[InvNo])=" & InvNo & ") AND ((tblInventoryData.DropShip)<>-1));"
   
This works perfectly but if my Inventory form is also open to one fo the items being updated, the update for that item does not occur. What can I do to fix this?
0
Comment
Question by:EclecticBob
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39752870
Access may have that record locked.

You could close the form if it's open, if that would work. To do that, run this just before  you run your query:

On Error Resume Next
DoCmd.Close acForm, "YourFormName"

Or you may just need to save any pending changes. You can do that in several ways. My favorite is to include a routine on the form to run standard record save code. In the form's code module, do something like this:

Function SaveForm() As Boolean
  If Me.Dirty Then Me.Dirty = False
End Function

To use that:

Forms("YourFormName").SaveForm

and then run your query.
0
 

Author Closing Comment

by:EclecticBob
ID: 39754432
Thank you, I'd rather not have to close the form but the SaveForm routine looks like the solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 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