How can I go to other field

S

Scott

I have below event in a form and stuck how to move to other field. It comes
up asking me to end the operation at FieldB before moving to FieldA.


Private Sub txt1stFieldB_BeforeUpdate(Cancel As Integer)

If IsNull([FieldA]) Then

MsgBox "The FieldA is empty. Please fill that first!"
Cancel = True

Me.txtFieldA.SetFocus

End If

End Sub

Any idea to fix it?

Thanks,

Scott
 
W

Wayne-I-M

Hi Scott

I can not see your application so I don't understand what you are trying to
do so I may be wrong - It "may" be an idea to set the message on enter of
Field A (if field B is Null) and not Before Update of Field B. Give it a try
and see if it's OK.
Something like this

Private Sub YourFieldA_Enter()
If (IsNull(Forms!FormName!YourFieldB)) Then
MsgBox "Some Message", vbOKOnly, "Message Title"
End If
End Sub

Hope this helps
 
B

Biz Enhancer

Quite possibly you are getting a 2108 error which is:
"You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method."

Put this in the "on Change" event instead:
Private Sub FieldA_Change()
If IsNull([FieldA]) Then
If MsgBox("The FieldA is empty. Please fill that first!", vbRetryCancel,
"Missing data") = vbRetry Then
Me.FieldB = ""
Me.FieldA.SetFocus

Else
'Do something else
End If
End If
End Sub

That will get past the error and ensure that the user can't go any further
in the form until FieldA has that data.

Hope it helps,
Regards,
Nick.
 
S

Scott

Biz,

Your assumption on error message is correct. With your instructions, I have
worked out below statement. However, I believe the user will have further
request once the missing item has filled, the focus should move back to
FieldD. How can I do it without normal tab order?


Private Sub txtFieldD_Change()

If IsNull([FieldA]) Then
If MsgBox("The FieldA is empty. Please fill that first!",
vbRetryCancel, "Missing data") = vbRetry Then
Me.txtFieldD = ""
Me.txtFieldA.SetFocus

End If
End If

If Not IsNull([FieldB]) And IsNull([FieldC]) Then
If MsgBox("FieldC is empty. Please fill that first!", vbRetryCancel,
"Missing data") = vbRetry Then
Me.txtFieldD = ""
Me.txtFieldC.SetFocus
End If
End If

End Sub

Thanks,

Scott

Biz Enhancer said:
Quite possibly you are getting a 2108 error which is:
"You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method."

Put this in the "on Change" event instead:
Private Sub FieldA_Change()
If IsNull([FieldA]) Then
If MsgBox("The FieldA is empty. Please fill that first!", vbRetryCancel,
"Missing data") = vbRetry Then
Me.FieldB = ""
Me.FieldA.SetFocus

Else
'Do something else
End If
End If
End Sub

That will get past the error and ensure that the user can't go any further
in the form until FieldA has that data.

Hope it helps,
Regards,
Nick.

Scott said:
I have below event in a form and stuck how to move to other field. It
comes
up asking me to end the operation at FieldB before moving to FieldA.


Private Sub txt1stFieldB_BeforeUpdate(Cancel As Integer)

If IsNull([FieldA]) Then

MsgBox "The FieldA is empty. Please fill that first!"
Cancel = True

Me.txtFieldA.SetFocus

End If

End Sub

Any idea to fix it?

Thanks,

Scott
 
S

Scott

Wayne,

Thanks for your suggestion. I put the same statement as per my reply to Biz
except the MsgBox statement in On Enter event and it works as well. What is
major difference between On Enter and On Change events?

Scott

Wayne-I-M said:
Hi Scott

I can not see your application so I don't understand what you are trying
to
do so I may be wrong - It "may" be an idea to set the message on enter of
Field A (if field B is Null) and not Before Update of Field B. Give it a
try
and see if it's OK.
Something like this

Private Sub YourFieldA_Enter()
If (IsNull(Forms!FormName!YourFieldB)) Then
MsgBox "Some Message", vbOKOnly, "Message Title"
End If
End Sub

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Me dispiace se gli inglesi non e molto buoni



Scott said:
I have below event in a form and stuck how to move to other field. It
comes
up asking me to end the operation at FieldB before moving to FieldA.


Private Sub txt1stFieldB_BeforeUpdate(Cancel As Integer)

If IsNull([FieldA]) Then

MsgBox "The FieldA is empty. Please fill that first!"
Cancel = True

Me.txtFieldA.SetFocus

End If

End Sub

Any idea to fix it?

Thanks,

Scott
 
B

Biz Enhancer

I don't completely understand what the user would be trying to do from your
descriptions. However, what is stopping your from using the "Else" condition
on the "If" statement? e.g.:

If IsNull(FieldA) then
'Goback and fill in data
Else
Me.FieldD.setfocus
End if

Now, of course this won't work on the "On Change" event as you will never
get to fill in FieldC before it throws you out to FieldD given that FieldA is
not null. However, that would work on a "Lost Focus" event.
Alternatively, keep the VBA in the "On Change" event and write a more
specific statement in the "Else" condition possibly using another conditional
"If" statement to achieve your second goal.

e.g.

Sub FieldC_OnChange()

If IsNull(FieldA) then
'Goback and fill in data
Else
If IsNull(FieldA) = False then
'maybe check to see if something else IsNull
Me.FieldD.setfocus
End if
End if
End Sub
Scott said:
Biz,

Your assumption on error message is correct. With your instructions, I have
worked out below statement. However, I believe the user will have further
request once the missing item has filled, the focus should move back to
FieldD. How can I do it without normal tab order?


Private Sub txtFieldD_Change()

If IsNull([FieldA]) Then
If MsgBox("The FieldA is empty. Please fill that first!",
vbRetryCancel, "Missing data") = vbRetry Then
Me.txtFieldD = ""
Me.txtFieldA.SetFocus

End If
End If

If Not IsNull([FieldB]) And IsNull([FieldC]) Then
If MsgBox("FieldC is empty. Please fill that first!", vbRetryCancel,
"Missing data") = vbRetry Then
Me.txtFieldD = ""
Me.txtFieldC.SetFocus
End If
End If

End Sub

Thanks,

Scott

Biz Enhancer said:
Quite possibly you are getting a 2108 error which is:
"You must save the field before you execute the GoToControl action, the
GoToControl method, or the SetFocus method."

Put this in the "on Change" event instead:
Private Sub FieldA_Change()
If IsNull([FieldA]) Then
If MsgBox("The FieldA is empty. Please fill that first!", vbRetryCancel,
"Missing data") = vbRetry Then
Me.FieldB = ""
Me.FieldA.SetFocus

Else
'Do something else
End If
End If
End Sub

That will get past the error and ensure that the user can't go any further
in the form until FieldA has that data.

Hope it helps,
Regards,
Nick.

Scott said:
I have below event in a form and stuck how to move to other field. It
comes
up asking me to end the operation at FieldB before moving to FieldA.


Private Sub txt1stFieldB_BeforeUpdate(Cancel As Integer)

If IsNull([FieldA]) Then

MsgBox "The FieldA is empty. Please fill that first!"
Cancel = True

Me.txtFieldA.SetFocus

End If

End Sub

Any idea to fix it?

Thanks,

Scott
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top