Table Form

[Carly]

I have created a AutoForm: Tabular and I have got 6 columns. I enter a product code into the 1st column, and when I tab off this column it runs the following code to fill the next 2 columns:

Private Sub Product_Code_AfterUpdate()
Dim Chk As Variant
Chk = DLookup("[description]", "[dbo_tsapmaterials]", "[product] = [Product_Code]")
    If Chk <> "" Then
        Me.Description = DLookup("[description]", "[dbo_tsapmaterials]", "[product] = [Product_Code]")
        Me.BUOM = Trim(DLookup("[unit]", "[dbo_tsapmaterials]", "[product] = [Product_Code]"))
    Else
        Me.Description = "Incorrect Material"
        Me.Product_Code.SetFocus
    End If
End Sub

The problem I have is that when the product doesn't exist in the table dbo_tsapmaterials & the Description sets to "Incorrect Material", it doesn't go back to the Product_Code column.
Is there any way this can be done, or is there a better way to do this?

Any help will be greatly received.

Kind Regards

Carly

 

[RuralGuy]

Use the BeforeUpdate event instead. Set Cancel = True if you want to hold the focus in the current control.