Cancel/delete constrained new row created in continuous subform?

[Richard]

A shipment of material is received.
The shipment contains several items.
Each item is assigned an internal tracking number for auditing
purposes and further processed.
The tracking number is mandatory, unique and non-zero, but _cannot_ be
obtained or assigned automatically by Access (due to business rules
[perhaps part of the anti-productivity initiative ;) ]).

Table A is the receive table
Columns: AID, ReceiveDate, CompanyID

Table B is a item tracking number table
Columns: BID, AID, TrackingNumber, ItemTypeID, ItemCount

A frmReceiving shows the fields Date and Company, and has a continuous
subform (linked by AID) for showing/entering the tracking number of
each item of the shipment received.

Suppose the tracking number is _not_ available, but something was
entered for ItemTypeId or Count. At this point the record is
incomplete.
The data entrist has to cancel or delete the 'new item row having a
blank tracking number' and go ferret out the tracking number from the
control department before entering the type and count.

When the records edit pencil icon is right clicked, so as to be "Cut"
from the context menu, this dialog appears:
"The field 'frmReceiving.TrackingNumber' can't contain a null value
because the required property for this field is set to true. Enter a
value in this field."

To prevent spurious data, I don't want a dummy number entered, just so
the record can be deleted!

Q: What would be a good strategy for canceling the 'incomplete' new
item row ?

I would like to have a 'cancel' or 'delete' command icon in the
continuous subform, but only show it if the tracking number is
missing, or if the tracking number entered would fail the unique
constraint.

TIA,
Richard

[Bruce]

On Feb 1, 11:31 am, "Richard" <[e-mail]:

A shipment of material is received.
The shipment contains several items.
Each item is assigned an internal tracking number for auditing
purposes and further processed.
The tracking number is mandatory, unique and non-zero, but _cannot_ be
obtained or assigned automatically by Access (due to business rules
[perhaps part of the anti-productivity initiative ;) ]).

Table A is the receive table
Columns: AID, ReceiveDate, CompanyID

Table B is a item tracking number table
Columns: BID, AID, TrackingNumber, ItemTypeID, ItemCount

A frmReceiving shows the fields Date and Company, and has a continuous
subform (linked by AID) for showing/entering the tracking number of
each item of the shipment received.

Suppose the tracking number is _not_ available, but something was
entered for ItemTypeId or Count. At this point the record is
incomplete.
The data entrist has to cancel or delete the 'new item row having a
blank tracking number' and go ferret out the tracking number from the
control department before entering the type and count.

When the records edit pencil icon is right clicked, so as to be "Cut"
from the context menu, this dialog appears:
"The field 'frmReceiving.TrackingNumber' can't contain a null value
because the required property for this field is set to true. Enter a
value in this field."

To prevent spurious data, I don't want a dummy number entered, just so
the record can be deleted!

Q: What would be a good strategy for canceling the 'incomplete' new
item row ?

I would like to have a 'cancel' or 'delete' command icon in the
continuous subform, but only show it if the tracking number is
missing, or if the tracking number entered would fail the unique
constraint.

To force users to enter a tracking number you can check the tracking
number field in the beforeupdate event of your form. If there isn't
one you can cancel (Cancel = True) and give user the option (using a
message box perhaps) of entering something by setting focus back to
the tracking number field or to discard the partial record by using
the Undo method of the form. Alternately you could use the error
event of the form itself to trap for both missing and invalid tracking
number:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 3058
MsgBox "Missing tracking number"
txtProductID.SetFocus
Case 3022
MsgBox "Invalid tracking number"
txtProductID.SetFocus
Case Else
MsgBox "Some other error"
txtProductID.SetFocus
End Select

Response = acDataErrContinue

End Sub

Hope this helps,
Bruce