Dlookup problem with sub form

[k8277]

I have three tables: 1. tblParts, 2. tblOrders, 3. tblOrderDetails.

The tables have the following fields:

tblParts: PartNum, Pcs per Box
tblOrders: OrderNumber
tblOrderDetails: OrderNumber, Order Detail Id, PartNumber, Quantity, NumberOfBoxes

tblOrderDetails is a sub form on tblOrders

The problem I am having is that after I select my PartNumber on the OrderDetails sub form, the Dlookup function returns a null value and my NumberOfBoxes does not get updated.

Here is my code from the OrderDetails Form:

Private Sub Part_Number_AfterUpdate()

Dim piecesperbox As Variant

piecesperbox = DLookup("[Pcs per Box]", "tblParts", "[PartNum]=" & Me!PartNumber)
Me!NumberOfBoxes = piecesperbox / Me!Quantity

End Sub

 

[Darrylles]

Hi,

a)Is PartNumber on your subform?
b)Where does 'piecesperbox' come from?

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[k8277]

Yes PartNumber is in the subform and piecesperbox comes from tblParts.

I think I got this working, I had to add a single quote in the criteria around Me!PartNumber.