Should be simple, dlookup or column count

[johnnybwis]

Ok, I'm new at this but here is what I have. It's subform that is limited by a selection in the main form - this works OK but I need some more fields to fill in on the subform. I first tried in the rowsource of the

txtUnitcount = Dlookup("NumberPerUnit", "Products", "ProductID = 2&quot

I thought I would point it at the combo box later but I get an error #Name? I even tried making up a bogus table just to try a different table for the data.

Next I tried bringing four colums into the combobox and on exit of combobox

txtUnitcount = cboProductID.Column(1)

No good either

Any help would be appreciated

 

[SBendBuckeye]

If your productid is numeric, try this:

txtUnitcount = Dlookup("NumberPerUnit", "Products", "ProductID = " & 2 )

Strings need single quotes ' and dates need pound signs # as below for the where clause:

, "LastName = '" & strVar & "'&quot
, "Birthday = #" & datVar & "#&quot

 

[johnnybwis]

Thanks for the reply but still no go

 

[VBslammer]

Can you explain your problem again? I can't figure out what you are trying to do. How is the subform 'limited' by a selection in the main form? Give us more detail.

VBSlammer

 

[johnnybwis]

Boy come back a week later and its even difficult to understand what I wrote, anyway I did get the DLookUp to work you just have to spell DLookUp correctly, also got the column count to work also but neither of these will actually work for me as I need be able to manually change the unit count field on occasion. So if your interested here is what I am trying to do.

I have a main form where I select customer then on the subform your selection is limited by the customer picked in the main form to products that customer has this works fine with this statement in the subform combo box

SELECT DISTINCTROW Products.ProductName, Products.ProductID, Products.NumberPerUnit, Products.WeightPerThousand
FROM Products
WHERE (((Products.CustomerID)=[Forms]![Purchase Orders]![SupplierID]))
ORDER BY Products.ProductName;

The problem I am trying to overcome is that in the subform when the product is picked I also need the unit count and weight to fill in other text boxes but I need to be able to type in a different unit count on occasion. DLookUp wont allow this and either will column count

txtUnitcount = cboProductID.Column(1)