Help me out

  • Thread starter Thread starter Ashish Kanoongo
  • Start date Start date
A

Ashish Kanoongo

I have following textboxes form

Tier1 Tier2 Tier3 (Labels)
tier1max tier2max tier3max
Fee1% Fee2% Fee3%

Following is my requirement:

In Tier Textboxes, amounts must ascend in value. Tier 1 cannot be 1,000,000 and Tier 2 500,000, for example. If a tier max amount is filled in, its associated Fee% must be filled in. Tiers must be filled in consecutively; ie. you would not have tier 1 filled in and tier 3 filled in.

Secondly suppose if you have entered value first time consecutively succesfully in (tier1max, tier2max, tier3max) 1,2,3, thne you edited record again as 4,2,3. My logic will fail in this case.

Let me know how do I this? I tried to validate on exit event, but did not able to handle it perfectly. COde are as follows

Private Sub txtTier1Max_Exit(Cancel As Integer)
If TxtTier1Max > 0 Then
If TxtTier2Max > 0 Then
If TxtTier1Max > TxtTier2Max Then
Me.TxtTier1Max.SetFocus
End If
End If
End If
If TxtTier1Max = 0 Then
txtTier1FeePerAnnum = 0
txtTier1FeePerAnnum.Enabled = False
Else
txtTier1FeePerAnnum.Enabled = True
End If
End Sub
 
The "logic" appears to be ok so I set up a form to try it. The problem appears to be in the SetFocus command. It is being run, but the focus is moving anyway. However, the OnExit event has a built-in way to handle this and it does work. Change

Me.TxtTier1Max.SetFocus
to
Cancel = True

The one "flaw" I do see is that you are forcing the user to change the textboxes in a certain order. If a user wanted to edit a record that had the values 1, 3, 5 and changed TxtTier3Max first to 3, intending to change TxtTier2Max to 2, they wouldn't be able to. They would have to change TxtTier2Max first. I wouldn't actually say this is a flaw, as long as the user knows they have to be changed in order.

--
Wayne Morgan
Microsoft Access MVP


I have following textboxes form

Tier1 Tier2 Tier3 (Labels)
tier1max tier2max tier3max
Fee1% Fee2% Fee3%

Following is my requirement:

In Tier Textboxes, amounts must ascend in value. Tier 1 cannot be 1,000,000 and Tier 2 500,000, for example. If a tier max amount is filled in, its associated Fee% must be filled in. Tiers must be filled in consecutively; ie. you would not have tier 1 filled in and tier 3 filled in.

Secondly suppose if you have entered value first time consecutively succesfully in (tier1max, tier2max, tier3max) 1,2,3, thne you edited record again as 4,2,3. My logic will fail in this case.

Let me know how do I this? I tried to validate on exit event, but did not able to handle it perfectly. COde are as follows

Private Sub txtTier1Max_Exit(Cancel As Integer)
If TxtTier1Max > 0 Then
If TxtTier2Max > 0 Then
If TxtTier1Max > TxtTier2Max Then
Me.TxtTier1Max.SetFocus
End If
End If
End If
If TxtTier1Max = 0 Then
txtTier1FeePerAnnum = 0
txtTier1FeePerAnnum.Enabled = False
Else
txtTier1FeePerAnnum.Enabled = True
End If
End Sub
 
Wayne

Thanks for the help, it work great. I have faced another issue, once user entered this information successfully, and next time he edit this record and put 5,4,3 (earlier it was 3,4,5) in three textbox, this logic will fail, so let me know how do I handle this situation?

Ashish
The "logic" appears to be ok so I set up a form to try it. The problem appears to be in the SetFocus command. It is being run, but the focus is moving anyway. However, the OnExit event has a built-in way to handle this and it does work. Change

Me.TxtTier1Max.SetFocus
to
Cancel = True

The one "flaw" I do see is that you are forcing the user to change the textboxes in a certain order. If a user wanted to edit a record that had the values 1, 3, 5 and changed TxtTier3Max first to 3, intending to change TxtTier2Max to 2, they wouldn't be able to. They would have to change TxtTier2Max first. I wouldn't actually say this is a flaw, as long as the user knows they have to be changed in order.

--
Wayne Morgan
Microsoft Access MVP


I have following textboxes form

Tier1 Tier2 Tier3 (Labels)
tier1max tier2max tier3max
Fee1% Fee2% Fee3%

Following is my requirement:

In Tier Textboxes, amounts must ascend in value. Tier 1 cannot be 1,000,000 and Tier 2 500,000, for example. If a tier max amount is filled in, its associated Fee% must be filled in. Tiers must be filled in consecutively; ie. you would not have tier 1 filled in and tier 3 filled in.

Secondly suppose if you have entered value first time consecutively succesfully in (tier1max, tier2max, tier3max) 1,2,3, thne you edited record again as 4,2,3. My logic will fail in this case.

Let me know how do I this? I tried to validate on exit event, but did not able to handle it perfectly. COde are as follows

