Can this error be trapped in BeforeUpdate

[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.

 

[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

 

[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

 

[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.

 

[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

 

[Bob Richardson]

perfect...the BeforeUpdate wasn't the place, but ONError was.