Can't Update Form Control Using DLookup

[cgdrake]

I've uploaded a database which is giving me trouble. It was a template someone downloaded and asked me to tweak. The form named Order Details Subform has a combo box control named ProductID. (I would have named it cboProductID, but oh well . . . )

Anyhoo, when I run the main form or the subform and click on product ID, the AfterUpdate event fires from a macro called SetUnitPrice in the group of macros called "Add an Order and Details Macros." It's using the DLookup function to find the UnitPrice from the Products table. When I click on a product, I then get an error message that says Missing Operator.

Can someone help and see if there is anything wrong with the DLookup syntax? I think it's OK, but now am beating my head against the wall.

 

[pbaldy]

Bleh.

DLookUp("UnitPrice","Products","Products.[ProductID] = '" & [Forms]![Add an Order and Details]![Order Details Subform].[Form]![ProductID].[Column](1) & "'")

 

[pbaldy]

I should add, since the price is already in the rowsource of the combo, why not just get it from there?

 

[cgdrake]

Thanks so much, I'll give it a go!

 

[cgdrake]

This is a dumb question, but how would I reference the rowsource of the combo since it's a select query? Also, the UnitPrice text box doesn't have a rowsource property. I can't get my head around this apparently . . .

 

[pbaldy]

In code,

Me.TextBoxName = Me.ComboBoxName.Column(x)

where x is the column number of the desired value. I suppose you could use SetValue in a macro.

 

[cgdrake]

OK, now I'm getting an error saying, "You can't assign a value to this object" after changing the statement to Me.UnitPrice = Me.ProductID.Column(6). The ControlSource property shows UnitPrice, which is the name of the text box. Does this need to be changed?

 

[cgdrake]

I checked on the RowSource query that drives the ProductID combo box. The query is as follows:

SELECT DISTINCTROW Products.ProductID, Products.ProductName, Products.PackageSize, Products.WeightLbs, Products.DealerUnitCost, Products.UnitPrice, Products.InStock FROM Products ORDER BY ProductID;

So I added code to the AfterUpdate event code to show the query field results:

Debug.Print Me.ProductID.Column(1)
Debug.Print Me.ProductID.Column(2)
Debug.Print Me.ProductID.Column(3)
Debug.Print Me.ProductID.Column(4)
Debug.Print Me.ProductID.Column(5)
Debug.Print Me.ProductID.Column(6)

I get these results:

Null
SEMI-SYNTHETIC BAR & CHAIN OIL
4 GALLONS
24.6
Null
Null
Null

Apparently the ProductID field, along with unit price, are null. But when I run the same query independent of the form, I get the correct results. What gives?

Also, should the ControlSource property of the combo box be [Order Details].ProductID since this information should go into the Order Details table? It now shows as just ProductID, and the form's RecordSource is a query called Order Details Extended, whose SQL reads as follows:

SELECT [Order Details].*, Products.ProductName, CCur(Nz([Quantity],0)*Products.UnitPrice*(1-[Discount])) AS [Extended Price], Products.UnitPrice
FROM [Order Details] INNER JOIN Products ON products.ProductId=[Order Details].ProductID;

I'm so confused!!

 

[boblarson]

Just to remember -

Your combo box NUMBER OF COLUMNS property needs to be set correctly for it to register. If you have the NUMBER OF COLUMNS property set to 3 then any other columns will be as if they aren't there.

The columns are referred to by their ZERO-BASED number (so the second column is Column(1), third column is Column(2), etc.)

 

[cgdrake]

Thanks Bob, sorry to be so daft, but is number of columns the same as ColumnCount? I already have that set correctly.

 

[cgdrake]

That column reference starting at 0 rather than 1 doesn't make sense to me, but I supposed mine is not to question Microsoft! Thanks for letting me know about that.

 

[cgdrake]

I think I have these errors fixed. I ended up deleting and rebuilding the combo and text boxes for Product ID and Unit Price respectively. I think most of my errors have to do with the fact that I'm tweaking a template someone else built, not to mention my skill level at the beginner level when it comes to building apps (rather than just using tables and queries). But I'm learning and will hopefully only get better.

Thanks for all your help.