Need Troubleshooting

**athyeh** · 09-20-2013, 01:13 PM

Hi Folks,

I have a form with the following VBA behind it.

The first thing I need troubleshooting with is the "Process" and Combo68 field. Right now I have Operators selecting the process they are working and either "Full Shift", "Lunch", or "Break" in Combo68. Based on their selection in Combo 68 I have the goal adjusting down to either 75% (Break) or 50% (Lunch).

I have the goal being recorded with each record so that I can query: Group By Hour and see what they their goal was for that hour. Therefore, if I select "Lunch" I need the Goal to be 13 until their hour runs out (handled by the Hour(Now()) statement).

Also need the goal to 'reset' after every hour to "Full Shift' (Default Value for Combo68) and the Goal displayed in "Goal" to be 35. (which it is not currently doing)

Code:

Private Sub Process_Change()
    If Me![Process] = "Authenticate" Then Me![HardGoal] = 35
    If Me![Process] = "Receive" Then Me![HardGoal] = 35
End Sub
Private Sub LID_AfterUpdate()
On Error GoTo ErrorHandler
    Const cQuote = """"
        Me!Operator.DefaultValue = cQuote & Me!Operator.Value & cQuote
        Me!Process.DefaultValue = cQuote & Me!Process.Value & cQuote
        Me!Goal.DefaultValue = cQuote & Me!Goal.Value & cQuote
    If Hour(Now()) > Nz(Me.tbxHour, -1) Then
        Me.Goal = 0
        Me.tbxCount = 0
        Me.tbxHour = Hour(Now())
    End If
    Me.tbxCount = Me.tbxCount + 1
    If Me![Combo68] = "Break" Then Me![Goal] = ([HardGoal] * 0.75)
    If Me![Combo68] = "Full Shift" Then Me![Goal] = ([HardGoal])
    If Me![Combo68] = "Lunch" Then Me![Goal] = ([HardGoal] * 0.5)
DoCmd.RunCommand acCmdSaveRecord
Me.Requery
ExitHandler:
    Exit Sub
ErrorHandler:
    Select Case Err
        Case 3022
            MsgBox "LID has already been recorded. Please use alternative LID."
            DoCmd.Hourglass False
            Resume ExitHandler
        Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    Exit Sub
End Sub

**June7** · 09-20-2013, 01:31 PM

If 'it is not currently doing' then what is it doing - error message, wrong results, nothing? Have you step debugged? See link at bottom of my post.

**orange** · 09-21-2013, 06:06 AM

What testing scheme did you do/have before allowing "Operators" to use the database?
Did the issue happen during testing? Did you actually test this part?

**athyeh** · 09-23-2013, 07:05 AM

Hi Folks,

Thank you for your replies.

When I say it is not "Working" I mean that at the end of the hour, Process type switches back to "Full Shift" but "Goal" stays at 26 or 13.

As far as testing goes, I currently have 36 people testing it everyday and this is one of two main 'bugs' that needs to be troubleshooted.

**athyeh** · 09-23-2013, 08:50 AM

I'm wondering now if this problem might be better solved through a query?

Set every record's goal = 35 (no more shift type selection)

Then through the query just do:

SELECT Sum(Table1.Goal)-60 AS SumOfGoal
FROM Table1;

To get the adjusted goal.

The question then becomes, how to adjust the query to account for people working longer/shorter than their standard 8 hours?

**athyeh** · 09-24-2013, 08:15 AM

I have identified the problem. With the code below, Goal updates back to 35, at the end of every hour just fine, until I change the Control Source of Me.Goal to Goal (a field in my table) (I need to record the associated goal rang-in with each record). Why would this be and how can I troubleshoot this?

Code:

Option Compare Database
Private Sub Form_Timer()
    Me!Clock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
End Sub
Private Sub Option102_Click()
    Me.LID = CEMA
End Sub
Private Sub Process_Change()
    If Me![Process] = "Authenticate" Then Me![HardGoal] = 35
    If Me![Process] = "Receive" Then Me![HardGoal] = 35
End Sub
Private Sub Combo68_Change()
    If Me![Combo68] = "Break" Then Me![Goal] = ([HardGoal] * 0.75)
    If Me![Combo68] = "Full Shift" Then Me![Goal] = ([HardGoal])
    If Me![Combo68] = "Lunch" Then Me![Goal] = ([HardGoal] * 0.5)
End Sub
Private Sub LID_AfterUpdate()
On Error GoTo ErrorHandler
    Const cQuote = """"
        Me!Operator.DefaultValue = cQuote & Me!Operator.Value & cQuote
        Me!Process.DefaultValue = cQuote & Me!Process.Value & cQuote
        Me!Goal.DefaultValue = cQuote & Me!Goal.Value & cQuote
    If Hour(Now()) > Nz(Me.tbxHour, -1) Then
        Me.Combo68 = "Full Shift"
        Me.Goal = 35
        Me.tbxCount = 0
        Me.tbxHour = Hour(Now())
    End If
    Me.tbxCount = Me.tbxCount + 1
