Help w/ Loop Function

[Sharkbyte]

I have a need to do multiple lookups, on a single table. I believe it can be
done with a Loop, but can't seem to get the code right. I'm hoping someone
can help me with it.

tblInventoryItemLocations
ItemID
LocationID
Quantity

frmUpdateParts
txtItemID
txt1Qty through txt15Qty
chkTruck1 through chkTruck15

I was trying something like this:

Dim LocID1 As Integer
Dim LocID2 As Double
Dim ItemID1 As String
Dim ItemID2 As String
Dim ItemID3 As String

LocID1 = 2
LocID2 = 1
ItemID1 = [Forms]![frmupdateparts]![txtItemID]

Do Until LocID1 = 17
If IsNull(DLookup("itemid", "tblinventoryitemlocations", "itemid =
[Forms]![frmupdateparts]![txtItemID] and locationid = ' & LocID1 & '")) Then
Else
ItemID2 = "[Forms]![frmupdateparts]![chkTruck'" & LocID2 & "']"
ItemID2 = -1

ItemID3 = "[forms]![frmupdateparts]![txt'" & LocID2 & "'qty]"
ItemID3 = DLookup("quantity", "tblinventoryitemlocations",
"itemid = '" & ItemID1 & "' and locationid = '" & LocID1 & "'")
End If

Loop


I am getting a Type Mismatch error, in the IF statement, at LocID1.

Any suggestions other than 15 IF statements?

Thanks, in advance.

Sharkbyte

 

[Graham R Seach]

You're treating LocID1 as a string inside the first DLookup function. Change
the quotes as follows:
and locationid = " & LocID1)) Then

You're doing the same thing in the second DLookup. Change the quotes as
follows:
"itemid = " & ItemID1 & " and locationid = " & LocID1)

This procedure doesn't make a lot of sense to me. What are you actually
trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia