Getting Continuous form dropdown values 1

[VBAjedi]

I have a continuous form (for entering order line-items) where each iteration has three fields: an "Item #" textbox, a "Quantity" textbox, and a "Price" combo box.

I'm trying to set up the "Price" combobox to query my "ItemPrices" table for the price related to the product/quantity entered in the textboxes.

My problem is that it seems to be returning the price for the product/quantity given for the FIRST continuous form iteration.

So if I have two items on an order, I fill in the Item # and Price for the first item, and when I click on the combo box for that iteration it returns the correct price. But when I fill in an Item # and Price for the second item and then click on the combo box for the second iteration, it returns the price for the first iteration. . .

It seems that somehow my query needs to be able to get the values from the "current" iteration's controls.

Hope this is clear. . . difficult to describe!

VBAjedi

 

[RoyVidar]

Have you bound the combo to your price field (controlsource property)?

Roy-Vidar

 

[VBAjedi]

The combo is bound to the price field in my OrderDetails table. The price I'm trying to lookup is in my ItemPrices table. The OrderDetails table stores the actual price we are charging for the item, while the ItemPrices table stores the current "suggested retail" price.

I chose this approach because I have to let my users query for the correct price, but still allow them to override that price with another. See thread700-927484 for detail on that.


VBAjedi

 

[VBAjedi]

If it helps, here's the SQL from my combobox query:

SELECT ItemPrices.ItemPrice
FROM ItemPrices
WHERE ((([ItemPrices]![ItemNumber])=[Forms]![OrderDetailsForm]![ItemNumber]) AND (([ItemPrices]![Quantity])=[Forms]![OrderDetailsForm]![Quantity]));

VBAjedi

 

[RoyVidar]

I don't do this kind of stuff, so I haven't got much detail on it, bit I think I'd try a requery of the combo, either in the on current event of the form, or in the gotfocus event of the combo.

[tt]me!cboCombo.requery[/tt]

Roy-Vidar

 

[VBAjedi]

Getting warmer. . . this works if I put the requery in the gotfocus event of the combo, and then open the OrderDetailsForm directly. However, if I open the Orders form, on which the OrderDetailsForm is a subform, I get prompted for both parameters when it tries to do the requery.

And for the life of me I can't find the On Current event for the details section of my subform, so I can't try the code in there.

VBAjedi

 

[RoyVidar]

He he - now entering some of the intricacies of Access...

Funny thing, when opening a form with a subform, the sequence of events are as follows:

subform open, load, resize, current, then the main forms open, load, resize, activate, gotfocus and current - which means my previous suggestion of using the subforms on current (found in the event tab for the form) would bomb...

Then, subforms are not recognized as open forms, but as a control on the main form, and must be referenced thru that. Next funny thing is that the reference must be performed with the subform control name, which might differ from the subform name as viewed in the database window.

Try first altering your query parameters to (using frmYourMainForm as pseudo reference):

[tt]WHERE ((([ItemPrices]![ItemNumber])=[Forms]![frmYourMainForm]![OrderDetailsForm].Form![ItemNumber]) AND (([ItemPrices]![Quantity])=[Forms]![frmYourMainForm]![OrderDetailsForm].Form![Quantity]));[/tt]

If that doesn't work, then use the expression builder from the criteria row in the query. Have the form open. Doubleclick thru forms, loaded forms, mainform, subform and the control on the sub form - that should give the correct reference, and work;-)

Roy-Vidar

 

[VBAjedi]

Fascinating!

I'm on my way out the door, and don't have time to test that. However, the information alone is well worth a star. I will test tomorrow morning and post back - whether it works or not. . .

VBAjedi

 

[VBAjedi]

Sweet - I had to use the expression builder, but it did work. MANY thanks! It would have taken me forEVER to figure that out on my own, and this project is extremely urgent. As in, there's a really nice lady in tears because she's buried under a mountain of work and about to quit her job if I can't find her a better/faster way for her to process orders.

I awarded another star but not sure it will take since I already gave you one.



VBAjedi