Module doesnt run, or even attempt to

C

chaostheory

Registered User.
Local time
Today, 13:33
Joined
Sep 30, 2008
Messages
69
Im not sure what happened but this module was written before my time with the company. And it has always run. I created a macro to run the module and a bunch of queries designed to update quantities in the database. For some reason the module has ceased to function. In order to make the module run in the macro i had to change it to a function (simply changed sub to public function). It ran for a few days just fine. One day i tried to run and i never got the usual "done" message. I went into the module itself and tried to run it, all i ever get is an hour glass for a second, then back to the blinking cursor. If i try to debug, i hit F8, same thing, hourglass, blinking cursor. If i insert a messagebox in the first line after the dim statement, it never pops up. Its completely dead. Any idea why, would it be a problem in the dim statement? and if it was shouldnt it tell me there is a syntax error?

Thanks for any help in advance, i dont like bugging my boss as shes a superemly busy hectic woman.

Code: 
Public Function autoupdate()
Dim ThisDB, SkidList, Qnt(1 To 12), Status(1 To 12), Size(1 To 10), RecCnt, Q, S, R, CurQnt As Integer, CurStatus, CurSize, Inspected
Dim SkidID
Set ThisDB = DBEngine.Workspaces(0).OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
Set SkidList = ThisDB.OpenRecordset("Skid List", dbOpenTable)
Qnt(1) = "Green Qnt"
Qnt(2) = "1st Fire Qnt In"
Qnt(3) = "1st Fire Qnt Out"
Qnt(4) = "Slice Qnt In"
Qnt(5) = "Slice Qnt Out"
Qnt(6) = "Plug Qnt In"
Qnt(7) = "Plug Qnt Out"
Qnt(8) = "2nd Fire Qnt In"
Qnt(9) = "2nd Fire Qnt Out"
Qnt(10) = "C/S Qnt In"
Qnt(11) = "C/S Qnt Out"
Qnt(12) = "C/S Qnt Out"
Status(1) = "01 Green"
Status(2) = "02 In 1st Fire"
Status(3) = "03 1st Fired"
Status(4) = "04 In Slice"
Status(5) = "05 Sliced"
Status(6) = "06 In Plug"
Status(7) = "07 Plugged"
Status(8) = "08 In 2nd Fire"
Status(9) = "09 2nd Fired"
Status(10) = "10 In Contour/Skin"
Status(11) = "11 Contoured/Skinned"
Size(1) = "Extruded Size"
Size(2) = "Sliced Size"
Size(3) = "Finished Size"
Size(4) = "Finished Size"
Size(10) = "Potential Size"
RecCnt = SkidList.RecordCount
SkidList.MoveFirst
For R = 1 To RecCnt
    SkidList.Edit
'SkidID = SkidList.Fields("Skid") 'debug
'If SkidList.Fields("Skid") Like "00320*" Then 'debug
    'SkidID = SkidList.Fields("Skid")
'End If 'debug
    For Q = 1 To 12
        'TRIAL = SkidList.Fields(Qnt(Q))  'dEBUG
        If IsNull(SkidList.Fields(Qnt(Q))) = True Or Q = 12 Then
            CurQnt = SkidList.Fields(Qnt(Q - 1))
            If CurQnt = 0 Then
                SkidList.Fields("Auto Status") = "Dead"
            Else
                SkidList.Fields("Auto Status") = Status(Q - 1)
            End If
            SkidList.Fields("Auto Cur Qnt") = CurQnt
            Exit For
        End If
    Next Q
    For S = 1 To 4
        If IsNull(SkidList.Fields(Size(S))) = True Or S = 4 Then
            If S = 2 And IsNull(SkidList.Fields(Size(10))) = False Then
                CurSize = SkidList.Fields(Size(10))
            Else
                CurSize = SkidList.Fields(Size(S - 1))
            End If
            SkidList.Fields("Auto Cur\Pot Size") = CurSize
            Exit For
        End If
    Next S
    SkidID = SkidList.Fields("Skid")
    Skid = "[Skid] = '" & SkidID & "'"
    QntInspected = DCount("[Skid]", "Bare Characterization", Skid)
    QntGone = DCount("[Order ID] & [If Reject, Defect]", "Bare Characterization", Skid)
    QntLeft = QntInspected - QntGone
    If QntInspected > 0 Then
        If QntInspected = CurQnt Then
            If QntLeft > 0 Then
                SkidList.Fields("Auto Status") = "13 Inspected"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            Else
                SkidList.Fields("Auto Status") = "Empty"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            End If
        Else
            SkidList.Fields("Auto Status") = "12 In Inspection"
            SkidList.Fields("Auto Cur Qnt") = CurQnt - QntGone
        End If
    End If
    SkidList.Update
    SkidList.MoveNext
