Error 3201 Trapping

[Guest]

I have a datasheet view sub form with a dropdown for inventory transaction
type and a dropdown for inventory item. Both the transaction type ID and
Inventory Item ID are required in the tblInvTransactions. The problem I am
having is capturing the error that occurs when the user begins to enter an
inventory transaction by selecting an inventory transaction type and then
attempts to click off the record being created. Note: They cannot select an
Inventory Item without first having selected an inventory transaction type,
it’s disabled.

I've tried using the On Error event but I cannot figure out how to stop the
“You cannot add or change a record because a related record is required in
table “tblStoresInventoryâ€. This is the master item table and there is a
pk/fk to tblInvTransactions. What I want to happen is have the insert
transaction canceled, maybe a custom message to the user, and then control
passed to where ever they clicked.

Any help would be appreciated.

Thanks,

Jim

 

[BruceM]

Check to be sure the subform control's Link Parent Field and Link Child
Field properties show the linking fields. If they do not, click one of
those properties on the property sheet and click the three dots to the
right. It may well suggest the correct choice; if not, change it as needed.
To view the subform control's properties, open the form in design view,
click the subform, and click View > Properties.
This assumes that you have the correct relationship set up between the main
form's record source and the subform's record source. Is there a
one-to-many relationship?
Trapping the error will do you no good. Even if it works, you will end up
with a subform record that is not related to any parent form record.
However, if you are trapping an error you need to do so in the event that
generates the error. In your case it would probably be the form's Before
Update event, which runs whenever you save the record (by navigating away
from it, for instance).

 

[Guest]

Bruce,

Neither of the fields I mentioned are the linking field. The forms work
fine so long as the user completes the data entry in the subform before
moving on. What I’m trying to handle is the situation where the user starts
a transaction and then decides to do something else.

It looks like the problem is that the record is created in the transaction
table when the user selects the Inventory Transaction Type and since the
inventory transaction type ID and the Inventory Item ID are required in the
transaction table the user cannot continue without selecting an inventory
item or canceling the transaction by hitting escape.

What I want to do is roll back the incomplete data entry without the user
getting the system error message. I want it to work as if the user hit the
escape key before clicking off of the subform.

I appreciate your help and the before update suggestion almost got me to
where I want to be, but I cannot figure out how to test for the error
condition in the before update event.

Thanks in advance,

Jim

 

[BruceM]

Try Me.Undo. It accomplishes the same thing as using the Esc key. In the
form's Before Update event you could do something like:

Dim strMsg as string, strTitle as String
strMsg = "An inventory item is needed. Click Cancel to void this record."
strTitle = "Inventory selection needed"

If Not IsNull(Me.txtTransType) And IsNull (txtInvItem) Then
If MsgBox(strMsg,vbOKCancel, strTitle) = vbCancel Then
Me.Undo
Cancel = True
Else
Me.txtInvItem.SetFocus
End If
End If

txtTransType and txtInvItem are text boxes bound to the fields you
mentioned. As for trapping the error, I believe the On Error event causes
something to happen *after* an error has occurred. If you want to trap an
error you need to do so in the event that causes the error. If a field that
is required at the table level is left blank, you will get an unfriendly and
perhaps puzzling standard error message when you try to save the record (by
navigating away from it, for instance). To customize the error message you
can use the form's Before Update event (you can think of it as the Before
Save event). Code in that event will run before the record is saved.
Cancel = True cancels the update (save).

 

[Guest]

Bruce,

Thanks for the help, it's working!

Thanks,

Jim