?
Solved

How do I overcome the "Property not found" error  when BeforeUpdate is cancelled in Access 2007?

Posted on 2008-02-06
6
Medium Priority
?
1,273 Views
Last Modified: 2013-11-27
I recently upgraded to Microsoft Access 2007 but I still use the old file format (Access 2000-2003). My code was working perfectly with Access 2003 but now I get an error saying "Property not found". I can't figure out what property it is looking for.

Could someone give me some tips?

Thanks,
camoh
Private Sub QtyOrdered_AfterUpdate()
On Error GoTo Err_QtyOrdered_AfterUpdate:
 
Me.Refresh
 
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim dbs As Database
 
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("Products", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("QryQtyOnHand", dbOpenDynaset)
 
Do Until rst1.EOF
    With rst2
        .FindFirst "[ProductID]= '" & rst1![ProductID] & "'"
        If .NoMatch Then
            rst1.Edit
            rst1!QtyOnHand = 0
            rst1!OpenOrders = 0
            rst1.Update
        Else
            rst1.Edit
            rst1!QtyOnHand = rst2!OnHand
            rst1!OpenOrders = rst2!OnOrder
            rst1.Update
        End If
    End With
rst1.MoveNext
Loop
 
rst1.Close
rst2.Close
 
Me.Refresh
 
 
Exit_QtyOrdered_AfterUpdate:
    Exit Sub
 
Err_QtyOrdered_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_QtyOrdered_AfterUpdate
    
End Sub
 
Private Sub QtyOrdered_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_QtyOrdered_BeforeUpdate:
 
Dim Msg, Style, Response
 
Msg = "Are you sure you want to change the ORIGINAL order quantity?"
Style = vbYesNo + vbDefaultButton2 + vbExclamation 'Define buttons.
 
If QtyOrdered.OldValue <> QtyOrdered.Value Then
    Response = MsgBox(Msg, Style, "Attention!")
    If Response = vbNo Then
        QtyOrdered.Undo
        Cancel = True '****When I run this line, I get an error.**** 
    End If
End If
 
Exit_QtyOrdered_BeforeUpdate:
    Exit Sub
 
Err_QtyOrdered_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_QtyOrdered_BeforeUpdate
    
End Sub

Open in new window

0
Comment
Question by:camoh
  • 3
  • 3
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20832454
Can you perform step-through debugging on this, and then identify that exact line that is throwing the error?
0
 

Author Comment

by:camoh
ID: 20833496
I can put a breakpoint into the events and step thru them but no error message shows up until it appears to be finished running code.

To be exact, the message pops up after line 64 is run but it happens only if line 59 is run.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20833538
>Private Sub QtyOrdered_AfterUpdate()
>Cancel = True '****When I run this line, I get an error.****

The AfterUpdate event does not have a Cancel parameter.  
AfterUpdate = After you update = it's already committed, so you can't change it.

Move your code into the BeforeUpdate event.

Private Sub QtyOrdered_BeforeUpdate(Cancel as Integer)   '<--- looky here, a Cancel parameter
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:camoh
ID: 20833590
>Move your code into the BeforeUpdate event.
It is only in the BeforeUpdate event already.

Sorry I might have made it confusing by pasting both the before and after update events in one snippet.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 20833628
I see, you are correct.

Just a guess, if you are using .Undo, then there may be nothing to cancel, hence the error message.

QtyOrdered.Undo
Cancel = True

Try removing the .Undo line.  If that doesn't work, remove both and throw in DoCmd.CancelEvent
0
 

Author Comment

by:camoh
ID: 20833796
Removing the .Undo line did not work.

The following code worked as per modifying your suggestion by replacing only the Cancel = True line
with DoCmd.CancelEvent.

I thought DoCmd.CancelEvent would cancel all previous code in the event but it does not appear to be doing so.

Thank you very much for your help!
camoh
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

588 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