Visual Basic Code

[Radhika]

The form I am working with has two fields that come from the same table:
1. Out of institution and off floor (drop down)
2. # Hours Worked (Text Field)

I am trying to create a code so that if 'PTO-half day' is selected in the
'Out of institution and off floor' field, a message comes up saying '# Hours
Worked' must be entered.

OR If 'PTO-half day' is selected in 'Out of institution and off floor' field
and '# Hours Worked' is left blank, a message comes up saying '# Hours
Worked' must be entered.

Here is what I tried:
In the Before Update event for 'Out of institution and off floor field', I
put in the following code:
Const ConMESSAGE = _
"Must enter # hrs worked."

If [Out of the institution and off the floor] = "PTO-half day" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

However, this always gives me an error msg when 'PTO-half day' is selected
and does not let me enter anything in '# Hours Worked.

How can I fix this?

Thank you
Radhika

 

[GeoffG]

Use the BeforeUpdate event of the form, like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Const strcMESSAGE As String = _
"As you have selected PTO-half day, " _
& "you must enter the number of hours."

Dim strMessage As String
Dim lngButtons As VbMsgBoxStyle
Dim strHeading As String

strMessage = strcMESSAGE
lngButtons = vbExclamation + vbOKOnly
strHeading = "Incomplete Data"

If (Me.txtOutOfInstitution = "PTO-half day") _
And (IsNull(Me.txtHoursWorked) Or _
Me.txtHoursWorked = 0) Then
MsgBox strMessage, lngButtons, strHeading
Cancel = True
End If

End Sub

Setting the CANCEL parameter to TRUE, prevents the record update.

Geoff

 

[Radhika]

Thank you!

Use the BeforeUpdate event of the form, like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Const strcMESSAGE As String = _
"As you have selected PTO-half day, " _
& "you must enter the number of hours."

Dim strMessage As String
Dim lngButtons As VbMsgBoxStyle
Dim strHeading As String

strMessage = strcMESSAGE
lngButtons = vbExclamation + vbOKOnly
strHeading = "Incomplete Data"

If (Me.txtOutOfInstitution = "PTO-half day") _
And (IsNull(Me.txtHoursWorked) Or _
Me.txtHoursWorked = 0) Then
MsgBox strMessage, lngButtons, strHeading
Cancel = True
End If

End Sub

Setting the CANCEL parameter to TRUE, prevents the record update.

Geoff

The form I am working with has two fields that come from the
same table:
1. Out of institution and off floor (drop down)
2. # Hours Worked (Text Field)

I am trying to create a code so that if 'PTO-half day' is
selected in the
'Out of institution and off floor' field, a message comes up
saying '# Hours
Worked' must be entered.

OR If 'PTO-half day' is selected in 'Out of institution and off
floor' field
and '# Hours Worked' is left blank, a message comes up saying
'# Hours
Worked' must be entered.

Here is what I tried:
In the Before Update event for 'Out of institution and off
floor field', I
put in the following code:
Const ConMESSAGE = _
"Must enter # hrs worked."

If [Out of the institution and off the floor] = "PTO-half
day" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

However, this always gives me an error msg when 'PTO-half day'
is selected
and does not let me enter anything in '# Hours Worked.

How can I fix this?

Thank you
Radhika

 

[GeoffG]

Additionally, you may wish to exclude negative values by using a
"Less Than or Equals" operator:

If (Me.txtOutOfInstitution = "PTO-half day") _
And (IsNull(Me.txtHoursWorked) Or _
Me.txtHoursWorked <= 0) Then

Geoff

Thank you!

Use the BeforeUpdate event of the form, like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Const strcMESSAGE As String = _
"As you have selected PTO-half day, " _
& "you must enter the number of hours."

Dim strMessage As String
Dim lngButtons As VbMsgBoxStyle
Dim strHeading As String

strMessage = strcMESSAGE
lngButtons = vbExclamation + vbOKOnly
strHeading = "Incomplete Data"

If (Me.txtOutOfInstitution = "PTO-half day") _
And (IsNull(Me.txtHoursWorked) Or _
Me.txtHoursWorked = 0) Then
MsgBox strMessage, lngButtons, strHeading
Cancel = True
End If

End Sub

Setting the CANCEL parameter to TRUE, prevents the record
update.

Geoff

The form I am working with has two fields that come from the
same table:
1. Out of institution and off floor (drop down)
2. # Hours Worked (Text Field)

I am trying to create a code so that if 'PTO-half day' is
selected in the
'Out of institution and off floor' field, a message comes up
saying '# Hours
Worked' must be entered.

OR If 'PTO-half day' is selected in 'Out of institution and
off
floor' field
and '# Hours Worked' is left blank, a message comes up
saying
'# Hours
Worked' must be entered.

Here is what I tried:
In the Before Update event for 'Out of institution and off
floor field', I
put in the following code:
Const ConMESSAGE = _
"Must enter # hrs worked."

If [Out of the institution and off the floor] = "PTO-half
day" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

However, this always gives me an error msg when 'PTO-half
day'
is selected
and does not let me enter anything in '# Hours Worked.

How can I fix this?

Thank you
Radhika