If TXTLV.value = "CML" And txtfrom.value >= TXTDSAN.value Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Exit Sub
End If
is there any mistake in code, any value i am putting it flases the errormsg only.
Logically, I don't see an issue but in Access, the most efficient way to refer to form controls is with the Me. reference. So:
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Exit Sub
End If
The issue could be that your date fields are defined as strings or formatted as strings. When you do that, the date acts like a string rather than like a date and you will frequently run into this type of issue.
As a string "01/10/2024" is less than "12/25/2023".
However, as a date #01/10/2024# is greater than #12/25/2023#.
REMEMBER, dates are stored internally as double precision numbers. The integer portion is the number of days since Dec 30, 1899 and the decimal represents the percent of the day that has elapsed. So:
The code is out of context. It belongs in the form's BeforeUpdate event AND it should include two more lines.
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Cancel = True
Exit Sub
Exit Sub
End If
If the code is not in the correct event, it will be ineffective. It will raise the error message but it will still allow bad data to be saved. You might want to take a look at one or more of these videos. If you are only going to watch one, the most entertaining is the YouTube video of an Access user group meeting where I made a presentation of the sample I built to help you to understand how form/control events work.
I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...
www.access-programmers.co.uk
PS, use the forum's code tag function to make your code retain its original format so it is more readable.
Logically, I don't see an issue but in Access, the most efficient way to refer to form controls is with the Me. reference. So:
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Exit Sub
End If
The issue could be that your date fields are defined as strings or formatted as strings. When you do that, the date acts like a string rather than like a date and you will frequently run into this type of issue.
As a string "01/10/2024" is less than "12/25/2023".
However, as a date #01/10/2024# is greater than #12/25/2023#.
REMEMBER, dates are stored internally as double precision numbers. The integer portion is the number of days since Dec 30, 1899 and the decimal represents the percent of the day that has elapsed. So:
The code is out of context. It belongs in the form's BeforeUpdate event AND it should include two more lines.
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Cancel = True
Exit Sub
Exit Sub
End If
If the code is not in the correct event, it will be ineffective. It will raise the error message but it will still allow bad data to be saved. You might want to take a look at one or more of these videos. If you are only going to watch one, the most entertaining is the YouTube video of an Access user group meeting where I made a presentation of the sample I built to help you to understand how form/control events work.
I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...
www.access-programmers.co.uk
PS, use the forum's code tag function to make your code retain its original format so it is more readable.
Logically, I don't see an issue but in Access, the most efficient way to refer to form controls is with the Me. reference. So:
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Exit Sub
End If
The issue could be that your date fields are defined as strings or formatted as strings. When you do that, the date acts like a string rather than like a date and you will frequently run into this type of issue.
As a string "01/10/2024" is less than "12/25/2023".
However, as a date #01/10/2024# is greater than #12/25/2023#.
REMEMBER, dates are stored internally as double precision numbers. The integer portion is the number of days since Dec 30, 1899 and the decimal represents the percent of the day that has elapsed. So:
The code is out of context. It belongs in the form's BeforeUpdate event AND it should include two more lines.
Code:
If Me.TXTLV = "CML" And Me.txtfrom >= Me.TXTDSAN Then
MsgBox "Commuted Leave can't be presanctioned, Please check!!!!", vbOKOnly + vbCritical, "Hello !! Information incomplete"
Me.TXTDSAN.SetFocus
Cancel = True
Exit Sub
Exit Sub
End If
If the code is not in the correct event, it will be ineffective. It will raise the error message but it will still allow bad data to be saved. You might want to take a look at one or more of these videos. If you are only going to watch one, the most entertaining is the YouTube video of an Access user group meeting where I made a presentation of the sample I built to help you to understand how form/control events work.
I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...
www.access-programmers.co.uk
PS, use the forum's code tag function to make your code retain its original format so it is more readable.
I need you help in another situation.
I am having two fileds of leave sanction i.e. From and To, I want to put a validation check, while applying any othrr leave, to see if the Period is is inbetween the leave already sanctioned.
how can I resolve this issue.
The validation code belongs in the BeforeUpdate event of the form. When multiple fields are involved it is especially awkward to try to validate in the control level events so just put the code in the FORM's BeforeUpdate event.
You need to use dCount() to count the number of rows where the From date is between the start and end dates of existing leave records. Then you need to use dCount() to count the number of rows where the to date is between the start and end dates of existing leave records. If either count is > 0, you have an overlap so you need to cancel the event as I showed in my earlier code.
