Getting Duplicate value in Index to "fire"

  • Thread starter Thread starter Al Camp
  • Start date Start date
A

Al Camp

Two fields, ConferenceID(key field) and EventNo. ConferenceID and EventNo
each can contain Dupes, but not the combination of the two.

Good data...
ConfID EventNo
14 A-1
14 B-1
15 A-1

Bad data...
14 A-2
14 A-2 'dupe within 14
15 B-5

MacDermott clued me in to View/Indexing in table design view (thanks
MacDermott!), so I set it up this way
NoDupesInEventNo ConferenceID Asc
EventNo Asc

Question... I want the dupe checking to occur right after the EventNo is
entered... not when the record is Updated normally. I tried Refresh and
Requery on the AfterUpdate event of eventNo, but the dupe error still
doesn't "fire".
It does work fine upon leaving/updating the record.

Couldn't figure out what to search for in Google groups...

Thanks in advance
Al Camp
 
Aircode, assuming ConfID is a Number type field, and EventNo is a Text type
field:

Private Sub EventNo_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
If IsNull(Me.ConfID) Or IsNull(Me.EventNo) Or _
(Me.ConfID = Me.ConfID.OldValue And _
Me.EventNo = Me.EventNo.OldValue) Then
'do nothing
Else
strWhere = "(ConfID = " & Me.ConfID & _
") AND (EventNo = """ & Me.EventNo & """)"
varResult = DLookup("ConfID", "MyTable", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate!"
End If
End If
End Sub

Private Sub ConfID_AfterUpdate()
Call EventNo_AfterUpdate
End Sub
 
Allen,
Hmmm.. I'm not sure I understand... I was using a Dlookup to determine
if the EventNo entry violated the ConferenceID/EventNo duplicates... but...
with my new table indexing I shouldn't need to do that any more.

My table indexing now properly traps duplicate EventNo (within the same
ConferenceID)... and the default Access Error returned is just fine... ("The
value you entered.... duplicate... index... yada yada)

**I just want to post the standard Access dupe index error immediately after
EventNo is entered... instead of... as I exit the record. EventNo
AfterUpdate Refresh/Requery/Recalc don't force the error to "fire." It
still fires only when leaving the record.

As always Allen... thanks for your help...
Al Camp

PS: ConferenceID is a key/linked/refintegrity field, so I don't enter
that value in the subform... the relationship handles that.)
 
Not sure I've understood you correctly.

The suggested code should catch the error immediately on entry, not after
all fields have been entered and the record is to be saved.

If you just want the default record, and there are no other required fields
or validation that needs to take place, I guess you could just put something
like this in the AfterUpdate event of the field to force it to save
instantly, and hence give you the default error if the save fails:
Me.Dirty = False
 
Allen,
Thanks for hanging in on this... and thanks for your patience.

Using my table indexing, as soon as I enter a duplicate EventNo, Access
knows that it's a violation, but doesn't report that error until I leave the
record.

You wrote...
something like this in the AfterUpdate event of the field to force it to
save instantly, and hence give you the default error if the save fails:
Me.Dirty=False
Click to expand...

That's exactly what I want to do, but... Me.Dirty = False in the EventNo
AfterUpdate still doesn't force the error to post immediately.
How can I force that record to Update?
Refresh, Requery, and Recalc don't work... (I'm flabbergasted that
Refresh doesn't cause the error post)
Some form of Update maybe?
When any record has been filled in, and that record is exited... the
table is updated. That's what I have to accomplish programatically.

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Allen,
Also tried... DoCmd.RunCommand accmdSaveRecord.

But... I put a Refresh in the OnEnter for the next field after EventNo.
It forces the error to post!
I think the error, posted in memory during the EventNo/AfterUpdate, is
preventing any form of Update within that field.
That "dupe index" error (R/T 3022) has no "Continue" option in the error
dialog box. Access probably won't allow a Refresh, or Save while this error
is in memory, and while still in that field.

Thanks for your help. I'm still going to try to find a way around this.
Using Access's "native" ability to trap the dupe error via indexing
violations seems to be the way to go. But... I will also save your Dlookup
code!
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Are you suggesting that the AfterUpdate event of the control is not firing?
If it fires, it should trigger the error. (Well, actually, you might see
another error message, such as "Cannot set property" if Access is blocked
from setting Dirty to False.) Try adding:
Debug.Print "EventNo_AfterUpdate at " & Now()
to the top of the procedure, set a break point (F9), and trace what happens
to the event when you enter something.

Of course, you won't get the event if the user is not typing into EventNo,
e.g. if you assign a value programmatically or by DefaultValue.
 
Allen,
The "AfterUpdate" event is firing OK (I could have put a Beep in there
and it would work) , but... no matter what I try, the error refuses to
display... until the record is exited. (EventNo is a manually entered
field.)

I've come up with a solution though...
I made EventNo a combobox field, and based the selections on a table of
"pre-set" EventNos (A1 thru A30, B1 thru B30, etc) The combo query uses a
cartesian realtionship (a Totals query) to compare against the EventNo
values already used in the subform.
Show all the EventNos in the preset table that do not have a match in the
EventNos of the subform.

After the user enters an EventNo, it disappears as choice from the combo
list. Now I can use the NotInList property of cboEventNo to catch
dupes...just within that unique ConferenceID.

I'm still stunned that the EventNo AfterUpdate Refresh doesn't force that
error to post! But, after a while, I just got the feeling that I was
fighting against Access all the way.

Hopefully this new tack will work out.
Thanks for all your help,
Al Camp
 
Okay, you have a workaround, so you're probably okay.

If the event is firing, not generating any error at all, and not saving,
there has to be a corruption of the code or the form or the index. Time for
a decompile or rebuild.
 
Back
Top