Form to create new entry: how to check uniqueness of value immediately (1 Viewer)

[maxgold]

Hello,

I have a form that I use to create new entries for my data table storing information on people. Each person is assigned a unique alphanumeric code. As soon as the user has entered an alphanumeric code into this field, I would like to check if it is unique.

Right now, I have set the field itself to unique - yes and required - yes. However, the uniqueness of the value seems to be checked only after I finish entering all the data which is too late for me.

Would it be possible to either do that with a macro or with a validation rule? Because the validation rule seems to be checked immediately after entering the value...

Best,
Max

 

[Gasman]

Use Dlookup() ?

 

[Petr Danes]

In the field's BeforeUpdate event - then you can cancel the update BEFORE it happens, which it sounds like you want.

Another way in in the field's Change event - there you can test even one keystroke at a time, if you want, and immediately flash a warning if the entry strays from permitted entries, before the user has entered the entire incorrect number. I use this method quite often to rapidly filter down a list of values, AS the user types. He can see the list shrink and stop typing when he sees what he needs, instead of me pre-determining what is the proper amount to type.

If you do not know how to do this, post back, and I will explain in more detail.

 

[The_Doc_Man]

When entering data to a field, by definition that field has focus. When you are about to enter data to another field or to click a control button or some other action to another control, you have to change focus, which means the field you are in has to LOSE focus. Use the field's LostFocus event to apply Gasman's DLookup or DCount suggestion to verify validity Then, if there is a problem,you can pop up a "Not Unique" message and force focus (see SetFocus, which is a method of any form control that is data-related) back to that field for re-entry. NOTE that you have to be careful about this since it essentially locks the form onto that field until a unique value is entered. It might be SAFER if you popped up a message, perhaps changed the color of the field's background, and let the user proceed, but then double-check in the Form_BeforeUpdate. If you actually DID lock down that user, it might be impossible to undo the new record, and Access WILL want to save that at some point, unique or not.

EDIT: You can also use Peter's suggestion, though the Change Event for a data entry control will fire on each character and that might be TOO MUCH of a good thing. The Change event is one event per character. The LostFocus event is fired once when you attempt to leave a value behind.

 

[Petr Danes]

When entering data to a field, by definition that field has focus. When you are about to enter data to another field or to click a control button or some other action to another control, you have to change focus, which means the field you are in has to LOSE focus. Use the field's LostFocus event to apply Gasman's DLookup or DCount suggestion to verify validity Then, if there is a problem,you can pop up a "Not Unique" message and force focus (see SetFocus, which is a method of any form control that is data-related) back to that field for re-entry. NOTE that you have to be careful about this since it essentially locks the form onto that field until a unique value is entered. It might be SAFER if you popped up a message, perhaps changed the color of the field's background, and let the user proceed, but then double-check in the Form_BeforeUpdate. If you actually DID lock down that user, it might be impossible to undo the new record, and Access WILL want to save that at some point, unique or not.

EDIT: You can also use Peter's suggestion, though the Change Event for a data entry control will fire on each character and that might be TOO MUCH of a good thing. The Change event is one event per character. The LostFocus event is fired once when you attempt to leave a value behind.

The BeforeUpdate event allows you to set its Cancel parameter, which automatically holds the form's focus in the field, instead of having to use SetFocus in code. Also, it allows you to leave the field with ESC, while using the LostFocus event can trap you in the field, as you pointed out.

 

[maxgold]

Thank you for the explaining the options at which point I could implement this! I think I will try and compare the BeforeUpdate and the LostFocus.

I am still fairly new to this, could someone also explain me how to check if the value exists already? I've had a look at Dlookup but I'm not entirely sure how I would go about this.

 

[Petr Danes]

Dlookup has three parameters:
1. The field you want returned from the lookup operation, for instance, you may want to look up the person's last name, using the unique code for a lookup key. In such a case, the first parameter would be "LastName", assuming that is the name of the field in the table which contains the last name.
2. The name of the table in which you want to do the lookup. You can even put entire queries in this parameter, but for your purposes, you will probably want just the name of the table.
3. The lookup condition. This can also be quite varied, but essentially, it is the WHERE clause of a query, without the keyword Where. In your case, it would probably be something like:
"Code = '" & txtCode & "'", where Code is the name of field containing the unique alphanumeric code you mention as wanting to check, and txtCode is the name of the text field on your form.

Now here, you are not interested in retrieving anything from the table, only checking if the value in question exists, so you need to wrap the DLookup function in a IsNull function to test that. Your end result should be something like:

If IsNull(Dlookup("1","tablename","Code = '" & txtcode.Text & "'")) then

    ' Code is NOT found, so may be used

Else

    ' Code was found, so it may NOT be used - set Cancel = True and exit

End If

The "1" is sort of a dummy parameter, since you don't want any actual value from the table. Mind the single quotes in the third parameter - they are necessary if you are looking up text values.

Put this code in the BeforeUpdate event of your text field and it should give you the results you need. Obviously, where I have comments, you will want to include what to do in the event that a bad value is entered - error mesage, flash something red on the form, or whatever you have decided is the proper method to warn the user about this condition.

 

[maxgold]

@Petr Danes : This works perfectly, thank you so much for the detailed explanation!

 

[Petr Danes]

@Petr Danes : This works perfectly, thank you so much for the detailed explanation!

Glad to help. I also had issues with DLookup back when I was first learning Access - I blindly mimicked examples and usually got it to work, but I didn't have a clear understanding of what I was doing. Some people don't like it for speed reasons, but it is very simple and very useful. I usually use it when starting something, and if it works, I leave it. Only if performance is an issue do I go to more complicated constructs.

 

[Pat Hartman]

There really is NO option between the Control's BeforeUpdate event and the Control's LostFocus event. The LostFocus event does not have a Cancel argument so you can't stop Access from saving the bad record. You can display as many error messages as you want. If you don't use the events intended for validation, and properly cancel the update when validation fails, you will save bad data.

Don't forget that you ALSO need to duplicate this particular validation in the Form's BeforeUpdate event. Why? Because if the user never moves the focus to the control in question, the control level events will not fire and the uniqueness text will NOT be performed. The Form's BeforeUpdate event is the flapper at the bottom of a funnel. You cannot save a record without passing through this event. It is your last opportunity to ensure the record is valid.

Having the field as required and defining a unique index is a good start but it isn't good enough if you want to give the user a friendly error message. Also, if the field is text and you have not changed the AllowZeroLengthString property from its default of Yes to No, then "blanks" will satisfy the Required attribute.

MS has waffled on the property settings for different data types and is currently on the wrong side of at least two of them. It defaults all numeric values to 0 which is wrong and it defaults the ZLS property to Yes which is also wrong in my opinion for the reason given above. In addition, a blank is a blank and you can't tell if the blank is null or ZLS just by looking at it so that also causes great confusion. Defaulting Long Integers which are most frequently used as foreign keys to 0 is wrong because 0 is almost certainly an invalid value and so it causes confusion when a user tries to save a record without a proper FK value. And defaulting currency and quantities to 0 is also wrong because 0 has meaning. It is far better to understand how to handle nulls. For example. In a table that holds grades, the average of (3,0,3) is 2 but the average of (3, null, 3) is 3 so the default impacts your calculation and it shouldn't. If the grade isn't entered yet, you don't want the average to be misrepresented.