VB Code problem, field lookup not working

[MikTylor]

Hi all

I stole some code from the Northwind DB to use in my own invoicing DB. I don't normally use code because I have enough trouble getting my head around the basics of access without delving into the dark & mysterious world of secret languages, so forgive my ignorance.

I have used the code to automatically update a cost field when a certain choice is made in a combo box on a sub-form (its displayed as a datasheet)

Everytime I make a choice the cost field is updated for that record with the same costs as the previous record. All I can assume is that its the code & in my butchering of it to suit my requirements I've inadvertently mucked it up. Can someone please have a look & see if you can spot any errors that may cause the above?

Thanks in advance gurus.

Private Sub ConsumableListID_AfterUpdate()
On Error GoTo Err_ConsumableListID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ConsumableListID = " & Me!ConsumableListID

' Look up product's unit price and assign it to UnitPrice control.
Me!Cost = DLookup("Cost", "Tbl_ConsumablesList", strFilter)

Exit_ConsumableListID_AfterUpdate:
Exit Sub

Err_ConsumableListID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ConsumableListID_AfterUpdate

End Sub

Cheers

Mik

 

[KenHigg]

Is ConsumableListID a text or numeric fld in the table?

 

[MikTylor]

Hi Ken

it's numeric.

 

[KenHigg]

Hum...

Sorry, I fail to see how this works, maybe I'm missing something and some else can take a peek...

 

[MikTylor]

Hey Ken
Would it help if I posted the DB?
I know its time consuming but if you have a moment I'd be very grateful.

Mik

 

[KenHigg]

Hum, wouldn't help me. I simply don't see how:

"ConsumableListID = " & Me!ConsumableListID

Is supposed to get the pk for the previous record...

 

[MikTylor]

Umm, that's what it does now, but I don't want it to do that I want it to get the correct cost from the "ConsumablesList" tbl.

Consumables list hold 3 records, each with a different cost or price. But in the sub-form it doesn't seem to matter which of the three I choose the cost is updated as the cost for the item in the previous record, even if they are different in the "ConsumablesList" tbl.

 

[MikTylor]

Actually, I've just noticed that it takes the cost from the first record in "ConsumablesList" everytime. So it's not actually from the previous record, its the first record.