0

I am getting runtime error 424 when I try to access my global dictionary's count. It works fine when in the sub routine that initializes the dictionary and right after the sub routine was called inside the form_open event. Why would I be getting this error when I click a button and the event handler function needs to kow the size of the dictionary so it can iterate through its items

below is my code, sorry if it is a mess just picked up VBA a week ago. This is not a school project I am making this app to track my times that I clocked in at work so that I can ensure myself that overtime will be included into my pay check

Option Compare Database
' Global Variables
Public clk_inout As Boolean
Public weekDict
Public weekOf As Variant
Public curDay As Variant
Option Explicit
' Initialize the weekly time chart control dictionary
Public Sub initDict()
    Set weekDict = CreateObject("Scripting.Dictionary")
    Dim dayDict
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_Sun_in
    dayDict.Add "Lunch_Out", Me.txt_Sun_LO
    dayDict.Add "Lunch_in", Me.txt_Sun_LI
    dayDict.Add "Lunch_total", Me.txt_Sun_LT
    dayDict.Add "Out", Me.txt_Sun_out
    dayDict.Add "Day_total", Me.txt_Sun_out
    dayDict.Add "Paid_OT", Me.txt_Sun_OT
    weekDict.Add 1, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_M_in
    dayDict.Add "Lunch_Out", Me.txt_M_LO
    dayDict.Add "Lunch_in", Me.txt_M_LI
    dayDict.Add "Lunch_total", Me.txt_M_LT
    dayDict.Add "Out", Me.txt_M_out
    dayDict.Add "Day_total", Me.txt_M_out
    dayDict.Add "Paid_OT", Me.txt_M_OT
    weekDict.Add 2, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_T_in
    dayDict.Add "Lunch_Out", Me.txt_T_LO
    dayDict.Add "Lunch_in", Me.txt_T_LI
    dayDict.Add "Lunch_total", Me.txt_T_LT
    dayDict.Add "Out", Me.txt_T_out
    dayDict.Add "Day_total", Me.txt_T_out
    dayDict.Add "Paid_OT", Me.txt_T_OT
    weekDict.Add 3, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_W_in
    dayDict.Add "Lunch_Out", Me.txt_W_LO
    dayDict.Add "Lunch_in", Me.txt_W_LI
    dayDict.Add "Lunch_total", Me.txt_W_LT
    dayDict.Add "Out", Me.txt_W_out
    dayDict.Add "Day_total", Me.txt_W_out
    dayDict.Add "Paid_OT", Me.txt_W_OT
    weekDict.Add 4, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_R_in
    dayDict.Add "Lunch_Out", Me.txt_R_LO
    dayDict.Add "Lunch_in", Me.txt_R_LI
    dayDict.Add "Lunch_total", Me.txt_R_LT
    dayDict.Add "Out", Me.txt_R_out
    dayDict.Add "Day_total", Me.txt_R_out
    dayDict.Add "Paid_OT", Me.txt_R_OT
    weekDict.Add 5, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_F_in
    dayDict.Add "Lunch_Out", Me.txt_F_LO
    dayDict.Add "Lunch_in", Me.txt_F_LI
    dayDict.Add "Lunch_total", Me.txt_F_LT
    dayDict.Add "Out", Me.txt_F_out
    dayDict.Add "Day_total", Me.txt_F_out
    dayDict.Add "Paid_OT", Me.txt_F_OT
    weekDict.Add 6, dayDict
    Set dayDict = Nothing
    
    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_Sat_in
    dayDict.Add "Lunch_Out", Me.txt_Sat_LO
    dayDict.Add "Lunch_in", Me.txt_Sat_LI
    dayDict.Add "Lunch_total", Me.txt_Sat_LT
    dayDict.Add "Out", Me.txt_Sat_out
    dayDict.Add "Day_total", Me.txt_Sat_out
    dayDict.Add "Paid_OT", Me.txt_Sat_OT
    weekDict.Add 7, dayDict
    Set dayDict = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
    ' Configure varaibles
    Me.TimerInterval = 60000 ' 10 sec Interval
    clk_inout = True
    weekOf = getFirstDayofWeek(Date)
    curDay = Date
    
    ' Load Time Card Data
    Call loadDates(Date)
    Call selectDay(Date)
    Call loadWeeksData(Date)
    Call initDict
    Debug.Print "Dictionary Count: " & weekDict.Count
End Sub

' Find a time card already
Private Sub but_search_Click()
    Dim strSQl As String
    Dim dateInput As Variant
    Dim rs As DAO.Recordset
    Dim dbs As DAO.Database
    Set dbs = CurrentDb()
    Debug.Print "Search Dict count: " & weekDict.Count
    dateInput = Format(InputBox("Date XX/XX/XXXX", "Search by week"), "mm/dd/yyyy")
    
    If dateInput = "" Then
        MsgBox ("Please enter a valid date")
        Exit Sub
    ElseIf IsDate(dateInput) Then
        strSQl = "SELECT Week_Of FROM Time_Clock " & _
                 "WHERE (Labor_date=#" & dateInput & "# " & _
                 "OR Week_Of=#" & dateInput & "#);"
        
        Set rs = dbs.OpenRecordset(strSQl)
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveLast
            rs.MoveFirst
            Dim pastWeek As Variant
            pastWeek = rs![week_of]
            loadDates (pastWeek)
            loadWeeksData (pastWeek)
        Else
            MsgBox ("Date does not exist")
        End If
    
        rs.Close
    Else
        MsgBox ("Invalid Date")
    End If
End Sub
2
Contributors
7
Replies
8
Views
7 Years
Discussion Span
Last Post by AndreRet
0

Error 424 is normally raised when an object is missing while reference has been given to it.. Your sub routine seems fine. I think your problem at the form open event is raised because you are putting a reference to an object that was NOT loaded. Try to reload all called objects OR reference the sheet name BEFORE calling any objects. This should solve your problem I'm sure.

0

the problem seems to be happening in but_search_click not form_open. I get the error when I call Debug.Print "Search Dict count: " & weekDict.Count line 112

0

I see you have set dbs to a database. Try and do the same with your recordset rs.

I think it is pointing a reference to rs, but do not know where the new rs is.

0

My apologies, though it still seems that your problem is with

weekdict.Count

. I need to see the function, or you need to set a reference to it in some way. It seems that on the button click event it raises it as an object?

I could not see any other objects before the error that might cause problems. Let me know.

0

Found my problem I was calling initDict too late in my form_open function

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.