form's shift change code not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have code that is supposed to automatically determine what shift
information was entered on based on the time of the entry. This occurs after
the "Add_record" button is clicked.

The problem is, no matter what time data is entered, the shift is always
"3". Why is this?

Private Sub Add_record_Click()
On Error GoTo Err_Add_record_Click

If Entry_time > #7:00:00 AM# And Entry_time <= #3:30:00 PM# Then
Shift = 1
ElseIf Entry_time > #3:30:00 PM# And Entry_time <= #12:00:00 AM# Then
Shift = 2
ElseIf Entry_time > #12:00:00 AM# And Entry_time <= #7:00:00 PM# Then
Shift = 3
End If

DoCmd.GoToRecord , , acNewRec

Exit_Add_record_Click:
Exit Sub

Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click

End Sub


Thanks for any suggestions.
 
You're not declaring Shift in your code, which implies that it's either a
global variable declared outside of the routine, or that you don't have
mandatory variable declaration turned on.

If it's a global variable, is it possible that it's being redefined
elsewhere?

If you don't have mandatory variable declaration, how are you determining
that its value is 3? (using Shift anywhere outside of that routine will not
provide you with the result that was calculated inside the routine)
 
I do not have mandatory declaration turned on. How do I do that?

I looked and cannot find where the variable is being defined as 3.
 
Your General Declarations section of your modules should contain at least
Option Compare Database
Option Explicit
I would never place this code in a form. It looks like a business rule that
may change and certainly might be used in more than one form or report.
Consider creating a user defined function:

Public Function GetShiftFromTime(pdatTime As Date) As Integer
Select Case pdatTime
Case #7:00:00 AM# To #3:30:00 PM#
GetShiftFromTime = 1
Case #3:30:00 PM# To 1 '1 is end of a day
GetShiftFromTime = 2
Case #12:00:00 AM# To #7:00:00 PM#
GetShiftFromTime = 3
End Select
End Function

You can then use this function in your code like:
Private Sub Add_record_Click()
'assumes Shift and Entry_Time are text boxes on your form
' and Entry_Time is a date/time value
On Error GoTo Err_Add_record_Click
Me.Shift = GetShiftFromTime(Me.Entry_Time)
DoCmd.GoToRecord , , acNewRec

Exit_Add_record_Click:
Exit Sub

Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click

End Sub
 
Back
Top