Can this error be trapped in BeforeUpdate

B

Bob Richardson

Assume we have an order entry situation...Customer...Order number...Item
number

I want it to be an error situation when the same ITEM number appears twice
(or more) on the same Order. Items are entered in a Dataview form.
 
T

tina

so you have fours tables? as in, tblCustomers, tblOrders, tblItems,
tblOrderItems. with relationships as

tblCustomers 1:n tblOrders
tblOrders 1:n tblOrderItems
tblItems 1:n tblOrderItems

if the above is correct, then in tblOrderItems you have a foreign key from
tblOrders, and a foreign key from tblItems. you can open tblOrderItems in
design view, and add a two-field index (on the menu bar, click View |
Indexes) with the index's Unique property set to Yes (and the Primary index
property remaining at the default value of No). make sure that the index
property on each of the foreign key fields in the table is *not* set to "No
Duplicates".

hth
 
T

tina

(sorry, i didn't quite complete the train of thought.)

the unique index in the table will prevent a duplicate *combination* of the
key values from the Orders and Items tables from being saved. during data
entry in a form, an attempt to save a duplicate combination of values in a
record will cause a trappable error to occur.

hth
 
B

Bob Richardson

Yes, that will flag the error. What I want to do is intercept the cryptic
"duplicate key" Access error message, and print my own error message.
 
T

tina

use the form's Error event, as

If DataErr = x Then
Response = acDataErrContinue
Msgbox "This is an error."
End If

replace x with the actual error code.

hth
 
Top