Private Sub txtTier1Max_Exit(Cancel As Integer)
If TxtTier1Max > 0 Then
If TxtTier2Max > 0 Then
If TxtTier1Max > TxtTier2Max Then
Me.TxtTier1Max.SetFocus
End If
End If
End If
If TxtTier1Max = 0 Then
txtTier1FeePerAnnum = 0
txtTier1FeePerAnnum.Enabled = False
Else
txtTier1FeePerAnnum.Enabled = True
End If
End Sub
 
That depends on how it was done. If 3,4,5 was in there and the 3 was replace with the 5 then it wouldn't fail, 5>3 and the Cancel would take effect. However, if all 3 values were erased then 5,4,3 was entered, then when the 5 was entered the 2nd box wouldn't be >0, so the test would past. Next, you put in the 4 and the test from the 1st box isn't run again. You will actually have to make this test in the 2nd textbox. You could do the same thing, but test "backwards" also.

The other thing to watch out for is, can 1 and 3 be filled in if 2 isn't. Currently, I don't see a check for this. Also, you are checking for >0 but you also mention "not filled in". Can the value be Null? If so, how do you want to handle a Null value? Should Null be handled the same as zero or differently?

--
Wayne Morgan
Microsoft Access MVP


Wayne

Thanks for the help, it work great. I have faced another issue, once user entered this information successfully, and next time he edit this record and put 5,4,3 (earlier it was 3,4,5) in three textbox, this logic will fail, so let me know how do I handle this situation?
 
I tried followings it works great, but not focus on same text box where he generates error. He is checkingproperly, but not able to set focus on same text box, it focus on first textbox of the form every time.

*-------------------
Dim a(5) As Double
Dim flag As Boolean
flag = True
a(1) = Tier1Max
a(2) = Tier2Max
a(3) = Tier3Max
a(4) = Tier4Max
a(5) = Tier5Max

For i = 1 To 5
For j = i To 5
If a(j) > 0 Then
If a(j) < a(i) Then
MsgBox "Tier " & j & " Can not be less than " & "Tier " & i
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo

flag = False
Exit Function
Else
If a(i) = 0 Then
MsgBox "You Must Fill Tiers Sequencially"
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo
flag = False
Exit Function
End If



End If
End If
Next j
Next i
If flag = True Then
' DoCmd.RunCommand acCmdSaveRecord
End If

Cancel = False
*--------------------------
That depends on how it was done. If 3,4,5 was in there and the 3 was replace with the 5 then it wouldn't fail, 5>3 and the Cancel would take effect. However, if all 3 values were erased then 5,4,3 was entered, then when the 5 was entered the 2nd box wouldn't be >0, so the test would past. Next, you put in the 4 and the test from the 1st box isn't run again. You will actually have to make this test in the 2nd textbox. You could do the same thing, but test "backwards" also.

The other thing to watch out for is, can 1 and 3 be filled in if 2 isn't. Currently, I don't see a check for this. Also, you are checking for >0 but you also mention "not filled in". Can the value be Null? If so, how do you want to handle a Null value? Should Null be handled the same as zero or differently?

--
Wayne Morgan
Microsoft Access MVP


Wayne

Thanks for the help, it work great. I have faced another issue, once user entered this information successfully, and next time he edit this record and put 5,4,3 (earlier it was 3,4,5) in three textbox, this logic will fail, so let me know how do I handle this situation?
 
Wayne

I finaly made it and its workingperfectly, see following code andlet me know your feedback

*-----------------------------
ArrAssignTier
If editflag = "edit" Then
For i = 0 To 4
If arrTier(i) = 0 Then
MsgBox "You Must Fill Tier Sequentially"
Cancel = True
Exit Sub
End If
Next i
End If
If TxtTier1Max.Value > 0 Then
txtTier1FeePerAnnum.Enabled = True
If editflag = "new" Then
For i = 0 To 0
If arrTier(i) = 0 Then
MsgBox "You Must Fill Tier Sequentially"
Cancel = True
Exit Sub
End If
Next i
End If
For i = 0 To 4
For j = i To 4
If arrTier(j) > 0 Then
If arrTier(j) < arrTier(i) Then
MsgBox "Tier " & j + 1 & " Can not be less than " & "Tier " & i + 1
Cancel = True
Exit For
End If
End If
Next j
'Exit For
Next i
Else
txtTier1FeePerAnnum.Enabled = False
txtTier1FeePerAnnum = 0
End If
*-----------------------------
Function ArrAssignTier()
ReDim arrTier(5)
arrTier(0) = Tier1Max
arrTier(1) = Tier2Max
arrTier(2) = Tier3Max
arrTier(3) = Tier4Max
arrTier(4) = Tier5Max
End Function
"Ashish Kanoongo" <[email protected]
m> wrote in message I tried followings it works great, but not focus on same text box where he generates error. He is checkingproperly, but not able to set focus on same text box, it focus on first textbox of the form every time.

*-------------------
Dim a(5) As Double
Dim flag As Boolean
flag = True
a(1) = Tier1Max
a(2) = Tier2Max
a(3) = Tier3Max
a(4) = Tier4Max
a(5) = Tier5Max

