Calculated field used in auto-fillin

[DLynnTX]

I posted this in the "other topics" section and decided it would probably fit better here.

I have an order-entry database that I pick up the item# from and want to auto-fill the price. However, the price is based on a calculation of the Price/Ton and Wt/Ft and is a Price/Ft price. If I try to use the Price/Ton (not a calculation) it works fine. If I use the Price/Ft, it does nothing. The item is selected from a lookup (query) that has four fields (item, wt/ft (calculated), price/ton, and price/ft(calculated). I am using the following After update event when I select the item.

Me.Price = Me.Combo17.Column(3)

As I said, if I substitute Column(2), it picks up the price/ton. What am I doing wrong? I have searched this site and tried different scenerios for 2.5 hours - I'm stuck.

Thank you

 

[TheAceMan1]

How are ya DLynnTX . . .

Be aware . . . [blue]column index starts at zero![/blue]

See Ya! . . . . . .

 

[DLynnTX]

Thank you. But, I got the price/ton to work - so it isn't the column number - I just used the next column because price/ft is two columns after price/ton (I probably stated column numbers incorrect on here). But it is not related to the column number because I have tried 0 through 4 and none of them except price/ton works. It is directly related to the fact that the field I am trying to use is a calculated field.

 

[TheAceMan1]

DLynnTX . . .

I can't see why your having this kind of problem. I have plenty of queries with complex calculations used for combo/list boxes and have never had this problem.

Perhaps you can post this mysterious SQL!

See Ya! . . . . . .

 

[DLynnTX]

The event tied to Afterupdate (when selecting the item) is:
Private Sub Combo17_AfterUpdate()
Me.Price = Me.Combo17.Column(3)
End Sub

In the query, this is the calculated field:
Price/Ft: ([Price/Ton]/2000)*[Wt/Ft]

And this is another calculated field so that the one above works:
Wt/Ft: Round(((Items!OD-Items!Wall)*10.69)*Items!Wall,2)

The order of the query is Item; Wt/Ft; Price/Ton & Price/Ft

It's all really simple and I don't understand why it works when I use the column for Price/Ton and not Price/Ft.
Thank you for your help

 

[TheAceMan1]

DLynnTX . . .

Thanks for the info, but for me the SQL [blue]will tell all[/blue] in this case!

Just post the actual SQL of the query . . .

See Ya! . . . . . .

 

[DLynnTX]

How do I do that?

 

[DLynnTX]

Is this what you need?

SELECT Items.Item, Items.[Price/Ton], Round(((Items!OD-Items!Wall)*10.69)*Items!Wall,2) AS [Wt/Ft], ([Price/Ton]/2000)*[Wt/Ft] AS [Price/Ft]
FROM Items;

 

[DLynnTX]

I just noticed in a previous post I misstated the order of the fields - it's Item, Price/Ton, Wt/Ft, and Price/Ft. And it works if I use column 1, but not if I use column 3.

 

[TheAceMan1]

DLynnTX . . .

The SQL (hopefully from the [blue]RowSource[/blue] property of the combo, or the SQL of the [blue]Query Name[/blue] that resides there looks fine. At this point I'd have too suspect some kind of corruption . . .

[blue] Delete and reconstitute the combo. See if that helps . . .[/blue]

See Ya! . . . . . .

 

[DLynnTX]

Well... I appreciate your help. I deleted the combo and recreated it - same problem. Guess I just need to figure out another way to get done what I need. Again - thank you for your help.