DoCmd.RunCommand acCmdSaveRecord
Me.Requery
ExitHandler:
    Exit Sub
ErrorHandler:
    Select Case Err
        Case 3022
            MsgBox "LID has already been recorded. Please use alternative LID."
            DoCmd.Hourglass False
            Resume ExitHandler
        Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    Exit Sub
End Sub

**June7** · 09-24-2013, 11:13 AM

What do you mean by 'change the Control Source'? I don't see any ControlSource being changed. I do see Values and DefaultValue being changed.

What is LID?

You can troubleshoot by step debug. Follow the code as it executes. When results deviate from expected, correct the code.

**athyeh** · 09-24-2013, 11:26 AM

Change The Control Source: In Design View, Click on Goal Textbox and then Go to "Data" tab, In Control Source field select "Goal" (Field in my Table), LID is an abbreviation of the name we associate with the barcode.

**June7** · 09-24-2013, 11:44 AM

You set the ControlSource property then use code to set its Value. Are you just continually changing the Value of the same record over and over? I don't know how user interacts with form. When do they move to new record? How does this impact the count value saved to Goal? Again, you will have to step debug. Do exactly what the user does, only one little step at a time and examine the code at each step of the process. Do events trigger? Are variables set as expected? Does code branch when it should?

**athyeh** · 09-26-2013, 08:10 AM

Yesterday: 9 Hours of frustrating trial and error, 1 broken copy of the DB later, IT WORKS!

Thank you for your replies. Thread Solved.

Code:

Option Compare Database
Private Sub Form_Timer()
    Me!Clock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
End Sub
Private Sub Process_Change()
    If Me![Process] = "Authenticate" Then Me![HardGoal] = 35
    If Me![Process] = "Receive" Then Me![HardGoal] = 35
End Sub
Private Sub Combo68_Change()
    If Me![Combo68] = "Break" Then Me![Goal] = ([HardGoal] * 0.75)
    If Me![Combo68] = "Full Shift" Then Me![Goal] = ([HardGoal])
    If Me![Combo68] = "Lunch" Then Me![Goal] = ([HardGoal] * 0.5)
End Sub
Private Sub LID_AfterUpdate()
On Error GoTo ErrorHandler
    Const cQuote = """"
        Me!Operator.DefaultValue = cQuote & Me!Operator.Value & cQuote
        Me!Process.DefaultValue = cQuote & Me!Process.Value & cQuote
        Me!Combo68.DefaultValue = cQuote & Me!Combo68.Value & cQuote
    If Hour(Now()) > Nz(Me.tbxHour, -1) Then
        Me.tbxCount = 0
        Me.Goal = 35
        Me.Combo68 = "Full Shift"
        Me.tbxHour = Hour(Now())
        Me!Combo68.DefaultValue = cQuote & Me!Combo68.Value & cQuote
    End If
    Me.tbxCount = Me.tbxCount + 1
DoCmd.RunCommand acCmdSaveRecord
Me.Requery
ExitHandler:
    Exit Sub
ErrorHandler:
    Select Case Err
        Case 3022
            MsgBox "LID has already been recorded. Please use alternative LID."
            DoCmd.Hourglass False
            Resume ExitHandler
        Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    Exit Sub
End Sub

**June7** · 09-26-2013, 12:16 PM

Sometimes that's what it takes. Congratulations!

Need Troubleshooting

Thread Tools

Need Troubleshooting

Similar Threads

Tab Control Troubleshooting

Troubleshooting Error: "You can't go to the specified record"

Help troubleshooting a VBA error on a MySQL query...

Posting Permissions