Condition to handle zero

[floid]

Want to warn someone after they exit my form not to leave a field value with a the
number 0 in it. My attempt below is not working.

In my before update event:
[tt]
Dim Response As String
If (Me![cust_id]) == 0 Then
Cancel = True
Response = MsgBox("Please fill in a cust id.&quot
Me![cust_id].SetFocus
End If
[/tt]

 

[jebry]

Hi!

You must be used to C++! Try using only one equal sign in the if statement.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JPeters]

How about you change it to run on AfterUpdate of the field..

So it looks like this

Private Sub Cust_Id_AFterUpdate()

If Me.cust_id = 0 Then
MsgBox("Please fill in a cust id.&quot
Me.cust_id.SetFocus
Else
'Set focus to next item in Tab Order? Or you can just remove the Else.
End If

End Sub ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[blarson0]

No, I think this sould be ran in the Before update. If you put it in the after update, it will add the record anyways. In Before update you can set cancel = true, as floid has, and it won't add the record.

HTH!

-Brad

 

[JPeters]

Oh, didn't realize those were his intentions. I apologize. Yeah... so do this.

Private Sub Cust_Id_BeforeUpdate()

If Me.cust_id = 0 Then
Cancel = True
MsgBox("Please fill in a cust id.&quot
Me.cust_id.SetFocus
Else
'Set focus to next item in Tab Order? Else optional
End If

End Sub

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[floid]

I now get the message to "Please fill in an cust id." but after that it exits out of my subform and doesnt go back to the OM ID like it should. Please advise if I have a problem with SetFocus or anything else I am missing.

[tt]
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Response As String
If Me.cust_id = 0 Then
Cancel = True
Response = MsgBox("Please fill in an cust id.&quot
Me.cust_id.SetFocus
End If
End Sub[/tt]

 

[JPeters]

Oh, this is in a subform? So when this is ran, it exits out of the subform and back to some focus in the main form? I've had this problem before, and it's a REAL pain in the rear to fix. You've basically got to tell it to GotoControl the control Id_Cust... You may have to dim subform as form, set subform = forms!MyForm, and then say GotoControl Forms.Cust_ID .. or something like that. You probably know how to code it just fine - but if you need me to rummage up the code that I use to accomplish this, please let me know.

Yeah, for some reason you can't set the focus back into a subform when you're working from a main form. Atleast it's given me troubles before.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[floid]

Sorry I meant to say this was a form.
Please advise how I can get it corrected.

 

[flaniganmj]

Instead of using SetFocus, try:

DoCmd.GotoControl "myControl"

Flan

 

[WildHare]

OR use the field's ON EXIT event - which, I believe, fires BEFORE the UPDATE event...

Sub CustID_Exit
if Me.CustID = 0 then
Msgbox "Please enter a valid customer id"
DoCmd.CancelEvent
End if

Canceling the EXIT event leaves you IN the customer ID Field....

Doesn't that look a bit easier?

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[floid]

I have tried many ways and still cant get this to work.
I tried this and not sure what "myControl" (DoCmd.GotoControl "myControl&quot represents??
Is it my field value?
DoCmd.GotoControl "Me.cust_id"
I tried this and it still didnt work.

I tried the onExit on the field and it only works if exiting from the field value not for exiting the whole form.

Any other suggestions???

 

[JPeters]

Floid,
It would be DoCmd.GoToControl "Cust_id"[/color. I've tried that on my form and it doesn't work. Also, Me.Cust_ID.SetFocus won't work for me. It sets the focus back to the CustID control for about a millisecond, then it kicks to the next thing in line in the tab order and stays there.
We can use the OnEnter event of the next item in the tab order to set the focus back to CustID for us.

So:
Private Sub Cust_ID_Exit(Cancel As Integer)
Dim RetVal As Integer
If Me.Cust_ID = "0" Then
Cancel = True
RetVal = MsgBox("Customer ID empty. Please fill in a Customer ID number.", vbOKOnly, "Empty Value&quot
If RetVal = 1 Then
'DoCmd.GoToControl "cust_id"
Me.Cust_ID.SetFocus 'These are what won't work
End If
End If
End Sub

Private Sub NextInTabOrder_Enter()
If Me.Cust_ID = "0" Then
Me.Cust_ID.SetFocus
End If
End Sub

I tried this, it works. You can remove the unworking code at your leasure. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------