Run-time Error 2471 while running For - loop

[frispee]

Hi,

I am running this code to calculate some expressions using the values stored in a table and then return them to another table. I am using a For loop with a counter to move from one row to the next. However, when I run the code, I get error 2471. The code is:

Private Sub LRB_TAC_btn_Click()
Dim NumParts Demand as Integer 

NumParts = Forms!LTAC_frm!NumParts_tbx.Value
For Counter = 1 To NumParts

Demand = DLookup("[Estimated Annual Usage]", "TempLRB_tbl", "PartID = 'Counter'")

PartID is of text data type. I am just starting out in using VBA for MS Access, and I know I have probably missed something that is rudimentary to you guys. So please bear with me

 

[pbaldy]

I don't see the logic, but the variable needs to be concatenated:

Demand = DLookup("[Estimated Annual Usage]", "TempLRB_tbl", "PartID = '" & Counter & "'")

 

[spikepl]

And not everyone has the entire list of error messages present - next time, also post the text of the message and whoich line genereted it. But you did give an error number, which is one up on "I got an error" D

 

[frispee]

@pblady: Thank you for that suggestion, but I am still getting an error (Type Mismatch). I shall explain what I am trying to do and maybe you could point me in the right direction.

1. Get all part numbers from the user
2. For each part number, I ll look up some specifications from a different table
3. Do some calculations on those specifications
4. Output the part numbers along with the calculated results to another table


Private Sub LRB_TAC_btn_Click()
Dim NumParts, Demand, Counter As Double

NumParts = Forms!LTAC_frm!NumParts_lbx.Value
For Counter = 1 To NumParts
Demand = DLookup("[Estimated Annual Usage]", "TempLRB_tbl", "PartID = '" & Counter & "'"
.
.
.... (Calculations)
TotalLogisticsCost = TransportCost + TransitImpact + SafetyImpactCost + LongLeadCost + FirmTimeCost + DutyCost
TotalCost = TotalLogisticsCost + AnnualCost
CostPerPart = TotalLogisticsCost / Demand
TotalCostPerPart = CostPerPart + PartCost

Set OutputLTAC = CurrentDb.OpenRecordset("LTACResults_tbl")
With OutputLTAC
.AddNew
.Fields("[Part]").Value = Part
.Fields("[Total Logistics Cost]").Value = TotalLogisticsCost
.Fields("[Total Cost]").Value = TotalCost
.Fields("[Total Logistics Cost Per Part]").Value = CostPerPart
.Fields("[Total Cost Per Part]").Value = TotalCostPerPart
.Update
'Next


End With
End Sub


I have changed the data type of PartID to integer. I am stumped and since I am just starting out, any help would be greatly appreciated

@spikepl: I will do so in all future posts. Thanks for pointing that out

 

[pbaldy]

My bad:

Demand = DLookup("[Estimated Annual Usage]", "TempLRB_tbl", "PartID = " & Counter)

But I'm still dubious. Would your PartID's be 1,2,3...?

 

[frispee]

@pbaldy: Thanks a lot!! It works as I want it to. You just solved a huge problem for me And you are absolutely right. PartID is 1, 2, 3...upto the number of parts that the user has entered. If he has entered 5 parts, PartID would be from 1 to 5. Actually, the user enters directly into the table (Subform) and I am using a round-about way of finding PartID. Do you have any suggestions?

 

[pbaldy]

Not offhand, but if I get a chance later I'll try to follow what's going on better.