Validate After Lost Focus

[kamau911]

Hello All,

I have a form that allows users to enter invoice numbers into an invoice number field that is stored in an invoice table. There is a validation rule that prohibits entry of invoice numbers that have been previously entered. This is an accounts payable operation and the invoice numbers come from our vendors. The purpose of this application is to track incoming invoices.

The form works but the user has to populate other data in every field before Access tests if the data is valid. What I would like is for Access to test if the data is valid as soon as the invoice number field losses the focus. I know it is something simple but... "simple is as simple does Forest.":banghead: Can anyone help me with this?

 

[RuralGuy]

Validation is most often done in the BeforeUpdate event of the control. Set Cancel = True to hold the focus in that control.

 

[kamau911]

Thanks for responding RuralGuy but I am not quite sure we are on the same page. As soon as the focus moves to another control on the form I want Access to test if the data that was entered in the control that just lost focus is valid based on the validation rule rather than waiting until a new record is going to be added. What is happening now is Access waits until the user is getting ready to create a new record before checking. I hope this makes it a little clearer what I am trying to do.

 

[RuralGuy]

It sounds like the validation is currently being done in the BeforeUpdate event of the FORM. You need to move it to the BeforeUpdate Event of the CONTROL. That will do what you want.

 

[kamau911]

Thanks I will give it a try.

 

[kamau911]

RuralGuy thank you so much for your help so far. I tried what you suggested and it did not really give me what I was looking for. Please bear with me. I am not much of a technical writer. Let me try again.

I have a data entry form linked to an invoice table. The table has an index on the invoice number field and is set to no duplicates. The idea is to avoid duplicate invoice number entries into the invoice table. The table will not allow duplicate entries as it stands. However, I want the test to happen while the invoice number control still has the focus. There is no validation rule connected to any controls or to the form as I stated in error earlier. The determining factor here is the index set to no duplicates. How can I get Access to test that the value entered on the invoice number control does not have a corresponding duplicate value in the table while the invoice number control still has the focus? This is a little different from the original post but I kind of had to think it through.
:banghead:

 

[RuralGuy]

Put something like this:

Private Sub [COLOR="Red"]InvoiceNo[/COLOR]_BeforeUpdate(Cancel As Integer)
   On Error GoTo Err_[COLOR="Red"]InvoiceNo[/COLOR]
   ' See if the InvoiceNo is already in the system.
   Me.RecordsetClone.FindFirst "[[COLOR="Red"]InvoiceNo[/COLOR]] = '" & Me.[COLOR="Red"]InvoiceNo[/COLOR] & "'"
   If Not Me.RecordsetClone.NoMatch Then
      '-- It is an existing invoice number.
      '
      MsgBox "[" & Me.[COLOR="Red"]InvoiceNo[/COLOR] & "] is a duplicate number!" & vbCrLf & _
             "Please re-enter the number."

      '-- And hold the focus in the current control
      Cancel = True
   End If

Exit_[COLOR="Red"]InvoiceNo[/COLOR]:
   Exit Sub

Err_[COLOR="Red"]InvoiceNo[/COLOR]:
   MsgBox "Error      : " & Err.Number & vbCrLf & _
          "Description: " & Err.Description
   Resume Exit_[COLOR="Red"]InvoiceNo[/COLOR]

End Sub

...replacing InvoiceNo with your actual ControlName and FieldName.