A2003 R/T error 2001

[Al Camp]

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc... and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[MacDermott]

You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

 

[Al Camp]

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1 Conference at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and EventNo in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp

You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[MacDermott]

An index can contain more than one field.
If you create an index with both the ConferenceID and the EventNo fields,
making it unique means that while there can be multiple instances of
ConferenceID and multiple instances of EventNo, there cannot be multiple
instances of any particular combination of ConferenceID and EventNo.
As I understand it, this is the effect you want...

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1 Conference at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and EventNo in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp

You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[Al Camp]

MacDermott,
OK... I think I misunderstood your first post. Do you mean create
another field in the table set to no dupes, and then concatenate
ConferenceID and EventID to that field on the subform?
I think that's what you mean, and that sounds like it should work.
I'll try it, and get back shortly.
Thanks a lot...
Al Camp

An index can contain more than one field.
If you create an index with both the ConferenceID and the EventNo fields,
making it unique means that while there can be multiple instances of
ConferenceID and multiple instances of EventNo, there cannot be multiple
instances of any particular combination of ConferenceID and EventNo.
As I understand it, this is the effect you want...

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be
used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1 Conference
at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and EventNo
in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp

You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via
ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but
no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error
just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't
see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[Al Camp]

Thanks MacDermott...
I'm still going nuts with the error handling code for this function, but
your idea/concept works.
Thanks,
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

An index can contain more than one field.
If you create an index with both the ConferenceID and the EventNo fields,
making it unique means that while there can be multiple instances of
ConferenceID and multiple instances of EventNo, there cannot be multiple
instances of any particular combination of ConferenceID and EventNo.
As I understand it, this is the effect you want...

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be
used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1 Conference
at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and EventNo
in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp

You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via
ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but
no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error
just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't
see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[MacDermott]

There's no need to create another field -
In fact, I'd advise against that approach in your situation.
Open your table in Design View.
On the Menu Bar, click on View - Indexes.
In the left-most column, add a name for your new index, e.g.
ConferenceEvent.
In the middle column, select ConferenceID.
You can leave the last column at the default Ascending.
Now go to the next row.
Leave the left-most column blank - this means this row is part of the same
index.
In the middle column, select EventID.
Again, don't worry about the last column.
That's all you need!
You can remove your new field, and you should still get the effect you want.

HTH

MacDermott,
OK... I think I misunderstood your first post. Do you mean create
another field in the table set to no dupes, and then concatenate
ConferenceID and EventID to that field on the subform?
I think that's what you mean, and that sounds like it should work.
I'll try it, and get back shortly.
Thanks a lot...
Al Camp

An index can contain more than one field.
If you create an index with both the ConferenceID and the EventNo fields,
making it unique means that while there can be multiple instances of
ConferenceID and multiple instances of EventNo, there cannot be multiple
instances of any particular combination of ConferenceID and EventNo.
As I understand it, this is the effect you want...

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be
used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1 Conference
at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and EventNo
in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp


You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via
ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event... but
no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled the
Previous Operation." And... the Help button associated with the error
just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't
see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp

 

[Al Camp]

OK... I'll give it a go. Thankyou
Al Camp

There's no need to create another field -
In fact, I'd advise against that approach in your situation.
Open your table in Design View.
On the Menu Bar, click on View - Indexes.
In the left-most column, add a name for your new index, e.g.
ConferenceEvent.
In the middle column, select ConferenceID.
You can leave the last column at the default Ascending.
Now go to the next row.
Leave the left-most column blank - this means this row is part of the same
index.
In the middle column, select EventID.
Again, don't worry about the last column.
That's all you need!
You can remove your new field, and you should still get the effect you
want.

HTH

MacDermott,
OK... I think I misunderstood your first post. Do you mean create
another field in the table set to no dupes, and then concatenate
ConferenceID and EventID to that field on the subform?
I think that's what you mean, and that sounds like it should work.
I'll try it, and get back shortly.
Thanks a lot...
Al Camp

An index can contain more than one field.
If you create an index with both the ConferenceID and the EventNo fields,
making it unique means that while there can be multiple instances of
ConferenceID and multiple instances of EventNo, there cannot be
multiple
instances of any particular combination of ConferenceID and EventNo.
As I understand it, this is the effect you want...

MacDermott,
In the subform table tblScheduledEvents, ConferenceID is indexed
DupesOK
because of the multiple events associated with each conference.
If EventNo is indexed DupesOK that still won't catch Dupe EventNos
entered against a particular Conference.
If set to NoDupes, then Event "A-1" or "A-2" foe ex. could only be
used
against 1 Conference one time... and never
be used again. So... somehow, I have to trap dupes within 1
Conference
at
the EventNo BeforeUpdate or AfterUpdate event.

Ex. good subform data...
ConfID EventNo
14 A-1
14 B-1
14 B-2
15 A-1
15 A-2
16 A-1 etc...

Need to prevent dupes like...
14 A-1
14 A-2
14 A-2

My concern is why my code generates the R/T 2001 error. I can't see
anything wrong with the syntax, and using both ConferenceID and
EventNo
in
the Where argument should pick up the occaisional dupes (within just that
Conference).

Thanks for the reply,
Al Camp


You can define a unique index on the table underlying your subform.
It should consist of 2 fields - ConferenceID and EventNo.

Hmmm... I sent this out over an hour ago and it still hasn't shown up.
Pardon the resend...

I'm trying to prevent duplicate entries in a field of a
continuous
subform (frmScheduledEvents).

The Main form, frmConferences, defines a Conference via a
ConferenceID
(Long), and the
subform lists Events (the MANY) at that conference linked via
ConferenceID.
Each
event has an EventNo identifier (Text) such as A-12, B-6, D-11, etc...
and
the Main and Sub are linked via ConferenceID (w/RefIntegrity)

Conference 12 and Conference 13 can each have an A-1 Event...
but
no
"individual" conference can have two A-1 entries.

So, I can't set the EventNo field to NoDupes... I'll have to check
each
time an EventNo is entered in the subform.

OK, so I coded this up...

Private Sub EventNo_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[EventNo]", "tblScheduledEvents", "EventNo= " &
Me.EventNo & " and ConferenceID = " & Me.ConferenceID)) Then
Exit Sub
Else
MsgBox "Duplicate EventNo " & EventNo & vbCrLf & "Please correct
entry...", vbOKOnly, "Duplicate Value"
Cancel = True
EventNo.Undo
End If
End Sub

Problem: My code throws a "Run Time Error 2001" "You Cancelled
the
Previous Operation." And... the Help button associated with the error
just
brings up a blank Help screen!

EventNo has no other code,

Hope this isn't a "can't see the forest for the trees", but I can't
see
anything wrong with my code/syntax.
Thanks in advance,
Al Camp