Currency validation rules In Forms HELP (ASAP)

[kekewong]

now i got 3 jobs that is engineer,trainee,cleck . For engineer, the salary must >=30000 and <=40000. For trainee, the salary must >=20000 and <=30000. For engineer, the salary must >=0 and <=20000. And i am trying to set the validation rules for it and it seems it cant work, can any one help me out? I just need someone to guild me for the first data validation for Job "engineer".
The code is at below:
-------------------------------------------------------------------
Private Sub Salary_AfterUpdate()
If (Me.job="engineer" And (Me.Salary >= 30000 And Me.Salary <= 40000)) Then
'allow the input
Else
Msgbox("your salary key in wrongly")
'Request to key in again ( I dont know want to use what code)
End If
End Sub
-----------------------------------------------------------------------

Can anyone Correct me?

 

[CyberLynx]

You have the right idea...just the wrong event. Place your code into the BeforeUpdate event. If the condition fails....then Cancel the event. and force the User to re-enter the proper data.

Think about it. The BeforeUpdate event fires before the data is saved to table and the AfterUpdate event is fired after the data is saved to Table. You want to work with this entry before it is saved to Table in case there is something wrong with it. This is why the BeforeUpdate event contains the Cancel property. Just like is shown below:

Private Sub Salary_BeforeUpdate([B][COLOR="Blue"]Cancel[/COLOR][/B] As Integer)
   If (Me.job="engineer" And (Me.Salary >= 30000 And Me.Salary <= 40000)) Then
      Exit Sub
   Else
      [COLOR="Blue"][B]Cancel = True[/B][/COLOR]
      Msgbox "The Salary you entered is incorrect. Please try again."
      [COLOR="Blue"]Me.Salary.SetFocus[/COLOR]
   End If
End Sub

.

 

[RuralGuy]

To keep the record straight, Cancel = True will hold the focus in the current control so there is no need to attempt to SetFocus to the current control.

 

[kekewong]

Private Sub Salary_BeforeUpdate(Cancel As Integer)
If (Me.job="engineer" And (Me.Salary >= 30000 And Me.Salary <= 40000)) Then
Exit Sub
Else
Cancel = True
Msgbox "The Salary you entered is incorrect. Please try again."
Me.Salary.SetFocus
End If
End Sub

-----------------------------------------------------------------------------

The code seems right,but it wont works ... i dont know why, got any Idea? It cant validate

 

[kekewong]

ANy body can help me ?

 

[Rabbie]

What actually happens with this code. We need it little more info than "It desn't work". Please give us any error messages you get and describe what happens.

 

[kekewong]

no error message come out , just no data validation occur , that means i can key in the salary with below 30000 while job is "engineer".

 

[Rabbie]

Can you post a cutdown copy of your DB

 

[kekewong]

i just key in this code......

Private Sub Salary_BeforeUpdate(Cancel As Integer)
If (Me.job="engineer" And (Me.Salary >= 30000 And Me.Salary <= 40000)) Then
Exit Sub
Else
Cancel = True
Msgbox "The Salary you entered is incorrect. Please try again."
Me.Salary.SetFocus
End If
End Sub

or like that..... got another way to set this kind of validation?

 

[RuralGuy]

Put a breakpoint at the If line and hover over the variables and see what they contain.

 

[kekewong]

what is a breakpoint?how to add it? example?

 

[missinglinq]

Is engineer spelled correctly in Me.Job?

Is Me.Salary actually defined as Currency datatype rather than Text?

 

[kekewong]

ya.. my salary is defined as currency in the Salary's Table . ya the spelling is correct @.@

 

[RuralGuy]

what is a breakpoint?how to add it? example?

Open the code window and click outside the margin and a breakpoint will be set there. Then run the form.

 

[boblarson]

Open the code window and click outside the margin and a breakpoint will be set there. Then run the form.

And here's a visual of how to set a breakpoint if you need one:
http://www.btabdevelopment.com/main...kpointtostepthroughcode/tabid/58/Default.aspx

 

[RuralGuy]

Thanks Bob. I keep forgetting you have picture examples. I'll try and start looking there for an example first.

 

[boblarson]

Thanks Bob. I keep forgetting you have picture examples. I'll try and start looking there for an example first.

Apparently the pics are generating some good interest. In fact, I saw a blog by Stephen Lebans with a screenshot from my site and the screenshot linked to that "quick tutorial" on my site.

 

[RuralGuy]

That is great. I knew it would be a good source of help.

 

[kekewong]

i have try already, and i already set the breake point . the validation not working (i swear) @.@ but wait. i have try this:
--------------------------------------------------------------
Private Sub Salary_BeforeUpdate(Cancel As Integer)
If (Me.Job="engineer" ) Then
vbInformation, "Data Validation"
Me.QueryPhone.SetFocus
Cancel = True
ElseIf
End Sub

-------------------------------------------------------
Then in the Properties of the Salary,i Set the "data validation" became ">=30000 And <=40000 " And it works . But i think this code awhile, it is only for "engineer" only , got another way to set it to became each job got reach salary validation .

 

[boblarson]

Try this:

Private Sub Salary_BeforeUpdate(Cancel As Integer)
Dim blnError As Boolean
Dim strJob As String

   Select Case Me.Job
	Case "Engineer"
		If Me.Salary Not Between 30000 And 40000 Then
            	   blnError = True
                   strJob = "Engineer"
                End If

	Case "Trainee"
		If Me.Salary Not Between 20000 And 30000 Then
            	   blnError = True
                   strJob = "Trainee"
                End If

	Case "Clerk"
		If Me.Salary Not Between 0 And 20000 Then
            	   blnError = True
                   strJob = "Clerk"
                End If
   End Select

   If blnError Then 
      Cancel = True
      MsgBox "You have entered a salary for the " & strJob & vbCrLf & _
"which is out of the appropriate range.  Please try again!, vbExclamation, "Salary Entry Error"
      Exit Sub
   End If

End Sub