How to verify Field Value exist?

[WineSnob]

I have a form to enter work order numbers and AmountPaid. What I need is an after update event to verify (look at all the records) and if the WO value in the form control does not exist then Msgbox " Not valid WO number". If the WO exists and there is already an AmountPaid > 0 then Msgbox " WO has been paid". If WO is valid and AmountPaid =0 then exit sub.
I am not sure how to code to look at each record and determine its value.
Thanks

 

[CBrighton]

You will want to account for both null and a zero length string.

Try something like this (change control names as required):

If Len(Nz([txtWO],"")) = 0 then
   msgbox "Not valid WO number"
   Exit Sub
Else
   If Not [txtAmountPaid] > 0 then
      MsgBox "WO has been paid"
      Exit Sub
   Else
      'Normal code for passing the 2 checks
   End If
End If

 

[WineSnob]

Thanks for the response. I guess I wasn't clear. I am looking in a table named tblWO in fieldname [NewWOno] for the control value(txtWOno) on the form.
If (txtWOno) does exist in tblWO field [NewWOno] and the field [AmountPaid] in tblWO is null or 0 then I will update the tblWO with that data.
If (txtWOno) does NOT exist in tblWO then Msgbox "WO does not exist"
If (txtWOno) does exist in tblWO BUT [AmountPaid] > 0 then Msgbox "WO Valid but Already Paid" and exit.

Hope that is a little clearer

 

[CBrighton]

Removing the Not from my code is all you need then (other than changing the control names to match yours).

The first If statement is checking for null / zero length in txtWOno. If it's not longer than 1 character after catching the nulls then display messagebox and exit.

The second If statement is only got to when txtWOno has at least 1 character.

Removing the Not in the second If statement will mean that if the record also has an amount paid > 0 then it should display a messagebox and exit the sub.

The comment in the code signifies where you want the process which you currently have to go, this will only be run if the criteria you have laid out is met.