Next R
SkidList.Close
MsgBox ("Done!")
End Function
 
Look for name conflicts. Specifically, see if there is a module named autoupdate. Lacking that, try looking for other subroutines or functions named autoupdate (even in form/report modules).

You should not have changed this to a function.
 
I can change it back to a sub, but im very new to this i didnt know how to make a macro run a sub. I read online that all i had to do was change "sub" to "public function" and it would run in the macro when called by its function name. Is there an easier way.

Can i leave it a sub and simple write a function that says

function callupdate()
call autoupdate()
end function

?

the only similarity i see is the module itself is named autoupdate and so is the sub in the module.

Other than that i see nothing else named autoupdate.
 
Last edited:
Ok i renamed the module to AutoUdateQnty
The sub (i changed it back) is now named autoupdate
i created a function in the same module called getupdate.

All it says is

Public function getUpdate()
Call autoupdate
end function

Still does the exact same thing, just an hourglass, then nothing. Back to cursor. :(
 
Well after 2 attempts i realized only zip was allowed, i can't zip due to office PC privelage restrictions. But im resourceful. I used a portable winrar, then it wouldnt let me upload a rar. So i renamed the rar to .zip HA...doh! Too big anyway.

so i uploaded a zip to my personal FTP for you to download since i cant upload it to here...

http://josephknight.net/files/mydatabase.zip

Thank the computer gods for portable appz
 
OK, just some basic things you need to address before you can get too far.

Error handling: there is none. By including error handling, I was able to see and manipulate problems in the code and generally get further along.

Implied data types. Don't depend on someone else to define data types for you (in (Dim) statements).

Code: 
For more on both of these, see:
Sub autoupdate()
Dim ThisDB As Variant
Dim SkidList As Variant
Dim Qnt(1 To 12) As Variant
Dim Status(1 To 12) As Variant
Dim Size(1 To 10) As Variant
Dim RecCnt As Variant
Dim Q As Variant
Dim S As Variant
Dim R As Variant
Dim CurQnt As Integer
Dim CurStatus As Variant
Dim CurSize As Variant
Dim Inspected As Variant
Dim SkidID
On Error GoTo Myerror
MsgBox "helloworld"
Set ThisDB = DBEngine.Workspaces(0).OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
Set SkidList = ThisDB.OpenRecordset("Skid List", dbOpenTable)
Qnt(1) = "Green Qnt"
Qnt(2) = "1st Fire Qnt In"
Qnt(3) = "1st Fire Qnt Out"
Qnt(4) = "Slice Qnt In"
Qnt(5) = "Slice Qnt Out"
Qnt(6) = "Plug Qnt In"
Qnt(7) = "Plug Qnt Out"
Qnt(8) = "2nd Fire Qnt In"
Qnt(9) = "2nd Fire Qnt Out"
Qnt(10) = "C/S Qnt In"
Qnt(11) = "C/S Qnt Out"
Qnt(12) = "C/S Qnt Out"
Status(1) = "01 Green"
Status(2) = "02 In 1st Fire"
Status(3) = "03 1st Fired"
Status(4) = "04 In Slice"
Status(5) = "05 Sliced"
Status(6) = "06 In Plug"
Status(7) = "07 Plugged"
Status(8) = "08 In 2nd Fire"
Status(9) = "09 2nd Fired"
Status(10) = "10 In Contour/Skin"
Status(11) = "11 Contoured/Skinned"
Size(1) = "Extruded Size"
Size(2) = "Sliced Size"
Size(3) = "Finished Size"
Size(4) = "Finished Size"
Size(10) = "Potential Size"
RecCnt = SkidList.RecordCount
SkidList.MoveFirst
For R = 1 To RecCnt
    SkidList.Edit
'SkidID = SkidList.Fields("Skid") 'debug
'If SkidList.Fields("Skid") Like "00320*" Then 'debug
    'SkidID = SkidList.Fields("Skid")
'End If 'debug
    For Q = 1 To 12
        'TRIAL = SkidList.Fields(Qnt(Q))  'dEBUG
        If IsNull(SkidList.Fields(Qnt(Q))) = True Or Q = 12 Then
            CurQnt = SkidList.Fields(Qnt(Q - 1))
            If CurQnt = 0 Then
                SkidList.Fields("Auto Status") = "Dead"
            Else
                SkidList.Fields("Auto Status") = Status(Q - 1)
            End If
            SkidList.Fields("Auto Cur Qnt") = CurQnt
            Exit For
        End If
    Next Q
    For S = 1 To 4
        If IsNull(SkidList.Fields(Size(S))) = True Or S = 4 Then
            If S = 2 And IsNull(SkidList.Fields(Size(10))) = False Then
                CurSize = SkidList.Fields(Size(10))
            Else
                CurSize = SkidList.Fields(Size(S - 1))
            End If
            SkidList.Fields("Auto Cur\Pot Size") = CurSize
            Exit For
        End If
    Next S
    SkidID = SkidList.Fields("Skid")
    Skid = "[Skid] = '" & SkidID & "'"
    QntInspected = DCount("[Skid]", "Bare Characterization", Skid)
    QntGone = DCount("[Order ID] & [If Reject, Defect]", "Bare Characterization", Skid)
    QntLeft = QntInspected - QntGone
    If QntInspected > 0 Then
        If QntInspected = CurQnt Then
            If QntLeft > 0 Then
                SkidList.Fields("Auto Status") = "13 Inspected"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            Else
                SkidList.Fields("Auto Status") = "Empty"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            End If
        Else
            SkidList.Fields("Auto Status") = "12 In Inspection"
            SkidList.Fields("Auto Cur Qnt") = CurQnt - QntGone
        End If
    End If
    SkidList.Update
    SkidList.MoveNext
Next R
SkidList.Close
MsgBox ("Done!")
Exit Sub
Myerror:
    MsgBox "Error: " & Err & vbCrLf & Error
    Resume Next
End Sub

Like I said, this is just a little farther along, not the solution. You need to do some clean up (identifying proper data types) before I can help you get much further.
 
ARgh!! Like i said this was running before i started as is.

Im not that familiar with access varaibles types.

I had to get out of the database for a guy to run some queries and take a lunch break, i will return to this in an hour or so to see if i can shed some light for you on types if you havent figured them all out by then

Dim DB as database :D
 
on a side note i will have to look into error handling. Programming is not my degree and im just doing for this job, but i pick things up so quickly i just cruise along till i hit a roadbump. Never used error handling in vba yet. But i like the idea and might use it soon after i research it a little.
 
No worry about your experience level. With the help you get here (even if it's bad help), you'll do a better job than what your predecessor did.

Also, you should split the database. That way, multiple people can work at the same time.
 
Sigh, how did you get it to do anything. I dimmed all those variables i copied in the error handler you wrote, i hit F8 and absolutely nothing happens, not even an hourglass its like i didnt even push a button. same thing if i hit the green arrow. Nothing.

Code: 
Sub autoupdate()
Dim ThisDB As Database
Dim SkidList As Recordset
Qnt(1 To 12) As String
Status(1 To 12) As String
Size(1 To 10) As String
RecCnt As Integer
Q As Integer
S As Integer
R As Integer
CurQnt As Integer
CurStatus As String
CurSize As String
Inspected As String
Dim SkidID As Integer
On Error GoTo Myerror
Set ThisDB = DBEngine.Workspaces(0).OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
Set SkidList = ThisDB.OpenRecordset("Skid List", dbOpenTable)
Qnt(1) = "Green Qnt"
Qnt(2) = "1st Fire Qnt In"
Qnt(3) = "1st Fire Qnt Out"
Qnt(4) = "Slice Qnt In"
Qnt(5) = "Slice Qnt Out"
Qnt(6) = "Plug Qnt In"
Qnt(7) = "Plug Qnt Out"
Qnt(8) = "2nd Fire Qnt In"
Qnt(9) = "2nd Fire Qnt Out"
Qnt(10) = "C/S Qnt In"
Qnt(11) = "C/S Qnt Out"
Qnt(12) = "C/S Qnt Out"
Status(1) = "01 Green"
Status(2) = "02 In 1st Fire"
Status(3) = "03 1st Fired"
Status(4) = "04 In Slice"
Status(5) = "05 Sliced"
Status(6) = "06 In Plug"
Status(7) = "07 Plugged"
Status(8) = "08 In 2nd Fire"
Status(9) = "09 2nd Fired"
Status(10) = "10 In Contour/Skin"
Status(11) = "11 Contoured/Skinned"
Size(1) = "Extruded Size"
Size(2) = "Sliced Size"
Size(3) = "Finished Size"
Size(4) = "Finished Size"
Size(10) = "Potential Size"
RecCnt = SkidList.RecordCount
SkidList.MoveFirst
For R = 1 To RecCnt
    SkidList.Edit
'SkidID = SkidList.Fields("Skid") 'debug
'If SkidList.Fields("Skid") Like "00320*" Then 'debug
    'SkidID = SkidList.Fields("Skid")
'End If 'debug
    For Q = 1 To 12
        'TRIAL = SkidList.Fields(Qnt(Q))  'dEBUG
        If IsNull(SkidList.Fields(Qnt(Q))) = True Or Q = 12 Then
            CurQnt = SkidList.Fields(Qnt(Q - 1))
            If CurQnt = 0 Then
                SkidList.Fields("Auto Status") = "Dead"
            Else
                SkidList.Fields("Auto Status") = Status(Q - 1)
            End If
            SkidList.Fields("Auto Cur Qnt") = CurQnt
            Exit For
        End If
    Next Q
    For S = 1 To 4
        If IsNull(SkidList.Fields(Size(S))) = True Or S = 4 Then
            If S = 2 And IsNull(SkidList.Fields(Size(10))) = False Then
                CurSize = SkidList.Fields(Size(10))
            Else
                CurSize = SkidList.Fields(Size(S - 1))
            End If
            SkidList.Fields("Auto Cur\Pot Size") = CurSize
            Exit For
        End If
    Next S
    SkidID = SkidList.Fields("Skid")
    Skid = "[Skid] = '" & SkidID & "'"
    QntInspected = DCount("[Skid]", "Bare Characterization", Skid)
    QntGone = DCount("[Order ID] & [If Reject, Defect]", "Bare Characterization", Skid)
    QntLeft = QntInspected - QntGone
    If QntInspected > 0 Then
        If QntInspected = CurQnt Then
            If QntLeft > 0 Then
                SkidList.Fields("Auto Status") = "13 Inspected"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            Else
                SkidList.Fields("Auto Status") = "Empty"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            End If
        Else
            SkidList.Fields("Auto Status") = "12 In Inspection"
            SkidList.Fields("Auto Cur Qnt") = CurQnt - QntGone
        End If
    End If
    SkidList.Update
    SkidList.MoveNext
Next R
SkidList.Close
MsgBox ("Done!")
Myerror:
    MsgBox "Error: " & Err & vbCrLf & Error
    Resume Next
End Sub
 
Last edited:
You forgot to "Dim" your variables. It works fine for me (other than that it won't work in my environment).

Code: 
Sub autoupdate()
Dim ThisDB As Database
Dim SkidList As Recordset
dim Qnt(1 To 12) As String
dim Status(1 To 12) As String
dim Size(1 To 10) As String
dim RecCnt As Integer
dim Q As Integer
dim S As Integer
dim R As Integer
dim CurQnt As Integer
dim CurStatus As String
dim CurSize As String
dim Inspected As String
Dim SkidID As Integer
On Error GoTo Myerror
Set ThisDB = DBEngine.Workspaces(0).OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
Set SkidList = ThisDB.OpenRecordset("Skid List", dbOpenTable)
Qnt(1) = "Green Qnt"
Qnt(2) = "1st Fire Qnt In"
Qnt(3) = "1st Fire Qnt Out"
Qnt(4) = "Slice Qnt In"
Qnt(5) = "Slice Qnt Out"
Qnt(6) = "Plug Qnt In"
Qnt(7) = "Plug Qnt Out"
Qnt(8) = "2nd Fire Qnt In"
Qnt(9) = "2nd Fire Qnt Out"
Qnt(10) = "C/S Qnt In"
Qnt(11) = "C/S Qnt Out"
Qnt(12) = "C/S Qnt Out"
Status(1) = "01 Green"
Status(2) = "02 In 1st Fire"
Status(3) = "03 1st Fired"
Status(4) = "04 In Slice"
Status(5) = "05 Sliced"
Status(6) = "06 In Plug"
Status(7) = "07 Plugged"
Status(8) = "08 In 2nd Fire"
Status(9) = "09 2nd Fired"
Status(10) = "10 In Contour/Skin"
Status(11) = "11 Contoured/Skinned"
Size(1) = "Extruded Size"
Size(2) = "Sliced Size"
Size(3) = "Finished Size"
Size(4) = "Finished Size"
Size(10) = "Potential Size"
RecCnt = SkidList.RecordCount
SkidList.MoveFirst
For R = 1 To RecCnt
    SkidList.Edit
'SkidID = SkidList.Fields("Skid") 'debug
'If SkidList.Fields("Skid") Like "00320*" Then 'debug
    'SkidID = SkidList.Fields("Skid")
'End If 'debug
    For Q = 1 To 12
        'TRIAL = SkidList.Fields(Qnt(Q))  'dEBUG
        If IsNull(SkidList.Fields(Qnt(Q))) = True Or Q = 12 Then
            CurQnt = SkidList.Fields(Qnt(Q - 1))
            If CurQnt = 0 Then
                SkidList.Fields("Auto Status") = "Dead"
            Else
                SkidList.Fields("Auto Status") = Status(Q - 1)
            End If
            SkidList.Fields("Auto Cur Qnt") = CurQnt
            Exit For
        End If
    Next Q
    For S = 1 To 4
        If IsNull(SkidList.Fields(Size(S))) = True Or S = 4 Then
            If S = 2 And IsNull(SkidList.Fields(Size(10))) = False Then
                CurSize = SkidList.Fields(Size(10))
            Else
                CurSize = SkidList.Fields(Size(S - 1))
            End If
            SkidList.Fields("Auto Cur\Pot Size") = CurSize
            Exit For
        End If
    Next S
    SkidID = SkidList.Fields("Skid")
    Skid = "[Skid] = '" & SkidID & "'"
    QntInspected = DCount("[Skid]", "Bare Characterization", Skid)
    QntGone = DCount("[Order ID] & [If Reject, Defect]", "Bare Characterization", Skid)
    QntLeft = QntInspected - QntGone
    If QntInspected > 0 Then
        If QntInspected = CurQnt Then
            If QntLeft > 0 Then
                SkidList.Fields("Auto Status") = "13 Inspected"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            Else
                SkidList.Fields("Auto Status") = "Empty"
                SkidList.Fields("Auto Cur Qnt") = QntLeft
            End If
        Else
            SkidList.Fields("Auto Status") = "12 In Inspection"
            SkidList.Fields("Auto Cur Qnt") = CurQnt - QntGone
        End If
    End If
    SkidList.Update
    SkidList.MoveNext
Next R
SkidList.Close
MsgBox ("Done!")
Myerror:
    MsgBox "Error: " & Err & vbCrLf & Error
    Resume Next
End Sub
 
Well i must say i have to apologize.

I tinkered with it all afternoon. Wasnt a problem in the code at all. It works fine. I managed to run a query on the database on the fields the code checks and one of them was somehow totally empty of info. Almost a whole blank record. Not sure what happened but as soon as i populated the 2344th record with data....it ran through to done.

I need alcohol now.

Thanks for all the help though and i will be looking into error handling in the future.
 
You must log in or register to reply here.

Similar threads

J
Run Time Error 380
Replies
4
Views
1,742
josros60
J
Lrn2Code
How to Run SQL in a Table Field from Module
Replies
3
Views
1,775
spikepl
spikepl
M
NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
Replies
0
Views
7,163
mdlueck
M
M
Please explain how to use this module
Replies
2
Views
4,194
mcdhappy80
M
B
  • Locked
Introduction to VBA Programming
Replies
0
Views
14,784
Banana
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom