MS-Access - how to trigger data insert before LostFocus event?

Question

I have an procedure that runs on a control's AfterUpdate event:

Private Sub cmdDelCountry_AfterUpdate()

Dim strID As String
strID = Me.ID
Dim strCase As String
strCase = 1

Dim cmdCommand As New ADODB.Command

With cmdCommand
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "uspSalesOrderHead_UPDATE"

        '@RowID
        .Parameters("@RowID").Value = strID

        '@Case
        .Parameters("@Case").Value = strCase

    .Execute

End With

End Sub

The procedure executes a stored SQL Server procedure to process some data on the server:

ALTER PROCEDURE uspSalesOrderHead_UPDATE

(
    @RowID int,
    @Case int
)

AS

IF @Case = 1 /* Delivery Country Select */

    BEGIN
        /* Update Order Head table */
        UPDATE dbo.tblSalesOrderHead
        SET dbo.tblSalesOrderHead.txtRegionCode     = dbo.tblSettingCountryCode.txtRegionCode,
            dbo.tblSalesOrderHead.txtCurrencyCode   = dbo.tblSettingCountryCode.txtCurrencyCode,
            dbo.tblSalesOrderHead.txtLanguageCode   = dbo.tblSettingCountryCode.txtLanguageCode
        FROM    dbo.tblSalesOrderHead
        INNER JOIN dbo.tblSettingCountryCode ON dbo.tblSalesOrderHead.txtDelCountry = dbo.tblSettingCountryCode.txtCountryCode
        AND     dbo.tblSalesOrderHead.ID            = @RowID;

        /* Update Order Line table */
        UPDATE  dbo.tblSalesOrderLine
        SET dbo.tblSalesOrderLine.txtRegionCode  = dbo.tblSalesOrderHead.txtRegionCode, 
            dbo.tblSalesOrderLine.txtCurrencyCode    = dbo.tblSalesOrderHead.txtCurrencyCode
        FROM    dbo.tblSalesOrderLine
        INNER JOIN dbo.tblSalesOrderHead ON dbo.tblSalesOrderLine.intOrderNo = dbo.tblSalesOrderHead.ID
        AND     dbo.tblSalesOrderLine.intOrderNo         = @RowID;
    END

The problem is that the control's ( cmdDelCountry ) value is not inserted into the table ( dbo.tblSalesOrderHead ) before it is requested by the stored SQL procedure: INNER JOIN dbo.tblSettingCountryCode ON dbo.tblSalesOrderHead.txtDelCountry = dbo.tblSettingCountryCode.txtCountryCode

How do I insert the data before the stored procedure is executed?

Answer 1

You can use (on the form)

 If Me.Dirty Then Me.Dirty = False

To force a save, but it may be better to consider the form's after update event, rather than the control.