conditional format based on subform combo

[Lesg]

Hi Guys,

I have an order processing form, with a combo box to select items for the order, I also have another form (frmlastXweeks) that shows ordered items [items] over the past X weeks.

Currently I am trying to format the past order items, [frmLastXweeks.items] to show in green when the item is added in the order processing form combo.

The combo box is bound to productID and the frmLastXweeks has the a field productID so been trying : Expression is: Forms![frmOrderPlacement]![frmOrderItems].Form![cboProducts]="txtproductID" = [productID]

I am sure that syntax is not correct, so looking for for guidance on this, plus I need to reload frmLastXweeks each time the combox has an onchange event, so thinking something like: me.frmLastXweeks, requery again not sure of the syntax for that, can you advise suggest way to achieve this please?

The goal is to highlight items in the order form on the past orders to show in green and prompt users to check for important items not highlighted and confirm they are not required.



Thanks

[June7]

So this is a form/subform arrangement? frmLastXweeks is main form and frmOrderItems is subform? That CF expression doesn't make sense to me. Exactly what is the condition to determine highlight? I have never used OnChange event, I use AfterUpdate. What code do you have behind combobox?

What determines a 'past' order, aren't all orders in the past?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Lesg]

Hey, thanks for the response. to answer your questions.

So the form is an order main form, (frmOrderPlacement) with subform (frmOrderItems) being the line items of the order, at the same time frmLastXweek opens and is a form to show the top sold items over the past 13 weeks. The clients wants operators to have this form showing so they can upsell products, and when one of the products in this list is added as a line item in the current order, they want the item to change to green, so the operator has visual representation of items ordered, and can then upsell items that are not highlighted. hope that make sense.

In terms event trigger, after update would be fine, the onchange already has a number of tasks assigned for product selection anyway. The key thing is a trigger to create a refresh of the form to trigger the condition change.

Thanks
Les

[June7]

So you want textbox in frmLastXweek to highlight if item is in the frmOrderItems current dataset?

That's a new one on me. My first thought is this could use DCount() in Conditional Format expression. So just did a quick test and works. You would use something like:

Expression is: DCount("*", "tblOrderItems", "OrderID=" & Forms!frmOrderPlacement.tbxOrderID & " AND ProductID=" & [ProductID) > 0

Then VBA to Refresh frmLastXweek in product combobox AfterUpdate event.

[Lesg]

Hey, thanks for that, works perfectly, I just need to now refresh frmlastXweeks each time a line item is added to show the update, can you suggest the event syntax to requery the frmlastXweeks, I have tried in the cboProduct combo afterupdate event : DoCmd.Requery [frmLastXweeks]

Thanks

[June7]

Forms!frmLastXweeks.Requery

or

Forms!frmLastXweeks.Refresh

[Lesg]

cheer thanks for that all working now.