item not found in this collection? 1

[davyre]

Hi all,
I have the code:

Dim dbs As Database
Dim subRS As DAO.Recordset
Dim partRS As DAO.Recordset
Dim selectSub As String
Dim selectPart As String

Dim updSubStock As String
Dim updPartStock As String

Set dbs = CurrentDb

UnitID = Me.UnitIDBox.Value
orderQty = Me.QtyBox.Value
selectSub = "SELECT * FROM TblUnitSub WHERE UnitID=" & UnitID
Set subRS = dbs.OpenRecordset(selectSub)
subRS.MoveFirst

Do While subRS.EOF = False
    'subStock = DLookup("Stock", "SubUnit", "SubUnitID=" & DLookup("SubID", "TblUnitSub", "UnitSubID=" & subRS(UnitSubID)))
    [highlight #FCE94F]subNeeded = subRS(Qty) * orderQty[/highlight]
    MsgBox (DLookup("SubID", "TblUnitSub", "UnitSubID=" & subRS(UnitSubID)))
    
    If subStock > subNeeded Then
        subStock = subStock - subNeeded
        updSubStock = "UPDATE SubUnit SET Stock=" & subStock & " WHERE UnitID=" & UnitID
        MsgBox ("Stock is deducted, subStock now is " & subStock)

at the highlighted line, it shows error saying item not found in this collection (The problem is on the subRS(Qty)). I am certain that in the table called TblUnitSub that is within the recordset contains field named Qty. The table is not empty.
anyone can point where I make mistake? THanks

 

[PHV]

subNeeded = subRS!Qty * orderQty

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

or using dot notation you can write
subRS.fields("Qty") which can be shortened to subRs("Qty")

 

[davyre]

thanks all!

 

[MajP]

BTW since that compiled I am guessing you do not have "Option Explicit" listed. I do not think it would have compiled with it set, and then you would have known it was a syntax error. Always use option explicit

 

[davyre]

what do you mean? I hv the Option Compare Database on the first line. But no "Option Explicit" anywhere.
care to explain option explicit? Thanks

 

[MajP]

http://www.fmsinc.com/free/newtips/vba/Option/index.html

This requires you to declare your variables. If you do not declare your variables, you will run into huge problems when writing any real programs.

example

public sub someProcedure
 x = 1 + "A"
 msgbox x
end sub

the above will compile and run if no "option explicit", if "option explicit" is set the above would not compile.
You would have to do

public sub someProcedure
 dim x as string
 x = 1 + "A"
 msgbox x
end sub

Everything basically get dimensioned as a variant, which is sloppy and inefficient, and extremely prone to hard to find errors. I knew you did not have option explicit because you code would not have compiled pointed out the syntax error. Instead it assumed Qty was a variable with a value of null.

 

[davyre]

thanks, but the code that I gave to you above was not complete i.e. I did not include all variables declaration into the snippet. Anw thanks for the info! New knowledge for me

 

[Andrzejek]

davyre,
In your VBA editor, you can go to Tools - Options... and check "Require Variable Declaration" checkbox. This will 'automagically' put "Option Explicit" at the top of any (new) code in Forms, Modules, etc.

This should be checked by default, but it is not :-(

Have fun.

---- Andy

 

[strongm]

>thanks, but the

That's not neccessarily the point. The point is that without Option Explicit, upon encountering what appears to be an undeclared variable VB will declare it for you, as a variant, thus causing often hard to spot errors. The following, which doesn't do what it first appears to do if Option Explicit is not set, is a somewhat contrived example but should make the point.

[blue]Dim filename as string

filename="MyFile"
Msgbox fi1ename[/blue]