none
Me.dirty = false throws error? RRS feed

  • Question

  • Hi everyone, 

    I have a main form for shipments, on which is a combobox which contains products ready to be added to the shipment. When a product is selected from the combobox, the product is added to the subform.

    If I don't save before a product is selected from the combobox, I get error

    3201: You cannot add or change a record because a related record is required in table.

    So to solve this issue, I added If Me.Dirty Then Me.Dirty = False beforehand, but now I get the error

    2759: The method you tried to invoke on an object failed. 

    The debug tool highlights Me.Dirty = False.

    The weird things are that if I choose to debug the error and then just click continue, code execution continues as expected.

    Also, this error obviously only occurs when adding the first product to a new shipment. In other cases, I guess the error does not occur since the underlying data is already saved to the database.

    Finally, I have almost the exact same set up for refunds with very similar code, and this one works perfectly. I am guessing there is a tiny difference somewhere which causes the error....

    What should I be looking for?

    Thanks for your help.

    Wednesday, January 4, 2017 8:34 PM

Answers

All replies

  • Hi,

    Can you post the code, if any, behind the combobox to add the selected item to the subform? Thanks.

    Wednesday, January 4, 2017 8:56 PM
  •     ProdID = Me.cboAddProduct.Value
        
        Set db = CurrentDb
        Set rs = Me.fsubRefundProducts.Form.RecordsetClone
        
        'Save Refund record to tblRefunds in order to then allow saving records to tblRefundProducts
        If Me.Dirty Then Me.Dirty = False '<-- PROBLEMATIC LINE
        
        With rs
            .AddNew
        
            'Fill information directly into record (ie. bypasses controls on subform)
            ![RefundID] = Me.txtRefundID.Value
            ![OrderedProductID] = ProdID
            ![UnitRefundAmount] = DLookup("[SellingPrice]", "tblProducts", "[ProductID] = " & ProdID)
            ![Quantity] = Me.cboAddProduct.Column(2) 'The 3rd column (ie 2nd index) holds the quantity to order. 
            .Update
        End With
        
        Set rs = Nothing
        
        'Requery the AddProduct combobox
        Me.cboAddProduct.Requery
        
        'Update subtotal
        Call UpdateTotal
        

    This is pretty much it. Literally the exact same code is on another form and works fine, which really throws me off.

    For what it's worth, I just placed a break point at the problematic line. When the code stopped, I manually clicked Save (on the Access ribbon) and then resumed execution, and everything worked.

    Wednesday, January 4, 2017 9:24 PM
  • Hi,

    Thanks. Just for fun, try commenting out the If Me.Dirty Then Me.Dirty=False line and replace it with the following to see if it makes any difference:

    DoCmd.RunCommand acCmdSaveRecord

    Hope it helps...

    Wednesday, January 4, 2017 9:47 PM
  • Sounds like a timing problem.  Try calling the DoEvents function after saving the parent form's current record.  If a single call doesn't work call it in a loop:

        Dim i As Integer
        
        For i = 1 To 1000
            DoEvents
        Next i

    Ken Sheridan, Stafford, England

    Wednesday, January 4, 2017 11:08 PM
  • Hi, 

    DoCmd.RunCommand acCmdSaveRecord

    I should have mentioned that I had already tried that.....same result.



    • Edited by SL02 Wednesday, January 4, 2017 11:49 PM
    Wednesday, January 4, 2017 11:31 PM
  • Ok I figured out what causes the problem, but have not idea why.

    As mentioned, the above code is located in the cboAddProduct_Click event. There are other events in that form, so I decided to disable each one one at a time. Once I found which event was causing the problem, I commented out each line in that event one at a time. 

    Conclusion:

    The problem comes from the Form_AfterUpdate (which runs immediately after the Me.Dirty=False is executed).

    More specifically, this is the problem code:

    If IsNull(Me.txtRefundRequestDate.Value) Then
        Me.txtRefundRequestDate = Date
    End If

    If this piece of code is commented out, no error. If it is not commented out, I get error 2759 as described above when attempting to save from the combobox click event.

    Wednesday, January 4, 2017 11:53 PM
  • Hi, Try moving the code to the Before Update event instead.
    Thursday, January 5, 2017 12:09 AM
  • You could either set the control's DefaultValue property to Date(), or, if you don't want the current date to appear in the form by default, move the code to the form's BeforeUpdate event procedure.

    Ken Sheridan, Stafford, England

    Thursday, January 5, 2017 12:10 AM
  • Hi SL02,

    I agree with the suggestion provided by .theDBguy and Ken Sheridan.

    if you think that suggestion given by them can solve your issue then I suggest to mark their suggestion as an answer.

    you can try to test with it and if you have any further question regarding that then you can let us know about that.

    we will try to provide suggestion for that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 2:43 AM
    Moderator
  • Moved the code to BeforeUpdate and all works flawlessly.

    If anyone stumbles on this post later on with a similar problem, do investigate your AfterUpdate event.

    Thanks for the help!

    Thursday, January 5, 2017 3:55 AM
  • Hi,

    Glad to hear you got it sorted out. We were happy to assist. Good luck with your project.

    Thursday, January 5, 2017 5:12 AM