For i = 1 To 5
For j = i To 5
If a(j) > 0 Then
If a(j) < a(i) Then
MsgBox "Tier " & j & " Can not be less than " & "Tier " & i
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo

flag = False
Exit Function
Else
If a(i) = 0 Then
MsgBox "You Must Fill Tiers Sequencially"
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo
flag = False
Exit Function
End If



End If
End If
Next j
Next i
If flag = True Then
' DoCmd.RunCommand acCmdSaveRecord
End If

Cancel = False
*--------------------------
That depends on how it was done. If 3,4,5 was in there and the 3 was replace with the 5 then it wouldn't fail, 5>3 and the Cancel would take effect. However, if all 3 values were erased then 5,4,3 was entered, then when the 5 was entered the 2nd box wouldn't be >0, so the test would past. Next, you put in the 4 and the test from the 1st box isn't run again. You will actually have to make this test in the 2nd textbox. You could do the same thing, but test "backwards" also.

The other thing to watch out for is, can 1 and 3 be filled in if 2 isn't. Currently, I don't see a check for this. Also, you are checking for >0 but you also mention "not filled in". Can the value be Null? If so, how do you want to handle a Null value? Should Null be handled the same as zero or differently?

--
Wayne Morgan
Microsoft Access MVP


Wayne

Thanks for the help, it work great. I have faced another issue, once user entered this information successfully, and next time he edit this record and put 5,4,3 (earlier it was 3,4,5) in three textbox, this logic will fail, so let me know how do I handle this situation?
 
I just gave it a quick glance and it should be fine. I didn't try to "break the logic". The main thing is that it is working. It is mostly If statements and small For loops which run quickly.

--
Wayne Morgan
Microsoft Access MVP


Wayne

I finaly made it and its workingperfectly, see following code andlet me know your feedback

*-----------------------------
ArrAssignTier
If editflag = "edit" Then
For i = 0 To 4
If arrTier(i) = 0 Then
MsgBox "You Must Fill Tier Sequentially"
Cancel = True
Exit Sub
End If
Next i
End If
If TxtTier1Max.Value > 0 Then
txtTier1FeePerAnnum.Enabled = True
If editflag = "new" Then
For i = 0 To 0
If arrTier(i) = 0 Then
MsgBox "You Must Fill Tier Sequentially"
Cancel = True
Exit Sub
End If
Next i
End If
For i = 0 To 4
For j = i To 4
If arrTier(j) > 0 Then
If arrTier(j) < arrTier(i) Then
MsgBox "Tier " & j + 1 & " Can not be less than " & "Tier " & i + 1
Cancel = True
Exit For
End If
End If
Next j
'Exit For
Next i
Else
txtTier1FeePerAnnum.Enabled = False
txtTier1FeePerAnnum = 0
End If
*-----------------------------
Function ArrAssignTier()
ReDim arrTier(5)
arrTier(0) = Tier1Max
arrTier(1) = Tier2Max
arrTier(2) = Tier3Max
arrTier(3) = Tier4Max
arrTier(4) = Tier5Max
End Function
"Ashish Kanoongo" <[email protected]
m> wrote in message I tried followings it works great, but not focus on same text box where he generates error. He is checkingproperly, but not able to set focus on same text box, it focus on first textbox of the form every time.

*-------------------
Dim a(5) As Double
Dim flag As Boolean
flag = True
a(1) = Tier1Max
a(2) = Tier2Max
a(3) = Tier3Max
a(4) = Tier4Max
a(5) = Tier5Max

For i = 1 To 5
For j = i To 5
If a(j) > 0 Then
If a(j) < a(i) Then
MsgBox "Tier " & j & " Can not be less than " & "Tier " & i
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo

flag = False
Exit Function
Else
If a(i) = 0 Then
MsgBox "You Must Fill Tiers Sequencially"
'Me.Undo
Cancel = True
DoCmd.RunCommand acCmdUndo
flag = False
Exit Function
End If



End If
End If
Next j
Next i
If flag = True Then
' DoCmd.RunCommand acCmdSaveRecord
End If

Cancel = False
*--------------------------
That depends on how it was done. If 3,4,5 was in there and the 3 was replace with the 5 then it wouldn't fail, 5>3 and the Cancel would take effect. However, if all 3 values were erased then 5,4,3 was entered, then when the 5 was entered the 2nd box wouldn't be >0, so the test would past. Next, you put in the 4 and the test from the 1st box isn't run again. You will actually have to make this test in the 2nd textbox. You could do the same thing, but test "backwards" also.

The other thing to watch out for is, can 1 and 3 be filled in if 2 isn't. Currently, I don't see a check for this. Also, you are checking for >0 but you also mention "not filled in". Can the value be Null? If so, how do you want to handle a Null value? Should Null be handled the same as zero or differently?

--
Wayne Morgan
Microsoft Access MVP


Wayne

Thanks for the help, it work great. I have faced another issue, once user entered this information successfully, and next time he edit this record and put 5,4,3 (earlier it was 3,4,5) in three textbox, this logic will fail, so let me know how do I handle this situation?
 
Back
Top