Check bound textbox for duplicates (1 Viewer)

[Atthe]

Hi All

I have a bound text box (Assembly_NumberSpec) and field (AssemblyNumberSpec) in tblAssemblies.

What I need to happen is after the user has enter data into (Assembly_NumberSpec) to check for any duplicates and in (AssemblyNumberSpec) in tblAssemblies
If duplicates are found I need to clear the textbox and re set the focus and display error msg 'This Assembly/Spec already exists'.

I have tried this but not had any success

If DCount("AssemblyNumberSpec", "[tblAssemblies]", "Assembly_NumberSpec = me.assembly_NumberSpec") > 0 Then
MsgBox "This Assembly/Spec already exists."

Any advice appreciated

 

[pbaldy]

Try

If DCount("AssemblyNumberSpec", "[tblAssemblies]", "Assembly_NumberSpec = " & me.assembly_NumberSpec) > 0 Then

This presumes that data type of the spec field is numeric

 

[Atthe]

Try

If DCount("AssemblyNumberSpec", "[tblAssemblies]", "Assembly_NumberSpec = " & me.assembly_NumberSpec) > 0 Then

This presumes that data type of the spec field is numeric

Sorry forgot to specify the data field is short text

 

[pbaldy]

General: DLookup Usage Samples

 

[Atthe]

General: DLookup Usage Samples

Tried this but getting couldn't find the field referred to in your expression

If DLookup("AssemblyNumberSpec", "tblAssemblies", "AssemblyNumberSpec = '" & forms!frmToolGroupManager!Assembly_NumberSpec & "'") Then

Also should I be putting the code in the before update event?

 

[pbaldy]

You've switched from DCount() to DLookup(), and previously there was an underscore in the field name, so double check table/field/control spellings.

Yes, typically the before update event of a control or the form is the place to do validation.

 

[LarryE]

Tried this but getting couldn't find the field referred to in your expression

If DLookup("AssemblyNumberSpec", "tblAssemblies", "AssemblyNumberSpec = '" & forms!frmToolGroupManager!Assembly_NumberSpec & "'") Then

Also should I be putting the code in the before update event?

If you are looking for dup AssemblyNumberSpec in tblAssemblies then you should check dups when they are entered. So on the Before Update event:

If DCount("[AssemblyNumberSpec]", "tblAssemblies", "[AssemblyNumberSpec] = Forms![frmToolGroupManager]![Assembly_NumberSpec]") = 1 Then
    Msgbox "This is a duplicate Assembly Number"
    Me.AssemblyNumberSpec = Null
End If

 

[Pat Hartman]

Use the dCount() in the control's BeforeUpdate event so you can cancel the event. For some reason, Me.ControlName.Undo doesn't seem to be backing out the value in a control any more so you can use Me.ControlName = Me.ControlName.OldValue instead.