duplicate entry flag to user

[markjkubicki]

I want something more than the usual crypic message to a user that the entry just made (into a no dups, primary key field) is a duplicate entry...
so, I added this code... but it bounces me out...

Private Sub txtType_BeforeUpdate(Cancel As Integer)
If Len(Nz(Me.txtType, "")) < 1 Then 'check for an entry
If DCount("[type]", "tbeFixtureTypeDetails", "[type]= '" & Me.Type & "'") > 0 Then 'check for an existing match
vmsgtxt = "This fixture type is already being used; please try again"
vmsgtitle = "DUPLICATE FIXTURE TYPE"
Response = MsgBox(vmsgtxt, vbCritical + vbRetryCancel, vmsgtitle)
vType = Me.txtType & "-DUP!"

*** this next line is the one that bounces me


Me.txtType = vType
End If
End If
...

run-time -2147352567 (80020009)
function set to the before... is preventing... saving the data...

any thoughts wouldbe greatly appreciated in advance,
Mark

[June7]

What happens if instead of that line, this:

Cancel = True

[markjkubicki]

that was good
but i'm still looking for a way to re-assign a value, and that's where the error occurs
the user will now realize that the entry is a duplicate, but re-assigning a value (from within the form) is a guessing game;

I'd like the opportunity to replace the value with a placeholder that the user can go back and edit at a future time.. i.e. after they've exited the form and reviewed the completet set of records (the table is seldom over 50 records, so it's not such a big deal to do... and I have some tools in place that make it fairly easy) hence the code:

vType = Me.txtType & "-DUP!"
Me.txtType = vType

the line: Me.txtType = ... is what's fighting me

[June7]

txtType is textbox bound to a text field? Is '-DUP' a suffix that is saved as part of the input value?

None of my BeforeUpdate procedures include setting the value of the control. Don't think you can. Try setting the textbox in another event, such as GotFocus:

If IsNull(txtType) Then txtType = "-DUP"