GoToControl in BeforeUpdate

[Matt Lockamy]

I'm using the BeforeUpdate event to validate information based on a
combination of fields. There is a REASON_CODE field where the user specifies
the reason he is returning an item, an ITEM_ACTUAL where the user specifies
which item he was actually sent, and a quantity field where the user
specifies the QTY he is returning.

If the user specifies the code for Wrong Item, I want to force him to enter
the item ID for the item he was actually sent. I couldn't figure out which
event to use on that field, so I used the BeforeUpdate event on the QTY field
(which is the last field on the subform). That way, if a user tries to enter
a quantity and the code is "WI", then he will be forced to enter an item in
the ITEM_ACTUAL field.

Here is my code:

Private Sub QTY_BeforeUpdate(Cancel As Integer)

Dim strCode As String
Dim strItemSent As String

strCode = ITEM_REASON_CODE.Value
strItemSent = Nz(ITEM_ACTUAL.Value)

If strCode = "WI" And strItemSent = "" Then
Cancel = True
MsgBox "If Reason Code WI is selected, you must specify which Item was
actually sent.", vbOKOnly
Me.QTY.Undo
DoCmd.GoToControl ITEM_ACTUAL.Name

End If

End Sub

The problem is that when I try to use the GoToControl method it tells me I
have to save the record, which is exactly what I don't want to do. In the
form I can just tab to another field without updating the record, why can't I
do it in VBA? I think it would be helpful to change the focus to the field
where the problem actually is.

Your help is very much appreciated.

 

[BruceM]

The text box may be bound to a field, but the text box (the control) bound
to that field is where the Before Update event occurs.

You refer to the "last field", but what if the user clicks the fields out of
order, or doesn't go to the "last" control at all? Could happen.

The form's Before Update may be a better choice for validation. It may be
something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strCode As String
Dim strItemSent As String

strCode = Me.ITEM_REASON_CODE
strItemSent = Nz(Me.ITEM_ACTUAL,"")

If strCode = "WI" And strItemSent = "" Then
MsgBox "If Reason Code WI is selected, you must specify " & _
"which Item was actually sent.", vbOKOnly
Me.ITEM_ACTUAL.SetFocus
Cancel = True
End If

End Sub

You could put code in the text box Before Update event, but you also need to
be prepared for the user not filling in the fields completely and in order.
Note that the Value property is redundant since it is the default property.
No harm to using it, but there usually is no need either.
Help about GoToControl show the use of the Name property (in Access 2003
Help, anyhow), but it is instead of, not in addition to the name of the
control. Using the Name property in this way:
DoCmd.GoToControl ITEM_ACTUAL.Name
will almost surely not work.

I think it is better to use the Me prefix (as I did with my code example) to
refer to the current form (the one in which the code is running). When the
Me prefix is followed by a dot, anything following is a property of the
form. This includes controls on the form and fields in the record source.
An advantage to doing it this way is that the Intellisense feature (I think
that's what it's called) gives you a list from which to choose as soon as
you type Me and a dot.

 

[BruceM]

That first sentence may be confusing. My point is that the control, not the
field, has the event procedure.

 

[Matt Lockamy]

Thanks for the response.

I did try that code but still got the error:

2108 - you must save the field before you execute the GoToControl action . . .

Error

 

[BruceM]

The code I suggested did not include GoToControl, but rather SetFocus. In
any case, if code is giving you trouble you should post it.