Background lookup On Lost Focus, looking for duplicates... IT DOESN'T WORK!!!

[richy240]

I am at a stand still; PLEASE HELP ME!

I have been researching this problem and trying many different things, but I cannot get it to work! Here is what I want to happen:

When I move my cursor from a text box (On Lost Focus), I would like the corresponding table to be checked for the value (a purchase order number). If this PO number already exists in my table, I would like an error to pop up. If it does not already exist, I would like to continue entering data in my form.

This should operate in the background and I would like that this happen as soon as I move away from the text box. This value acts as the Primary Key for the table, and will throw an error if the PO number already exists after I try to move away from the RECORD. But I want it to happen when I enter the PO number (or immediately after - when I move away from the form field) in order to save the time and effort of entering redundant data.

Does this make sense? I have tried using ADO to run a query and check the length of the resulting recordset, but I can't seem to get it to work. (That is, if the recordset contains records, this record must already exist; If the recordset is at EOF then continue entering data.) I am familiar with ADO in ASP, but not VBA.

Does anyone have any recommednations? Please help!

Thanks in advance.

 

[Pat Hartman]

1. Use the BeforeUpdate event of the control rather than the LostFocus event. You only want to run the code if the PO value is changed, not just because someone tabbed through the field. Then cancel the event if a duplicate is found.
2. The simplest way to lookup a single key value is to use DLookup(). It is not the most efficient, but it requires only a single line of code so most people find it easier.

If IsNull(DLookup("YourField", "YourTable", "YourField = " & 
Me.YourField)) Then
    Cancel = True
    Msgbox "error ......", vbokonly
End If

 

[richy240]

Oh... That seems easy enough. Thanks. I'll give it a try and post the results!

Thanks again!

 

[richy240]

Wait... the BeforeUpdate event fired immediately before a record is updated...

That isn't going to work (and actually does not produce the desired result once it is run). You have, however, provided me with valuable advice with DLookup.

I do want this to happen in the LostFocus event, that way if this record does already exist it will throw an error and if the record does not exist it does nothing. I am not concerned about running code every time someone leaves this field because it won't be used all that much anyway. I am as concerned with performance as I am with functionality here.

I am going to use your recommendation with DLookup, but alter it some to reflect my actual requirements. Thanks again for your help.

I hope I am on the right track here.

 

[richy240]

Do I have any idea what I am talking about?

 

[richy240]

OK - I got it to work with the help of some other people. You were right... I thought you were talking about the BeforeUpdate of the form, not the control. I understand what you meant now, and you were exactly right.

Thank you... I appriciate your help!