format decimal places in a continuous form

[GKIL67]

I have a continuous form with a currency field (price) that I need to format it
to 4 decimal places if it is less than 1 and to 2 decimal places if it is greater than 1.
How difficult can this be?

iif(int([me.price])=0,Format([me.price],"#,##0.0000€"),Format([me.price],"#,##0.00€"))

However, the format stays as I want it ONLY in the current record.
All the other records inherit the current record's format, doesn't look good!

I can't figure a way to fix it. Can it be done?
Can anybody with enough experience, kindly asking, point me to the right direction?

Thank you!!

 

[NickHa]

On a continuous form, you are essentially showing the same form multiple times, so the behaviour you see is "works as designed".
The only solution I can think of is to have a text field which you can format as you need before the display (in other words, an additional field in your data set puerly for individual record formatting).
Maybe someone else can provide a better solution.

 

[GKIL67]

Well... there are actually 4 price fields for each record, I don't even want to think to double those fields up. It's amazing... I've browsed like 20 pages of threads, but no luck..!
There must be a smart way to do it, via VBA or something...

 

[saykul]

Look at Conditional Formatting on the ribbon.
Sakir Aykul

 

[GKIL67]

saykul, what do you mean? How can Conditional formatting apply to decimals..?!

 

[Galaxiom]

Derive a field in the RecordSource query using your formula and bind the textbox to that field.

 

[GKIL67]

Thank you Galaxiom, it was suggested before by NickHa... but even then, how would be possible to edit the field and change the price? Gee...

 

[Galaxiom]

Typically, Continuous Forms with this kind of requirement would only be used to display data. A pop up Single Form is used to edit the current record.

 

[GKIL67]

Can't argue on it! However there are 2 text fields and 4 prices to process 20-30 records at a time (products). Seems much easier to my friend - the user - to deal with continuous forms (like an excel sheet) than poping up another form... So, we did it.
And now I'm looking for a smart way to get around the "issue"... wondering if there is such a way!

 

[Galaxiom]

There is one way but it is not simple.

Popup a small edit form right on top of the active control using the click event of the control. This form has all the borders etc removed and contains just a single textbox which appears to be the control it is covering.

The difficult part would be getting it to pop up in exactly the right place. The position of the window, form and control as well as the current record must all be accounted for and then the MoveSize Method used to place the popup. Open the form invisible, move it and then make it visible to prevent the distracting jump as it moves from centre screen to the designated location.

 

[GKIL67]

Galaxiom, it is simple, but... laborious... to do it just for the heck of it
I mean, I would not mind the code... but I do not see much chiming, so I guess the design sucks... who knows, maybe later on something more efficient will pop up!
Thank you!

 

[Galaxiom]

I think you will find there is no other way around it. Similar questions about the limitations of Continuous Forms have been asked here before and no better technique has been revealed.

The most common variant is the desire to define a per-record RowSource for combos and listboxes in situations with cascading combos or to support continued display of since deprecated choices on old records.

If you do ending up coding this solution I would suggest you might consider generalising the solution to cover this requirement too or at least keep it in mind while you are designing the module.

 

[GKIL67]

Sure, but I would much better go back and design a single form or implement a way to group/filter records of similar pricing than displaying, hiding and aligning boxes...
Much obliged for your contribution!