On Exit Event - tab/click header vs. detail (1 Viewer)

[brucesilvers]

I have a continuous form, with data entry fields in both the form header and the detail section.

I've attached an "On Exit" event to the ProdQty field in the detail section. The prime function of the event code is to run an update query on the table (which is the data source for the form).

The code executes properly (update query runs successfully and form refreshes) when I Tab out of that field. It also executes properly when I use the mouse to click on another field in the detail section.

However, when I use the mouse to click on a field in the form header or form footer sections, the On Exit code does not execute properly.

Here's the On Exit Sub:

Private Sub ProdQty_Exit(Cancel As Integer)
On Error GoTo ProdQty_Error

If (Int(Me!ProdQty) <> Me!ProdQty) Or ((Me!ProdQty / Me!LDU) < 1) Or (Int(Me!ProdQty / Me!LDU) <> (Me!ProdQty / Me!LDU)) Then
    Me.ProdQty.BackColor = 255
    Me.ProdQty.ForeColor = 16777215
    Beep
    MsgBox "The quantity you entered is invalid for this product." & vbCrLf & vbCrLf & "Please check the LDU (least divisible unit) and enter a new quantity.", vbExclamation, "Quantity Error"
    DoCmd.GoToRecord acActiveDataObject, "Order Form", acPrevious
    DoCmd.GoToRecord acActiveDataObject, "Order Form", acNext
    DoCmd.GoToControl "ProdQty"
    Exit Sub
Else
    Me.ProdQty.BackColor = 255
    Me.ProdQty.BackColor = 16777215
    Me.ProdQty.ForeColor = 0
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Update Product Qty in Current Order", acViewNormal, acEdit
    Me.Requery
    Me.Refresh
    Me.Repaint
    
    DoCmd.GoToControl "ProductCombo"
End If

ProdQty_Error:
    Exit Sub

End Sub

Here's the Update Query:

UPDATE [Current Order] INNER JOIN Products ON [Current Order].[Product #] = Products.[Product #] SET [Current Order].Quantity = Forms![Order Form]!ProdQty, [Current Order].Price = IIf([Forms]![Order Form]![PriceCodeCombo]="CS",[Products]![Contract Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="EDW",[Products]![Ed Wholesale]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="R2",[Products]![R2 Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="R3",[Products]![R3 Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="R4",[Products]![R4 Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="R5",[Products]![R5 Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="R6",[Products]![R6 Net]*([Forms]![Order Form]![ProdQty]/[Forms]![Order Form]![ProdLDU1]),IIf([Forms]![Order Form]![PriceCodeCombo]="W",[Products]![Wholesale Net]))))))))
WHERE ((([Current Order].[Product #])=[Forms]![Order Form]![Product1]));

I tried copying that code to "After Update", "On Change", and "On Lost Focus" events but the results were the same.

Any suggestions would be greatly appreciated. Thanks!

 

[llkhoutx]

Your posting presume that the ProdQty_Exit subroutine is bound to control Prod_Qty and Prod_Qty loses focus. Is that what happens? It's not what you're saying.

That event should not otherwise be firing, unless another event also fires it. This is quite common. It only takles a Call Prod_Qty(DumyCancel) inline instruction, with or without the Call.

Set a break point on the "Private Sub ProdQty_Exit(Cancel As Integer)" instruction line to verify when that code is executed.

 

[Pat Hartman]

The OnExit event should not be used for this type of code. Code that edits a particular field should be placed in the BEFOREupdate event for that field or in the form's BeforeUpdate event. That way you can cancel the event if the data is invalid. Cancelling the event, prevents the bad data from being saved so you don't have to run an update query.

PS, running an update query against a recordset that you have open for your form, can result in update conflicts which cause confusing error messages to be displayed to your users.


Your code should look more like this assuming you are using a bound form. Of course, if you are using an unbound form, you will need to run the update queries.

Private Sub ProdQty_BeforeUpdate(Cancel As Integer)
On Error GoTo ProdQty_Error

If (Int(Me!ProdQty) <> Me!ProdQty) Or ((Me!ProdQty / Me!LDU) < 1) Or (Int(Me!ProdQty / Me!LDU) <> (Me!ProdQty / Me!LDU)) Then
    Me.ProdQty.BackColor = 255
    Me.ProdQty.ForeColor = 16777215
    Beep
    MsgBox "The quantity you entered is invalid for this product." & vbCrLf & vbCrLf & "Please check the LDU (least divisible unit) and enter a new quantity.", vbExclamation, "Quantity Error"
    Cancel = True
    Exit Sub
Else
    Me.ProdQty.BackColor = 255
    Me.ProdQty.BackColor = 16777215
    Me.ProdQty.ForeColor = 0
 End If

ProdQty_Error:
    Exit Sub

End Sub

 

[brucesilvers]

Not Sure What to Do From Here

I'm really at a loss as to where to go with this, and so I've attached the database with the form in question [Order Form]. I would be VERY appreciative if someone could take a look at it.

No matter what I try, the ProdQty isn't recognized as having changed unless you {Tab}, {Enter}, or click-on-another-record, to leave the [ProdQty] field. Since our users are mostly mouse-happy rather than keyboard-comfortable, we really would like to get the update to happen even if someone clicks with the mouse outside the ProdQty field.

I understand that updating records currently being displayed can be problematic, but I haven't experienced such problems yet with this form.

The form IS tied to the Current Order table, but pricing exists in a different table based on [ProdQty] and [PriceTypeCode], and it was expressly requested that price changes be visible in real-time.

Currently, the underlying table is empty until the user selects a Customer and a ShipTo location. At that point, there is a single record in the Current Order table (no products or prices) simply to hold the customer info and to have something to display as the form first opens.

New orders, and orders being edited, sit in the Current Order table until they are either saved (append to CAMEX Orders) or canceled (simply discard the Current Order table records). Completed orders reside in the CAMEX Orders table.

Is it possible to alter my current form so that prices can be updated even if the user clicks on the form header or other location, or do I need to start from scratch with a different approach?

I apologize for my ignorance and thank you immensely for any assistance you can provide on this.

 

[llkhoutx]

You're Qty AfterUpdate creates and error and you don't catch it. solve that issue and you may eliminate your instat problem. LDU appears to be the problem.

You need to work on the follwoing:

(1) The form's "Cycle" property is set to 'All Records' meaning that you'll tab off the record on onto the next when the last field is exited. I would think that you'd not want to exit a record unintentionally. Set the "Cycle" property to 'Current Record' which will cause the form to stay on the current record until you force it to move.

(2) I think that your design is defective. Order line items should be a separate table, unless there's only one line item per record.

(3) Various order fields are locked.

 

[brucesilvers]

Thank you both for your help on this so far!!! I'll be redesigning the whole thing based on both of your input.

Apparently, I was so concerned about the form in this project that I ignored good table design - even if my coding skills are weak, I should know better when it comes to tables.

It sounds like what I need to do is:

1. Separate and normalize my tables (just because data is being displayed a certain way doesn't mean it needs to be stored that way);
   
2. Bind the form to either my order-headers table or to a query that joins all the info;
   
3. Use a continuous form or use a subform to display the order-lines, either way drawing from a query rather than the actual table being updated;
   
4. Run updates, appends, etc. on table data as a BeforeUpdate event;
   
5. Double check my form's cycle property is correct based on the data source feeding the form; and
   
6. Call code snippets I use repetitively rather than repeating them in various events.

I'll be implementing these changes over the next day or 2. Thanks again!!!