Notifying user of a dup ID on a form

[warpmail]

I have created a table called Master. It contains ID Number (text field). The ID Number is indexed, and duplicates are not allowed. I've also created a form called Registration based on this table. The problem is that if a duplicate ID is entered on the form, the error message appears only after the form is saved and then closed! This means that the entire form is filled out before the user knows that he wasted data entry time. I have tried to use the DLookup function as a validation rule under properties for this field, but without success. Is there a way to let the user know that a duplicate ID has been entered as soon as he advances to the next field within the form?

Thanks

 

[randy700]

How about putting the dlookup in the OnExit event of the text box? Something like...

Private Sub txtID_Exit(Cancel as Integer)
If Not IsNull(DLookup("IDNumber","tblMaster","IDNumber = '" & me.txtID & "'")) Then
Put the message box here.
Put additional code here if needed.
End If
End Sub


Randy

 

[mp9]

Try experimenting with your DLookup approach but using it in the BeforeUpdate or AfterUpdate events of the field on your form.

Hope this helps.

http://www22.brinkster.com/accessory/

 

[autex]

I would do something kinda like this.

dim chk as string
dim stlinkcriteria as string
dim rs as dao.recordset

set rs=me.recordsetclone

chk=me.idnumber field name.value

stlinkriteria="[idnumber field name]=" & "'" & chk & "'"

if dcount("idnumber field name", "table id is in", stlinkcriteria)>0 then
msgbox "you screwed up"
end if

set rs=nothing

 

[autex]

oh sorry put that code in before update of the id number field

 

[mp9]

On reflection, DCount will be faster than DLookup, plus you can just check for your DCount being greater than 0 or not, rather than having to invoke IsNull type functions.

As an aside, if you do use DCount you'll find it's slightly faster just to count everything rather than a specific field.

In other words, if your table is called Master, your table field is called [ID Number] and the form text box is called ID, your code to check would be something like this in the ID's BeforeUpdate event:

Private Sub ID_BeforeUpdate(Cancel As Integer)
    If DCount("*", "Master", "[ID Number]=" & ID) > 0 Then
        MsgBox "Duplicate value.", vbExclamation
        Cancel = True
        ID.Undo
    End If
End Sub

If the ID Number can be text as well as digits you need to concatenate in some quotes, i.e.

...
    If DCount("*", "Master", "[ID Number]='" & ID & "'") > 0 Then
...

Hope this helps some more!

http://www22.brinkster.com/accessory/

 

[warpmail]

I have the following event procedure code for a form:

Private Sub ID_Number_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master", "[ID Number]='" & [ID Number] & "'") > 0 Then
MsgBox "Duplicate ID - enter another ID or exit form", vbExclamation
Cancel = True
[ID Number].Undo
End If
End Sub

When a duplicate ID is entered the first message 'Duplicate ID - enter another ID or exit form', appears. When the user clicks OK on this box another dialog box appears with the following:
'The value in the field record violates the validation rule for the record or field.'
I would like to prevent this second dialog box from popping up. The ID Number is a text field and is set up as indexed, no duplicates in the table.