Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Error 94 and moving past EOF

After putting in a counter I found that the first time I run my code it works correctly but on the second time I receive an Error 94.  The second time the counter count 1 extra time. Am I doing something incorrect here?
Private Sub lngQuantity_AfterUpdate()
Dim lngInvCount As Long
Dim lngInProcess As Long
Dim Pass As Single
lngInvCount = 0
lngInProcess = 0
Pass = 0
 
rsInventory.MoveFirst
rsInventory.Find "PartNo =" & Me.txtPartNo
lngInvCount = rsInventory!Quantity
rsSimpleTrans.MoveLast
rsSimpleTrans.MoveFirst
Do While Not rsSimpleTrans.EOF
    Pass = Pass + 1
    MsgBox Pass
    If rsSimpleTrans!PartNo = Me.txtPartNo Then
        If rsSimpleTrans!QuantityOut = Null Or rsSimpleTrans!QuantityIn = Null Then
            Exit Sub
        End If
        lngInProcess = lngInProcess + (rsSimpleTrans!QuantityOut - rsSimpleTrans!QuantityIn)
    End If
    rsSimpleTrans.MoveNext
Loop
lngInvCount = lngInvCount - lngInProcess
MsgBox "Current Inventory Count: " & lngInvCount
End Sub

Open in new window

0
mstape123
Asked:
mstape123
  • 3
  • 3
  • 2
2 Solutions
 
rockiroadsCommented:
what type is quantityout? try wrapping using isnull instead

also where is rsInventory defined and initialised?

also check to see if you do find a partno
eg


    lngInvCount = 0
    lngInProcess = 0
    Pass = 0
     
    rsinventory.MoveFirst
    rsinventory.Find "PartNo =" & Me.txtPartNo
   
    'ALWAYS CHECK FOR A MATCH
    If rsinventory.NoMatch = True Then
        bLoop = False
        MsgBox "Not found"
    Else
        lngInvCount = rsinventory!Quantity
        rsSimpleTrans.MoveFirst
        bLoop = True
    End If
   
    While bLoop = True
        If rsSimpleTrans.EOF Then
            bLoop = True
        Else
            If rsSimpleTrans!PartNo = Me.txtPartNo Then
                If IsNull(rsSimpleTrans!QuantityOut) Or IsNull(rsSimpleTrans!QuantityIn) Then
                    bLoop = True
                Else
                    lngInProcess = lngInProcess + (rsSimpleTrans!QuantityOut - rsSimpleTrans!QuantityIn)
                End If
            End If
            rsSimpleTrans.MoveNext
        End If
    Wend
0
 
ducky801Commented:
Error 94 is Invalid Use of Null, right?  
What line number are you erroring out on?  
Is it possible that you're reading a null value from the table that your code is reading which causes it to break?
Can you post your DB?
AR
0
 
mstape123Author Commented:
The Error Happens onLine 21.   Lines 7, 12, 15, 16, 18,19 & 20 were only used for troubleshooting.  The testing for a match is a great note. Thank you!  This is the first try at the use of find.

The message "msgbox pass" (line 16) on the second input of an item in the the transaction log (rsSimpleTrans) count one more time than there are records.  That is when it blows out.  I use the exact same information each time so I know the item is found.

My goal in the end is to let the user know when inventory needs reordered or there is not enough of the items in inventory to fill the order

Thank You both for the help.
Private Sub lngQuantity_AfterUpdate()
Dim lngInvCount As Long
Dim lngInProcess As Long
Dim Pass As Single
lngInvCount = 0
lngInProcess = 0
 
 
rsInventory.MoveFirst
rsInventory.Find "PartNo =" & Me.txtPartNo
lngInvCount = rsInventory!Quantity
 
rsSimpleTrans.MoveFirst
Do While Not rsSimpleTrans.EOF
    
    If rsSimpleTrans!PartNo = Me.txtPartNo Then
        lngInProcess = lngInProcess + (rsSimpleTrans!QuantityOut - rsSimpleTrans!QuantityIn)
    End If
    rsSimpleTrans.MoveNext
Loop
 
lngInvCount = lngInvCount - lngInProcess
MsgBox "Current Inventory Count: " & lngInvCount
End Sub

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
rockiroadsCommented:
there is nothing on line 21? or do you mean the original code?
to handle nulls in one go

lngInProcess = lngInProcess + (NZ(rsSimpleTrans!QuantityOut,0) - NZ(rsSimpleTrans!QuantityIn,0))


0
 
ducky801Commented:
If your goal is to just retrieve qty, may i sugges using dlookup, rather than iterating through your entire recordset (assumes that the part number is a unique in the table):
Dim int_PartCount as integer
int_PartCount = dlookup("QuantityOut", "Inventory", "Partnum = '" & me.txtpartno & "'")
if Int_partCount < 10 Then 'tell the user
msgbox("Less than 10 remaining!"
end if
 
Hope this helps
 
AR
0
 
mstape123Author Commented:
Let me tell you 2 gentlemen you are great.  I wish I could give both point on this one. Great answer.
Ducky the last code is great code and I will adapt it and solve the problem also.  I have 3 files a daily transaction log table, a inventory table and a master transaction log table for permanent tracking.
So current inventory will be inventory - what is in the daily transaction log.  Add to the this is for a tool room inventory and employees will be checking in as well as checking out.

Rockiroad your last solution worked.  I am going to look up exactly what the NZ is.

0
 
rockiroadsCommented:
Glad to have helped

NZ(variable,default value)

if variable is null then use default value instead of null
0
 
ducky801Commented:
I'm glad you got an answer.  Thanks for the points.  Since you're doing subtraction across mulitple tables, you may want to read up on the Dcount, and Dsum functions as well.  Best of luck!
 
AR
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now