Cancel triggers error in Requery

[fgaiga]

I have a main form + subform bound by a RecordSource/Requery everytime something changes in the mainform.

In my subform I've got a save button to save/update the information in my subform.

As I wanted to avoid the ACCESS default behavior that updates/add records each time you do some action, I decided, as advised in some of the threads, to add "Cancel = True" in the "BeforeUpdate" event on my subform each time the Save button was not pressed.

Unfortunately, this causes a side-effect when I need to update the RecordSource of the subform because the "BeforeUpdate" event is activated, the "Cancel = True" is triggered (because I didn't press the Save button) and then I end up with this error:

Run-time error '2107' The value you entered doesn't meet the validation rule defined for the field or control


Before I implemented the "Cancel true", my ResordSource statement was working.


Does the "Cancel true" has some impact on something else ?? Any ideas ??

Cheers.

Fab.

 

[alastair69]

Your "Cancel = True" is running to early and the information is been canceled before it is saved.

Alastair

 

[fgaiga]

Your "Cancel = True" is running to early and the information is been canceled before it is saved.

Alastair

Exactly Alastair, I don't want to save the information if I don't click on the "Save" button and so that's why I want to Cancel the action.


You said, it is too early, so in which event do I need to put the Cancel if not in the "BeforeUpdate" event ???

 

[Pat Hartman]

The BeforeUpdate is running because something has dirtied the record. Are you dirtying the record with your code or is the user actually entering something in the subform?

 

[fgaiga]

Yes I am dirtying the record on purpose: Each time the user on the mainform is selecting something new that affects the subform, I do a requery based on the key fields in my subform. EIther I find the information and I display it in the subform either the information is non existing and I populate automatically the subform with new values.

It is up to the user to accept those new values; that's the reason why he must press the "Save" button to confirm those new values. If the user doesn't need those new values, he will navigate on the mainform and select something else and then my Requery will run again.

In the end I am happy that the "BeforeUpdate event" is triggered; the issue is with this Cancel statement that sets something that I don't understand.

 

[Pat Hartman]

You shouldn't dirty the record. That is what is causing the problem.

 

[fgaiga]

So ok I won't dirty the record; but then how do I present new information in my subform and let the user decides if he keeps the info (by clicking the "Save" button") or if he discards it by just going back to the main form ??

 

[Pat Hartman]

If you are inserting records to the subform's RecordSource, why don't you just let the user tell you if he wants to insert. Your process now is to insert the row and then let him decide if he wants to keep it.

 

[fgaiga]

If the user then answers Yes to the creation. How do I "cleanly" present information to him in the subform ? Do I have to use an "insert" statement ? or something else ??

 

[Pat Hartman]

Here is an excerpt from one of my applications. It is an order entry form. The main form represents the OrderHeader and there are two subforms. One for the OrderDetails and one that shows current inventory. This code is from a procedure in the inventory subform. On a double click in the product name field, that item is added to the OrderDetails table. The requery makes it show in the subform and the setFocus moves the focus to the Quantity field so the order taker can enter the quantity.

    Dim strSQL As String
    Dim InventoryType As String
    Dim Con As ADODB.Connection
    Set Con = CurrentProject.Connection
    If Me.Parent!txtOrderType = "Regular" Then
        InventoryType = "C"
    Else
        InventoryType = "P"
    End If
    strSQL = "Insert INTO CustomerOrderDetails ([Order ID], [Product ID], [Order Price], [Conversion], [CurrentOrPre]) "
    strSQL = strSQL & " Values(" & Me.Parent!txtOrderID & ", " & Me.txtProductID & ", " & Me.txtHighPrice & "," & Me.Conversion & ",'" & InventoryType & "');"
    Con.Execute strSQL
    Con.Close
    Set Con = Nothing
    Me.Parent!sfrmOrderDetails.Requery
    Me.Parent!sfrmOrderDetails.SetFocus
    Me.Parent!sfrmOrderDetails.Form!txtquantity.SetFocus

 

[fgaiga]

Thanks Pat;

I made something almost similar and it works properly; thanks for your major input. I've just one small complaint: at the execute SQL statement, I get an Access window message "You are about to append 1 row(s)...."

Is it possible to get rid of that annoying message ??

 

[jubb]

DoCmd.SetWarnings False

inserted in the code before you execute the INSERT query then

DoCmd.SetWarnings True

after the INSERT will rectify your prob.

Hope this helps.

 

[fgaiga]

OK. Thanks. I found that solution 2 min ago and it works !